Category Archives: Breaking News

How to easily convert a single database or all databases from MyISAM storage engine to InnoDB storage engine

InnoDB is a more robust engine and a better database table type for modern applications.  Using InnoDB will increase your sites performance, speed, and they will have better chances to survive most database hiccups that can occur

The steps below will allow you to update “Individual” or “ALL” existing database installations using the MyISAM storage engine to the InnoDB storage engine.

Requirements

You’ll need the following in order to convert your databases:

1. The current version of phpMyAdmin or an SSH client

2. A database version that supports full text indexes for InnoDB table types

I will show you three different processes to convert your database(s).

Processes Used Below:

1. Using phpMyAdmin to convert a single database from using MyISAM engine to InnoDB engine

2. Using phpMyAdmin to convert all databases from using MyISAM engine to InnoDB engine

3. “easiest” Using SSH to convert all databases from using MyISAM engine to InnoDB engine

Single Database Conversion From MyISAM to InnoDB using phpMyAdmin:

1. Backup Your Database “Just in case”

2. Launch phpMyAdmin. You must explicitly click on the name of your database in the top of the left column. There are sometimes multiple databases, so make sure you are clicking the correct database. “Do not click on the one labelled information_schema.”

3. Click the Query tab

4. In the query box, paste the following code replacing “your-db-name” with your actual DB name and press the submit query button, this will pull up a list of tables that need to alter to InnoDB.

SELECT CONCAT(‘ALTER TABLE ‘,TABLE_NAME,’ ENGINE=InnoDB;’)

FROM INFORMATION_SCHEMA.TABLES

WHERE ENGINE=’MyISAM’

AND table_schema = ‘your-db-name’;

5. Select dropdown to show 500 rows and then Click the “+Options” link above the results, choose the “Full texts” option, and press the “Go” button in the shaded area at the lower right

6. Select the “Show all” checkbox

7. Copy all the queries using the “Copy to clipboard” link in the “Query results operations” box below the results

8. Paste the result into a text editor to see the full output and copy all the lines STARTING with “ALTER TABLE” to your clipboard, they will look like the example below

example:

ALTER TABLE wp_commentmeta ENGINE=InnoDB;          

ALTER TABLE wp_comments ENGINE=InnoDB;   

ALTER TABLE wp_links ENGINE=InnoDB;

9. Click the SQL tab above the results, make sure you are still in the database you were working with

10. Paste the ALTER TABLE statements into the textarea and press the “Go” button in the lower right shaded area, there will be a pause, depending on how many tables are getting converted, it can take a few minutes to complete

To confirm your database was successfully converted, click your database name in the left column, and all table types should now say InnoDB.

All Database Conversion From MyISAM to InnoDB using phpMyAdmin:

1. Backup Your Database(s) “Just in case”

2. Launch phpMyAdmin. You will see all your databases on the left, you are in the main phpMyAdmin section.  Instead of seeing a query tab, you will see an SQL tab.

3. Click the SQL tab

4. In the SQL box, paste the following code and press the “Go” button in the lower right, this will pull up a list of all the databases and their tables that need to be altered to InnoDB.

SELECT CONCAT(‘ALTER TABLE ‘, TABLE_SCHEMA,’.’,TABLE_NAME, ‘ ENGINE = InnoDB;’)

FROM information_schema.TABLES

WHERE ENGINE=’MyISAM’

AND TABLE_SCHEMA NOT IN(‘mysql’,’information_schema’,’performance_schema’)

5. Select dropdown to show 500 rows and then Click the “+Options” link above the results, choose the “Full texts” option, and press the “Go” button in the shaded area at the lower right

**IMPORTANT** Due to the number of databases and tables, you will have to repeat steps 1-10 till step 4 produces 0 results other than the mysql database results as they will not be converted due to permissions.

6. Select the “Show all” checkbox

7. Copy all the output results which will look similar to the results produced using the above single database query output but have the database.table instead of just the table

8. Paste the result into a text editor to see the full output and copy all the lines STARTING with “ALTER TABLE” to your clipboard, they will look like the example below.

In this example, since you are doing an SQL query on all your databases, the ALTER TABLE strings will look slightly different

ALTER TABLE user_database.wp_commentmeta ENGINE=InnoDB;            

ALTER TABLE user_database.wp_comments ENGINE=InnoDB;    

ALTER TABLE user_database.wp_links ENGINE=InnoDB;

If in your text editor you notice the output has spaces between some query’s, remove the spaces

ALTER TABLE user_database.wp_commentmeta ENGINE=InnoDB;            

ALTER TABLE user_database.wp_comments ENGINE=InnoDB;

ALTER TABLE user_database.wp_links ENGINE=InnoDB;

Should look like

ALTER TABLE user_database.wp_commentmeta ENGINE=InnoDB;            

ALTER TABLE user_database.wp_comments ENGINE=InnoDB;    

ALTER TABLE user_database.wp_links ENGINE=InnoDB;

9. Again, Click the SQL tab above the results

10. Paste the ALTER TABLE statements into the textarea and press the “Go” button in the lower right shaded area, there will be a pause, depending on how many databases and tables are getting converted, it can take a few minutes to complete

To confirm all your databases were successfully converted other than the mysql database, click eack of your database names in the left column, and all table types should now say InnoDB except for some tables in the mysql database.

All Database Conversion From MyISAM to InnoDB using an SSH client:

1. LOGIN TO ROOT SERVER USING YOUR SSH CLIENT, I USE PUTTY

user: root

Root Password: password

[root@server ~]#

“You will need your MySQL password for step #3” “If you know your MySQL password, continue to step #3, if not, go to next step

On servers running cPanel, the root MySQL credentials are stored locally in a file that only the root user can access. You can open the file using an explorer client like WinSCP or by connecting to root via SSH like in step #1.

By running the command in #2 “cat /root/.my.cnf”, you can view the MySQL credentials

2. [root@server1 ~]# cat /root/.my.cnf

That should output a result containing the MySQL password, which will appear similar to the following:

[root@server1 ~]# cat /root/.my.cnf

[client]

user=root

password=”password123456789″

Make a note of the current password that shows between the quotations on your output

3. LOGIN TO MYSQL

[root@server1 ~]# mysql -u root -p

Enter password: “enter the password you received from step #2”

4. at the command line: mysql>

you will need to enter: SELECT CONCAT(‘ALTER TABLE ‘,TABLE_NAME,’ ENGINE=InnoDB;’)FROM INFORMATION_SCHEMA.TABLES;

example:

mysql> SELECT CONCAT(‘ALTER TABLE ‘,TABLE_NAME,’ ENGINE=InnoDB;’)FROM INFORMATION_SCHEMA.TABLES;

5. Press enter and all done if executed properly

This query will produce output similar to what’s below. Note this is just an example which will show output for all the databases and tables.

+——————————————————————————-+

| CONCAT(‘ALTER TABLE ‘,TABLE_NAME,’ ENGINE=InnoDB;’)

+——————————————————————————-+

| alter table CHARACTER_SETS engine=INNODB;

| alter table COLLATIONS engine=INNODB;

| alter table COLUMN_PRIVILEGES engine=INNODB;

| alter table ENGINES engine=INNODB;

| alter table EVENTS engine=INNODB;

| alter table COLLATION_CHARACTER_SET_APPLICABILITY engine=INNODB;

| alter table COLUMNS engine=INNODB;

| alter table FILES engine=INNODB;

| alter table GLOBAL_STATUS engine=INNODB;

| alter table GLOBAL_VARIABLES engine=INNODB;

| alter table KEY_COLUMN_USAGE engine=INNODB;

| alter table OPTIMIZER_TRACE engine=INNODB;

| alter table PARAMETERS engine=INNODB;

**NOTE** If you want to change engines of a specific database instead of all databases then execute the below query.

mysql> SELECT CONCAT(‘ALTER TABLE ‘,TABLE_NAME,’ ENGINE=InnoDB;’)FROM INFORMATION_SCHEMA.TABLES; WHERE TABLE_SCHEMA=’database name’;

Press enter and all done if executed properly

I know this tutorial seems repetitive in some areas depending on your skill level. The tutorial was written to help even the unskilled succeed in the conversion.

If something was missed or you see an in accuracy, please let me know in the comments below. I wrote this tutorial while actually doing each step to ensure accuracy but could have a mistype.

Written by: Joe Lovrek

Dropbox users report spam emails amidst fears of data breach


Dropbox users are reporting receiving spam messages through the unique email addresses associated with their accounts on the file hosting service. The issue is a particular cause for concern given Dropbox’s high-profile security breach last year.

The issue was first raised by user Forrest F, who asked in Dropbox’s support forum why the company had leaked or given out his email address. 

“I have an internal to my company email address that I used for Dropbox only and I am getting the same fake Paypal scam emails. This has been happening since about Monday,” explained another user.


Since then, more users have stepped forward claiming that they were also receiving spam emails in the dedicated email addresses they created for their Dropbox subscriptions.

The initial response from Dropbox is that this might be yet more fallout from the 2012 security breach and that it is investigating the matter. The company said it doesn’t believe the spam emails are the result of a new data breach, but said it “remains vigilant given the recent wave of security incidents at other tech companies.”

Why IT security pros can be scarier than the ‘bad guys’


I thought I harbored a healthy amount of paranoia before I went to this week’s RSA Conference for IT security professionals in San Francisco. But now I’m just plain scared—and not about hackers and phishers, the perennial bogeymen of the Internet underground.

No, the people who scare me even more are the security professionals who work for big business. They want my online data, your online data, everyone’s online data. And they want it more than even the bad guys who make headlines.

Big business isn’t evil incarnate, and the companies clamoring for our data aren’t the agents of destruction who would steal our identities for profit or erase our family photos just for kicks. But to the business leaders at e-commerce sites, social networks, and even banks, online privacy is something that must be managed at best, and mitigated at worse.

It’s an annoyance that must be dealt with. It’s something that gets in their way.

They want our data so they can track us, categorize us, and use what they know about us to sell us something—or sell what they know about us to someone else. Or, as Trevor Hughes, the President and CEO of the International Association for Privacy Professionals (IAPP), told me directly, “Your data is the currency of the information economy.”

And our online activity is minting more money all the time.

Our data is hard currency

It took just one shocking hour at the RSA conference to destroy every naive hope I might have had about online privacy. Hughes spoke to a large audience of IT professionals tasked with managing customer and user data, and named what he considered to be the hot-button privacy issues of the year: location data, facial recognition, and Do Not Track, among others. He also touched on more sweeping topics like federal regulations and public policy.

IAPP
“Your data is the currency of the new information economy,” said Trevor Hughes, of the IAPP.

I was intensely interested in all of these issues as an active, web-surfing individual, but I also quickly realized that the other attendees in the room looked at these issues from the other side—from the perspective of their companies, which gather customer data and use it for business opportunity.

Their job is not to worry about protecting our privacy, but to worry about navigating privacy regulations, and protecting themselves from lawsuits and fines. One thorny example Hughes cited was the mobile privacy guidelines paper released by the California Attorney General’s office earlier this year, to supplement the California Online Privacy Protection Act (COPPA). In a message accompanying the guidelines, Attorney General Kamala Harris encouraged mobile app developers to adopt a “‘surprise minimization’ approach…to alert users and give them control over data practices that are not related to an app’s basic functionality or that involve sensitive information.” Easier said than done on the small screens of mobile platforms, said Hughes: “That user interface is incredibly limited.”

Your location, your activity, your face: all fair game

Hughes also delved into issues surrounding “contextualization”—using your online data to customize “content” (read: advertisements) to your browsing habits and personal demographics. Obviously, contextualization is already a widespread (and profitable) business tool, as anyone who’s experienced targeted ads on Google already knows.

The data set used for contextualization is diving ever deeper, though. “Context will put the debate on targeted ads on steroids,” Hughes told the crowd. “Not only are we going to have the sensitivity of where you’ve been online, but where you are in the world, and what you are doing and thinking.”

Oh, but it gets better. Facial recognition, anyone? You can tell your friends not to tag you in their photos all you want, but that’s small potatoes.

“We will see the anonymity of crowds dissipate,” Hughes said, predicting that photos taken by other people, or by cameras installed in public places, will be used to find you wherever you are. Remember the Where’s Waldo? children’s books, where you had to find Waldo among huge crowds in famous places around the world? Who knew that the happy, wool-capped Waldo would be the harbinger of privacy problems to come.

Do not track me… please?

When the Obama Administration introduced its Consumer Privacy Bill of Rights in February, 2012, the bill cited “privacy-enhancing technologies such as the ‘Do Not Track’ mechanism” as safeguards against many of the tactics that Hughes’ audiences members would like to preserve. Choose not to be tracked, and web sites wouldn’t be able to collect information about you. It’s the ultimate protection, right? No, think again.

“Do Not Track is a very, very complicated and challenging issue,” Hughes said. Indeed, there’s no standard implementation for data tracking from browser to browser, and that’s an inconvenient truth for anyone who would need to implement Federal policy (which hasn’t yet been passed). But for Hughes, the real problem for privacy professionals is, “how do you switch it off or maintain it switched-off.”

Yes, you heard right: Do Not Track would be just another hoop that big business needs to jump through—or circumvent entirely.

Unfortunately, for now, businesses that want to track our data don’t even have to worry about the technical vagaries of Do Not Track. “None of this has the force of law yet,” said Hughes. “Without the ability of regulators to enforce, we may not have any enforcement at all. Do Not Track may not have any consequences.”

You can see where this is heading. And Hughes confirmed as much: “Some organizations have come out and said they will ignore Do Not Track.”

Giving away your online data—willingly

Unless you’re some sort of virtual exhibitionist who actually wants to sacrifice online privacy for fun and profit, data tracking should scare you. But it’s also important to remember that the basic operating principles of our open Internet—an Internet where very expensive content is given away for free—require a certain amount of data sacrifice.

Indeed, if you want all the complex, nuanced benefits of social sharing, you have to actually share yourself. And you’re probably already doing this, sacrificing your data quite willingly.

Ted Schlein, of venture capital firm Kleiner Perkins Caufield Byers, brought up this paradox while speaking at a cybersecurity session at RSA. “People kind of care about privacy, and then they don’t,” he said. “Facebook has a conversation about a new privacy policy, people get excited about it, and then Zuckerberg says something, and they calm down.”

Oez/Shutterstock

He’s right, of course. Periodic privacy imbroglios haven’t slowed the popularity of social networking sites, photo-sharing sites, and apps like Foursquare, even though all of these services gather information about us in order to grow revenue. Pinterest was recently valued at $2.5 billion—not because it’s making any money, but because its users are enthusiastically pinning products to their pages, making them ripe for retail sales pitches. Their data is the currency.

Big business is working over-time to collect data about us, and the more time we spend online, the more opportunities we give them to do so. So in the end, I wonder whether it’s scarier that businesses are collecting our data, or that we’re so willingly letting them do it.