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

Introduction to xtarbackup backup

There are two main tools in Xtrabackup:

Xtrabackup: a tool for hot backup of data in innodb,xtradb tables. You cannot back up other types of tables or data table structures.

Innobackupex: a perl script that encapsulates xtrabackup and provides the ability to back up myisam tables.

1. View the system environment * [root@db01 ~] # cat / etc/redhat-release CentOS release 6.8 (Final)

Mysql version

[root@db01] # mysql-Vmysql Ver 14.14 Distrib 5.6.35, for linux-glibc2.5 (x86x64) using EditLine wrapper II. Deploy xtarbackup

1, install the package

Yum install-y http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

2. Check whether the package exists.

[root@db01 ~] # yum list | grep percona

3. Update the software package

Yum install percona-xtrabackup-22

4. Add mysql option to enable related functions (error log, binlog binary log, datadir pointing, etc.)

Log-bin=mysql-bindatadir=/application/mysql/datalog-error=/application/mysql/data/db01.err III. Xtarbakcup parameters and configuration file description

3.1 basic syntax:

[root@db01 mysql] # innobackupex-- user=root-- password=123456-- socket=/tmp/mysql.sock-- defaults-file=/etc/my.cnf / tmp/ syntax explanation:-- user= database user-- password= database password-- socket= specifies socket-- default-file= specifies the configuration file / tmp/ is the location at the end

The following content is displayed, which is basically regarded as a successful backup

Innobackupex: Backup created in directory'/ tmp/2017-12-13 June 12-06-55'innobackupex: MySQL binlog position: filename 'mysql-bin.000004', position 120171213 12:07:07 innobackupex: Connection to database server closed171213 12:07:07 innobackupex: completed OK!

3.2 View backup files

[root@db01 mysql] # ll / tmp/2017-12-13 / 12-06-55 /

The description of each document:

(1) xtrabackup_checkpoints-backup type (such as full or incremental), backup status (such as whether there is no prepare status) and LSN (log serial number) range information every InnoDB page (usually 16K in size) contains a log serial number, that is, LSN,LSN is the system version number of the entire database system, and the LSN associated with each page can list how this page has changed recently. Backup_type = full-backupedfrom_lsn = 0to_lsn = 2097320last_lsn = 2097320compact = 0 (2) xtrabackup_binlog_info-the binary log currently in use by the mysql server and the location where the backup is the binary log event at this moment (3) xtrabackup_pos_innodb-the current posistion of the binary log and the binary log 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 in backup command

3.3 Note:

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

The innobackupex command will create a BACKUP-DIR directory to store backup data

It is also important to note that the user who backs up the database needs to have the corresponding permissions, and the following parameters can be used if you want to backup with a user with minimum permissions:

Mysql > GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *. * TO 'bkpuser'@'localhost'; mysql > FLUSH PRIVILEGES; IV. Backup case

1. Simulated data

Use oldboyinsert into test values (1); insert into test values (2); insert into test values (3); insert into test values (4); insert into test values (5); select * from test;mysql > select * from test +-1 | full01 | 2 | full02 | 3 | full03 | 4 | full04 | | 5 | full05 | +-+-+ 5 rows in set (0.00 sec)

2. Full backup at 0: 00 on 2018-03-21

Date-s "2018-03-21" innobackupex-defaults-file=/etc/my.cnf-user=root-password=oldboy123-socket=/application/mysql-5.6.34/tmp/mysql.sock-no-timestamp / server/backup/new_base_full

3. Incremental backup at 0: 00 on 2018-03-22

Mysql-e "use oldboy;insert into test values (6);" mysql-e "use oldboy;insert into test values (7);" mysql-e "select * from oldboy.test "+-+-- +-- + | id | name | +-+-+ | 1 | full01 | | 2 | full02 | | 3 | full03 | | 4 | full04 | | 5 | full05 | | 6 | new_inc_one_1 | 7 | new_inc_one_2 | +-+-- + |

First incremental backup

Date-s "2018-03-22" innobackupex-defaults-file=/etc/my.cnf-- user=root-- password=oldboy123-- socket=/application/mysql-5.6.34/tmp/mysql.sock-- no-timestamp-- incremental-basedir=/server/backup/new_base_full-- incremental / server/backup/new_one_inc

4. Incremental backup at 0: 00 on 2018-03-23

Mysql-e "use oldboy;insert into test values (8 select stories inception two1');" mysql-e "use oldboy;insert into test values (9 minutes news inception two2');" mysql-e "select * from oldboy.test;"

Second incremental backup

Date-s "2018-03-23" innobackupex-defaults-file=/etc/my.cnf-- user=root-- password=oldboy123-- socket=/application/mysql-5.6.34/tmp/mysql.sock-- no-timestamp-- incremental-basedir=/server/backup/new_one_inc-- incremental / server/backup/new_two_inc

5. Update data after 0: 00 on 2018-03-23-10:00

Mysql-e "use oldboy;insert into test values (10 select realbindings 3');" mysql-e "use oldboy;insert into test values (11);" mysql-e "select * from oldboy.test;"

6. There was a failure at 10:00 in the morning on 2018-03-23

Mysql-e "use oldboy;update test set name='oldboy';" mysql > select * from test;+----+-+ | id | name | +-- +-+ | 1 | oldboy | 2 | oldboy | | 3 | oldboy | | 4 | oldboy | 5 | oldboy | | 6 | oldboy | 7 | oldboy | 8 | oldboy | 9 | oldboy | | 10 | oldboy | 11 | oldboy | +-- +-- + 11 rows in set (0.00 sec)

It is recommended to stop the library:

/ etc/init.d/mysqld stop

Restore the database:

7. Merge data files

Innobackupex-apply-log-use-memory=32M-redo-only / server/backup/new_base_full/innobackupex-apply-log-use-memory=32M-redo-only-incremental-dir=/server/backup/new_one_inc / server/backup/new_base_full/innobackupex-apply-log-use-memory=32M-incremental-dir=/server/backup/new_two_inc / server/backup/new_base_full/

8. Start recovery

Cd / application/mysqlmv data data.11cp-a / server/backup/new_base_full datachown-R mysql.mysql data

9. Deal with binlog

[root@db02 mysql] # cat / server/backup/new_two_inc/xtrabackup_binlog_infooldboy-bin.000006 2286mysqlbinlog-d oldboy--start-position=2286 oldboy-bin.000006-r bin.sqlmysqlbinlog-d oldboy oldboy-bin.000003 oldboy-bin.000004 oldboy-bin.000005 > > bin.sqlcd / application/mysql/logs/mysqlbinlog-d oldboy--start-position=2286 oldboy-bin.000006-r / server/backup/new_bin.sql delete one line of update. Update test set name='oldboy'

10. Enable firewall iptables

Iptables-I INPUT!-s $server IP-p tcp-j DROP

11. Restore incremental sql to database

[root@db02 tools] # mysql oldboy

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.