MySQL Database migration
Hi ,
I Need to migrate a MySQL Database of size 300 GB and its live environment only has 3 hours downtime to perform the task. Please advise me which backup and restore method will help me to do this activity effectively.
I have two methods in hand.
1. mysqldump backup of full Database.
2. Cold backup of mysql database directory to New server.
Database lies in a redhat cluster. Please advise me for any other effecient way to perform this activity?
Regards,
naresh
-
Official comment
Hi,
It is really hard to tell what is the best method for you because it depends on, like, everything - data structure, hardware, network speed.
From the options you mentioned, cold backup (rsync, for example) is likely to be faster - it's limited by I/O performance of your hardware and network speed between the hosts. Mysqldump tends to be much slower and it's rather unlikely to complete dump and load of 300GB data within 3h. Actually, this is something easy to test if you have mysqldump backup already available - just try to restore it and count time.
What I'd suggest to do is something different - use xtrabackup instead. It's online hot backup solution which allows you to take a physical backup online, without stopping the backup host. You can easily use it to create a new slave, setup a replication between current and new host, and finally, take a short downtime to stop all traffic and redirect it to new host.
If you use ClusterControl, there's an option to create a slave using xtrabackup - just point&click and CC does it for you. Your task will be only to perform a switchover (stop old host and redirect all traffic to new host). Even this can be automated if you use ClusterControl to deploy proxies like HAProxy or MaxScale. If you route traffic through such proxy, ClusterControl will be able to perform a switchover from UI and proxies will react on such change in replication topology.
Thanks,
KrzysztofComment actions -
Thanks for the help and the information will be really useful for me. I am new in maintaning mysql database and have some doubts.
if I use mysqldump backup to take full database in my live environment when there is some application load. Will it cause any issue or lock in the database?
mysqldump -u root -p --all-databases --single-transaction > /backup_mysql/all_db_backup.sql
Regards,
Naresh
-
Hi,
Sorry for the delay but somehow I wasn't notified about your reply. Using mysqldump in the way you described will not lock your database but it's useful only as long as you use InnoDB tables. Otherwise your backup is not consistent. Of course, it adds some sort of load on the system so it may affect your operations anyway.
Thanks,
Krzysztof
Please sign in to leave a comment.
Comments
3 comments