Category Archives: Programming

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

WP Facebook Auto Publish Powered By : XYZScripts.com
Bunk Beds