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.
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
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
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
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
The user must have SELECT privileges for the database(s) on the source server and have CREATE, INSERT, UPDATE on the destination server.
You can exclude specific options by using the
specifying an SQL pattern expression. For example, to exclude
objects that start with xy, use '--exclude=xy%'.
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.