Documentation
Resource
Rate This Document
Findability
Accuracy
Completeness
Readability
SubmitClose

Performing Incremental Backup and Restoration

  1. Create a backup directory on the primary database.
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    mkdir -p /data/xbackup/full/
mkdir -p /data/xbackup/inc1/
mkdir -p /data/xbackup/inc2/
ll /data/xbackup/
ll /data/xbackup/full/
ll /data/xbackup/inc1/
ll /data/xbackup/inc2/
rm -rf /data/xbackup/full/*
rm -rf /data/xbackup/inc1/*
rm -rf /data/xbackup/inc2/*
  2. Back up the data of the primary database (incremental backup).
    1. Go to the bin directory of XtraBackup.
      • XtraBackup 8.0.13:
        1
        cd /usr/local/xtrabackup-8.0.13/bin/
      • XtraBackup 2.4.20:
        1
        cd /usr/local/xtrabackup-2.4.20/bin/
    2. Create full backup.
      1
      ./xtrabackup --user=root --password=123456 --port=3306 --host=192.168.220.58 --socket=/data/mysql/run/mysql.sock --parallel=16 --backup --target-dir=/data/xbackup/full/
    3. Create the first incremental backup.
      1
      ./xtrabackup --backup --user=root --password=123456 --port=3306 --socket=/data/mysql/run/mysql.sock --host=192.168.220.58 --target-dir=/data/xbackup/inc1/ --incremental-basedir=/data/xbackup/full/
    4. Create the second incremental backup.
      1
      ./xtrabackup --backup --user=root --password=123456 --port=3306 --socket=/data/mysql/run/mysql.sock --host=192.168.220.58 --target-dir=/data/xbackup/inc2/ --incremental-basedir=/data/xbackup/inc1/
    5. Query the LSN in the xtrabackup_checkpoints file in the full backup directory.
      1
      cat /data/xbackup/full/xtrabackup_checkpoints
    6. Query the binlog position in xtrabackup_binlog_info file in the full backup directory.
      1
      cat /data/xbackup/full/xtrabackup_binlog_info
    7. Query the LSN in the xtrabackup_checkpoints file in the incremental backup directory.
      1
2
      cat /data/xbackup/inc1/xtrabackup_checkpoints
cat /data/xbackup/inc2/xtrabackup_checkpoints
    8. Query the binlog position in xtrabackup_binlog_info file in the incremental backup directory.
      1
2
      cat /data/xbackup/inc1/xtrabackup_binlog_info
cat /data/xbackup/inc2/xtrabackup_binlog_info
  3. Query backup data on the primary database.
    1
2
3
4
5
6
7
8
    ll /data/xbackup/
ll /data/xbackup/full/
ll /data/xbackup/inc1/
ll /data/xbackup/inc2/
du -sh /data/xbackup/
du -sh /data/xbackup/full/
du -sh /data/xbackup/inc1/
du -sh /data/xbackup/inc2/
  4. Copy the backup data from the primary database to the secondary databases.
    1. Create a backup directory on the secondary databases.
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
      mkdir -p /data/xbackup/full/
mkdir -p /data/xbackup/inc1/
mkdir -p /data/xbackup/inc2/
ll /data/xbackup/
ll /data/xbackup/full/
ll /data/xbackup/inc1/
ll /data/xbackup/inc2/
rm -rf /data/xbackup/full/*
rm -rf /data/xbackup/inc1/*
rm -rf /data/xbackup/inc2/*
    2. Copy the backup data from the primary database to the secondary databases.
      1
2
3
      scp -r /data/xbackup/full/* root@192.168.220.152:/data/xbackup/full/
scp -r /data/xbackup/inc1/* root@192.168.220.152:/data/xbackup/inc1/
scp -r /data/xbackup/inc2/* root@192.168.220.152:/data/xbackup/inc2/
    3. Query backup data on the secondary databases.
      1
2
3
4
5
6
7
8
      ll /data/xbackup/
ll /data/xbackup/full/
ll /data/xbackup/inc1/
ll /data/xbackup/inc2/
du -sh /data/xbackup/
du -sh /data/xbackup/full/
du -sh /data/xbackup/inc1/
du -sh /data/xbackup/inc2/
  5. Stop the MySQL process on the secondary databases.
    1
2
3
    ps -ef | grep mysql
/usr/local/mysql/bin/mysqladmin -uroot -p123456 shutdown -S /data/mysql/run/mysql.sock
ps -ef | grep mysql
  6. Perform the following operations on the secondary databases to restore the backup data of the primary database to the secondary databases (restoration of incremental backup):
    1. Go to the bin directory of XtraBackup.
      • XtraBackup 8.0.13:
        1
        cd /usr/local/xtrabackup-8.0.13/bin/
      • XtraBackup 2.4.20:
        1
        cd /usr/local/xtrabackup-2.4.20/bin/
    2. Prepare for full backup.
      1
2
      ./xtrabackup --prepare --apply-log-only --target-dir=/data/xbackup/full/
ll /data/xbackup/full/

      If "completed OK" is displayed, the preparation is complete.

    3. Merge the first incremental backup into the full backup.
      1
2
      ./xtrabackup --prepare --apply-log-only --target-dir=/data/xbackup/full/ --incremental-dir=/data/xbackup/inc1/
ll /data/xbackup/full/
    4. Merge the second incremental backup into the full backup.
      ./xtrabackup --prepare --target-dir=/data/xbackup/full/ --incremental-dir=/data/xbackup/inc2/
ll /data/xbackup/full/
    5. Restore data.
      1
      rsync -avrP /data/xbackup/full/ /data/mysql/data/
  7. Start the databases.
    ps -ef | grep mysql
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
  8. Log in to the databases to query data.
    1
2
    /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock
show databases;
This site uses cookies to improve your browsing experience. By continuing to browse this site, you agree to our use of cookies.View Details