1

I am migrating a product to AWS and we have 1.5TB of MySQL database. I want to migrate it to AWS with minimum downtime possible. I am planning to achieve following.

  1. Take one full backup with Percona XtraBackup innobackupex.
  2. Restore it on AWS node.
  3. Then on the day of final migration take an incremental backup and restore it on AWS node.

So my question is, is it possible this way ?

According to my research it is not possible to restore only "incremental" backup. The way Percona described that we need to take full backup then incremental backups and then prepare them and get a one full backup that cane be restored on destination.

1

First lets understand that to take an incremental backup you need to keep full backup on the server where you are taking incremental.

Though you take a full backup on SERVER(A) you may push to AWSSERVER(B). It doesn't matter, but inorder to take an incremental you need to keep the full backup copy as source backup.

UPDATE

Below is the approach for incremental, you will be able to get your answers whether the idea you have is a feasible one or not.

  1. After Xtrabackup Full backup is done.

mkdir -p /backup/

innobackupex --defaults-file=/etc/my.cnf --user=mysql --password=‘****’ /backup/

From here /backup/2017-06-01_18-01-52 full backup set is obtained.

  1. Take incremental backup with full backup.

mkdir -p /backup/INCR

innobackupex --defaults-file=/etc/my.cnf --user=mysql --password=‘*****’ --incremental /backup/INCR --incremental-basedir=/backup/2017-06-01_18-01-52 --slave-info

From here /backup/INCR/2017-06-01_18-10-41/ incremental delta data is obtained.

  1. Merge the backups

innobackupex --defaults-file=/etc/my.cnf --user=mysql --password=‘*****’ --apply-log-only /backup/2017-06-01_18-01-52/ --incremental-dir=/backup/INCR/2017-06-01_18-10-41/

From here /backup/2017-06-01_18-01-52/2017-06-01_18-22-29 Final backup set is merged to parent(FULL BACKUP) and created a sub directory out of it.

  1. Apply logs to Final Backup Set.

innobackupex --defaults-file=/etc/my.cnf --user=mysql --password=‘*****’ --apply-log /backup/2017-06-01_18-01-52/2017-06-01_18-22-29

  1. Now this is your final data set : /backup/2017-06-01_18-01-52/2017-06-01_18-22-29/* , replace data dir to this data set -> Ensure every 1 to 4 steps had no errors and completed OK is found on all outputs. Test the result incremental backup data on a Test DB and Restore it to see if the server starts without any error messages

If your my.cnf is pointed to /mysql/data/

  • service mysqld stop
  • mv /mysql/data/* /tmp/MOVED/
  • cp -r /backup/2017-06-01_18-01-52/2017-06-01_18-22-29/* /mysql/data/
  • chown -R mysql:mysql /mysql/data/
  • chmod -R 775 /mysql/data/
  • service mysqld start
  • Login to mysql

Check error log and do few select queries .

  • Thanks @Mannoj, So it means after restoring one full backup i can't restore subsequent incremental backups individually ? – xs2rashid Jun 1 '17 at 10:23
  • you mean to say, you will restore the first full backup and bring the Mysql up on AWS with full backup set. And then you will start incremental on source and then take that delta files and add it to AWS Mysql (FullBackup). Are you trying to do something like this? – Mannoj Jun 1 '17 at 10:42
  • Exactly, I there any way i can do that ? – xs2rashid Jun 1 '17 at 11:06
  • if you have the binary logs on source, just ship the binlogs over to the AWS server and replay them there, make sure you know where to start from based on your last full backup also make sure you retain the binary logs on source. you're basically executing all transactions that were not in the full backup. – jerichorivera Jun 4 '17 at 23:06
0

You can do in another way also if your environment and business allows.

  1. Take a Percona full backup from source server (A) with master info (binary log position) if binary log is enabled.

  2. Restore the backup on target machine (AWS).

  3. Make target machine as slave of source machine (A).

  4. Once it get synced stop all applications calls for few minutes and make target (B) as real master to accept read and write calls.

  • Thanks, Actually Source machine is not a single instance it is part of Galera cluster. Can i make slaves to a galera cluster node ? – xs2rashid Jun 1 '17 at 10:26
  • And what are you planning for destination AWS ? Is it gona be Galera? Also does Source and Destination have connectivity by any means? – Mannoj Jun 1 '17 at 11:14
  • Yes, Source and destination are connected through VPN tunnel, and AWS will also have Galera Cluster. – xs2rashid Jun 1 '17 at 11:22
  • this works as well, you can setup async replication between two galera nodes in separate locations. make the AWS nodes as passive (read-only) cluster until you decide to promote it as active cluster. – jerichorivera Jun 4 '17 at 23:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.