0

I want to migrate a MySQL database to another MySQL database (different server and only terminal access). I believe I can do a dump on it, but then I won't have the latest data. How do I make sure that I have the latest data if I am not allowed to have downtime? Is it possible?

Please excuse my ignorance if any (newbie in dba). I have read all the suggestions and duplicate questions before creating this new one. But none of them seems to be the one I am looking for.

1
  • If you have not already set up replication or LVM, you will incur downtime. – Rick James Mar 23 '20 at 18:37
0

In general I don't think it's possible to move a database without any downtime with MySQL standard tools.

However you can try to use mysqldump and mysqlbinlog to minimize "data loss": see section "Example: mysqldump + mysqlbinlog for Backup and Restore" in https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog-backup.html

0

The easiest way is to setup master-master replication (tons of docs available)

Initial replica setup can be made by mysqldump with --master-data option. I hope you have used InnoDB engine for all the tables so dump will use a consistent snapshot with no locking.

Then you'll get the pair of syncronized servers that propagates writes to each other.

If you have used MariaDB fork instead of MySQL then you have also an option to use Galera cluster functionality that acts exactly like M-M replication but has a lot of advantages including self-initialization of the new-added nodes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.