Detailed steps for configuring mysql8.0.20 with binlog2sql and simple backup and recovery

Detailed steps for configuring mysql8.0.20 with binlog2sql and simple backup and recovery

First step installation

1. Install MySQL

2. Install Python3

[root@localhost /]#yum install python3

3. Download the binlog2sql file to your local computer (the file is in Baidu Cloud)

[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. Modify requirements.txt in binlog2sql, change PyMySQL==0.7.11 to 0.9.3, save and exit

[root@localhost binlog2sql]# vi requirements.txt
PyMySQL==0.9.3
wheel==0.29.0
mysql-replication==0.13

5. Install and check to make sure it is 0.9.3 or there will be an error

[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: [email protected]
License: "MIT"
Location: /usr/local/lib/python3.6/site-packages
Requires:

Step 2: Prepare MySQL data

1. It is best to add the security directory secure-file-priv=/test to the configuration file and restart 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

Note: Everyone has a different configuration file path.

2. Enter 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;

The third step is to test the binlog2sql directory.

[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. Start the operation of backing up the tables in the database
2.1 View the operations under the database csdn just now

 [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 Operations under the backup database 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 View the sql file just backed up

[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. View the delete statement separately

[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. Save the delete statement in reverse to the sql file and view it

[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. Enter MySQL and restore deleted data

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)

Summarize

This is the end of this article about the configuration of mysql8.0.20 with binlog2sql and the detailed steps of simple backup and recovery. For more relevant mysql8.0.20 binlog2sql configuration and backup and recovery content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Explain MySQL's binlog log and how to use binlog log to recover data
  • MySQL uses binlog logs to implement data recovery
  • Implementation of mysql using mysqlbinlog command to restore accidentally deleted data
  • MySQL uses mysqldump+binlog to completely restore the deleted database principle analysis
  • MySQL Binlog Data Recovery: Detailed Explanation of Accidentally Deleting a Database
  • How to use binlog for data recovery in MySQL
  • Detailed steps to restore MySQL database through binlog files on Linux
  • MySQL database was attacked and tampered with (using backup and binlog for data recovery)
  • Teach you to automatically restore the log file (binlog) of the MySQL database
  • Binlog related commands and recovery techniques in MySQL
  • MySQL database recovery (using mysqlbinlog command)
  • MySQL restores data through binlog

<<:  How to configure Tomcat and run your first Java Web project on IntelliJ IDEA 2018

>>:  Common front-end JavaScript method encapsulation

Recommend

React nested component construction order

Table of contents In the React official website, ...

Solution to the problem "Table mysql.plugin doesn't exist" when deploying MySQL

Today I deployed the free-installation version of...

Detailed explanation of jQuery method attributes

Table of contents 1. Introduction to jQuery 2. jQ...

How to install Apache service in Linux operating system

Download link: Operating Environment CentOS 7.6 i...

Detailed explanation of the use of filter properties in CSS3

Recently, when I was modifying the intranet porta...

Detailed explanation of Nginx process scheduling problem

Nginx uses a fixed number of multi-process models...

Tutorial for installing MySQL 8.0.18 under Windows (Community Edition)

This article briefly introduces how to install My...

Detailed Introduction to the MySQL Keyword Distinct

Introduction to the usage of MySQL keyword Distin...

Teach you how to build Redis cluster mode and sentinel mode with docker in 5 minutes

Table of contents 1. Preparation Pull the redis i...

Detailed explanation of the use of router-view components in Vue

When developing a Vue project, you often need to ...

React tips teach you how to get rid of hooks dependency troubles

A very common scenario in react projects: const [...

CSS form validation function implementation code

Rendering principle In the form element, there is...

Maven project remote deployment && How to configure database connection using tomcat

one. Remote deployment using tomcat 1.1 Problems ...

Embed codes for several older players

The players we see on the web pages are nothing m...

Detailed explanation of several ways to install CMake on Ubuntu

apt install CMake sudo apt install cmake This met...