Michael Samuel Michael Samuel - 6 months ago 15
MySQL Question

Migration of MYSQL database without losing records

I'm migrating a MYSQL DB from one host to another so I run the following command to backup the DB from the old hosting:

mysqldump -u **** -p **** | gzip > /home/***/***.sql.gz


And then use the following command to import the DB to the new host:

zcat /home/***/***.sql.gz | mysql -u *** -p ***


After successfully importing the DB, I point the domain to the new DNS.
The problem is that the website is active so new records are very likely to get inserted after the last backup. So, I may need to run the command once again after full DNS propagation.

So, my question, does the
mysql
command insert the new rows and update the existing ones or does it actually totally drop the tables and start over with the backup? If that happens, the records that have been inserted after DNS propagation might get lost!

Thanks

Answer

If you look at the output of mysqldump (before you gzip it) you will see that it contains a sequence of

DROP TABLE x;
CREATE TABLE x (...);
INSERT INTO x (...) VALUES (...);

So, no, it does not do an insert / replace, it drops and recreates the tables.