主頁 > 知識庫 > mysql8.0.20配合binlog2sql的配置和簡單備份恢復的步驟詳解

mysql8.0.20配合binlog2sql的配置和簡單備份恢復的步驟詳解
熱門標簽：湖南人工外呼系統(tǒng)多少錢 申請外呼電話線路 百度地圖圖標標注中心 南通自動外呼系統(tǒng)軟件 廣東人工電話機器人 日照旅游地圖標注 石家莊電商外呼系統(tǒng) 信陽穩(wěn)定外呼系統(tǒng)運營商 芒果電話機器人自動化

第一步 安裝

 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.com/PyMySQL/PyMySQL/
Author: yutaka.matsubara
Author-email: yutaka.matsubara@gmail.com
License: "MIT"
Location: /usr/local/lib/python3.6/site-packages
Requires:

第二步 準備MySQL數(shù)據(jù)

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, 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> 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 查看剛才數(shù)據(jù)庫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備份數(shù)據(jù)庫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 --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

5.進入MySQL，恢復被刪除的數(shù)據(jù)

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配置和備份恢復內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:
  • 解說mysql之binlog日志以及利用binlog日志恢復數(shù)據(jù)的方法
  • MySQL使用binlog日志做數(shù)據(jù)恢復的實現(xiàn)
  • mysql利用mysqlbinlog命令恢復誤刪除數(shù)據(jù)的實現(xiàn)
  • MySQL使用mysqldump+binlog完整恢復被刪除的數(shù)據(jù)庫原理解析
  • Mysql的Binlog數(shù)據(jù)恢復:不小心刪除數(shù)據(jù)庫詳解
  • mysql如何利用binlog進行數(shù)據(jù)恢復詳解
  • Linux上通過binlog文件恢復mysql數(shù)據(jù)庫詳細步驟
  • MySQL數(shù)據(jù)庫遭到攻擊篡改(使用備份和binlog進行數(shù)據(jù)恢復)
  • 教你自動恢復MySQL數(shù)據(jù)庫的日志文件(binlog)
  • MySQL中的binlog相關命令和恢復技巧
  • MySQL數(shù)據(jù)庫恢復(使用mysqlbinlog命令)
  • MySQL通過binlog恢復數(shù)據(jù)

標簽：沈陽 惠州 天津 阿里 公主嶺 牡丹江 合肥 呼和浩特

巨人網(wǎng)絡通訊聲明：本文標題《mysql8.0.20配合binlog2sql的配置和簡單備份恢復的步驟詳解》，本文關鍵詞  mysql8.0.20,配合,binlog2sql,；如發(fā)現(xiàn)本文內容存在版權問題，煩請?zhí)峁┫嚓P信息告之我們，我們將及時溝通與處理。本站內容系統(tǒng)采集于網(wǎng)絡，涉及言論、版權與本站無關。
  • 相關文章
  • 下面列出與本文章《mysql8.0.20配合binlog2sql的配置和簡單備份恢復的步驟詳解》相關的同類信息！
  • 本頁收集關于mysql8.0.20配合binlog2sql的配置和簡單備份恢復的步驟詳解的相關信息資訊供網(wǎng)民參考！
    • 推薦文章