I want to copy my production database to my development environment. I copied website_production into mysql of my developemnt environment, so using SHOW DATABASES; now I have
website_development website_production
as databases. How can I copy and overwrite all tables with their content from website_production into website_developemnt using mysql commands?
It's not MySQL commands but it's the easiest method, from command line:
$ mysqldump -u user --password=pass live_db_name | mysql -u user --password=pass -h localhost duplicate_db_name
Works on Windows terminal too.
Glitch answer is usually good, but stored procedures and functions will not be in backup, therefore not copied to second database.
This is why I always do (--routines can be used instead of -R in command below):
$ mysqldump ${source_db} -R | mysql ${dest_db}
In fact, as I make a regular dump for backup purposes, I prefer to use the backup dump itself, therefore keeping it in a file:
mysqldump ${source_db} -R > ${source_db}.sql
mysql ${dest_db} < ${source_db}.sql
Note: I always avoid -u and -p parameters, for security reasons.
This solution I use for development environment Wordpress installs. In my case wp_options is the data I am to overwrite using values from wp_options_apache. The Example #1 query only overwrites 1 value per row updated on the table, but one could easily overwrite multiple columns by separating the assignments with commas such as 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;
