mysql8.0.20配合binlog2sql的配置和简单备份恢复的步
网络编程 2021-07-05 14:37www.168986.cn编程入门
这篇文章主要介绍了mysql8.0.20配合binlog2sql的配置和简单备份恢复的步骤,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
第一步 安装
1.安装MySQL
2.安装Python3
[root@localhost /]#yum install python3
3.下载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 tests
4.修改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.13
5.安装和检查,确保是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./PyMySQL/PyMySQL/ Author: yutaka.matsubara Author-email: yutaka.matsubara@gmail. 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.f 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 automit=0 log_bin=/data/mysqldata/mysql-bin [root@localhost mysqldata]# systemctl start mysqld
注每个人都配置文件路径都不一样
2.进入MySQL
Wele 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, 2020, 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> mit; mysql> update t1 set id=10 where id=1; mysql> delete from t1 where id=3; mysql> mit;
第三步 测试 进入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 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (2); #start 609 end 807 time 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (3); #start 609 end 807 time 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (4); #start 609 end 807 time 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (5); #start 609 end 807 time 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (6); #start 609 end 807 time 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (7); #start 609 end 807 time 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (8); #start 609 end 807 time 2020-09-25 02:21:21 UPDATE `csdn`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 917 end 1095 time 2020-09-25 02:21:39 DELETE FROM `csdn`.`t1` WHERE `id`=3 LIMIT 1; #start 917 end 1183 time 2020-09-25 02:21:48
2.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.sql
2.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 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (2); #start 609 end 807 time 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (3); #start 609 end 807 time 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (4); #start 609 end 807 time 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (5); #start 609 end 807 time 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (6); #start 609 end 807 time 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (7); #start 609 end 807 time 2020-09-25 02:21:21 INSERT INTO `csdn`.`t1`(`id`) VALUES (8); #start 609 end 807 time 2020-09-25 02:21:21 UPDATE `csdn`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 917 end 1095 time 2020-09-25 02:21:39 DELETE FROM `csdn`.`t1` WHERE `id`=3 LIMIT 1; #start 917 end 1183 time 2020-09-25 02:21:48
3.单独查看删除语句
[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 2020-09-25 02:21:48
4.把删除语句反转保存到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 --s-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
5.进入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配置和备份恢复内容请搜索狼蚁SEO以前的文章或继续浏览狼蚁网站SEO优化的相关文章希望大家以后多多支持狼蚁SEO!
上一篇:详解mysql慢日志查询
下一篇:MySQL索引失效的几种情况汇总
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南