Detailed explanation of how to use binlog2sql to quickly roll back after MySQL misoperation
|
Preface In daily work or study, it is inevitable that you will make mistakes due to "carelessness" when operating the database. If you need to recover quickly, it is unlikely to recover through backup. The following article mainly introduces the method of using binlog2sql to quickly roll back after Mysql misoperation. Without further ado, let's take a look at the detailed introduction: 1. General explanation: DML (data manipulation language): They are SELECT, UPDATE, INSERT, and DELETE. As their names suggest, these four commands are used to operate on the data in the database. DDL（data definition language）： DDL has more commands than DML. The main commands are CREATE, ALTER, DROP, etc. DDL is mainly used to define or change the structure of a table, data types, links and constraints between tables, and other initialization tasks. They are mostly used when creating a table. DCL（Data Control Language）： It is a database control function. It is used to set or change the permissions of database users or roles, including (grant, deny, revoke, etc.) statements. By default, only sysadmin, dbcreator, db_owner, or db_securityadmin have the authority to execute DCL. 2. Installation of binlog2sql Parse the sql you want from mysql binlog. Depending on the options, you can get the original SQL, rollback SQL, insert SQL without primary key, etc. 2.1. Purpose
2.2 Installation # cd /usr/local # git clone https://github.com/danfengcao/binlog2sql.git # ls binlog2sql games java lib64 mariadb sbin src # cd binlog2sql # pip install -r requirements.txt -bash: pip: command not found -------------Install pip tool------------- # wget https://bootstrap.pypa.io/get-pip.py # python get-pip.py # pip -V # View pip version pip 9.0.1 from /usr/lib/python2.7/site-packages (python 2.7) # pip install -r requirements.txt Requirement already satisfied: PyMySQL==0.7.8 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 1)) Requirement already satisfied: wheel==0.24.0 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 2)) Requirement already satisfied: mysql-replication==0.9 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 3)) 2.3. The minimum set of permissions required by the user: It is recommended to grant select, super/replication client, replication slave permissions mysql > GRANT SELECT,REPLICATION SLAVE,REPLICATION CLIENT ON *.* to flashback@'localhost' identified by 'flashback'; mysql > GRANT SELECT,REPLICATION SLAVE,REPLICATION CLIENT ON *.* to flashback@'127.0.0.1' identified by 'flashback'; 2.4 Basic Usage Parse standard SQL shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -ddatabase -t table1 table2 --start-file='mysql-bin.000002' --start-datetime='2017-01-12 18:00:00' --stop-datetime='2017-01-12 18:30:00' --start-pos=1240 Parse the rollback SQL shell> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147 3. Test: 3.1. Create a new table users create table cope_users like info_users; # Create a new table insert into cope_users select * from info_users limit 500; # Insert 500 rows of data delete from cope_users where id<20; # Delete 20 rows of data 3.2. Parsing standard SQL # python /usr/local/binlog2sql/binlog2sql/binlog2sql.py -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-datetime='2017-07-11 15:10:00' --stop-datetime='2017-07-11 15:12:00' DELETE FROM `ttt`.`users` WHERE `uid`='0e8e2609c748bbb052d7' AND `ip`='172.16.208.32' AND `sex`=0 AND `app_ver`='5.2.3' AND `device_type`=2 AND `guides`='' AND `last_login_time`=1481602129 AND `id`=1 AND `latitude`='' AND `add_time`=1481602080 AND `recharge_time`=0 AND `token_change_time`=1481602129 AND `expire_time`=0 AND `nickname`='阿超' AND `device_id`='cc0e154d9b5dd703eccc7d8a0dbc0f67d64b79e8' AND `push_key`='' AND `level`=0 AND `mobile`='18810895535' AND `settings`='' AND `longitude`='' AND `signature`='' AND `os_ver`='' LIMIT 1; #start 79078 end 83053 time 2017-07-11 15:11:50 DELETE FROM `ttt`.`users` WHERE `uid`='b5cfbdb4205b56703a97' AND `ip`='172.16.208.48' AND `sex`=0 AND `app_ver`='5.2.2' AND `device_type`=2 AND `guides`='' AND `last_login_time`=1481602096 AND `id`=2 AND `latitude`='' AND `add_time`=1481602096 AND `recharge_time`=0 AND `token_change_time`=1481602096 AND `expire_time`=0 AND `nickname`='家长091410' AND `device_id`='fedea666076a7906be53523acc7a8b32811354fe' AND `push_key`='7759d6772c9851a2bfc13835a3d7e7da' AND `level`=0 AND `mobile`='13629470521' AND `settings`='' AND `longitude`='' AND `signature`='' AND `os_ver`='' LIMIT 1; #start 79078 end 83053 time 2017-07-11 15:11:50 3.3. Parse the rollback SQL # python /usr/local/binlog2sql/binlog2sql/binlog2sql.py --flashback -h127.0.0.1 -P3306 -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-position=79078 --stop-position=83053 Check the parsed sql, if it is correct, you can print it into the sql file /data/backup/rollback.sql
# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py --flashback -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-position=79078 --stop-position=83053> /data/backup/rollback.sql
# cat /data/backup/rollback.sql
`id`, `latitude`, `add_time`, `recharge_time`, `token_change_time`, `expire_time`, `nickname`, `device_id`, `push_key`, `level`, `mobile`, `settings`, `longitude`, `signature`, `os_ver`) VALUES ('24667530f4b16a446b3e', '172.16.218.75', 0, '5.2.93', 3, '{\"2103\":1,\"2100\":1,\"2101\":1,\"2102\":1,\"2104\":1,\"2105\":1}', 1490239125, 19, '', 1481610680, 0, 1490239125, 0, 'zf', 'da75b093-bd22-48f6-bbb1-d3296e29e9b5', 'be05183f80a96e788e0b0a99d1275392', 0, '15101538925', '', '', '', ''); #start 79078 end 83053 time 2017-07-11 15:11:50
INSERT INTO `ttt`.`users`(`uid`, `ip`, `sex`, `app_ver`, `device_type`, `guides`, `last_login_time`, `id`, `latitude`, `add_time`, `recharge_time`, `token_change_time`, `expire_time`, `nickname`, `device_id`, `push_key`, `level`, `mobile`, `settings`, `longitude`, `signature`, `os_ver`) VALUES ('77e50b4910a9389057ed', '172.16.218.37', 0, '5.2.1.14', 3, '', 1488787835, 18, '39.978212', 1481610517, 0, 1488787835, 0, '陈俊宇', 'ed0a273d-74de-4173-92c6-55d92597bc79', '', 0, '18612482272', '', '116.306826', '', ''); #start 79078 end 83053 time 2017-07-11 15:11:50 mysql connection configuration -h host; -P port; -u user; -p password Parsing Mode
Resolution range control
Object filtering
3.4. Start rolling back # mysql -uroot -p000000 < /data/backup/rollback.sql 3.5. Login database verification IV. Precautions 4.1. The following parameters are set in the configuration file: server_id = 1 log_bin = /data/mysql/mysql-bin.log max_binlog_size = 1G binlog_format = row binlog_row_image = full # default 4.2. The MySQL service must be started during flashback Because it obtains binlog content through the BINLOG_DUMP protocol, it needs to read the server-side information_schema.COLUMNS table to obtain the meta information of the table structure before it can be spliced into SQL statements. Therefore, the minimum permissions required to be provided to users are as follows: GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'%'; In the source code, python-mysql-replication is mainly used as real-time parsing of MySQL binlog to obtain each EVENT. python-mysql-replication implements the MySQL replication protocol. The client pretends to be a slave to obtain the master's binlog and EVENT. 4.3. Most of the time, standard SQL and rollback SQL can be parsed for insert, update, and delete. One exception: after insert, update, delete operations, drop/truncate table. Although all events are recorded in binlog at this time, when binlog2sql is used to generate standard SQL and rollback SQL, the corresponding table of DML operation can no longer be found. 4.4. DDL cannot use binlog2sql to flash back data. Summarize The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM. You may also be interested in:
|
<<: Explanation of the new feature of Hadoop 2.X, the recycle bin function
>>: Explanation of the working mechanism of namenode and secondarynamenode in Hadoop
Recommend
The principle and implementation of js drag effect
The drag function is mainly used to allow users t...
Use personalized search engines to find the personalized information you need
Many people now live on the Internet, and searchin...
Detailed explanation of querying JSON format fields in MySQL
During the work development process, a requiremen...
WeChat applet custom tabBar step record
Table of contents 1. Introduction 2. Customize ta...
Implementation of adding a mask layer effect when the CSS mouse hovers over the image
First look at the effect: When the mouse moves ov...
Example of how to automatically start an application service in a Docker container
If you want the application service in the Docker...
Example of using Dockerfile to build an nginx image
Introduction to Dockerfile Docker can automatical...
CSS sprites technology integrates multiple backgrounds into one PNG image CSS positioning
The image integration technology used by American...
Detailed steps to install mysql in Win
This article shares the detailed steps of install...
Summary of Linux file basic attributes knowledge points
The Linux system is a typical multi-user system. ...
Whitespace processing in HTML/CSS and how to preserve whitespace in the page
Whitespace rules in HTML In HTML, multiple spaces...
JavaScript history object explained
Table of contents 1. Route navigation 2. History ...
Debian virtual machine created by VirtualBox shares files with Windows host
the term: 1. VM: Virtual Machine step: 1. Downloa...
JavaScript MouseEvent Case Study
MouseEvent When the mouse performs a certain oper...
Some suggestions for HTML beginners and novices, experts can ignore them
Feelings: I am a backend developer. Sometimes when...