Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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
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.
