0

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).

GTS Joe
  • 3,612
  • 12
  • 52
  • 94

1 Answers1

1

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

olkid
  • 86
  • 4
  • Hi, I ran the command without execute and it didn't work. I just received a long list of options for the mysql command. I looked at my list of databases and it wasn't duplicated. – GTS Joe Nov 25 '15 at 05:54
  • some useful information here: http://stackoverflow.com/questions/25794/mysql-copy-duplicate-database – olkid Nov 25 '15 at 19:49
  • Thank you so much for your help, it worked! One small update to your code, though. In the second line, the -n isn't necessary. – GTS Joe Nov 27 '15 at 17:14