Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.5Mb
PDF (A4) - 1.5Mb
EPUB - 366.6Kb
HTML Download (TGZ) - 289.8Kb
HTML Download (Zip) - 301.9Kb


MySQL Utilities 1.5 Manual  /  ...  /  How can you make a copy of a database and change the storage engine?

3.1.3 How can you make a copy of a database and change the storage engine?

Sometimes you may have need to create a copy of a database but want to change the storage engine of all tables to another engine.

For example, if you are migrating your database to InnoDB (a wise choice), you can copy the database to a new database on a new server and change the storage engine to InnoDB for all of the tables. For this, we can use the mysqldbcopy utility.

Objectives

In this example, we want to make a copy of the world database but change the storage engine to InnoDB and rename the database accordingly.

You can cause all tables in the destination databases to use a different storage engine with the --new-storage-engine option.

Example Execution

shell> mysqldbcopy --source=root:root@localhost:3306 \
--destination=root:root@localhost:3307 --new-storage-engine=InnoDB \
world:world_innodb
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database world renamed as world_innodb
# Replacing ENGINE=MyISAM with ENGINE=InnoDB for table `world_innodb`.city.
# Copying TABLE world_innodb.city
# Replacing ENGINE=MyISAM with ENGINE=InnoDB for table `world_innodb`.country.
# Copying TABLE world_innodb.country
# Replacing ENGINE=MyISAM with ENGINE=InnoDB for table `world_innodb`.countrylanguage.
# Copying TABLE world_innodb.countrylanguage
# Copying data for TABLE world_innodb.city
# Copying data for TABLE world_innodb.country
# Copying data for TABLE world_innodb.countrylanguage
#...done.

shell> mysql -uroot -proot -h 127.0.0.1 --port=3307 -e "SHOW DATABASES"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sakila             |
| world              |
| world_innodb       |
+--------------------+

shell> mysql -uroot -p -h 127.0.0.1 --port=3307 -e "SHOW CREATE TABLE world_innodb.countrylanguage\G"
*************************** 1. row ***************************
       Table: countrylanguage
Create Table: CREATE TABLE `countrylanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`),
  KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
        

Discussion

Notice here we created a copy of the database and changed all tables in the destination database to use the InnoDB storage engine with the --new-storage-engine option.

We show proof of the change by displaying the CREATE statement for one of the tables on the destination server.

Notice we also renamed the database by using the old_name:new_name syntax.

Permissions Required

The user must have SELECT privileges for the database(s) on the source server and have CREATE, INSERT, UPDATE on the destination server.

Tips and Tricks

You can exclude specific options by using the --exclude option specifying an SQL pattern expression. For example, to exclude objects that start with xy, use '--exclude=xy%'.

You can use REGEXP patterns in the --exclude option by specifying --regexp in addition to the --exclude option.

Risks

Should the copy fail in the middle, the destination databases may be incomplete or inconsistent. Should this occur, drop the destination database in question, repair the cause of the failure, and restart the copy.

If you are changing the storage engine from InnoDB, you may encounter warnings or errors if the tables contain foreign keys and the new storage engine does not support foreign keys.


User Comments
Sign Up Login You must be logged in to post a comment.