1. Description of Xtrbackup backup mechanism
xtrabackupBackup isData FileThe physical backup process is as follows:
- When backing up, the redo thread is turned on. The data changes are no longer refreshed to the disk data files, but are written to the redo log.
- The files in the database data directory no longer change, and the backup of the data file begins.
- Lock table, only read
- Backup the last binlog
- Backup redo logs, b_logfile0, b_logfile1, etc.
2. xtrabackup backup requires specified parameters
According to the above backup process, the conditions required for xtrabackup to backup data are:
1. Connectmysql, turn on and off the redo process
Method 1: You can connect through the host (--host), port (--port), user (--user), and password (--password).
Method 2: You can passsocketFile, user (--user), password (--password) to connect.
The socket file location can be obtained through various channels:
- Get it through configuration file
- xtrabackup reads configuration files in order by default: /etc/ /etc/mysql/ /usr/etc/ ~/.
- Specify mysql configuration file: --defaults-file
- Specified by the --socket parameter.
When running a mysql instance using a container, the socket files read through the configuration file are all in the container, not in the host, and xtrabackup backup will error.
It is recommended to use [Method 1] to connect.
2. Get the data directory to back up mysql instance
Method 1: Get it through configuration file
- xtrabackup reads configuration files in order by default: /etc/ /etc/mysql/ /usr/etc/ ~/.
- Specify mysql configuration file: --defaults-file
Method 2: Specify through the --datadir parameter
When running mysql instance using a container, the data directory locations through the configuration file are all in the container, not in the host, and xtrabackup backup will error.
Therefore, it is recommended to use [Method 2] for container mysql, specifying the data directory in the host. Of course, the data directory must be mounted to the host.
3. Default description
The following operating instructions are for general purpose (host running, container running) and are used uniformly:
- The host (--host), port (--port), user (--user), password (--password) can be used to connect msyql.
- Use --datadir to specify the data directory.
3. Full backup and restore
1. Full backup
mkdir -p /bak/xtrabackup/mysql_ins_name/full_$(date +%Y%m%d) xtrabackup --datadir=/data/mysql_dev_cis --user=root --password='' --host=127.0.0.1 --port=3308 --backup --target-dir=/bak/xtrabackup/mysql_ins_name/full_$(date +%Y%m%d)
- --backup: perform backup operation
- --datadir: Specify the mysql data directory
- --target-dir: Specify the backup location (the directory is required to be empty)
--databases: Specifies the list of database (dbname) or table() to be backed up, split by spaces.
--databases-file: Specify the file to explain. You need to back up the database (dbname) or table () to be divided by rows.
2. Restore data
(1) Backup file preprocessing-prepare
Generally speaking, after the backup is completed, the data cannot be used for recovery operations.
Because the backup data may contain transactions that have not been committed yet or transactions that have been committed but have not been synchronized to the data file, the data file still handles inconsistent state.
The main function of "prepare" is to make the data file consistent by rolling back uncommitted transactions and synchronizing already committed transactions to the data file.
xtrabackup --prepare --target-dir=/bak/xtrabackup/mysql_ins_name/full_$(date +%Y%m%d)
(2) Restore data
- Stop the database that needs to be restored.
You can restore to other databases or to the original database.
- Clearing requires restoring the database data directory. (Backup is recommended)
rm -rf /data/mysql_dev_cis_3309
- Restore
xtrabackup --datadir=/data/mysql_dev_cis_3309 --copy-back --target-dir=/bak/xtrabackup/mysql_ins_name/full_$(date +%Y%m%d)
- --copy-back: perform restore operation
- --target-dir: backup file location
- --datadir: The database data directory needs to be restored
Full backup and restore will also backup and restore the user information of the source database.
IV. Incremental backup and restore
1. Incremental backup
(1) Full backup
mkdir -p /bak/xtrabackup/mysql_ins_name/full_$(date +%Y%m%d) xtrabackup --datadir=/data/mysql_dev_cis --user=root --password='' --host=127.0.0.1 --port=3308 --backup --target-dir=/bak/xtrabackup/mysql_ins_name/full_$(date +%Y%m%d)
(2) Incremental backup (first time)
mkdir -p /bak/xtrabackup/mysql_ins_name/incre_1 xtrabackup --datadir=/data/mysql_dev_cis --user=root --password='' --host=127.0.0.1 --port=3308 --backup --target-dir=/bak/xtrabackup/mysql_ins_name/incre_1 --incremental-basedir=/bak/xtrabackup/mysql_ins_name/full_$(date +%Y%m%d)
--target-dir: The directory location for incremental backups.
--incremental-basedir: The first incremental backup is based on a full backup.
(3) Incremental backup (second time)
mkdir -p /bak/xtrabackup/mysql_ins_name/incre_2 xtrabackup --datadir=/data/mysql_dev_cis --user=root --password='' --host=127.0.0.1 --port=3308 --backup --target-dir=/bak/xtrabackup/mysql_ins_name/incre_2 --incremental-basedir=/bak/xtrabackup/mysql_ins_name/incre_1
--target-dir: The directory location for incremental backups.
--incremental-basedir: Non-first incremental backup is based on the last incremental backup.
2. Restore data
(1) Backup file preprocessing-prepare
- prepare full backup
xtrabackup --prepare --apply-log-only --target-dir=/bak/xtrabackup/mysql_ins_name/full_$(date +%Y%m%d)
--apply-log-only: Except for the last incremental backup of prepare, you do not need to add --apply-log-only, all the previous prepares need to add this parameter.
- prepare first incremental backup
xtrabackup --prepare --apply-log-only --target-dir=/bak/xtrabackup/mysql_ins_name/full_$(date +%Y%m%d) --incremental-dir=/bak/xtrabackup/mysql_ins_name/incre_1
- prepare last incremental backup
xtrabackup --prepare --target-dir=/bak/xtrabackup/mysql_ins_name/full_$(date +%Y%m%d) --incremental-dir=/bak/xtrabackup/mysql_ins_name/incre_2
Note: The last incremental backup of prepare does not need to add --apply-log-only
(2) Restore data
- Stop the database that needs to be restored.
You can restore to other databases or to the original database.
- Clearing requires restoring the database data directory. (Backup is recommended)
rm -rf /data/mysql_dev_cis_3309
- Restore
After preparing, you only need to restore the full backup
xtrabackup --datadir=/data/mysql_dev_cis_3309 --copy-back --target-dir=/bak/xtrabackup/mysql_ins_name/full_$(date +%Y%m%d)
- --copy-back: perform restore operation
- --target-dir: backup file location
- --datadir: The database data directory needs to be restored