MySQL restore only Incremental backups with innobackupex

Question

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.

Answer 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.

2. 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.

3. 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.

4. 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

5. 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 .

Answer 2

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.