安装Xtrabackup实现备份MySQL详细步骤

下文我给大家简单讲讲关于安装Xtrabackup实现备份MySQL详细步骤，大家之前了解过相关类似主题内容吗？感兴趣的话就一起来看看这篇文章吧，相信看完安装Xtrabackup实现备份MySQL详细步骤对大家多少有点帮助吧。

一、安装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

二、安装MySQL

1.安装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

2.更改时间戳设置

# 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  ##显示指定默认值为timestamp类型的字段

3.启动MySQL

# systemctl start mysqld# systemctl status mysqld

4.配置MySQL密码

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

a.新版本MySQL安装完成后会生成临时的初始密码

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

修改MySQL密码

注意：MySQL 5.7默认安装了密码安全检查插件(validate_password)，默认密码检查策略要求密码必须包含:大小写字母、数字和特殊符号，并且长度不能少于8位。

MySQL官网MySQL 5.7密码策略详细说明:

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

b.修改密码策略

# vi /etc/my.cnfvalidate_password_policy = LOW              ##密码长度不少于8位即可# systemctl restart mysqld# mysql -u root -pmysql> set password for 'root'@'localhost'=password('12345678');

c.官方数据库示例

# 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   ##还原数据库结构mysql> source /root/sakila-db/sakila-data.sql    ##将数据写入数据库

三、innobackupex常用命令

--backup 默认选项

--defaults-file 指定要备份的mysql实例的my.cnf文件，必须为第一个选项

--port 端口

--socket 连接套字节的位置，默认为/var/lib/mysql/mysql.sock

--host 主机

--no-timestamp 指定了这个选项备份会直接备份在BACKUP-DIR，不再创建时间戳文件夹

--target-dir 指定了这个选项备份会直接备份在BACKUP-DIR，不再创建时间戳文件夹

--use-memory 指定备份所用内存大小，默认为100M，与--apply-log同用

--apply-log 从备份恢复

--apply-log-only 在恢复时，停止恢复进程不进行LSN，只使用log

--copy-back 复制备份文件

--incremental 建立增量备份

--incremental-basedir=DIRECTORY

指定一个全库备份的目录作为增量备份的基础数据库

--incremental-dir=DIRECTORY

指定增量备份与全库备份合并建立一个新的全备目录

--prepare 从backup恢复

--compress 压缩选项

四、xtrabackup备份后的主要文件

(1)xtrabackup_checkpoints -- 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；

每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。

(2)xtrabackup_binlog_info -- mysql云服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

(3)xtrabackup_binlog_pos_innodb -- 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。

(4)xtrabackup_binary -- 备份中用到的xtrabackup的可执行文件。

(5)backup-my.cnf -- 备份命令用到的配置选项信息。

五、Innobackupex备份

1.创建全备

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

2.应用全备日志

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

3.查看备份状态

# cat /backup/2017-04-03_12-45-44/xtrabackup_checkpoints backup_type = full-prepared           ##全备from_lsn = 0                       ##备份开始点to_lsn = 9692219                   ##备份结束点last_lsn = 9692228compact = 0recover_binlog_info = 0

4.查看二进制日志事件信息

# cat /backup/2017-04-03_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.进行全备恢复

a.删除数据库、停止并破坏MySQL

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

b.恢复全备

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

注:如无法启动SQL，可能是SELINUX的问题

# vim /etc/selinux/configSELINUX=disabled

六、Innobackupex增量备份

1.创建数据库和表

# 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(1,'tom',12345678901,'2001-01-01');mysql> insert into plus values(2,'jack',12345678911,'2011-11-11');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)

2.基于全备进行增量备份

# innobackupex --defaults-file=/etc/my.cnf  --user=root --password=12345678 --incremental --incremental-basedir=/backup/2017-04-03_21-34-08/ /backup/001/......completed OK!# cat /backup/001/2017-04-03_21-41-27/xtrabackup_checkpoints backup_type = incremental            ##增量备份from_lsn = 9692219                  ##备份开始点to_lsn = 9699700                    ##备份结束点last_lsn = 9699709compact = 0recover_binlog_info = 0

3.应用全备日志

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

4.应用第一次增量备份日志

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

5.基于全备进行第一次增量备份恢复

# systemctl stop mysqld# rm -rf /var/lib/mysql# innobackupex --copy-back /backup/2017-04-03_21-34-08/2017-04-03_21-44-08/......completed OK!# 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.基于第一次增量备份进行备份

a.向表中添加数据

# mysql -u root -pmysql> use abc;mysql> insert into plus values(3,'rose',12345678912,'2012-12-12');mysql> insert into plus values(4,'jordan',12345678923,'2012-12-23');

b.应用第二次增量备份日志

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

c.查看备份状态

# 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.基于全备份和第一次增量备份，恢复第二次增量备份

# systemctl stop mysqld# rm -rf /var/lib/mysql# innobackupex --copy-back /backup/2017-04-03_21-34-08/2017-04-03_21-50-11/......completed OK!# 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-12 ||    4 | jordan | 12345678923 | 2012-12-23 |+------+--------+-------------+------------+4 rows in set (0.00 sec)

七、Xtrabackup备份

1.创建全备

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

2.应用全备日志

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

3.查看备份状态

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

4.恢复备份

# 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.00# 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)

八、Xtrabackup增量备份

1.第一次增量备份

# 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(1,'Jordan',23,'Chicago','Bulls');mysql> insert into superstar values(2,'Yao',11,'Houston','Rockets');

2.应用第一次增量备份日志

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

3.查看备份状态

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

4.第二次增量备份

# mysql -u root -pmysql> use ball;mysql> insert into superstar values(3,'Russell',6,'Boston','Celtics');mysql> insert into superstar values(4,'Pierce',34,'Boston','Celtics');

5.应用第二次增量备份日志

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

6.查看备份状态

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

7.准备第一次增量备份

# 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.准备第二次增量备份

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

9.合并恢复备份

# 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 OK!# 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)

大家觉得安装Xtrabackup实现备份MySQL详细步骤这篇文章怎么样，是否有所收获。如果想要了解更多相关，可以继续关注我们的行业资讯板块。