binlog2sql参考

安装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/binlog2sqlgit clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt详细的使用方法请参考官网：https://github.com/danfengcao/binlog2sql
另外，binlog2sql不需每台机器都安装，可以只安装在一台机器上面，分析其它数据库的日志，下面列出一些常用的使用选项：1.查看指定的数据库，多个数据库用空格发开：
python binlog2sql.py -h292.168.56.201 -P3306 -uroot -p’MYsql#123′ -d app –start-file=’mysql-bin.000008′
多个数据库：
python binlog2sql.py -h292.168.56.201 -P3306 -uroot -p’MYsql#123′ -d app data –start-file=’mysql-bin.000008’生产闪回的sql加参数-B：
python binlog2sql.py -B -h292.168.56.201 -P3306 -uroot -p’MYsql#123′ -d app –start-file=’mysql-bin.000008’2.查看指定数据库下面指定的表的操作语句，多个表用逗号隔开：
python binlog2sql.py -h292.168.56.201 -P3306 -uroot -p’MYsql#123′ -d app -t t_user_info –start-file=’mysql-bin.000008′
多个表：
python binlog2sql.py -h292.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 -h292.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 -h292.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 -h292.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 -h292.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 -h292.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 -h292.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 -h292.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 [DATABA开发云主机域名SES [DATABASES …]]]
[-t [TABLES [TABLES …]]] [–only-dml]
[–sql-type [SQL_TYPE [SQL_TYPE …]]] [-K] [-B]
[–back-interval BACK_INTERVAL]Parse MySQL binlog to SQL you wantoptional 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 sleepconnect 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 useinterval 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 processtype filter:
–only-dml only print dml, ignore ddl
–sql-type [SQL_TYPE [SQL_TYPE …]]
Sql type you want to process, support INSERT, UPDATE,
DELETE.

相关推荐: Mysql 高级

sql_mode 是一个容易忽视的变量，默认情况下为空，可以忍耐一些非法操作，在生产环境中，必须将其设置为严格模式，在开发测试环境中配该变量也是很有必要的，因为这样可以在生产之前发现问题。sql_mode 常用值如下：和其它数据库相比，MySQL 有点与众不同…