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

Below, I will give you a brief introduction to the detailed steps of installing Xtrabackup to implement backup MySQL. Have you learned about similar topics before? If you are interested, let's take a look at this article. I believe it is more or less helpful for everyone to read the detailed steps of installing Xtrabackup to back up MySQL.

First, install Xtrabackup

# wget-- no-check-certificate http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm# rpm-ivh percona-release-0.1-4.noarch.rpm# yum list | grep percona# yum-y install percona-xtrabackup-24

Second, install MySQL

1. Install MySQL

# yum-y install http://repo.mysql.com//mysql57-community-release-el7-9.noarch.rpm# yum list | grep mysql-community# yum-y install mysql mysql-server mysql-devel

two。 Change timestamp settings

# cat / var/log/mysqld.log | grep "timestamp" [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-- explicit_defaults_for_timestamp server option (see documentation for more details). # vi / etc/my.cnfexplicit_defaults_for_timestamp=true # # displays fields with specified default values of timestamp type

3. Start MySQL

# systemctl start mysqld# systemctl status mysqld

4. Configure MySQL password

# mysql Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

a. A temporary initial password is generated after the installation of the new version of MySQL is completed.

# grep 'temporary password' / var/log/mysqld.log [Note] A temporary password is generated for root@localhost: qhAnfco2o) HB

Modify MySQL password

Note: the password Security check plug-in (validate_password) is installed by default in MySQL 5.7. the default password checking policy requires that passwords must contain uppercase and lowercase letters, numbers, and special symbols, and must be no less than 8 digits in length.

The password policy of MySQL 5.7 on MySQL official website is described in detail:

Http://dev.mysql.com/doc/refman/5.7/en/validate-password-options-variables.html#sysvar_validate_password_policy

b. Modify password policy

# vi / etc/my.cnfvalidate_password_policy = LOW # # password length is no less than 8 digits # systemctl restart mysqld# mysql-u root-pmysql > set password for 'root'@'localhost'=password (' 12345678')

c. Official database example

# wget http://downloads.mysql.com/docs/sakila-db.tar.gz# tar-zxvf sakila-db.tar.gz sakila-db/sakila-db/sakila-data.sqlsakila-db/sakila-schema.sqlsakila-db/sakila.mwb [root@localhost ~] # pwd/root# mysql-u root-pmysql > source / root/sakila-db/sakila-schema.sql # # restore database structure mysql > source / root/sakila-db/sakila-data.sql # # write data to the database

Third, innobackupex common commands

-- backup default option

-- defaults-file specifies the my.cnf file of the mysql instance to be backed up, which must be the first option

-- port port

-- location of socket connection socket bytes. Default is / var/lib/mysql/mysql.sock.

-- host host

-- no-timestamp specifies this option. Backups will be backed up directly in BACKUP-DIR, instead of creating a timestamp folder.

-- target-dir specifies this option. Backups will be backed up directly in BACKUP-DIR, instead of creating a timestamp folder.

-- use-memory specifies the amount of memory used for backup. Default is 100m, which is used with-- apply-log.

-- apply-log restore from backup

-- apply-log-only when restoring, stop the recovery process without LSN, only use log

-- copy-back copies backup files

-- incremental establishes incremental backup

-- incremental-basedir=DIRECTORY

Specify a directory for a full database backup as the base database for incremental backup

-- incremental-dir=DIRECTORY

Specify the combination of incremental backup and full database backup to create a new full directory

-- prepare recovers from backup

-- compress compression option

IV. Main files after xtrabackup backup

(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, 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) xtrabackup_binlog_info-the location of the binary log files currently used by the mysql CVM and 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 used for the InnoDB or XtraDB table.

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

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

5. Innobackupex backup

1. Create a complete set

# innobackupex-defaults-file=/etc/my.cnf-user=root-password=12345678 / backup/.completed OK!

two。 Application complete log

# innobackupex-- apply-log / backup/2017-04-03-12-45-44/.completed OK!

3. View backup status

# cat / backup/2017-04-03 backup 12-45-44/xtrabackup_checkpoints backup_type = full-prepared # # full from_lsn = 0 # # backup start point to_lsn = 9692219 # # backup end point last_lsn = 9692228compact = 0recover_binlog_info = 0

4. View binary log event information

# cat / backup/2017-04-03 21:34:09end_time 12-45-44/xtrabackup_info uuid = 694e5590-1828-11e7-81d2-000c291bd2a1name = tool_name = innobackupextool_command =-- defaults-file=/etc/my.cnf-- user=root-- password=... / backup/tool_version = 2.4.6ibbackup_version = 2.4.6server_version = 5.7.17start_time = 2017-04-03 21:34:09end_time = 2017-04-03 21:34:13lock_time = 0binlog_ Pos = innodb_from_lsn = 0innodb_to_lsn = 9692219partial = Nincremental = Nformat = filecompact = Ncompressed = Nencrypted = N

5. Perform a full recovery

a. Delete the database, stop and destroy the MySQL

# mysql-u root-pmysql > show databases;mysql > drop database sakila;Query OK, 30 rows affected (0.59sec) # systemctl stop mysqld# cp-R / var/lib/mysql / root# rm-rf / var/lib/mysql

b. Restore full readiness

# innobackupex-- copy-back / backup/2017-04-03 root 21-34-08/.completed Oklahs # chown-R mysql.mysql / var/lib/mysql# systemctl start mysqld# mysql-u root-pmysql > show databases

Note: if you cannot start SQL, it may be a problem with SELINUX

# vim / etc/selinux/configSELINUX=disabled

VI. Innobackupex incremental backup

1. Create databases and tables

# mysql-u root-pmysql > create database abc;mysql > use abc;mysql > create table plus (id int (10), name varchar (20), phone char (11), birth date); mysql > show tables;mysql > insert into plus values +-+ | id | name | phone | birth | +-+ | 1 | tom | 12345678901 | 2001-01-01 | | 2 | jack | 12345678911 | 2011-11 -11 | +-+ 2 rows in set (0.00 sec)

two。 Incremental backup based on completeness

# innobackupex-- defaults-file=/etc/my.cnf-- user=root-- password=12345678-- incremental--incremental-basedir=/backup/2017-04-03 backup 21-34-08 / / backup/001/.completed Okun # cat / backup/001/2017-04-03 backup 21-41-27/xtrabackup_checkpoints backup_type = incremental # # incremental backup from_lsn = 9692219 # # backup starting point to_ Lsn = 9699700 # # backup end point last_lsn = 9699709compact = 0recover_binlog_info = 0

3. Application complete log

# innobackupex-- defaults-file=/etc/my.cnf-- user=root-- password=12345678-- apply-log-only / backup/2017-04-03 backup/2017 21-34-08/.completed OK!

4. Apply the first incremental backup log

# innobackupex-defaults-file=/etc/my.cnf-user=root-password=12345678-apply-log-only / backup/2017-04-03 backup/2017 21-34-08 /-- incremental-dir=/backup/001/.completed OK!

5. Restore the first incremental backup based on full availability

# systemctl stop mysqld# rm-rf / var/lib/mysql# innobackupex-- copy-back / backup/2017-04-03mm 21-34-08Universe 2017-04-03pm 21-44-08/.completed OKlines # chown-R mysql.mysql / var/lib/mysql# systemctl start mysqld# mysql-u root-pmysql > show databases;mysql > use abc;mysql > select * from plus +-+ | id | name | phone | birth | +-+ | 1 | tom | 12345678901 | 2001-01-01 | | 2 | jack | 12345678911 | 2011-11 -11 | +-+ 2 rows in set (0.00 sec)

6. Backup based on the first incremental backup

a. Add data to the table

# mysql-u root-pmysql > use abc;mysql > insert into plus values; mysql > insert into plus values

b. Apply the second incremental backup log

# innobackupex-defaults-file=/etc/my.cnf-user=root-password=12345678-apply-log-only / backup/2017-04-03 backup/2017 21-34-08 /-- incremental-dir=/backup/002/.completed OK!

c. View backup status

# cat / backup/002/2017-04-03 # 21-48-54/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 9699700to_lsn = 9696137last_lsn = 9696146compact = 0recover_binlog_info = 0

d. Restore the second incremental backup based on the full backup and the first incremental backup

# systemctl stop mysqld# rm-rf / var/lib/mysql# innobackupex-- copy-back / backup/2017-04-03mm 21-34-08Universe 2017-04-03pm 21-50-11/.completed ox # chown-R mysql.mysql / var/lib/mysql# systemctl start mysqld# mysql-u root-pmysql > show databases;mysql > use abc;mysql > select * from plus +-+ | id | name | phone | birth | +-+ | 1 | tom | 12345678901 | 2001-01-01 | | 2 | jack | | 12345678911 | 2011-11-11 | | 3 | rose | 12345678912 | 2012-12 | | 4 | jordan | 12345678923 | December 23 | +-+ 4 rows in set (0.00 sec) |

7. Xtrabackup backup

1. Create a complete set

# xtrabackup-defaults-file=/etc/my.cnf-user=root-password=12345678-backup-target-dir=/backup/full.completed OK!

two。 Application complete log

# xtrabackup-defaults-file=/etc/my.cnf-prepare-user=root-password=12345678-apply-log-only-target-dir=/backup/full.completed OK!

3. View backup status

# cat / backup/full/xtrabackup_checkpoints backup_type = log-appliedfrom_lsn = 0to_lsn = 9692712last_lsn = 9692721compact = 0recover_binlog_info = 0

4. Restore backup

# systemctl stop mysqld# rm-rf / var/lib/mysql# cd / backup/full/# rsync-rvt-- exclude 'xtrabackup_checkpoints'-- exclude' xtrabackup_logfile'. / / var/lib/mysqlsent 151722380 bytes received 6476 bytes 15971458.53 bytes/sectotal size is 151681109 speedup is 1.0 chown-R mysql.mysql / var/lib/mysql# systemctl start mysqld# mysql-u root-pmysql > show databases +-+ | Database | +-+ | information_schema | | abc | | mysql | | performance_schema | | sakila | | sys | +-+ 6 rows in set (0.09 sec)

8. Xtrabackup incremental backup

1. First incremental backup

# mysql-u root-pmysql > create database ball;mysql > use ball;mysql > create table superstar (id int (5), name varchar (20), number int (2), city varchar (20), team varchar (10); mysql > insert into superstar values

two。 Apply the first incremental backup log

# xtrabackup-defaults-file=/etc/my.cnf-user=root-password=12345678-backup-target-dir=/backup/inc1-incremental-basedir=/backup/full.completed OK!

3. View backup status

# cat / backup/inc1/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 9692712to_lsn = 9763373last_lsn = 9763382compact = 0recover_binlog_info = 0

4. Second incremental backup

# mysql-u root-pmysql > use ball;mysql > insert into superstar values; mysql > insert into superstar values)

5. Apply the second incremental backup log

# xtrabackup-defaults-file=/etc/my.cnf-user=root-password=12345678-backup-target-dir=/backup/inc2-incremental-basedir=/backup/inc1/

6. View backup status

# cat / backup/inc2/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 9763373to_lsn = 9766964last_lsn = 9766973compact = 0recover_binlog_info = 0

7. Prepare for the first incremental backup

# xtrabackup-defaults-file=/etc/my.cnf-user=root-password=12345678-prepare-apply-log-only-target-dir=/backup/full-incremental-dir=/backup/inc1.completed OK!

8. Prepare for the second incremental backup

# xtrabackup-defaults-file=/etc/my.cnf-user=root-password=12345678-prepare-target-dir=/backup/full-incremental-dir=/backup/inc2.completed OK!

9. Merge restore backup

# systemctl stop mysqld# rm-rf / var/lib/mysql# xtrabackup-- defaults-file=/etc/my.cnf-- user=root-- password=12345678-- copy-back-- target-dir=/backup/full.completed Oklahs # chown-R mysql.mysql / var/lib/mysql# systemctl start mysqld# mysql-u root-pmysql > use ball;mysql > select * from superstar +-+ | id | name | number | city | team | +-+ | 1 | Jordan | 23 | Chicago | Bulls | | 2 | Yao | 11 | Houston | Rockets | | 3 | Russell | 6 | Boston | Celtics | 4 | Pierce | 34 | Boston | Celtics | +-+ 4 rows in set (0.00 sec) |

What do you think of the detailed steps of installing Xtrabackup to back up MySQL? what do you think of this article? If you want to know more about it, you can continue to follow our industry information section.

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.