Shulou(Shulou.com)06/01 Report--

An open source database backup tool that can be downloaded and used on the official website

Percona-backupxtra will perform incremental backups for InonoDB and full backups for MyISAM data.

1 xtrabackup can only back up tables from innodb and xtradb engines, but not from myisam engines.

2 innobackupex is a Perl script that encapsulates xtrabackup, which supports backing up both innodb and myisam, but requires a global read lock when backing up myisam. Also, myisam does not support incremental backups.

3 the other two tools are relatively niche, xbcrypt is used for encryption and decryption; xbstream, similar to tar, is a stream file format implemented by Percona itself that supports concurrent writing. Both are used when backing up and decompressing (if the backup uses encryption and concurrency).

Mysql: the table is a MyISAM engine, which reads more and writes less, and has good performance.

Implementation: to be honest, it is better to look at this than to look at the information generated by the backup, which is shown in every step, which will give you a better understanding of how innobackupex is backed up.

1 first, a process of xtrabackup_log background detection will be started to detect changes in mysql redo in real time. As soon as new log writes are found in redo, the log will be written to the log file xtrabackup_log immediately.

2 copy the innodb data file and system tablespace file idbdata1 to the corresponding place with the default timestamp as the backup directory

3 after the replication ends, perform the flush table with read lock operation

4 copy .frm .myd .myi file

5 and get the position of binary log at this moment

6 unlock the table unlock tables

7 stop the xtrabackup_log process

Full backup:

# innobackupex-user=root-password=mysql / PATH/TO/BACKUP-DIR/

If you want to use a least privileged user for backup, you can create such a user based on the following command

> CREATE USER 'bkpuser'@'localhost' IDENRIFIED BY' PASSWORD'

> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bkpuser'

> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *. * TO 'bkpuser'@'localhost'

> FLUSH PRIVILEGES

The RELOAD privilege enables use of the FLUSH statement. It also enables mysqladmin commands that are equivalent to FLUSH operations: flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, and reload.

The REPLICATION CLIENT privilege enables the use of the SHOW MASTER STATUS, SHOW SLAVE STATUS, and SHOW BINARY LOGS statements.

When using innobackupex backup, it calls xtrabackup to back up all InnoDB tables, copies all related files (.frm) about the definition of the table structure and related files for MyISAM, MERGE, CSV and ARCHIVE tables, as well as files related to triggers and database configuration information. These files are saved to a directory with time commands.

While backing up, innobackupex also creates the following files in the backup directory:

(1) xtrabackup_checkpoints backup type (such as full or incremental), backup status (such as prepared status) and LSN (log serial number) scope information

Each InnoDB page (usually 16K in size) contains a log sequence number, that is, LSN,LSN is the system version number of the entire database system, and the LSN associated with each page indicates how the page has changed recently.

(2) the binary log files currently in use by the xtrabackup_binlog_infomysql server and the location of the binary log events up to the moment of backup.

3) Information collection of xtrabackup_info backup

(4) xtrabackup executable files used in xtrabackup_binary backup

(5) configuration option information used in backup-my.cnf backup command

(6) xtrabackup_checkpoints

Backup_type = full-backuped

From_lsn = 0 from which block to start the backup

To_lsn = 1637454 where to backup

Last_lsn = 1637454 Last data block

The log sequence number of each data block is maintained, and if the data changes, the log sequence number will go forward once, so it can make incremental backups based on this number. Back up when there is a change, and there is no need to back up without a change.

When using innobackupex for backup, you can also use the-- no-timestamp option to prevent the command from automatically creating a directory named by time, so that the innobackupex command will create a BACKUP-DIR directory to store the backup data.

In general, after the backup is complete, the data cannot be used for restore operations because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. The main function of "preparation" is to make the data file consistent by rolling back the uncommitted transaction and synchronizing the committed transaction to the data file.

3. Restore data from a full backup

The-- copy-back option of the innobackupex command is used to perform the restore operation, which performs the restore process by copying all data-related files to the mysql server DATADIR directory. Innobackupex uses backup-my.cnf to get information about the DATADIR directory.

# innobackupex-copy-back / PATH/TO/BACKUP-DIR/-copy-back or-force-non-empty-directories

Datadir must be empty and innobackupex-copy-back will not overwrite existing files

When restoring, you need to shut down the service first, and if the service is started, you cannot restore to datadir.

If executed correctly, the last lines of the output information are usually as follows:

Innobackupex: Starting to copy InnoDB log file

...

Innobackupex: Finished copying back files.

Innobackupex: completed OK!

Please make sure that "completed OK!" appears in the last line of the above message.

When the data is restored to the DATADIR directory, you also need to make sure that the owner and group of all data files are the correct users, such as mysql, otherwise, you need to modify the owner and group of the file before starting mysqld, such as:

Chown-R mysql.msyql / data/

4. Use innobackupex for incremental backup

Each InnoDB page contains a LSN message, and whenever the relevant data changes, the LSN of the related page will automatically grow. This is exactly where InnoDB tables can be backed up incrementally, that is, innobackupex is achieved by backing up pages that have changed since the last full backup.

To achieve the first incremental backup, you can use the following command:

# innobackupex-incremental / backup-incremental-dir=BASEDIR

Ex:

Innobackupex-- user=root-password=mysql-- incremental-basedir=/backup/2017-03-298 12-32-56 /-- incremental / backup/

The next incremental backup will be backed up with the previous incremental backup, that is, a new directory on the incremental-basedir=, not a full one.

Where BASEDIR refers to the directory where the full backup is located, and after the execution of this command, the innobackupex command creates a new time-named directory in the / backup directory to hold all incremental backup data. In addition, when you perform an incremental backup again after an incremental backup, its-- incremental-dir should point to the directory where the last full backup was located.

It should be noted that incremental backups can only be applied to InnoDB or XtraDB tables. For MyISAM tables, incremental backups are actually full backups.

There are some differences between "prepare" incremental backups and collating full backups, especially:

(1) it is necessary to "replay" committed transactions on each backup (including full and individual incremental backups), and after "replay", all backup data will be merged into the full backup.

(2) "rollback" uncommitted transactions based on all backups.

Therefore, the operation becomes:

# innobackupex-apply-log-redo-only BACKUP-DIR\ / / each BACKUP-DIR is the location where all files are backed up

Then execute:

# innobackupex-apply-log-redo-only BACKUP-DIR-incremental-dir=INCREMENTAL-DIR-1

Then comes the second increment:

# innobackupex-- apply-log-- redo-only BACKUP-DIR (the directory here is not the directory of the full backup, but the directory of the last incremental backup, because it was restored on the basis of the last incremental backup)-- incremental-dir=INCREMENTAL-DIR-2

Where BASE-DIR refers to the directory where the full backup is located, INCREMENTAL-DIR-1 refers to the directory of the first incremental backup, INCREMENTAL-DIR-2 refers to the directory of the second incremental backup, and so on, that is, if there are multiple incremental backups, do the same for the first time.

First prepare the document.

My database data is done with LVM under / data/, and I would also like to emphasize here that the binary log must not be on the same disk as the data, which cannot be overreminded. It is best to put the log in a place where the raid1 has a mirror.

Mkdir / backup/

1. # innobackupex-- user=root-- password=mysql / backup/// for full backup

2. # innobackupex-- user=root-- password=mysql-- incremental-basedir=/backup/2017-03-29mm 13-49-52 /-- incremental / backup/ for the first incremental backup

3. # innobackupex-- user=root-- password=mysql-- incremental-basedir=/backup/2017-03-29mm 12-49-52 /-- incremental / backup/ for the second incremental backup

4. # innobackupex-- apply-log-- redo-only / backup/2017-03-29 September 12-32-56 Universe / the first full recovery, but the data is not returned to / data directory

5. # nnobackupex-- apply-log-- redo-only / backup/2017-03-29 backup 12-32-56 /-- incremental-dir=/backup/2017-03-29 million 13-49-52 / / restore the first incremental backup

6. # innobackupex-- apply-log-- redo-only / backup/2017-03-29mm 12-32-56 /-incremental-dir=/backup/2017-03-29mm 13-53-57 Universe / restore the second incremental backup

7. # innobackupex-- copy-back / backup/2017-03-29quarter 12-32-56 innobackupex / rollback operation is performed. Since the perpare operation has been done before, it can be rolled back directly, and the data will be returned.

8. # chown-R mysql.mysql / data/*

9. # service mysqld start// if Starting MySQL.. appears at this time ERROR! The server quit without updating PID file (/ var/mysql/data/localhost.localdomain.pid).

You can killall mysqld to kill the remaining process before starting

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.