一、安装 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.cnf
- explicit_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.cnf
- validate_password_policy = LOW ##密码长度不少于8位即可
- # systemctl restart mysqld
- # mysql -u root -p
- mysql> 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.sql
- sakila-db/sakila-schema.sql
- sakila-db/sakila.mwb
- [root@localhost ~]# pwd
- /root
- # mysql -u root -p
- mysql> 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 = 9692228
- compact = 0
- recover_binlog_info = 0
4. 查看二进制日志事件信息
- # cat /backup/2017-04-03_12-45-44/xtrabackup_info
- uuid = 694e5590-1828-11e7-81d2-000c291bd2a1
- name =
- tool_name = innobackupex
- tool_command = --defaults-file=/etc/my.cnf --user=root --password=... /backup/
- tool_version = 2.4.6
- ibbackup_version = 2.4.6
- server_version = 5.7.17
- start_time = 2017-04-03 21:34:09
- end_time = 2017-04-03 21:34:13
- lock_time = 0
- binlog_pos =
- innodb_from_lsn = 0
- innodb_to_lsn = 9692219
- partial = N
- incremental = N
- format = file
- compact = N
- compressed = N
- encrypted = N
5. 进行全备恢复
a. 删除数据库、停止并破坏 MySQL
- # mysql -u root -p
- mysql> 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 -p
- mysql> show databases;
注: 如无法启动 SQL,可能是 SELINUX 的问题
- # vim /etc/selinux/config
- SELINUX=disabled
六、Innobackupex 增量备份
1. 创建数据库和表
- # mysql -u root -p
- mysql> 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 = 9699709
- compact = 0
- recover_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 -p
- mysql> 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 -p
- mysql> 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 = incremental
- from_lsn = 9699700
- to_lsn = 9696137
- last_lsn = 9696146
- compact = 0
- recover_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 -p
- mysql> 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-applied
- from_lsn = 0
- to_lsn = 9692712
- last_lsn = 9692721
- compact = 0
- recover_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/mysql
- sent 151722380 bytes received 6476 bytes 15971458.53 bytes/sec
- total size is 151681109 speedup is 1.00
- # chown -R mysql.mysql /var/lib/mysql
- # systemctl start mysqld
- # mysql -u root -p
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | abc |
- | mysql |
- | performance_schema |
- | sakila |
- | sys |
- +--------------------+
- 6 rows in set (0.09 sec)
八、Xtrabackup 增量备份
1. 第一次增量备份
- # mysql -u root -p
- mysql> 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 = incremental
- from_lsn = 9692712
- to_lsn = 9763373
- last_lsn = 9763382
- compact = 0
- recover_binlog_info = 0
4. 第二次增量备份
- # mysql -u root -p
- mysql> 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 = incremental
- from_lsn = 9763373
- to_lsn = 9766964
- last_lsn = 9766973
- compact = 0
- recover_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 -p
- mysql> 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)
来源: http://www.tuicool.com/articles/MJzEFnE