MySQL Database migration

Comments

3 comments

  • Official comment
    Avatar
    Krzysztof Ksiazek

    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,
    Krzysztof

  • Avatar
    Naresh Shankar

     

    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

  • Avatar
    Krzysztof Ksiazek

    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.

Powered by Zendesk