Mysql主从复制数据和数据回闪[删库到跑路]（mysql 5.
2017-12-09 本文已影响0人
MicoCube
-
- 版本一致
- 初始化表，并在后台启动mysql
- 修改root的密码
-
修改主服务器master:
#vi /etc/my.cnf [mysqld] log-bin=mysql-bin //[必须]启用二进制日志 server-id=220 //[必须]服务器唯一ID，默认是1，一般取IP(192.168.10.220)最后一段 -
修改从服务器slave:
#vi /etc/my.cnf [mysqld] log-bin=mysql-bin //[必须]启用二进制日志 server-id=221 //[必须]服务器唯一ID，默认是1，一般取IP(192.168.10.221)最后一段 -
在主服务器上建立帐户并授权slave:
#/usr/local/mysql/bin/mysql -uroot -p mysql>CREATE USER 'mysync'@'192.168.10.221' IDENTIFIED BY 'q123456'; mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'192.168.10.221' identified by 'q123456'; //一般不用 root帐号，%表示所有客户端都可能连，只要帐号，密码正确，此处可 用具体客户端IP代替，如192.168.145.221，加强安全。 -
登录主服务器的mysql，查询master的状态
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000008 | 708 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) -
配置从服务器
mysql>change master to master_host='192.168.145.220', master_user='mysync',master_password='q123456', master_log_file='mysql-bin.000008',master_log_pos=708; //注意不要断开，708数字前后无单引号。 mysql>start slave; //启动从服务器复制功能 -
查看从服务器状态
mysql> show slave status; #注意Slave_IO_Running: Yes //此状态必须YES #注意Slave_SQL_Running: Yes //此状态必须YES #检查状态时发现Fatal error: The slave I/O thread stops because #master and slave have equal MySQL server UUIDs; these UUIDs #must be different for replication to work. # 因为测试时使用的虚拟机，整个复制了另一个虚拟机，所以，俩份 #mysql是一模一样的，mysql 5.6的复制引入了uuid的概念，各个复制结 #构中的server_uuid得保证不一样，但是查看到直接copy data文件夹 #后server_uuid是相同的，show variables like '%server_uuid%';解决方 #法：找到data文件夹下的auto.cnf文件，修改里面的uuid值，保证各个 #db的uuid不一样，重启db即可,另外server_id也检查一下，是否相同 -
主从配置完成
-
主从库会同步数据行，但并不会同步库和表的结构信息
-
在主从上都创建数据库
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci -
在主从上都上创建表
CREATE TABLE `test` ( `id` int NOT NULL AUTO_INCREMENT , `name` varchar(255) NULL , `age` int NULL , PRIMARY KEY (`id`) ); -
在master上插入数据[会同步到从库]
INSERT INTO `test`.`test` (`id`, `name`, `age`) VALUES ('1', 'mico', '18'); INSERT INTO `test`.`test` (`id`, `name`, `age`) VALUES ('2', 'cube', '20'); -
在master上删除数据（模拟误操作[会同步到从库]）
delete from test;mysql> select * from test; Empty set (0.00 sec) -
binglog2sql简介
利用binlog2sql闪回工具，来恢复误删除的数据。我们都知道binlog的作用是备份恢复和完成MySQL的主从复制功能。利用mysqlbinlog工具可以进行基于时间点或者位置偏移量的数据恢复工作，在生产环境中遇到误删除，改错数据的情况，那是常有的事儿。我们都知道Oracle数据库有闪回功能，而MySQL本身没有自带闪回，但我们可以使用binlog2sql来完成这项工作。binlog是以event作为单位，来记录数据库变更的数据信息，闪回就是可以帮助我们重现这些变化数据信息之前的操作。也就是说对于insert操作，会生成delete语句，反之delete操作，会生成insert语句。对于update操作，也会生成相反的update语句。这款工具只能使用在binlog格式为row模式下。 -
安装依赖
python-pip ， PyMySQL ， python-mysql-replication， wheel argparse -
如果是内网环境，需要手动下载三方包安装，此次测试使用pip安装三方包
wget https://bootstrap.pypa.io/get-pip.py python get-pip.py pip -V #查看pip版本 unzip binlog2sql-master.zip cd binlog2sql-master pip install –r requirements.txt -
python binlog2sql.py --help-B, --flashback 生成回滚语句 --start-file 需要解析的binlog文件 --start-position 解析binlog的起始位置 --stop-position解析binlog的结束位置 --start-datetime 从哪个时间点的binlog开始解析，格式必须为datetime --stop-datetime 到哪个时间点的binlog停止解析，格式必须为datetime -d, --databases 只输出目标db的sql -t, --tables 只输出目标tables的sql -
确定当前binlog文件和position位置
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000008 | 1932 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
可以看到当前binlog是：mysql-binlog.000008
位置偏移量：1932
- 需要预估下时间，误操作的时间范围应该在下午14点20分到14点30分之间：
[root@localhost binlog2sql]# python binlog2sql.py -h192.168.10.220 - P3306 -uroot -pmico -dtest -ttest --start-file='mysql-bin.000008' --start- datetime='2017-12-09 14:20:00' --stop-datetime='2017-12-09 14:30:00' # 结果发现啥都没有。。。执行date命令发现时区不对。。。现在是凌 # 晨2点： [root@localhost binlog2sql]# python binlog2sql.py -h192.168.10.220 - P3306 -uroot -pmico -dtest -ttest --start-file='mysql-bin.000008' INSERT INTO `test`.`test`(`age`, `id`, `name`) VALUES (18, 1, 'mico'); #start 1524 end 1697 time 2017-12-09 01:46:27【插入语句】 INSERT INTO `test`.`test`(`age`, `id`, `name`) VALUES (20, 2, 'cube'); #start 1728 end 1901 time 2017-12-09 01:46:27【插入语句】 DELETE FROM `test`.`test` WHERE `age`=20 AND `id`=2 AND `name`='cube' LIMIT 1; #start 1932 end 2105 time 2017-12-09 02:00:21【第一个删除语句】 DELETE FROM `test`.`test` WHERE `age`=18 AND `id`=1 AND `name`='mico' LIMIT 1; #start 2136 end 2309 time 2017-12-09 02:00:21【第二个删除语句】 # 缩小范围: [root@localhost binlog2sql]# python binlog2sql.py -h192.168.10.220 -P3306 -uroot -pmico -dtest -ttest --start-file='mysql-bin.000008' --start-datetime='2017-12-09 02:00:00' --stop-datetime='2017-12-09 02:01:00' DELETE FROM `test`.`test` WHERE `age`=20 AND `id`=2 AND `name`='cube' LIMIT 1; #start 1932 end 2105 time 2017-12-09 02:00:21【删除语句】 DELETE FROM `test`.`test` WHERE `age`=18 AND `id`=1 AND `name`='mico' LIMIT 1; #start 2136 end 2309 time 2017-12-09 02:00:21【删除语句】 - 由上一步可知，误操作的sql位置在1932到2309之间：
[root@localhost binlog2sql]# python binlog2sql.py -h192.168.10.220 -P3306 -uroot -pmico -dtest -ttest --start-file='mysql-bin.000008' --start-position=1932 --stop-position=2309 -B >t_rollback.sql [root@localhost binlog2sql]# cat t_rollback.sql INSERT INTO `test`.`test`(`age`, `id`, `name`) VALUES (18, 1, 'mico'); #start 2136 end 2309 time 2017-12-09 02:00:21 INSERT INTO `test`.`test`(`age`, `id`, `name`) VALUES (20, 2, 'cube'); #start 1932 end 2105 time 2017-12-09 02:00:21 - 使用回滚文件，恢复数据
mysql -uroot -pmico test < t_rollback.sql - 检验数据是否恢复成功
mysql> select * from test; +----+------+------+ | id | name | age | +----+------+------+ | 1 | mico | 18 | | 2 | cube | 20 | +----+------+------+ 2 rows in set (0.00 sec)
