mysql8.0.20配合binlog2sql的配置和简单备份恢复的步骤详解
16lz
2021-12-16
第一步 安装
1.安装MySQL
2.安装Python3
[root@localhost /]#yum install python3
[root@localhost /]#mkdir tools[root@localhost /]#cd tools[root@localhost tools]# lltotal 317440-rw-r--r--. 1 root root 317440 Sep 21 23:55 binlog2sql.tar[root@localhost tools]#tar -xvf binlog2sql.tar[root@localhost tools]#cd binlog2sql[root@localhost binlog2sql]# lltotal 52drwxr-xr-x. 3 mysql mysql 91 Jun 13 08:14 binlog2sqldrwxr-xr-x. 2 mysql mysql 54 Jun 13 07:45 example-rw-r--r--. 1 mysql mysql 35141 Jun 13 07:45 LICENSE-rw-r--r--. 1 mysql mysql 9514 Jun 13 07:45 README.md-rw-r--r--. 1 mysql mysql 54 Jun 13 07:45 requirements.txtdrwxr-xr-x. 2 mysql mysql 37 Jun 13 07:45 tests
[root@localhost binlog2sql]# vi requirements.txtPyMySQL==0.9.3wheel==0.29.0mysql-replication==0.13
[root@localhost binlog2sql]# pip3 install -r requirements.txt[root@localhost binlog2sql]# pip3 show pymysqlName: PyMySQLVersion: 0.9.3Summary: Pure Python MySQL DriverHome-page: https://github.com/PyMySQL/PyMySQL/Author: yutaka.matsubaraAuthor-email: yutaka.matsubara@gmail.comLicense: "MIT"Location: /usr/local/lib/python3.6/site-packagesRequires:
[root@localhost /]# mkdir test[root@localhost /]# chown -R mysql.mysql test[root@localhost mysqldata]#vi my.cnfsecure-file-priv=/testbasedir=/application/mysqldatadir=/data/mysqlsocket=/data/mysqldata/mysql.socklog_error=/data/mysqldata/mysql8.0.errport=3306server_id=6secure-file-priv=/testautocommit=0 log_bin=/data/mysqldata/mysql-bin [root@localhost mysqldata]# systemctl start mysqld
2.进入MySQL
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 11Server version: 8.0.20 MySQL Community Server - GPLCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show master status\g;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 156 | | | |+------------------+----------+--------------+------------------+-------------------+mysql> create database csdn;mysql> use csdnmysql> insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8);mysql> commit;mysql> update t1 set id=10 where id=1;mysql> delete from t1 where id=3;mysql> commit;
[root@localhost binlog2sql]# python3 binlog2sql.py -h 192.168.0.112 -P3306 -uroot -p123 -d csdn -t t1 --start-file='mysql-bin.000001'USE b'csdn';create database csdn;USE b'csdn';create table t1 (id int);INSERT INTO `csdn`.`t1`(`id`) VALUES (1); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (2); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (3); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (4); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (5); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (6); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (7); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (8); #start 609 end 807 time 2020-09-25 02:21:21UPDATE `csdn`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 917 end 1095 time 2020-09-25 02:21:39DELETE FROM `csdn`.`t1` WHERE `id`=3 LIMIT 1; #start 917 end 1183 time 2020-09-25 02:21:48
[root@localhost binlog2sql]# python3 binlog2sql.py -h 192.168.0.112 -P3306 -uroot -p123 -d csdn -t t1 --start-file='mysql-bin.000001' >/test/binlog2sql.sql
[root@localhost binlog2sql]# cat /test/binlog2sql.sqlUSE b'csdn';create database csdn;USE b'csdn';create table t1 (id int);INSERT INTO `csdn`.`t1`(`id`) VALUES (1); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (2); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (3); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (4); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (5); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (6); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (7); #start 609 end 807 time 2020-09-25 02:21:21INSERT INTO `csdn`.`t1`(`id`) VALUES (8); #start 609 end 807 time 2020-09-25 02:21:21UPDATE `csdn`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 917 end 1095 time 2020-09-25 02:21:39DELETE FROM `csdn`.`t1` WHERE `id`=3 LIMIT 1; #start 917 end 1183 time 2020-09-25 02:21:48
[root@localhost binlog2sql]# python3 binlog2sql.py -h 192.168.0.112 -P3306 -uroot -p123 -d csdn -t t1 --start-file='mysql-bin.000001' --sql-type=deleteUSE b'csdn';create database csdn;USE b'csdn';create table t1 (id int);DELETE FROM `csdn`.`t1` WHERE `id`=3 LIMIT 1; #start 917 end 1183 time 2020-09-25 02:21:48
[root@localhost binlog2sql]# python3 binlog2sql.py -h 192.168.0.112 -P3306 -uroot -p123 -d csdn -t t1 --start-file='mysql-bin.000001' --sql-type=delete --start-position=917 --stop-position=1183 -B >/test/roll.sql[root@localhost binlog2sql]# cat /test/roll.sql INSERT INTO `csdn`.`t1`(`id`) VALUES (3); #start 917 end 1183 time 2020-09-25 02:21:48
mysql> source /test/roll.sqlQuery OK, 1 row affected (0.00 sec)mysql> select * from t1;+------+| id |+------+| 10 || 2 || 4 || 5 || 6 || 7 || 8 || 3 |+------+8 rows in set (0.00 sec)
更多相关文章
- MySQL系列多表连接查询92及99语法示例详解教程
- Android(安卓)- Manifest 文件 详解
- Android的Handler机制详解3_Looper.looper()不会卡死主线程
- Selector、shape详解（一）
- android2.2资源文件详解4--menu文件夹下的菜单定义
- Android发送短信方法实例详解
- Android(安卓)读取资源文件实例详解
- 详解Android中的屏幕方向
- Android学习笔记(10)————Android的Listview详解1（ArrayAdapte