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

The following mainly brings you what are the main tools for mysql backup. I hope these contents can bring you practical use, which is also the main purpose of this article for editing mysql backup tools. All right, don't talk too much nonsense, let's just read the following.

Backup and restore scripts and tools

1) 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 are commercial software that require authorization and are very expensive. Xtrabackup is more powerful than ibbackup, but it is open source.

2) 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.

Two demonstration steps

First extract the installation package

Copy all programs under bin to / usr/bin

There are two main tools in Xtrabackup:

Xtrabackup: a tool for hot backup of data in innodb and xtradb tables. It supports online hot backup. Innodb tables can be backed up 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.

3) install plug-ins using yum

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

Option 1: xtrabackup full backup + binlog incremental backup

Create a backup directory

Full backup

Innobackupex-- user=root-- password=123456 / opt/mysqlbackup/full appears OK completed

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

The backup database will create a directory with the name of the current date under the backup directory

Document description

(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 (2) xtrabackup_binlog_info-the location of the binary log file currently in use on the mysql CVM and the binary log event up to the moment of backup.

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

(4) xtrabackup_binary-the executable file of xtrabackup used in backup

(5) backup-my.cnf-configuration option information used for backup commands

When using innobackupex for backup, you can also use the-- no-timestamp option to prevent the command from automatically creating a directory named after time.

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:

At this point, it is completely successful, then insert a few pieces of data into a library in mysql, and then perform an incremental backup to make a binary log incremental backup of the database changes after the full backup:

View binlog log location on full backup (position)

Simulate and modify the database

Incremental backup binaries

2 restore database binaries

First simulate the deletion or transfer of data files, and then make a full backup and restore.

The-- apply-log option of the innobakupex command can be used to achieve the above functionality. -- apply-log indicates that the log is applied to the data file, and the data in the backup file is restored to the database after completion:

Restore database syntax:

# innobackupex-- copy-back / opt/mysqlbackup/full/2016-09-12 11-29-55 /

The-- copy-back here indicates that data recovery is performed. After the data recovery is complete, the permissions of the relevant files need to be modified before the mysql database can start normally.

Restore database syntax

Data has successfully restored and restarted mysql

Need to ensure that all data file owners and groups

The restore was successful, but the incremental backup was not successful.

In order to prevent a large number of binary logs from being generated during restore, you can temporarily close the binary logs during restore and then restore:

Mysql > set sql_log_bin=0

Mysql > source / opt/mysqlbackup/inc/2016-09-12.sql

The data was restored successfully

Restart binary log set sql_log_bin=1

What are the main tools for mysql backup above? do you think it is very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.