Mysql-xtrabackup 与MySQL5.7 binlog 实现数据即时点恢复
一、数据库准备
1. rpm -e mariadb-libs postfix
tar xf mysql-5.7.14-1.el7.x86_64.rpm-bundle.tar
2. 安装
yum install mysql-community-client-5.7.14-1.el7.x86_64.rpm mysql-community-server-5.7.14-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.14-1.el7.x86_64.rpm mysql-community-libs-5.7.14-1.el7.x86_64.rpm mysql-community-common-5.7.14-1.el7.x86_64.rpm
3. 数据库初始化
5.6:mysql_install_db --user=mysql
有可能报错-> 解决方法：yum install perl-Data-Dumper -y
5.7: mysqld --initialize --user=mysql
tail /var/log/mysqld.log
………
2017-12-01T02:18:54.261204Z 1 [Note] A temporary password is generated
for root@localhost: Xrpwbb#hP8yp
4. 启动服务并登录数据库
[root@localhost ~]# mysql -uroot -pXrpwbb#hP8yp
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
报错解决：
[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql
[root@localhost ~]# systemctl restart mysqld
5. 修改数据库密码
[root@localhost ~]# mysqladmin -uroot -pXrpwbb#hP8yp password 123456
6. vim /etc/my.cnf
添加
.............
server-id=1
log-bin=mysql-bin //开启binlog日志
character-set-server=utf8 //设置字符集
validate_password=off //关闭密码检测插件
7. 准备库表素材
[root@localhost ~]# mysql -uroot -p123456 -e 'create database db1;'
[root@localhost ~]# mysql -uroot -p123456 -e 'create table db1.t1(id int,name varchar(20));'
[root@localhost ~]# mysql -uroot -p123456 -e 'insert into db1.t1 values(1,"a"),(2,"b"),(3,"c");'
[root@localhost ~]# mysql -uroot -p123456 -e 'select * from db1.t1;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
二. 安装percona-xtrabackup
1. 使用innobackup完全备份
创建backup用户：
[root@localhost ~]# mysql -uroot -p123456 -e "grant SELECT,RELOAD,SHOW DATABASES,LOCK TABLES,SUPER,REPLICATION CLIENT,CREATE TABLESPACE,PROCESS on *.* to backup@'localhost' identified by '123456';"
完全备份：
[root@localhost ~]# mkdir -p /bak/full
[root@localhost~]# innobackupex --user=backup --password=123456 --no-timestamp /mysqlbackup/full/full_`date +%F`
查看
[root@localhost full]# ls
full_2017-12-02-11:21:08
[root@localhost full]# cd full_2017-12-02-11:21:08/
[root@localhost full_2017-12-02-11:21:08]# ls
backup-my.cnf ibdata1 sys xtrabackup_info
db1 mysql xtrabackup_binlog_info xtrabackup_logfile
ib_buffer_pool performance_schema xtrabackup_checkpoints
继续插入数据
[root@localhost ~]# mysql -uroot -p123456 -e 'insert into db1.t1 values(4,"d");'
[root@localhost ~]# mysql -uroot -p123456 -e 'insert into db1.t1 values(5,"e");'
误操作删除
[root@localhost ~]# mysql -uroot -p123456 -e 'delete from db1.t1 where id>3;'
继续插入数据
[root@localhost ~]# mysql -uroot -p123456 -e 'insert into db1.t1 values(6,"f"),(7,"g");'
误删除数据库
[root@localhost ~]# mysql -uroot -p123456 -e 'drop database db1;'
2.过程恢复操作
1) 刷新binlog日志
[root@localhost ~]# mysql -uroot -p123456 -e 'flush logs;'
2) 查看binlog日志
方法一
[root@localhost ~]# mysql -uroot -p123456 -e "show master status;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
#一般查看前一个日志文件
mysql> show binlog events in 'mysql-bin.000002';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.18-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 1 | 310 | create database db1
………………………
方法二：使用mysqlbinlog
#注意5.7版本，insert语句已经加密，默认看不到，查看时加上选项[root@localhost ~]# mysqlbinlog --base64-output=DECODE-ROWS –vv /var/lib/mysql/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171202 13:28:13 server id 1 end_log_pos 123 CRC32 0xfbf051da Start: binlog v 4, server v 5.7.18-log created 170810 20:35:03 at startup
ROLLBACK/*!*/;
# at 123
#171202 13:29:03 server id 1 end_log_pos 154 CRC32 0x7761f86f Previous-GTIDs
# [empty]
# at 154
#171202 31:29:32 server id 1 end_log_pos 219 CRC32 0xb60eddec Anonymous_GTID last_committed=0 sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#171202 13:29:44 server id 1 end_log_pos 310 CRC32 0xbbe85598 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1502368628/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database db1
/*!*/;
# at 310
#171202 13:31:08 server id 1 end_log_pos 375 CRC32 0x3d54173c Anonymous_GTID last_committed=1 sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 375
#171302 13:32:08 server id 1 end_log_pos 488 CRC32 0x10705617 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1502368628/*!*/;
create table db1.t1(id int,name varchar(20))
…………………………………….
…………………..
3) 根据日志文件提取全备后数据
- 基于时间点提取插入第4条和第5条数据
[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 --start-datetime="2017-12-02 13:47:41" --stop-datetime="2017-12-02 13:47:57" > time.sql
- 基于位置点提取插入第6条和第7条数据
[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 --start-position="2306" --stop-position="2502" -r position.sql
3.恢复全备
应用日志
[root@localhost ~]# innobackupex --user=backup --password=123456 --apply-log /mysqlbackup/full/full_2017-12-02-13:47:17
[root@localhost ~]# systemctl stop mysqld
删除数据文件
[root@localhost ~]# rm -rf /var/lib/mysql/*
还原数据
[root@localhost ~]# innobackupex --user=backup --password=123456 --copy-back / mysqlbackup/full/full_2017-12-02-13:47:17
授权
[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql
[root@localhost ~]# systemctl restart mysqld
检查全备：
[root@localhost ~]# mysql -uroot -p -e 'select * from db1.t1;'
Enter password:
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
4. 增量恢复
[root@localhost ~]# mysql -uroot -p < time.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'select * from db1.t1;'
Enter password:
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+------+------+
[root@localhost ~]# mysql -uroot -p < position.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'select * from db1.t1;'
Enter password:
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
+------+------+