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

The following content mainly brings you a brief analysis of using xtrabackup for data backup. The knowledge mentioned here, which is slightly different from books, is summarized by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

As described earlier, mysqldump backup uses logical backup, and its biggest disadvantage is that the backup and recovery speed is slow. For a database less than 50G, this speed is acceptable, but if the database is very large, it is not suitable to use mysqldump backup.

At this time, you need an easy-to-use and efficient tool, and xtrabackup is one of them, known as the free version of InnoDB HotBackup.

The Xtrabackup implementation is a physical backup and is a physical hot backup.

Xtrabackup provides two command-line tools:

Xtrabackup: dedicated to backing up data from InnoDB and XtraDB engines

Innobackupex: this is a perl script that invokes the xtrabackup command during execution so that you can back up both InnoDB and MyISAM engine objects.

Xtrabackup is a mysql database backup tool provided by percona with the following features:

(1) the backup process is fast and reliable

(2) the backup process will not interrupt the transaction in progress.

(3) it can save disk space and traffic based on compression and other functions.

(4) automatic backup verification

(5) the reduction speed is fast.

Official link address: http://www.percona.com/software/percona-xtrabackup; you can download the source code compilation and installation, or you can download the appropriate RPM package or install it using yum or download the binary source package.

1. Install xtrabackup

1) download xtrabackup

Wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

2) decompression

Tar zxf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

3) enter the decompression directory

Cd percona-xtrabackup-2.4.4-Linux-x86_64/

4) copy all programs under bin to / usr/bin

[root@localhost percona-xtrabackup-2.4.4-Linux-x86_64] # cp bin/* / usr/bin/

There are two main tools in Xtrabackup:

Xtrabackup: is a tool for hot backup of data in innodb and xtradb tables, supports online hot backup, and can back up Innodb tables without locking, but this tool cannot operate Myisam engine tables; innobackupex: a perl script that encapsulates xtrabackup and can handle both Innodb and Myisam, but requires a read lock when dealing with Myisam. Because read locks are required to operate on Myisam, which blocks writes to online services, and Innodb does not have such restrictions, the greater the proportion of Innodb table types in the database, the more advantageous it is.

4) install the relevant plug-ins

Yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5-y

5) download percona-toolkit and install

Wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpmrpm-vih percona-toolkit-2.2.19-1.noarch.rpm

After installing xtrabackup, you can start the backup.

Option 1: xtrabackup full backup + binlog incremental backup

Backup:

Create a backup directory. Full is the directory where the full backup is stored, and inc is the directory where the incremental backup is stored.

1) full backup

Basic syntax: # innobackupex-- user=DBUSER-- password=DBUSERPASS / path/to/BACKUP-DIR/

/ path/to/BACKUP-DIR/: specifies the destination directory where the backup is stored, and the backup process creates a directory named after the backup time to store the backup files.

Note:

-- defaults-file=/etc/my.cnf specifies the configuration file my.cfg for mysql, which must be the first parameter if specified.

-- database specifies the database to be backed up. The database specified here is only valid for MyISAM tables and is complete for InnoDB data (InnoDB data in all databases are backed up, not only the specified database is backed up, but also during recovery).

The following prompt appears to indicate success:

Backed up files:

At the same time of the backup, the backup data creates a directory under the backup directory with the name of the current date and time to store the backup files:

The description of each document:

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

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

(2) the binary log files currently in use on the xtrabackup_binlog_info:mysql CVM and the location of the binary log events up to the moment of backup.

(3) xtrabackup_binlog_pos_innodb: the current position of the binary log file and the binary log file for the InnoDB or XtraDB table.

(4) xtrabackup_binary: executable file of xtrabackup used in backup

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

Note: the user who backs up the database needs to have the appropriate permissions, and if you want to use a user with minimum privileges for backup, you can create such a user based on the following command:

Mysql > create user 'fjc'@'localhost' identified by' 123456 leaders make sure MySQL > revoke all privileges,grant option from 'fjc'@'localhost';mysql > grant reload,lock tables,replication client, process on *. * to' fjc'@'localhost';mysql > flush privileges

At this point, it is completely successful, then insert a few pieces of data into a library in mysql, and then make an incremental backup.

Simulate database modification:

2) incremental backup binaries:

View binlog log location on full backup (position)

Incremental backup binaries

Restore:

Simulate database corruption, which is simulated by deleting data catalog files directly.

(1) prepare (prepare) a full backup

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.

At the end of the prepare process, the InnoDB table data has been rolled forward to the end of the entire backup, rather than to the point where the xtrabackup just started.

The-- apply-log option of the innobakupex command can be used to achieve the above functionality. Such as the following command:

Note:

/ opt/mysqlbackup/full/2017-03-22room02-10-57 / the name of the directory where the backup file is located

-- use-memory option to specify the amount of memory it can use, which is usually 100m by default. If enough memory is available, you can allocate more memory to the prepare process to improve its completion speed.

(2) restore the database

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. Such as the following command:

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 data file before starting mysqld.

Restart MySQL and verify the restored data:

(3) restore incremental backup:

In order to prevent a large number of binary logs from being generated during restore, the binary logs can be temporarily closed during restore and then restored.

Restart the binary log and verify that the restored data is restored and that the data is restored.

Option 2: xtrabackup full backup + xtrabacup incremental backup

When we did incremental backups earlier, we used the same old method: backing up binary logs. Actually, xtrabackup also supports incremental backup.

Let's first introduce the backup principle of xtrabackup:

A redo log file is maintained within InnoDB, which we can also call a transaction log file (transaction log, transaction log). The transaction log stores recorded changes to each InnoDB table data. When InnoDB starts, InnoDB examines the data file and transaction log and performs two steps: it applies the committed transaction log to the data file and rolls back the modified but uncommitted data.

Xtrabackup remembers log sequence number (LSN) at startup and copies all data files. The replication process takes some time, so if the data file changes during this period, it will put the database at a different point in time. At this point, xtrabackup runs a background process that monitors the transaction log and copies the latest changes from the transaction log. Xtrabackup must do this continuously because the transaction log rotates repeated writes and can be reused. So since xtrabackup starts, it keeps recording the changes of each data file in the transaction log, which is the backup process of xtrabackup.

So each InnoDB page will contain a LSN information, and whenever the relevant data changes, the LSN of the related page will automatically grow. This is the basis on which InnoDB tables can be backed up incrementally.

XtraBackup is based on the crash-recovery function of InnoDB. It will copy the data file of innodb, because the table is not locked, the copied data is inconsistent, and crash-recovery is used during recovery to make the data consistent.

When InnoDB starts, it checks data file and transaction log first, and does two steps:

1.It applies committed transaction log entries to the data files

2.it performs an undo operation on any transactions that modified data but did not commit.

So during the prepare process, XtraBackup uses the copied transactions log to crash recovery the backed up innodb data file.

Backup:

It is also backed up and restored for the previous test environment.

(1) full backup

-defaults-file specifies the configuration file of the database, which must be used as the first parameter if used

-- user specifies the user name to connect to the database;-- password specifies the password to connect to the database

-- port specifies the port number to connect to the database

-- backup implements backup to target-dir

-- path to the directory where target-dir=name backup files are stored

-- database specifies the database to be backed up. The specified database is only valid for the table structure of MyISAM table and InnoDB table, and is complete for InnoDB data (InnoDB data in all databases are backed up, not only the specified database is backed up, but also during recovery)

/ opt/mysqlbackup/full/ is the location where backup files are stored.

After the backup is completed, some of the information is as above.

View full backup files

(2) incremental backup

First incremental backup

Enter a row of data

Execute backup command

-- incremental-basedir specifies the location of the last full backup or incremental backup files (that is, if it is the first incremental backup, it points to the directory where the full backup is located, and when you perform an incremental backup again after performing an incremental backup, its-- incremental-basedir should point to the directory where the last incremental backup was located).

View incremental backup files

Note:

The incremental backup here is actually only for InnoDB, and for MyISAM, it's still a full backup.

Second incremental backup

Enter a row of data

Execute backup command

Note: second incremental backup-incremental-basedir points to the location of the last incremental backup file

View incremental backup files

Restore

The tb1 table is deleted directly here.

(1) full backup and recovery

Note: before the recovery, if the full backup is on the remote host, first copy the full backup to the local host. If it is a tar package, you need to unpack it first. The unpacking command is: tar-izxf xxx.tar, where you must use the-I parameter (ignore the 0-byte block in the archive (which usually means the end of the file).

(2) restore to the time of the first increment

The steps for incremental backup recovery are basically the same as those for full backup recovery, except that the process of applying logs is slightly different. When an incremental backup is restored, all incremental backups are applied to the data files of the full backup one by one, and then the data in the full backup is restored to the database.

(3) restore to before the second incremental backup

(4) restore the whole library

(5) follow-up work

Shut down the database

Start rsync data file

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 data file before starting mysqld.

Verification

Scheme 3: innobackupex full database backup + innobackupex incremental backup

Backup

(1) full backup

Execute the above command, and then view the full backup file:

(2) the first incremental backup

Enter a row of data

Execute the backup command, and then view the backup file:

(3) second incremental backup

Insert a row of data

Execute the backup command, and then view the backup file:

Restore

The tb1 table is also deleted here.

(1) restore full backup

-- redo-only is used to prepare incremental backup content to merge data into a full backup directory, which is used in conjunction with incremental-dir incremental backup directory.

(2) restore the first incremental backup based on full backup

(3) restore the second incremental backup based on the full backup and the first incremental backup

(4) follow-up work

Stop the database

All files under the empty data directory

Copy the recovered data to the appropriate directory according to the requirements of the configuration file.

Modify the properties of a data file

Start the mysql service, verify it, and find that the data is back.

Attached: Xtrabackup's "streaming" and "backup compression" functions

Xtrabackup supports the "stream" function for backup data files, that is, the backup data can be transferred to tar programs for archiving through STDOUT, instead of being saved directly to a backup directory by default. To use this feature, you only need to use the-- stream option. Such as:

Innobackupex-- user=root-- password= "123456"-- stream=tar / opt/mysqlbackup/full/ | gzip > / opt/mysqlbackup/full/full_ `date +% Fathers% H% M% S`.tar.gz

For the above brief analysis of using xtrabackup for data backup, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sale and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.