61

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?

Peanut
  • 3,753
  • 3
  • 31
  • 45
Vinod HC
  • 1,557
  • 5
  • 20
  • 38
  • 1
    This was super helpful for me: https://stackoverflow.com/questions/675289/cloning-a-mysql-database-on-the-same-mysql-instance#comment79088681_675299 – Ryan Sep 05 '17 at 21:59

8 Answers8

115
$ mysqldump yourFirstDatabase -u user -ppassword > yourDatabase.sql
$ mysql yourSecondDatabase -u user -ppassword < yourDatabase.sql
  • 1
    This is really nice and easy. Thank you! – MartyIX Jun 23 '12 at 08:13
  • 11
    Probably obvious to most, but I missed it the first few times I tried it. This should be done from the regular command line, not from the mysql prompt. Also, you might want to add " -uroot" or whatever your user's name is, right after the "mysqldump" (1st line) and after the "mysql" (2nd line). – charliepark Aug 28 '12 at 17:57
  • I'd vote this answer up, if it also included the user/password command line option – superjos Oct 08 '12 at 10:36
  • 3
    I guess `-ppassword` is the same as `--password=` (I don't know that command options)... so here's my vote ;) – superjos Oct 09 '12 at 15:25
  • 29
    It should be noted that this does not create the new Database. You first need to create the new Database (in this example 'yourSecondDatabase' ) and then run the above commands. – Kevin Bradshaw Jan 02 '13 at 10:31
  • I feel like this is a missing feature in Mysql. It would come in handy on so many occasions. – Captain Hypertext Dec 15 '16 at 04:21
  • @charliepark thank you for your comment. I did not realize this was NOT at a mysql prompt. Now that I know that, user647772's answer is working for me... And with the addition of kevin-bradshaw's comment. – HPWD Oct 26 '17 at 15:41
  • `mysqldump` will skip stored procedures by default. This may not be what you expect. Use `mysqldump --routines` if you want to include stored procedures in your clone. – Mark Roberts Sep 21 '20 at 11:16
30
mysqldump -u <user> --password=<password> <DATABASE_NAME> | mysql -u <user> --password=<password> -h <hostname> <DATABASE_NAME_NEW>
Kai Sternad
  • 22,214
  • 7
  • 47
  • 42
  • 1
    Is this as fast as using mysqldump and mysqlimport as per these instructions? http://dev.mysql.com/doc/refman/5.5/en/copying-databases.html – Adam Nelson Apr 05 '11 at 16:00
  • For 5.6 now at: https://dev.mysql.com/doc/refman/5.6/en/mysqldump-copying-database.html – Felix Apr 05 '17 at 14:45
23

Like accepted answer but without .sql files:

mysqldump sourcedb -u <USERNAME> -p<PASS> | mysql destdb -u <USERNAME> -p<PASS>
user570605
  • 738
  • 7
  • 9
7

In case you use phpMyAdmin

  1. Select the database you wish to copy (by clicking on the database from the phpMyAdmin home screen).
  2. Once inside the database, select the Operations tab.
  3. Scroll down to the section where it says "Copy database to:"
  4. Type in the name of the new database.
  5. Select "structure and data" to copy everything. Alternately, you can select "Structure only" if you want the columns but not the data.
  6. Check the box "CREATE DATABASE before copying" to create a new database.
  7. Check the box "Add AUTO_INCREMENT value."
  8. Click on the Go button to proceed.
akos
  • 137
  • 1
  • 3
  • 7
4

There is mysqldbcopy tool from the MySQL Utilities package. http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcopy.html

Alexey F
  • 1,763
  • 14
  • 19
  • 1
    I tried to use the mysqldbcopy utility, but got all sorts of issues: required lots of privileges (like SUPER privileges at some point!), did not manage to copy the foreign keys, etc. In the end I fell back to the mysqldump solution - which is much simpler and better. – mortensi Jan 01 '15 at 12:57
  • 1
    This is by far (at least for me) the fastest way to clone a database of almost 4GB. I am impressed!!! – John Anderton Nov 04 '21 at 09:02
  • the link and the tool was removed someday. – Moshe L May 24 '23 at 08:35
0

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

bristweb
  • 948
  • 14
  • 14
-2
$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']."`");
}
vse.fm team
  • 1
-2

$ mysqldump yourFirstDatabase -u user -ppassword --routines > yourDatabase.sql

$ mysql yourSecondDatabase -u user -ppassword < yourDatabase.sql

-This method will get dump data along with procedures.

Rushi Dalvi
  • 1
  • 4
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 06 '23 at 08:57