[日期:2017-09-02] | [字体:大 中 小] |
DML(data manipulation language):
它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
DDL(data definition language):
DDL比DML要多,主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用
DCL(Data Control Language):
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。在默认状态下,只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL
从mysql binlog解析出你要的sql。根据不同选项,你可以得到原始sql、回滚sql、去除主键的insert sql等。
数据快速回滚(闪回)
主从切换后数据不一致的修复
从binlog生成标准SQL,带来的衍生功能
- # 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
- -------------安装pip工具-------------
- # wget https://bootstrap.pypa.io/get-pip.py
- # python get-pip.py
- # pip -V#查看pip版本
- 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))
select, super/replication client, replication slave权限
建议授权
- 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';
- 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
- 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
- create table cope_users like info_users; # 新建表
- insert into cope_users select * from info_users limit 500; # 插入500行数据
- delete from cope_users where id<20; # 删除20行数据
- # 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
- # 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
- 查看解析出的sql,如无误,可打印到sql文件中/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连接配置
- -h host; -P port; -u user; -p password
- 解析模式
- --realtime 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。
- --popPk 对INSERT语句去除主键。可选。
- -B, --flashback 生成回滚语句。可选。与realtime或popPk不能同时添加。
- 解析范围控制
- --start-file 起始解析文件。必须。
- --start-pos start-file的起始解析位置。可选。默认为start-file的起始位置;
- --end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为realtime,此选项失效。
- --end-pos end-file的末尾解析位置。可选。默认为end-file的最末位置;若解析模式为realtime,此选项失效。
- 对象过滤
- -d, --databases 只输出目标db的sql。可选。默认为空。
- -t, --tables 只输出目标tables的sql。可选。默认为空。
- #mysql - uroot - p000000 < /data/backup / rollback.sql
- server_id = 1
- log_bin = /data/mysql/mysql-bin.log
- max_binlog_size = 1G
- binlog_format = row
- binlog_row_image = full # 默认
因为它是通过 BINLOG_DUMP 协议来获取 binlog 内容,需要读取server端 information_schema.COLUMNS 表,来获取表结构的元信息,才能拼接成 SQL 语句。因此需要给用户提供的最小权限如下:
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO 'user'@'%';
源码中,主要是使用 python-mysql-replication 作为实时解析 MySQL binlog 来获取各个 EVENT。
python-mysql-replication 实现了 MySQL 复制协议,客户端伪装成 slave 来获取主的 binlog 和 EVENT。
一种情况例外:insert、updete、delete操作之后,drop/truncate table。 此时虽然在binlog中记录了所有的event,但是使用binlog2sql生成标准sql、回滚sql的时候已经找不到了dml操作的相应的表
来源: http://www.linuxidc.com/Linux/2017-09/146673.htm