Geek Answers Handbook

Copy and rewrite one database to another using mysql

I want to copy my production database into my development environment. I copied website_production into mysql of my development environment, so using SHOW DATABASES; now I have

website_development website_production

as databases. How can I copy and overwrite all tables with their contents from production_ site to website_developemnt using mysql commands?

+8
source share
3 answers

This is not MySQL, but it is the simplest command line method:

$ mysqldump -u user --password=pass live_db_name | mysql -u user --password=pass -h localhost duplicate_db_name

It also works on a Windows terminal.

+15
source

This is the solution I use for the Wordpress development environment. In my case, wp_options is data that I have to overwrite using the values ​​from wp_options_apache. Query No. 1 only overwrites 1 value for each row updated in the table, but you can easily overwrite several columns by separating the assignments with commas, for example, in the second example.

Example # 1:

  UPDATE `wp_options` AS a LEFT JOIN `wp_options_apache` AS b ON a.option_name = b.option_name SET a.option_value = b.option_value;

Example # 2:

  UPDATE `old_data` AS o LEFT JOIN `new_data` AS n ON o.prime_key = n.prime_key SET o.username = n.username, o.password = n.password, o.email = n.email;
0
source

The glitch gives the answer, usually a good one, but the stored procedures and functions will not be in the backup, therefore they will not be copied to the second database.

This is why I always do ( --routines can be used instead of -R in the command below):

 $ mysqldump ${source_db} -R | mysql ${dest_db}

In fact, since I am doing a regular dump for backup purposes, I prefer to use the backup dump itself, so I store it in a file:

 mysqldump ${source_db} -R > ${source_db}.sql mysql ${dest_db} < ${source_db}.sql

Note: I always avoid the -u and -p options for security reasons .

0
source

More articles:


All Articles