1.错误日志
| 错误日志作用: 记录MySQL的启动、停止信息以及在MySQL运行过程中的错误信息。
参数log_error(默认开启) 修改后重启生效 log_error=[path/[file_name]],如果不指定文件名，则默认hostname.err. 查看错误日志路径： mysql> show variables like '%log_error%'; 删除错误日志后: <5.5.7版本时，flush logs会将filename.err命名为filename.err_old >5.5.7版本则只是会重建错误日志，也就是除非错误日志被删除了,会重建一个; 否则不会对线上错误日志有影响。 注意:删除错误日志后，不会自动重建。需要flush logs或者重启数据库。 mysqladmin -u root -p flush-logs 或者 mysql>flush logs;重新后生成错误日志(同名). |
2.MySQL general日志(默认关闭)
| 作用:会记录所有的mysql内执行的sql语句（注意是所有语句，包括查询语句）.一般不开启,因为要记录所有的语句,所以IO压力比较大. 当统计某些sql的执行频率,写脚本过滤一些sql.是用到. 查看general日志路径：mysql> show variables like '%general_log%'; general_log(默认关闭){0-关闭,1-开启} 可以在线打开和关闭; set global general_log='ON’; general_log_file=file_name, 不指定文件名只指定路径则为hostname.log (例如主机名为master,则为master.log) |
3.MySQL慢查询日志(默认关闭)
| 作用:记录慢sql,执行比较慢的sql.
路径: mysql> show variables like '%slow_query_log%'; MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在 MySQL中响应时间超过阀值的语句。(默认关闭) 注意:慢查询日志不会计算语句在开始执行之前等待锁的时间 (initial slow query)，mysqld只会从语句等待的锁释放掉，并开始执行开始计算时间(执行开始后的锁定时间会记录)。所以慢查询日志中的语句顺序和语句的发出顺序或者general log的顺序不同。 相关参数： slow_query_log =1/0 ---开启关闭慢查询 long_query_time = 1 2 3 ---慢查询阀值.(单位是s) log_queries_not_using_indexes:记录没有用索引的查询 参数:log_throttle_queries_not_using_indexes:#设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间 参数:min_examined_row_limit:要检查的行数大于等于N时 才记录为慢查询，前提是必须满足long_query_time和 log-queries-not-using-indexes约束。 参数:log_slow_admin_statements:管理语句是否记录 参数:log_slow_slave_statements:从库的语句执行是否记录. 慢日志信息例子： # Time: 2018-10-18T21:56:45.731545+08:00 # User@Host: root[root] @ localhost [] Id: 5 # Query_time: 3.059106 Lock_time: 0.000208 Rows_sent: 1048576 Rows_examined: 1048576 SET timestamp=1539871005; select * from lbg; 分析： Time:---sql执行完时间。 root[root] @ localhost ---用户、发出sql的ip Query_time: ----sql执行时间 Lock_time: ---查询等待锁的时间 Rows_sent -----结果集大小 Rows_examined ---扫描行数 SET timestamp ----发出sql的时间 慢日志分析工具: MySQLdumpSlow工具 -s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回 的记录数来排序， ac、at、al、ar，表示相应的倒叙; -t, 是top n的意思，即为返回前面多少条的数据; -g, 后边可以写一个正则匹配模式，大小写不敏感的; 比如: 统计用时最长的10条sql mysqldumpslow -s t -t 10 mysql-slow.log 执行频率最高的10条sql mysqldumpslow -s c -t 10 mysql-slow.log 例子： [root@lbg logs]# mysqldumpslow -s t -t 10 mysql-slow.log Reading mysql slow query log from mysql-slow.log Count: 1 Time=18.34s (18s) Lock=0.00s (0s) Rows=1048576.0 (1048576), []@[] throttle: N 'S' warning(s) suppressed. Count: 1 Time=3.06s (3s) Lock=0.00s (0s) Rows=1048576.0 (1048576), root[root]@localhost select * from lbg Count: 4 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost insert into lbg select * from lbg Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 6. |
4.binlog
1.binlog作用及文件路径
| 作用: binlog其实是（binary log）是MySQL的二进制日志，以二进制的形式记录了对于数据库的变更操作，记录所有的mysql的变化情况(不包括select show 等查询语句)，其左右有：
1.用来查看mysql变更 2.mysql的备份恢复 3.mysql的主从复制 查看bin_log路径： mysql> show variables like '%log_bin_basename%'; [root@lbg mysql3306]# ll mysql-bin* -rw-r----- 1 mysql mysql 154 Oct 18 22:07 mysql-bin.000001 -rw-r----- 1 mysql mysql 43 Oct 18 22:07 mysql-bin.index 说明： mysql-bin.000001，数字慢慢增大，刚生成的binlog大小为154. 其中mysql-bin.index 里记录的就是bin-log文件的位置。 [root@lbg mysql3306]# cat mysql-bin.index /home/mysql3306/mysql3306/mysql-bin.000001 |
2.bin-log生成方式
| 1.mysql在启动时候,会生成新的binlog.
2.flush logs ----重新生成错误日志，也会重新生成bin-log 3.当达到单个binlog 文件的阀值的时候 mysql> show variables like '%max_binlog%'; Variable_name Value max_binlog_cache_size 8589934592 max_binlog_size 1073741824 max_binlog_stmt_cache_size 18446744073709547520 注意：当超过1GB文件的时候,会自动切到新的binlog,执行某些大事务,为了事务的完整性,binlog要等事务执行完成后切换,所以binlog可能大于1GB. |
3.bin-log相关参数
| binlog_cache_size 内存分配,线程级别的内存分配,要给每一个线程单独的分配binlog cache
总binlog的cache大小=binlog_cache_size*线程数(这也是buffer-pool为最大为内存75%的原因之一,要留一部分内存给binlog和操作系统用) max_binlog_cache_size,全局参数,所有的binlogcache size总和不超过该值,超过该值,报下面错: Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage” max_binlog_size:设置 binlog文件的最大值，默认和最大是1GB，并不能严格限定二进制文件的大小 binlog_do_db: 此参数表示只记录指定数据库的二进制日志 binlog_ignore_db: 此参数表示不记录指定的数据库的二进制日志 binlog_checksum 为 {CRC32|NONE} 写binlog时，会将内容生成校验位，之后存储在binlog中。默认情况下，服务器记录事件的长度以及事件本身，并使用它来验证事件是否正确写入。也可以通过设置binlog_checksum系统变量来使服务器为事件写入校验和。 log_bin与log_bin_basename:决定了msyql 的binlog的名字，生成的binlog名字为mysql-bin.000001 binlog_format: 规定binlog的格式，binlog有三种格式statement，row以及mixed，默认使用statement，建议使用row格式 expire_logs_days :过期时间(单位是天) sync_binlog: 值为0或1或n, 在提交n次事务后，进行binlog的落盘，0为不进行强行的刷新操作，而是由文件系统控制刷新日志文件. 如果是在线交易和帐有关的数据建议设置成1, 如果是其它数据可以保持为０即可。(双1配置参数之一.) 0 每1s落盘一次 1 每次commit落盘一次 n 每n个事务落盘一次 sql_log_bin --关闭当前session的binlog (导入大量数据时,不希望记录binlog时.导完再开启参数) |
4.清理binlog
| 在开启mysql的主从后，会产生大量的binlog日志文件，可能占用大量的磁盘空间,
1.手工删除binlog mysql> reset master; //删除master的binlog ---会删除所有的binlog文件,重置为1个,非常危险. ###reset master后，会造成slave无法找到master的严重后果 mysql> reset slave; //删除slave的中继日志 mysql> purge master logs before '2012-03-30 17:20:00'; //删除指定日期以前的日志索引中binlog日志文件 mysql> purge master logs to 'binlog.000002'; //删除指定日志文件的日志索引中binlog日志文件 或者直接用操作系统命令直接删除:直接rm(mysql-bin日志,删除索引信息mysql-bin.index里对应信息.)注意:不能删除正在使用的binlog(1.编号最大的binlog 2.从库还没有同步完成的binlog,) 这种方法尽量不用. 2.自动删除binlog(通过参数expire_logs_days ) 通过binlog参数（expire_logs_days ,设置binlog的过期参数）来实现mysql自动删除binlog show binary logs; show variables like 'expire_logs_days'; set global expire_logs_days=7; |
5.binlog格式
| binlog格式由参数binlog_format定义.分三种: statement , row,mixed 格式. statement格式: 特点：记录每一条数据的SQL语句，将执行的每一条SQL记录在binlog中. 优点:减少日志量，节省IO,提高性能。 缺点：某些SQL中的函数无法使用，比如SYSDATE()，在同步过程中会出现无法同步的问题。 row格式: 特点：修改之前和修改之后的行的信息. binlog中仅仅记录哪一条记录被修改，不记录sql语句，会详细记录每一row的更改细节，不会出现无法复制的问题。 优点:安全，一般线上使用row格式。 缺点:因为要记录每一条修改记录的日志, 数据比较大,可能对数据网络压力 IO压力造成影响大量占用磁盘IO和大量使用硬盘空间。 mixed格式: 特点：结合了上面两种方式,一般的语句使用SQL语句来记录，遇到特殊的语句使用row格式来记录，保证数据的一致性和复制的准确性。 注意：上面三种都是针对DML语句, DDL不管在哪个格式,都只记录语句.如 drop table test1; 查看binlog格式: mysql> show variables like '%binlog_format%'; 修改binlog格式： mysql> set global binlog_format='row'; 显示存在的binlog: mysql> show binary logs; 查看binlog文件内容： mysql> show binlog events; 一般我们我们如何在binlog里面定位一个事务? 文件名:mysql-bin.000001. 加上position点可以定位一个事务. position:就是写入binlog的字节数.(从154开启,前面是无意义信息.开始文件就是154) xid :是储存引擎内部分布式事务编号。 |
6.查看binlog文件
| 在数据库里查看：mysql> show binlog events;（这是经过被解析后的结果，其实文件底层是二进制） 查看binlog文件： [root@lbg1 mysql3306]# mysqlbinlog mysql-bin.000003 -vv >/tmp/lbg.bin --vv的作用:第一个v,显示sql,第二个v,显示sql的字段类型和其他信息. [root@lbg1 mysql3306]# cat /tmp/lbg.bin @1指表的第1个字段。@2表示第2个字段。底下蓝色的###段落是其上面对应乱码翻译后的解释。 注意：mysqlbinlog命令文件是不是安装目录下的bin文件里（which mysqlbinlog），因为可能系统安装过mysql,那么可能读取的是/usr/bin/mysqlbinlog，低版本解析有问题。 mysqlbinlog使用方法: 截取pos点范围或者时间范围的binlog:start-datetime stop-datetime 或者start-position和stop-position 。 使用mysqlbinlog查看binlog: /usr/local/mysql/bin/mysqlbinlog /home/mysql3306/mysql3306/mysql-bin.000001 > /tmp/mysql_binlog.sql 根据时间查看binlog: /usr/local/mysql/bin/mysqlbinlog --start-date="2017-04-20 9:00:00" --stop-date="2017-07-20 18:00:00" /home/mysql3306/mysql3306/mysql-bin.000001 > /tmp/mysql_binlog.sql start和stop的范围是 [start, stop) 根据pos点查看binlog: /usr/local/mysql/bin/mysqlbinlog bin.000017 --start-position=1959 --stop-position=2057 -vv > /tmp/a.sql 解析row格式的mysqlbinlog为SQL格式: mysqlbinlog mysql-bin.000001 --base64-output=DECODE-ROWS -vv > /tmp/all.bin --base64-output=DECODE-ROWS 是为了适应mysql, server设置binlog_format=row。 注意：若用于恢复数据，则一定不能带--base64-output=DECODE-ROWS选项，否则会恢复有问题。 |
7.导入多个binlog的注意事项
| binlog恢复:
注意，如果有多个 binlog文件想要恢复， 不要一个一个恢复 shell> mysqlbinlog binlog.000001 | mysql -u root -p shell> mysqlbinlog binlog.000002 | mysql -u root -p 上面这种恢复方式是错误的，如果 binlog.000001 中创建了一个临时表(CREATE TEMPORARY TABLE)，而 binlog.000002 中要使用这个临时表，但是 第一个线程(binlog.000001) 在释放的时候会删除临时表,此时第二个线程(binlog.000002) 就无法使用这个临时表了. 正确的做法如下: 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" |
8.binlog和redo的区别
| Binlog:二进制日志是一种逻辑日志，可能只记录了对应的SQL语句。
Innodb存储引擎的的redo日志记录的是数据页被修改的的物理格式日志， 两种日志的写入时间也不一样，binlog只在事务提交的时候写入，redo需要在事务过程中不断的写入。 |
9.MyFlash的使用
| 该工具注意事项
1.binlog格式必须为row,且binlog_row_image=full 2.仅支持5.6与5.7 3.只能回滚DML（增、删、改） 使用美团工具如下： [root@lbg1 MyFlash-master]# unzip MyFlash.zip [root@lbg1 MyFlash-master]# ls binary binlog_output_base.flashback build.sh doc README.md source testbinlog 安装依赖并编译： yum -y install glib2* gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback 完成上面操作后： [root@lbg1 MyFlash-master]# cd binary/ [root@lbg1 binary]# ls flashback mysqlbinlog20160408 ---实际此文件在编译前就有。 其中要使用的就是flashback工具。 使用命令： 1.回滚整个文件： ./flashback --binlogFileNames=haha.000041 mysqlbinlog binlog_output_base.flashback | mysql -h -u -p 2.回滚该文件中的所有deldte语句 ./flashback --sqlTypes='DELETE' --binlogFileNames=haha.000041 mysqlbinlog binlog_output_base.flashback | mysql -h -u -p 说明：使用flashback后会生成当前路径下生成新的二进制binlog文件。想查看的话可msyqlbinlog 文件名 > /tmp/lbg.bin，再查看/tmp/lbg.bin即可,想恢复数据直接source /tmp/lbg.bin即可。 3.根据position点恢复数据 [root@lbg1 soft]# /soft/MyFlash-master/binary/flashback --start-position=801 --stop-position=1591 --sqlTypes='DELETE' --binlogFileNames=/home/mysql3306/mysql3306/mysql-bin.000001 注意：查找start-position时需是begin字段前的position点，stop-position的值是commit后的position点。 4.回滚大文件： 回滚 ./flashback --binlogFileNames=haha.000042 切割⼤文件 ./flashback --maxSplitSize=1 --binlogFileNames=binlog_output_base.flashback 应⽤ mysqlbinlog binlog_output_base.flashback.000001 | mysql -h -u -p ... mysqlbinlog binlog_output_base.flashback. | mysql -h -u -p |
10.binlog实时远程备份命令
| [root@lbg2 lbg]# mysqlbinlog --read-from-remote-server --raw --host=192.168.88.8 --port=3306 --user=root --password=root --stop-never mysql-bin.000001
----生成的binlog在本地。 不足：这个方式有个问题，对于常规的主从复制来说，如果主从直接的连接断开了，则从会自动再次连接，而对于mysqlbinlog，如果断开了，并不会自动连接。可通过脚本来弥补上述不足。实际上定义了一个死循环，如果备份失败，则10s后重新连接。 |