1. Overall explanation:
DML(data manipulation language):
They are SELECT, UPDATE, INSERT, DELETE and, like their names, are the languages used to manipulate data in a database
DDL(data definition language):
There are more DDLs than DML s. The main commands are CREATE, ALTER, DROP, etc. DDL is mainly used to define or change the structure of tables (TABLE), data types, links and constraints between tables, etc. They are mostly used when building tables.
DCL(Data Control Language):
Is a database control function.Is a statement used to set or change database user or role permissions, including (grant,deny,revoke, and so on).By default, only sysadmin,dbcreator,db_owner, or db_securityadmin are authorized to execute DCL
2. Bilog2sql Installation
Resolve your SQL from mysql binlog.Depending on the options, you can get the original sql, rollback sql, insert sql with the primary key removed, and so on.
2.1. Purpose
_Fast data rollback (flash back)
_Repair of data inconsistency after master-slave switching
_Derived functionality of generating standard SQL from binlog
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. Set of minimum permissions required by user:
select, super/replication client, replication slave permissions
Recommended Authorization
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 out 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
Resolve 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. Testing:
3.1. New table users
create table cope_users like info_users; # 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`='A Chao' 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`='Parents 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. Resolve 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 //View the parsed SQL and, if correct, print to 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, 'Chen Junyu', '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 --realtime keeps synchronizing binlog.Optional.Synchronize without adding to the latest binlog location when the command is executed. --popPk removes the primary key from the INSERT statement.Optional. -B, --flashback generates a rollback statement.Optional.It cannot be added at the same time as realtime or popPk. Resolution Range Control --start-file starts parsing the file.Must. --start-pos start-file's starting parse location.Optional.The default is the starting location of the start-file; --end-file parses the file at the end.Optional.The default is start-file with the same file.This option is not valid if the resolution mode is realtime. --end-pos end-file end parse position.Optional.Default to the last location of the end-file; this option is disabled if the parsing mode is realtime. Object filtering -d, --databases only outputs sql for target db.Optional.The default is empty. -t, --tables only outputs sql for the target tables.Optional.The default is empty.
3.4, Start Rollback
# mysql -uroot -p000000 < /data/backup/rollback.sql
3.5. Logon database validation
4. Notes
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. MySQL service must be started when flashback occurs
Because it obtains binlog content through the BINLOG_DUMP protocol, it needs to read the server-side information_schema.COLUMNS table to get meta-information of the table structure before it can be stitched into a SQL statement.So the minimum privileges you need to give users are as follows:
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'%';
The source code mainly uses python-mysql-replication as the real-time parsing MySQL binlog to get each EVENT.
python-mysql-replication implements the MySQL replication protocol, with clients masquerading as slave s to obtain the master binlog and EVENT.
4.3, insert, update, delete can resolve standard sql and rollback sql most of the time
An exception is drop/truncate table after insert, updete, delete operations.Although all event s are recorded in binlog at this time, the corresponding tables for dml operations cannot be found when using binlog2sql to generate standard SQL and rollback SQL