mysql8.0.20配合binlog2sql的配置和简单备份恢复的步骤详解

目录

第一步 安装第二步 准备MySQL数据第三步 测试 进入binlog2sql目录下的binlog2sql下

第一步 安装

1.安装MySQL

2.安装Python3

[root@localhost /]#yum install python33.下载binlog2sql文件到本地（文件在百度云盘）

[root@localhost /]#mkdir tools

[root@localhost /]#cd tools

[root@localhost tools]# ll

total 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]# ll

total 52

drwxr-xr-x. 3 mysql mysql 91 Jun 13 08:14 binlog2sql

drwxr-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.txt

drwxr-xr-x. 2 mysql mysql 37 Jun 13 07:45 tests4.修改binlog2sql中的requirements.txt，把PyMySQL==0.7.11改为0.9.3,保存退出

[root@localhost binlog2sql]# vi requirements.txt

PyMySQL==0.9.3

wheel==0.29.0

mysql-replication==0.135.安装和检查，确保是0.9.3 不然出错

[root@localhost binlog2sql]# pip3 install -r requirements.txt

[root@localhost binlog2sql]# pip3 show pymysql

Name: PyMySQL

Version: 0.9.3

Summary: Pure Python MySQL Driver

Home-page: https://github.com/PyMySQL/PyMySQL/

Author: yutaka.matsubara

Author-email: yutaka.matsubara@gmail.com

License: “MIT”

Location: /usr/local/lib/python3.6/site-packages

Requires:


第二步 准备MySQL数据

1.配置文件最好加入安全目录secure-file-priv=/test，重启MySQL

[root@localhost /]# mkdir test

[root@localhost /]# chown -R mysql.mysql test

[root@localhost mysqldata]#vi my.cnf

secure-file-priv=/test

basedir=/APPlication/mysql

datadir=/data/mysql

socket=/data/mysqldata/mysql.sock

log_error=/data/mysqldata/mysql8.0.err

port=3306

server_id=6

secure-file-priv=/test

autocommit=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 11

Server version: 8.0.20 MySQL Community Server – GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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 csdn

mysql> 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;


第三步 测试 进入binlog2sql目录下的binlog2sql下

[root@localhost binlog2sql]# pwd

/tools/binlog2sql/binlog2sql

[root@localhost binlog2sql]# ll

total 24

-rwxr-xr-x. 1 mysql mysql 7747 Jun 13 07:45 binlog2sql.py

-rwxr-xr-x. 1 mysql mysql 11581 Jun 13 07:45 binlog2sql_util.py

-rw-r–r–. 1 mysql mysql 92 Jun 13 07:45 __init__.py

drwxr-xr-x. 2 mysql mysql 44 Jun 13 07:50 __pycache__2.开始备份库下的表的操作

2.1 查看刚才数据库csdn下的操作

[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 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (2); #start 609 end 807 time 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (3); #start 609 end 807 time 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (4); #start 609 end 807 time 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (5); #start 609 end 807 time 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (6); #start 609 end 807 time 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (7); #start 609 end 807 time 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (8); #start 609 end 807 time 2022-09-25 02:21:21

UPDATE `csdn`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 917 end 1095 time 2022-09-25 02:21:39

DELETE FROM `csdn`.`t1` WHERE `id`=3 LIMIT 1; #start 917 end 1183 time 2022-09-25 02:21:482.2备份数据库csdn下的操作

[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.sql2.3 查看刚才备份的sql文件

[root@localhost binlog2sql]# cat /test/binlog2sql.sql

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 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (2); #start 609 end 807 time 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (3); #start 609 end 807 time 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (4); #start 609 end 807 time 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (5); #start 609 end 807 time 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (6); #start 609 end 807 time 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (7); #start 609 end 807 time 2022-09-25 02:21:21

INSERT INTO `csdn`.`t1`(`id`) VALUES (8); #start 609 end 807 time 2022-09-25 02:21:21

UPDATE `csdn`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 917 end 1095 time 2022-09-25 02:21:39

DELETE FROM `csdn`.`t1` WHERE `id`=3 LIMIT 1; #start 917 end 1183 time 2022-09-25 02:21:483.单独查看删除语句

[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

USE 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 2022-09-25 02:21:484.把删除语句反转保存到sql文件中，并且查看

[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 2022-09-25 02:21:485.进入MySQL，恢复被删除的数据

mysql> source /test/roll.sql

Query 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)
总结

到此这篇关于mysql8.0.20配合binlog2sql的配置和简单备份恢复的步骤详解的文章就介绍到这了,更多相关mysql8.0.20 binlog2sql配置和备份恢复内容请搜索共生网络以前的文章或继续浏览下面的相关文章希望大家以后多多支持共生网络！