Clone MySQL database
MysqlDatabaseMysqldumpMysql Problem Overview
I have database on a server with 120 tables.
I want to clone the whole database with a new db name and the copied data.
Is there an efficient way to do this?
Mysql Solutions
Solution 1 - Mysql
$ mysqldump yourFirstDatabase -u user -ppassword > yourDatabase.sql
$ mysql yourSecondDatabase -u user -ppassword < yourDatabase.sql
Solution 2 - Mysql
mysqldump -u <user> --password=<password> <DATABASE_NAME> | mysql -u <user> --password=<password> -h <hostname> <DATABASE_NAME_NEW>
Solution 3 - Mysql
Like accepted answer but without .sql files:
mysqldump sourcedb -u <USERNAME> -p<PASS> | mysql destdb -u <USERNAME> -p<PASS>
Solution 4 - Mysql
In case you use phpMyAdmin
- Select the database you wish to copy (by clicking on the database from the phpMyAdmin home screen).
- Once inside the database, select the Operations tab.
- Scroll down to the section where it says "Copy database to:"
- Type in the name of the new database.
- Select "structure and data" to copy everything. Alternately, you can select "Structure only" if you want the columns but not the data.
- Check the box "CREATE DATABASE before copying" to create a new database.
- Check the box "Add AUTO_INCREMENT value."
- Click on the Go button to proceed.
Solution 5 - Mysql
There is mysqldbcopy tool from the MySQL Utilities package. http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcopy.html
Solution 6 - Mysql
If you want to make sure it is an exact clone, the receiving database needs to be entirely cleared / dropped. This way, the new db only has the tables in your import file and nothing else. Otherwise, your receiving database could retain tables that weren't specified in your import file.
ex from prior answers:
DB1 == tableA, tableB
DB2 == tableB, tableC
DB1 imported to -> DB2
DB2 == tableA, tableB, tableC //true clone should not contain tableC
the change is easy with --databases
and --add-drop-database
(see mysql docs). This adds the drop statement to the sqldump so your new database will be an exact replica:
$ mysqldump -h $ip -u $user -p$pass --databases $dbname --add-drop-database > $file.sql
$ mysql -h $ip $dbname -u $user -p$pass < $file.sql
of course replace the $ variables and as always, no space between password and -p. For extra security, strip the -p$pass from your command
Solution 7 - Mysql
$newdb = (date('Y')-1);
$mysqli->query("DROP DATABASE `".$newdb."`;");
$mysqli->query("CREATE DATABASE `".$newdb."`;");
$query = "
SELECT
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'rds'
";
$result = $mysqli->query($query)->fetch_all(MYSQLI_ASSOC);
foreach($result as $val) {
echo $val['TABLE_NAME'].PHP_EOL;
$mysqli->query("CREATE TABLE `".$newdb."`.`".$val['TABLE_NAME']."` LIKE rds.`".$val['TABLE_NAME']."`");
$mysqli->query("INSERT `".$newdb."`.`".$val['TABLE_NAME']."` SELECT * FROM rds.`".$val['TABLE_NAME']."`");
}