Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use MySQL bingol". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian slowly and deeply to study and learn "how to use MySQL bingol" together!

1. Binlog backup

[mysql@mysql5 binlog]$mysqlbinlog --read-from-remote-server --raw --host=192.168.1.210 --port=65535 --user=yang --password=oracle --stop-never --result-file=/mysqldb/backup/backup_192.168.1.210/binlog/ master-bin.000001

2. Parse binlog

[mysql@mysql5 binlog]$ mysqlbinlog -v -v -v master-bin.000007 > 07.txt

[mysql@mysql5 binlog]$ vi 07.txt

/*! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*! 50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#180807 11:18:46 server id 6666601 end_log_pos 123 CRC32 0x67606c44 Start: binlog v 4, server v 5.7.23-log created 180807 11:18:46

BINLOG '

lg9pWw9puWUAdwAAAHsAAAAAAAQANS43LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AURsYGc=

'/*!*/;

# at 123

#180807 11:18:46 server id 6666601 end_log_pos 194 CRC32 0xcc8d05b0 Previous-GTIDs

# 27b983f4-99ed-11e8-ab5d-000c29e42a1f:1-16

# at 194

#180807 11:37:34 server id 6666601 end_log_pos 259 CRC32 0x440038b5 GTID last_committed=0 sequence_number=1 rbr_only=no

SET @@SESSION.GTID_NEXT= '27b983f4-99ed-11e8-ab5d-000c29e42a1f:17'/*!*/;

............

3. binlog recovery

[mysql@mysql5 binlog]$ mysqlbinlog --start-position=161899 --stop-position=323048 --skip-gtids /mysqldb/db_slave/binlog/slave-bin.000001|mysql -uroot -p -S/mysqldb/db_test01/mysql.sock employees

experimental

1. Import employees database

2. Create a table

mysql> create table test1 like dept_emp;

Query OK, 0 rows affected (0.85 sec)

mysql> alter table test1 drop primary key;

Query OK, 0 rows affected (0.31 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into test1 select * from dept_emp limit 10000;

Query OK, 10000 rows affected (0.16 sec)

Records: 10000 Duplicates: 0 Warnings: 0

3. mysqldump backup employees database

[mysql@mysql5 db_slave]$ mysqldump -uroot -p -S/mysqldb/db_slave/mysql.sock --master-data=2 --single-transaction --databases employees --set-gtid-purged=off > testdump.sql

Enter password:

4. Analog business

mysql> insert into test1 select * from dept_emp limit 10000;

Query OK, 10000 rows affected (0.50 sec)

Records: 10000 Duplicates: 0 Warnings: 0

mysql> delete from test1 limit 1000;

Query OK, 1000 rows affected (0.30 sec)

mysql> create table test2 like dept_emp;

Query OK, 0 rows affected (0.27 sec)

5. Find problems and roll back

MASTER_LOG_POS=161899

at 323048

[mysql@mysql5 binlog]$ mysqlbinlog -v -v -v slave-bin.000001 > 1.txt

[mysql@mysql5 binlog]$ vi 1.txt

[mysql@mysql5 db_slave]$ vi testdump.sql

mysql> source/mysqldb/db_slave/testdump.sql #empty instance #

[mysql@mysql5 binlog]$ mysqlbinlog --start-position=161899 --stop-position=323048 --skip-gtids /mysqldb/db_slave/binlog/slave-bin.000001|mysql -uroot -p -S/mysqldb/db_test01/mysql.sock employees

5. Results

6. Import deleted data

[mysql@mysql5 ~]$ mysqldump -uroot -p -S /mysqldb/db_test01/mysql.sock employees test1 --where="1=1 limit 1000;" --set-gtid-purged=OFF > test3.sql

Enter password:

[mysql@mysql5 ~]$ vi test3.sql

[mysql@mysql5 ~]$ mysql -uroot -p -S /mysqldb/db_slave/mysql.sock --database=employees --table=test1 < test3.sql

mysql: [Warning] mysql: ignoring option '--table' due to invalid value 'test1'

Enter password

Thank you for reading, the above is "how to use MySQL bingol" content, after the study of this article, I believe that everyone on how to use MySQL bingol this problem has a deeper experience, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.