Moving a database (db1) from one server to the next and there was one table (big_table) that was so large it took longer to import into the destination than our preferred maintenance window so we kept the application communicating with the source database server while the destination continued to import.

Now we want to redo the process to get what has changed in two steps:

1. dump all tables from source except the big_table and make sure we don't overwrite or drop the very large table already in the destination when we import
2. dump the new/updated records from the big_table since the last timestamp and import those delta records into the existing destination db.big_table

For the first step, will this work to preserve db1 and db.big_table in the destination while recreating all other tables?

mysqldump -h host1 db1 --ignore-table=db1.big_table --no-create-db | mysql -h host2 db1

For the second step, if I know there are no updates and only new records in big_table since a known timestamp, will this work do that?

mysqldump -h host1 db1 big_table --no-create-db --no-create-info  --where "timestamp > '${LAST_EXPORTED_TIMESTAMP}'" | mysql -h host2 db1

I am trying to understand if each of these "patch" attempts of changes in source since the first import into destination doesn't try to drop/create db1 or db1.big_table but instead just captures and imports all tables except big_table and only new records in big_table since a known timestamp.

Is there a better way to do this than I've drafted?

CodePudding user response：

mysqldump does not one table (big_table) that was so large;
you can use meb or xtrabackup

CodePudding user response：

A better way to do this is to use MySQL replication.

1. Take a full backup of the source instance. You can use mysqldump, but it's faster to use a physical backup tool like Percona XtraBackup. Make sure you capture the binary log coordinates at the time of the backup (this is enabled by default in XtraBackup).

2. Restore the backup to your new server. Your applications may continue to use the old server while this is going.

3. Configure the new server as a replica of the old one, its source.

4. Start replication and let all changes to all tables replicate to the new server until it is all caught up. This might take minutes to hours, but it doesn't much matter how long it takes, because the applications should still be using the old server while this is syncing up.

5. Take a very brief outage to switch applications to use the new server. If your traffic is very busy, you might want to be extra sure that it is fully caught up before letting applications use it for read-write traffic. But it's likely that this will only take a few seconds.