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