文章目录
binlog
查看所有binlog文件
show binary logs;
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 3116921 | No |
| binlog.000002 | 1150 | No |
| binlog.000003 | 1362 | No |
| binlog.000004 | 179 | No |
| binlog.000005 | 200 | No |
| binlog.000006 | 13098 | No |
| binlog.000007 | 2187 | No |
| binlog.000008 | 156 | No |
| binlog.000009 | 156 | No |
+---------------+-----------+-----------+
9 rows in set (0.01 sec)
查看binlog状态
show master status ;
# 结果
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000009 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
查看某个binlog内容
show binlog events in 'binlog的名字'
mysqlbinlog
mysqlbinlog使用
-
生成
.sql
文件mysqlbinlog --start-position=219 --stop-position=1347 /var/lib/mysql/bin.000003 >/home/rollback.sql
-
使用文件恢复数据
mysql -uroot -proot < rollback.sql
-
使用gtid生成恢复文件
#截取 1-20号gtid,跳过 14 ,18号gtid mysqlbinlog --skip-gtids --include-gtids='483e9795-ad5b-11ea-86e8- 000c298e182d:1-20' --exclude-gtids='483e9795-ad5b-11ea-86e8-000c298e182d:14','483e9795-ad5b-11ea86e8-000c298e182d:18' mysql-bin.000009 >/home/rollback.sql
mysqlbinlog的命令参数:
普通参数
-h
或--host
:MySQL服务器的主机名或IP地址。-u
或--user
:连接到服务器时使用的MySQL用户名。-p
或--password
:连接到服务器时使用的密码。-P
或--port
:连接所使用的TCP/IP端口号。-v
或--verbose
:启用详细输出。-t
或--to-last-log
:仅显示最后一个日志文件中的事件。-d
或--database
:仅显示指定数据库的事件。-R
或--result-file
:将输出写入指定文件。--base64-output
:控制二进制日志事件的输出格式。-j
或--hexdump
:以十六进制格式显示事件的内容。--start-datetime
:仅显示指定日期时间之后的事件。--stop-datetime
:仅显示指定日期时间之前的事件。--start-position
:仅显示指定位置之后的事件。--stop-position
:仅显示指定位置之前的事件。--no-defaults
:不读取任何选项文件。
gtid
gtid是mysql做的加强,GTID = server_uuid :transaction_id,开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了
开启gtid
在my.cnf
加入以下参数,
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
mysqlbinlog
使用gtid的参数(mysql5.6)
--include-gtids
:mysqlbinlog
将仅包含与指定 GTID 匹配的事件。GTID 必须作为逗号分隔的值列表提供。--exclude-gtids
:mysqlbinlog
将排除所有与指定 GTID 匹配的事件。GTID 必须作为逗号分隔的值列表提供。--skip-gtids
:mysqlbinlog
将跳过所有与指定 GTID 匹配的事件。GTID 必须作为逗号分隔的值列表提供。
binlog2sql
这是一个帮助解析binlog的开源py项目。
下载安装
下载或克隆binlog2sql安装包
git clone https://github.com/danfengcao/binlog2sql.git
修改requirements.txt
这里需要修改一下PyMySQL的版本,改成支持mysql8.0的0.9.0
版本,也不要太高,否则可能缺少某个moudle包
PyMySQL==0.9.0
wheel==0.29.0
mysql-replication==0.13
使用国内地址安装依赖包
cd binlog2sql
pip3 install -r requirements.txt -i http://pypi.douban.com/simple/ --trusted-host pypi.douban.com
或者设置全局使用国内地址
在~
目录下,创建.pip/pip.conf
,内容如下,设置py为国内下载地址
[global]
timeout = 6000
index-url = http://pypi.douban.com/simple
trusted-host = pypi.douban.com
使用可能遇到的问题
# 更新PyMySQL到支持8.0
pip3 install --upgrade PyMySQL==0.9.0
使用
解析binlog
# start-file=binglog日志的名字,不带目录
# 查看数据
python3 /home/app/binlog2sql-master/binlog2sql/binlog2sql.py -h127.0.0.1 -P3307 -uroot -p'root' -dtest -t t --start-file='binlog.000007' > rollback.sql && cat rollback.sql
# 根据delete的position加 -B参数生成回滚数据
python3 /home/app/binlog2sql-master/binlog2sql/binlog2sql.py -h127.0.0.1 -P3307 -uroot -p'root' -dtest -t t --start-file='binlog.000007' --start-position=1872 --stop-position=2156 -B > rollback.sql && cat rollback.sql
使用sql文件恢复数据
mysql -h127.0.0.1 -P3307 -uroot -proot < rollback.sql
命令解析
解析模式
- –stop-never 持续解析binlog。可选。默认False,同步至执行命令时最新的binlog位置。
- -K, --no-primary-key 对INSERT语句去除主键。可选。默认False
- -B, --flashback 生成回滚SQL,可解析大文件,不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。
- –back-interval -B模式下,每打印一千行回滚SQL,加一句SLEEP多少秒,如不想加SLEEP,请设为0。可选。默认1.0。
解析范围控制
- –start-file 起始解析文件,只需文件名,无需全路径 。必须。
- –start-position/–start-pos 起始解析位置。可选。默认为start-file的起始位置。
- –stop-file/–end-file 终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never,此选项失效。
- –stop-position/–end-pos 终止解析位置。可选。默认为stop-file的最末位置;若解析模式为stop-never,此选项失效。
- –start-datetime 起始解析时间,格式’%Y-%m-%d %H:%M:%S’。可选。默认不过滤。
- –stop-datetime 终止解析时间,格式’%Y-%m-%d %H:%M:%S’。可选。默认不过滤。
对象过滤
- -d, --databases 只解析目标db的sql,多个库用空格隔开,如-d db1 db2。可选。默认为空。
- -t, --tables 只解析目标table的sql,多张表用空格隔开,如-t tbl1 tbl2。可选。默认为空。
- –only-dml 只解析dml,忽略ddl。可选。默认False。
- –sql-type 只解析指定类型,支持INSERT, UPDATE, DELETE。多个类型用空格隔开,如–sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型,则三者都不解析。