安装pip,参照安装网址:https://pip.pypa.io/en/stable/installing/
curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py python get-pip.py
安装git: yum install git -y
安装binlog2sql,参照网站:https://github.com/danfengcao/binlog2sql
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql pip install -r requirements.txt
详细的使用方法请参考官网:https://github.com/danfengcao/binlog2sql 另外,binlog2sql不需每台机器都安装,可以只安装在一台机器上面,分析其它数据库的日志,下面列出一些常用的使用选项:
1.查看指定的数据库,多个数据库用空格发开: python binlog2sql.py -h192.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008' 多个数据库: python binlog2sql.py -h192.168.56.201 -P3306 -uroot -p'MYsql#123' -d app data --start-file='mysql-bin.000008'
生产闪回的sql加参数-B: python binlog2sql.py -B -h192.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008'
2.查看指定数据库下面指定的表的操作语句,多个表用逗号隔开: python binlog2sql.py -h192.168.56.201 -P3306 -uroot -p'MYsql#123' -d app -t t_user_info --start-file='mysql-bin.000008' 多个表: python binlog2sql.py -h192.168.56.201 -P3306 -uroot -p'MYsql#123' -d app -t t_user_info t_product_info --start-file='mysql-bin.000008'
生产闪回的sql加参数-B python binlog2sql.py -B -h192.168.56.201 -P3306 -uroot -p'MYsql#123' -d app -t t_user_info t_product_info --start-file='mysql-bin.000008'
3.指定binlog的pos点进行闪回--start-position=xxx --stop-position=xxxx python binlog2sql.py -B -h192.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008' --start-position=1219 --stop-position=1410
指定binglog的时间进行闪回--start-datetime="2019-01-09 15:55:41" --stop-datetime="2019-01-09 16:06:48" python binlog2sql.py -B -h192.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008' --start-datetime="2019-01-09 14:58:47" --stop-datetime="2019-01-09 15:02:37"
4.指定多个binlog日志: python binlog2sql.py -B -h192.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008' --stop-file='mysql-bin.000009'
5.binlog2sql在ddl进行操作时候也可以记录下来,如创建表,修改表,创建用户等,但是闪回只支持dml,其实看到ddl自己也明白了什么回事,如果不想输出ddl, 可以使用--only-dml参数进行控制: python binlog2sql.py --only-dml -h192.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008' --stop-file='mysql-bin.000010'
6.过滤指定的dml操作insert,update,delete: python binlog2sql.py --only-dml --sql-type INSERT UPDATE DELETE -h192.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008' --stop-file='mysql-bin.000010'
7.以下为binlog2sql输出的内容供实例参考:
python binlog2sql.py -h192.168.56.201 -P3306 -uroot -p'MYsql#123' -d app -t t_user_info --start-file='mysql-bin.000008'
USE app;
create database app;
CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'FEE4A573DDC077A021A143383A52AAB67E75FA7C';
GRANT ALL PRIVILEGES ON . TO 'root'@'%';
USE app;
CREATE TABLE t_user_info
(
id
int NOT NULL AUTO_INCREMENT ,
name
varchar(255) NULL ,
address
varchar(255) NULL ,
PRIMARY KEY (id
)
);
INSERT INTO app
.t_user_info
(address
, id
, name
) VALUES ('shanghai', 1, '张三'); #start 1219 end 1407 time 2019-01-09 14:58:46
USE data;
DROP TABLE t_data
/ generated by server */;
USE app;
TRUNCATE table t_product_info;
8.binlog2sql的具体用法请参考; https://github.com/danfengcao/binlog2sql
或者 ./binlog2sql.py --help ./binlog2sql.py --help usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]] [-P PORT] [--start-file START_FILE] [--start-position START_POS] [--stop-file END_FILE] [--stop-position END_POS] [--start-datetime START_TIME] [--stop-datetime STOP_TIME] [--stop-never] [--help] [-d [DATABASES [DATABASES ...]]] [-t [TABLES [TABLES ...]]] [--only-dml] [--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B] [--back-interval BACK_INTERVAL]
Parse MySQL binlog to SQL you want
optional arguments: --stop-never Continuously parse binlog. default: stop at the latest event when you start. --help help information -K, --no-primary-key Generate insert sql without primary key if exists -B, --flashback Flashback data to start_position of start_file --back-interval BACK_INTERVAL Sleep time between chunks of 1000 rollback sql. set it to 0 if do not need sleep
connect setting: -h HOST, --host HOST Host the MySQL database server located -u USER, --user USER MySQL Username to log in as -p [PASSWORD [PASSWORD ...]], --password [PASSWORD [PASSWORD ...]] MySQL Password to use -P PORT, --port PORT MySQL port to use
interval filter: --start-file START_FILE Start binlog file to be parsed --start-position START_POS, --start-pos START_POS Start position of the --start-file --stop-file END_FILE, --end-file END_FILE Stop binlog file to be parsed. default: '--start-file' --stop-position END_POS, --end-pos END_POS Stop position. default: latest position of '--stop- file' --start-datetime START_TIME Start time. format %Y-%m-%d %H:%M:%S --stop-datetime STOP_TIME Stop Time. format %Y-%m-%d %H:%M:%S;
schema filter: -d [DATABASES [DATABASES ...]], --databases [DATABASES [DATABASES ...]] dbs you want to process -t [TABLES [TABLES ...]], --tables [TABLES [TABLES ...]] tables you want to process
type filter: --only-dml only print dml, ignore ddl --sql-type [SQL_TYPE [SQL_TYPE ...]] Sql type you want to process, support INSERT, UPDATE, DELETE.