CODEWITHSUNDEEP
mysql
user929062
user929062

Reputation: 817

Copy and overwrite one database into another using mysql

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?

Upvotes: 13

Views: 16485

Answers (3)

Bruno
Bruno

Reputation: 621

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.

Upvotes: 3

OnethingSimple
OnethingSimple

Reputation: 511

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;

Upvotes: 0

Glitch Desire
Glitch Desire

Reputation: 15061

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.

Upvotes: 25

Related Questions