How to use Xtrabackup for incremental backup of mysql

Using Xtrabackup for mysql incremental backup

Now the xtrabackup version has been upgraded to 8.0, but it is only supported for mysql8.0. We still use 2.4 , but 2.4 has a relatively big change compared to the previous 2.1: innobackupex functions are all integrated into xtrabackup, there is only onebinary, and for compatibility reasons, innobackupex is a soft link of xtrabackup, that is, xtrabackup now supports non-Innodb table backup. And Innobackupex will be removed in the next version (already removed in 8.0). It is recommended to replace innobackupex with xtrabackup. There are also some other new features. For more instructions, please see the new version of xtrabackup.

Installation

If the installation requires dependencies, install the dependencies

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-xtrabackup-24

Set up the database for backup accounts

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;

Full backup

xtrabackup --user=bkpuser --password=123456 --backup --target-dir=/data/backups/mysql
# 会看到输出
200603 09:55:37 Executing UNLOCK TABLES
200603 09:55:37 All tables unlocked
200603 09:55:37 [00] Copying ib_buffer_pool to /data/backups/mysql/ib_buffer_pool
200603 09:55:37 [00]        ...done
200603 09:55:37 Backup created in directory '/data/backups/mysql/'
200603 09:55:37 [00] Writing /data/backups/mysql/backup-my.cnf
200603 09:55:37 [00]        ...done
200603 09:55:37 [00] Writing /data/backups/mysql/xtrabackup_info
200603 09:55:37 [00]        ...done
xtrabackup: Transaction log of lsn (837940114) to (837940123) was copied.
200603 09:55:37 completed OK!

• Prepare the backup

xtrabackup --prepare --target-dir=/data/backups/mysql

• Copy the backup

In order to demonstrate the full backup, I will just do it directly Move the data directory where my blog mysql is stored

mv /var/lib/mysql /var/lib/mysql_bak
mkdir /var/lib/mysql
xtrabackup --copy-back --target-dir=/data/backups/mysql  # 这样会保留原始备份 他会将当时读到my.cnf的datadir设置为恢复路径
200603 10:47:42 [01]        ...done
200603 10:47:42 [01] Copying ./performance_schema/mutex_instances.frm to /var/lib/mysql/performance_schema/mutex_instances.frm
200603 10:47:42 [01]        ...done
200603 10:47:42 [01] Copying ./performance_schema/events_transactions_history_long.frm to /var/lib/mysql/performance_schema/events_transactions_history_long.frm
200603 10:47:42 [01]        ...done
200603 10:47:42 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
200603 10:47:42 [01]        ...done
200603 10:47:42 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
200603 10:47:42 [01]        ...done
200603 10:47:42 completed OK!

• The backup is successful and the blog can be accessed normally after restarting it hahahahahahahahahahaha

# 将恢复目录的属主更改一下
chown -R mysql:mysql mysql
/etc/init.d/mysql start

If you do not want to back up the data during recovery, you can use the xtrabackup --move-back command

Incremental backup

Incremental backup is based on existing data Yes, that’s OK. You need to create a full backup first, and then record the recording points at that time.

• Create backup

xtrabackup --user=bkpuser --password=123456 --backup --target-dir=/data/backups/base
# 基于全量备份进行增量
xtrabackup --user=bkpuser --password=123456 --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base

• Check the backup type to confirm that it is an incremental backup.

root@longing:/data/backups/inc1# cat xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 837943393
to_lsn = 837943393
last_lsn = 837943402
compact = 0
recover_binlog_info = 0
flushed_lsn = 837943402

from_lsn is the starting LSN of the backup. For incremental backup, it mustto_lsn is the same as the previous base backup.

In this case you can see to_lsn (last checkpoint LSN) and last_lsn (last copied LSN ), which means there is some traffic on the server during the backup process.

• We can test the first increase and continue to create increments before creating a few pieces of data

xtrabackup --user=bkpuser --password=123456 --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1

• Prepare to restore

There are already 3 backups. We need to prepare the basic data first, and then prepare the two increments

xtrabackup --user=bkpuser --password=123456 --prepare --apply-log-only --target-dir=/data/backups/base
xtrabackup --user=bkpuser --password=123456 --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc1
xtrabackup --user=bkpuser --password=123456 --prepare --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2

xtrabackup --apply-log-only Should be used when merging all increments except the last one. Once prepared, incremental backups are identical to full backups and they can be restored in the same way.

• Restore

xtrabackup --copy-back --target-dir=/data/backups/base

You can see the data inserted in the middle, awesome!

Summary of questions

• Incremental backup steps

• Create a basic backup

• Increment under certain conditions Backup creation

• Prepare all backups. All incrementals based on the basic backup are equivalent to merge operations

• Finally, you can restore it directly just like the full backup.

Principle

InnoDB will maintain a redo log file internally, we can also call it a transaction log file. The transaction log will store each InnoDB Record modification of table data. When InnoDB starts, InnoDB checks the data files and transaction logs and performs two steps: it applies (rolls forward) committed transaction logs to the data files, and rolls back modified but uncommitted data.

Xtrabackup will remember log sequence number (LSN) at startup, and copy all data files. The copying process takes some time, so if the data files are modified during this period, the database will be at a different point in time. At this time, xtrabackup will run a background process to monitor the transaction log and copy the latest modifications from the transaction log. Xtrabackup This operation must be done continuously because the transaction log will be written repeatedly in rotation, and the transaction log can be reused. Therefore, xtrabackup has continuously recorded the modifications of each data file in the transaction log since startup. The above is the backup process of xtrabackup.

Why not use "--apply-log-only" for the last incremental backup

The last "preparation" operation does not need to skip the rollback operation, so that the data files used for recovery are local It's taken care of. When the service starts, it will not enter the rollback phase again. If this parameter is used for the last time, the server will enter the rollback phase after it starts. So this --apply-log-only is only used to merge increments to avoid the next increment being unavailable. See See man xtrabackup

为什么备份完后要准备备份 "prepare"

一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。他的作用是使数据文件处于一致性状态，方法是回滚未提交的事务并同步已提交的事务至数据文件。

为什么选择这个做备份?

• mysqldump 备份缺点

效率较低,备份和还原速度慢,份过程中,数据插入和更新操作会被挂起

• MySQL 备份工具

跨平台性差,备份时间长,冗余备份,浪费存储空间

• XtraBackup

备份过程中不锁库表,适合生产环境,由专业组织Percona提供( 改进MySQL分支 )

• XtraBackup能对表 库进行备份吗?

当然了,只不过博主太懒了,写起来太麻烦了,不过原理都是差不多,操作也差不多,大家自己搞搞!

xtrabackup --user=bkpuser --password=123456 --backup --databases="u_test" --no-timestamp --target-dir=/data/backups/u
xtrabackup --prepare --target-dir=/data/backups/u

还原

• 先prepare，利用--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态

• copy，因为是部分备份，不能直接用--copy-back，只能手动来复制需要的库，也要复制ibdata(数据字典）

• cp /data/backups/u/ibdata1 /var/lib/mysql/

• cp -r u/u_test /var/lib/mysql/

The above is the detailed content of How to use Xtrabackup for incremental backup of mysql. For more information, please follow other related articles on the PHP Chinese website!