CODEWITHSUNDEEP
mysqldatabase-migration
user3756488
user3756488

Reputation: 233

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?

Upvotes: 0

Views: 348

Answers (1)

s_ramesh
s_ramesh

Reputation: 41

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]

Upvotes: 1

Related Questions