1：binlog 配置
1.1 binlog记录模式及参数配置
DDL：全部记录，定义语言
DML：除select 之外都会进行记录
#在shell脚本中/etc/my.cnf
#开启binlog并写明存放日志得位置，默认使用的配置为
log-bin=mysql-bin,
这样日志是存放到data目录下
log-bin = /usr/local/mysql/data/log-bin
#指定索引文件的位置
log_bin_index=/usr/local/mysql/data/mysql-bin.index
#日志保留期限，超过期限将被删除
expire_logs_days=7
#指定集群中mysql服务器的ID ，在数据库集群中是唯一的,习惯指定内网IP地址末尾
server_id = 108
#设置三种bin-log日志模式，在MySQL5.7之后binlog默认记录模式为row
binlog_format=statement|row
#设置binlog的大小
max_binlog_size=500m
#设置binlog缓存日志大小即当前有多少事务cache在内存中
binlog_cache_size=1M #
DML操作不频繁设置为<=1m ，dml频繁且事务大 2-4m
max_binlog_cache_size #32位系统为4G 64位系统位16P1.2 查看binlog日志
在mysql下面查看对应binlog日志文件
查看当前写入的binlog日志文件
mysql> show master status \G*************************** 1. row *************************** File: mysql-bin.000001 #当前写入的binlog日志 Position: 1062 #当前写入的节点 Binlog_Do_DB: #指定mysql中哪个数据库记录binlog日志 Binlog_Ignore_DB: #指定mysql中哪个数据库步记录binlog日志 Executed_Gtid_Set: 1 row in set (0.00 sec)查看binlog日志列表
mysql>show binary logs;
| Log_name | File_size |+------------------+-----------+|
mysql-bin.000001 | 1062 |+------------------+-----------+1 row in set (0.00 sec)查看具体binlog日志中内容
mysql >show binlog events in 'mysql-bin.000001';
#比较清晰的查看出当前日志中存放的内容，包含开始节点，结束节点。通过binlog日志文件查看日志信息
[root] mysqlbinlog --no-defaults --database=mydb --start-datetime="2019-01-01 00:00:00" --stop-datetime="2019-12-30 00:00:00" mysql-bin.000001 > 2019.txt
#参数说明
--no-defaults #不读取任何选项文件
--database=mydb #指定读取mydb数据库的binlog日志
--start-datetime="2019-01-01 00:00:00" #用于指定日志记录开始时间
--stop-datetime="2019-12-30 00:00:00" #用于指定日志记录结束时间
mysql-bin.000001 #从指定的binlog日志中进行读取数据
>2019.txt #将读取到的日志文件输出到2019.txt中根据position点进行查看日志
mysqlbinlog --no-defaults --start-position=123 --stop-position=463 --database=mydb mysql-bin.000001 >mydb.sql
--start-position=123 #开始点
--stop-position=463 #结束点/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#191222 5:47:51 server id 1 end_log_pos 123 CRC32 0x4669d9db Start: binlog v 4, server v 5.7.28-log created 191222 5:47:51 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;BINLOG 'B5P+XQ8BAAAAdwAAAHsAAAABAAQANS43LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAHk/5dEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAAdvZaUY='/*!*/;WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.# at 123#191222 5:47:51 server id 1 end_log_pos 154 CRC32 0x721fbd14 Previous-GTIDs# [empty]# at 154#191222 5:51:43 server id 1 end_log_pos 219 CRC32 0x293ed234 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=noSET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;COMMIT /* added by mysqlbinlog *//*!*/;