Skip to content
Advertisement

Duplicate MySQL Database in Linux CLI without Exporting an SQL File

I have a MySQL database which I want to duplicate using the Ubuntu Linux CLI without first having to download a MySQL file. I tried the following command:

mysql -uroot -e’mysqldump -uroot db_old | mysql -uroot backup db_new;’

But got this error: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use.

What am I doing wrong? What I’m looking for is for db_new to be created containing the same data as db_old (in other words, copy db_old and name the new database db_new, all in one command without needing to export the data to a file).

Advertisement

Answer

First, create the new database

mysql -uroot -pyourpasswd -e "Create database db_new;"

Then run the following (you don’t need to execute “-e”)

mysqldump -uroot -pyourpasswd -n db_old | mysql -uroot -pyourpasswd db_new;

From the man page:

mysqldump is also very useful for populating databases by copying data from one MySQL server to another:

   shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

The “-n” option is short for “–no-create-db”

If you don’t need a password for your root connection (not recommended), then remove the “-pyourpasswd” from all statements

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement