--BINLOG
--概念：binlog其实是（ binary log）是MySQL的二进制日志，以二进制的形式记录了对于数据库的变更操作
--不包括select和show操作
--1. 用来查看mysql变更
--2. mysql的备份恢复
--3. mysql的主从复制
--文件位置
--binlog默认放置在数据目录下
--binlog的命令方式
mysql-bin.000001
--binlog文件的生成方式
--1. MySQL 启动的时候会产生新的binlog
--2. MySQL服务器在执 flush logs; 可以产生新的binlog文件
--关于binlog的参数
sync_binlog = 1
log-bin = mysql-bin
binlog_format = row
expire_logs_days = 10
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1024M
--相关参数讲解
log-bin
--决定了msyql的binlog的名字，生成的binlog名字为mysql-bin.000001
binlog_format
--规定binlog的格式，binlog有三种格式statement，row以及mixed，默认使用statement，建议使用row格式
expire_logs_days
--过期时间
binlog_do_db
--此参数表示只记录指定数据库的二进制日志
binlog_ignore_db
--此参数表示不记录指定的数据库的二进制日志
sync_binlog
--在提交n次事务后，进行binlog的落盘， 0为不进行强行的刷新操作，而是由文件系统控制刷新日志文件
--如果是在线交易和帐有关的数据建议设置成1, 如果是其它数据可以保持为０即可。
max_binlog_size
--binlog文件的最大值，默认和最大是1GB，并不能严格限定二进制文件的大小
max_binlog_cache_size
--表示的是binlog 能够使用的最大cache 内存大小
--只是为事务缓存的大小而设置,推荐的最大值为4GB
--binlog_cache_use和binlog_cache_disk_use两者结合可以用来调整binlog_cache_size的大小
--Binlog_cache_use--表示用binlog_cache_size缓存的次数;（事务类）二进制日志已缓存的条数（内存中）
--binlog_cache_disk_use（事务类）二进志日志缓存的已经存在硬盘的条数
--当我们执行多语句事务的时候 所有session的使用的内存超过max_binlog_cache_size的值时
--就会报错： “Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”
binlog_stmt_cache_size
--管理语句缓存的大小;发生事务时非事务语句的缓存的大小
--binglog_stmt_cache_use和binlog_stmt_cache_disk_use两者结合可以有来调整 binlog_stmt_cache_size的大小
--max_binlog_cache_size针对事务语句，max_binlog_stmt_cache_size针对非事务语句
log_bin_trust_function_creators
--当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。
--如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。
--设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。
--如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建
--如果数据库没有使用主从复制，那么就可以将参数log_bin_trust_function_creators设置为1
--这个动态设置的方式会在服务重启后失效，所以我们还必须在my.cnf中设置，加上log_bin_trust_function_creators=1，这样就会永久生效
binlog_order_commits
--当binlog_order_commits关闭时，直接unlock LOCK_sync，由各个session自行进入Innodb commit阶段(随后调用的finish_commit(thd))，
--这样不会保证binlog和事务commit的顺序一致，如果你不关注innodb的ibdata中记录的binlog信息，那么可以关闭这个选项来稍微提高点性能
--MySQL binlog 格式
statment格式
--特点：记录每一条数据的SQL，将执行的每一条SQL记录在binlog中，减少日志量，节省IO,提高性能
--缺点：某些SQL中的函数无法使用，比如SYSDATE()，在同步过程中会出现无法同步的问题
mixed格式
--特点：一般的语句使用SQL语句来记录，遇到特殊的语句使用row格式来记录，保证数据的一致性和复制的准确性
row格式
--特点：binlog中仅仅记录哪一条记录被修改，不记录dml的sql语句，会详细记录每一row的更改细节，不会出现无法复制的问题
--缺点：因为要记录每一条修改记录的日志，所以大量占用磁盘IO和大量使用硬盘空间
--设定主从复制模式
vi /etc/my.cnf
log-bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
binlog_format="MIXED"
-- 也可以在运行时动态修改binlog的格式
SET SESSION binlog_format = 'STATEMENT';
SET SESSION binlog_format = 'ROW';
SET SESSION binlog_format = 'MIXED';
SET GLOBAL binlog_format = 'STATEMENT';
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_format = 'MIXED';
--查看binlog信息
-- 如果用户有super权限，可以启动或禁止当前会话的binlog记录
set sql_log_bin=1/0;
-- 查看master的binlog⽇志
show master logs;
show binary logs;
-- 用于提供master二进制日志文件的状态信息
show master status;
-- 显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中
show slave hosts;
-- 使用show binary logs;
show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 65429 |
| mysql-bin.000002 | 1175289 |
| mysql-bin.000003 | 1451 |
| mysql-bin.000004 | 3469 |
| mysql-bin.000005 | 238 |
| mysql-bin.000006 | 191 |
+------------------+-----------+
-- 查看binlog events
show binlog events;
+------------------+------+-------------+-----------+-------------+----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+----------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 28703306 | 120 | Server ver: 5.6.25-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Query | 28703306 | 201 | BEGIN
|
| mysql-bin.000001 | 201 | Query | 28703306 | 301 | use `test1`; insert into t1 values(1) |
| mysql-bin.000001 | 301 | Xid | 28703306 | 332 | COMMIT /* xid=27 */
|
| mysql-bin.000001 | 332 | Query | 28703306 | 413 | BEGIN
|
| mysql-bin.000001 | 413 | Query | 28703306 | 513 | use `test1`; insert into t1 values(2) |
| mysql-bin.000001 | 513 | Xid | 28703306 | 544 | COMMIT /* xid=28 */
|
| mysql-bin.000001 | 544 | Query | 28703306 | 625 | BEGIN
|
| mysql-bin.000001 | 625 | Query | 28703306 | 725 | use `test1`; insert into t1 values(3) |
| mysql-bin.000001 | 725 | Xid | 28703306 | 756 | COMMIT /* xid=29 */
|
| mysql-bin.000001 | 756 | Query | 28703306 | 837 | BEGIN
|
| mysql-bin.000001 | 837 | Query | 28703306 | 937 | use `test1`; insert into t1 values(1) |
| mysql-bin.000001 | 937 | Xid | 28703306 | 968 | COMMIT /* xid=30 */
|
| mysql-bin.000001 | 968 | Query | 28703306 | 1049 | BEGIN
|
| mysql-bin.000001 | 1049 | Query | 28703306 | 1150 | use `test1`; delete from t1 where id=1 |
| mysql-bin.000001 | 1150 | Xid | 28703306 | 1181 | COMMIT /* xid=33 */
--更为方便的查询命令：
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
--选项解析：
IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] 偏移量(不指定就是0)
row_count 查询总条数(不指定就是所有行)
--截取部分查询结果：
*************************** 20. row ***************************
Log_name: mysql-bin.000021 ----------------------------------------------> 查询的binlog日志文件名
Pos: 11197 ----------------------------------------------------------> pos起始点:
Event_type: Query ----------------------------------------------------------> 事件类型：Query
Server_id: 1 --------------------------------------------------------------> 标识是由哪台服务器执行的
End_log_pos: 11308 ----------------------------------------------------------> pos结束点:11308(即：下行的pos起始点)
Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5') ---> 执行的sql语句
*************************** 21. row ***************************
Log_name: mysql-bin.000021
Pos: 11308 ----------------------------------------------------------> pos起始点:11308(即：上行的pos结束点)
Event_type: Query
Server_id: 1
End_log_pos: 11417
Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */
*************************** 22. row ***************************
Log_name: mysql-bin.000021
Pos: 11417
Event_type: Query
Server_id: 1
End_log_pos: 11510
Info: use `zyyshop`; DROP TABLE IF EXISTS `type`
--这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数；
--A.查询第一个(最早)的binlog日志：
mysql> show binlog events\G;
--B.指定查询 mysql-bin.000021 这个文件：
mysql> show binlog events in 'mysql-bin.000021'\G;
--C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
mysql> show binlog events in 'mysql-bin.000021' from 8224\G;
--D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;
--E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;
--使用mysqlbinlog查看binlog
--mysqlbinlog这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息
mysqlbinlog /home/mysql3306/mysql3306/mysql-bin.000001 > /tmp/mysql_binlog.sql
--查看bin
mysqlbinlog -v --base64-output=decode-rows mysql-bin.000001 > /tmp/weixin0511new.sql
--根据时间查看binlog
mysqlbinlog --start-date="2017-07-20 9:00:00" --stop-date="2017-07-20 18:00:00"
/home/mysql3306/mysql3306/mysql-bin.000001 > /tmp/mysql_binlog.sql
# at 505（ 起始位置是505字节）
#170524 15:16:01 server id 28201（ server id） end_log_pos 580 CRC32 0x177be206 （结束位置） Query
thread_id=3(线程id) exec_time=0（事件执行时间） error_code=0（错误码）
SET TIMESTAMP=1495610161/*!*/;（时间戳）
BEGIN
/*!*/;
# at 580
#170524 15:16:01 server id 28201 end_log_pos 675 CRC32 0x800ff886 Query thread_id=3
exec_time=0 error_code=0
SET TIMESTAMP=1495610161/*!*/;
insert into t1 values (2)
/*!*/;
# at 675
#170524 15:16:01 server id 28201 end_log_pos 706 CRC32 0x8162c63d Xid = 116
COMMIT/*!*/;
--mysqlbinlog常见的选项有以下几个：
--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
--start-position：从二进制日志中读取指定position 事件位置作为开始
--stop-position：从二进制日志中读取指定position 事件位置作为事件截至
--解析row格式的mysqlbinlog为SQL格式
/usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000004
--base64-output=DECODE-ROWS 是为了适应mysql server设置binlog_format=row
-----------------------binlog日志挖掘(误删除数据)---------------------
--1.mysqlbinlog分析日志：
mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /home/mysql3307/logs/mysql-bin.000037 > fenxi1.log
--2.开启参数binlog_rows_query_log_events有助于binlog的分析，binlog日志可以显示具体的sql语句。
show variables like 'binlog_rows_query_log_events';
set global binlog_rows_query_log_events=on;
--退出生效
--3.从重定向的日志中抽取delete或者update语句：
#sed -n '/UPDATE `zt`.`t1`/,/@15=/{p}' fenxi1.log > fenfen1.log
#sed -n '/DELETE FROM `zt`.`t2`/,/@15=/{p}' bt.log >bt1.log
--4.将抽取的delete语句用recover.py 转换成insert语句：
python recover.py fenfen.log 2 >reinsert.sql
--恢复： source reinsert.sql （完美）
--2数字代表这个表有几个字段，相应改成几
--备注：
--1.多个二进制日志读取方式：（即追binlog的方式，（1）可用于备份恢复，(2)删除binlog里的delete语句再追binlog）
shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p ##
--或者
shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
--或者
shell> mysqlbinlog binlog.00000[1-2] > /tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"
--2.基于时间的操作：
mysqlbinlog --start-date="2017-06-12 16:30:00" --stop-date="2017-06-12 17:16:00" F:\mysql\mysql-5.6.21-winx64\mysql-5.6.21-winx64\data\mysql-bin.000001 |mysql -uroot -p
--3.基于pos号的操作：
# mysqlbinlog -B --start-position=296 --stop-position=429 mysql-bin.000004 | mysql -uroot -p
--清理过期binlog日志
--在开启mysql的主从后,会产生大量的binlog日志文件,可能占用大量的磁盘空间
--没有主从同步的情况下清理日志
-- 删除master的binlog
reset master;
-- 删除指定日期以前的日志索引中binlog日志文件
purge master logs before '2018-06-06 17:20:00';
-- 删除指定日志文件的日志索引中binlog日志文件
purge master logs to 'binlog.000002';
--或者直接操作系统命令直接删除
rm -rf
-- 使用reset master 重置binlog文件
--reset master后，会造成slave无法找到master的严重后果
reset master;
-- 会清除所有的binlog文件，并且重置为一个
show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 151 |
+------------------+—————+
-- 查看正在使用的binlog
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 151 | | | |
+------------------+----------+--------------+------------------+-------------------+
--直接使用rm 命令删除不是当前使用的binlog
rm -rf mysql-bin.000004
--##切记不要删除正在使用的binlog
--MySQL主从同步下安全清理binlog日志
--进入从服务器mysql控制台
mysql -u root -p
-- 检查从库正在读取哪个日志,有多个从服务器,选择时间最早的一个做为目标日志
show slave status\G;
--进入主服务器mysql控制台
-- 获得主库服务器上的一系列日志
show master status\G
show master logs;
-- 删除binlog.000005之前的，不包括binlog.000058
PURGE MASTER LOGS TO 'binlog.000058';
-- 清除2018-06-07 13:00:00前binlog日志
PURGE MASTER LOGS BEFORE '2018-06-07 13:00:00';
-- 清除3天前binlog日志
PURGE MASTER LOGS BEFORE DATE_SUB( NOW(), INTERVAL 3 DAY);
-- 设置自动清理MySQL binlog日志
-- 通过binlog参数(expire_logs_days)来实现mysql自动删除binlog
show binary logs;
show variables like 'expire_logs_days';
set global expire_logs_days=3;
--编辑配置
vi /etc/my.cnf
expire_logs_days = 15 #自动删除15天前的日志,默认值为0,表示从不删除
log-bin=mysql-bin #注释掉之后，会关闭binlog日志
binlog_format=mixed #注释掉之后，会关闭binlog日志
--binlog和redo的区别
--MySQL的数据库上层的二进制日志是一种逻辑⽇志，只记录了对应的SQL语句。
--Innodb存储引擎的的redo日志记录的是数据页被修改的的物理格式日志，
--两种日志的写入时间也不一样，binlog只在事务提交的时候写入，redo需要在事务过程中不断的写入。
--归档bin日志
mv mysql-bin.000001 mysql-bin.000001.bak
--刷新产生新日志
mysql -uroot -p -e "flush binary logs"
--mv至备份路径
mv mysql-bin.000001.bak /backup/
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
