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

Mysql big data backup and incremental backup and restore

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

At present, there are two mainstream tools to implement physical hot backup: ibbackup and xtrabackup; ibbackup requires licensing and is expensive, while xtrabackup is more powerful than ibbackup and is open source

Xtrabackup provides two command-line tools:

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

Innobackupex: this is a perl script that calls the xtrabackup command during execution to back up InnoDB or objects of the MyISAM engine.

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.

Use a script to fully back up the database

Execute script

Check to see if the catalog is successful.

Start binaries, restart services

Create databases, tables, insert data

The data in the above figure is before backup, and there are incremental backups, which are described below.

Install the software package that backs up the database

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

Cd percona-xtrabackup-2.4.4-Linux-x86_64/

Cp bin/* / usr/bin/

Install dependency packages using yum

Create a backup directory (full: full storage directory, inc: incremental backup directory)

The appearance of an OK at the end indicates that it is ready.

Check what is backed up in the full directory

Xtrabackup_checkpoints-- backup type, backup status, lsn scope information

The binary log files currently in use by the xtrabackup_binlog_info--mysql server and the location of the binary log events up to the moment the backup is made.

Xtrabackup_binlog_pos_innodb-the current position of the binary log file and the binary log file for the InnoDB or XtraDB table.

Xtrabackup_binary-the executable file of xtrabackup used in backup

Backup-my.cnf-configuration option information used by backup commands

Check the number of the log file in info

Insert new data into the database

Back up incremental binaries

Restore database

In order to ensure the security of the database, we remove the data mv from the database.

Prepare to restore the database

Note: the screenshot above is only part of it, and there are still many output uncut.

Just see ok.

Restore database

View database catalog data

You can see that the data has been restored, but the master group is root, because it was restored by the root user, so modify the master group

Restart the database systemctl restart mysqld

View the data in the database

Restore incremental data backup

Note: a lot of binaries will be generated when reducing incremental backups. Close binaries.

Then open the binary file to see if the data is restored.

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.