Search code examples
mysqldatabase-migration

MySQL Database migration to new server

We have a common database in MySQL 5.6 and many services are using that. one of the services want to migrate some tables from common database to new MySQL server 5.7. The old MySQL server continuously using by another service. The total data size is around 400GB. Is there any recommended procedure?

Solution

  • Two Approaches

    Approach: 1

    1. create a slave with mysql version 5.7 and replicate only the common database with the below option replicate-db
    2. At the point of no feeds happening on master, and no lag in slave. Use this as a new server, by stopping the slave and disconnect the master from slave.

    On slave:

    1. STOP SLAVE
    2. To use RESET SLAVE, the slave replication threads must be stopped $> RESET SLAVE

    On Master:

    1. Remove the replication user
    2. FLUSH LOGS

    Approach:2

    Try the backup method

    Since the db size is 400 GB, the mysqldump won't be sufficient.

    Try partial backup method using xtrabackup: xtrabackup --backup --tables-file=/tmp/tables.txt

    Once the Backup has been completed, verify and restore it into the new server version 5.7.

    Reference:

    https://www.percona.com/doc/percona-xtrabackup/2.4/xtrabackup_bin/xbk_option_reference.html#cmdoption-xtrabackup-tables-file

    np: On both approaches, make sure to check the table/mysql version compatibility [5.6 vs 5.7]