这里有新鲜出炉的 Mysql 教程,程序狗速度看过来!
MySQL 是一个开放源码的小型关联式数据库管理系统,开发者为瑞典 MySQL AB 公司。MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。
下面小编就为大家带来一篇 Mysql 数据库之 Binlog 日志使用总结 (必看篇)。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
binlog 二进制日志对于 mysql 数据库的重要性有多大,在此就不多说了。下面根据本人的日常操作经历,并结合网上参考资料,对 binlog 日志使用做一梳理:
一、binlog 日志介绍
1)什么是 binlog
binlog 日志用于记录所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个 DELETE)的所有语句。语句以 "事件" 的形式保存,它描述数据更改。
2)binlog 作用
因为有了数据更新的 binlog,所以可以用于实时备份,与 master/slave 主从复制结合。
3)和 binlog 有关参数
log_bin
设置此参数表示启用 binlog 功能,并指定路径名称
log_bin_index
设置此参数是指定二进制索引文件的路径与名称
binlog_do_db
此参数表示只记录指定数据库的二进制日志
binlog_ignore_db
此参数表示不记录指定的数据库的二进制日志
max_binlog_cache_size
此参数表示 binlog 使用的内存最大的尺寸
binlog_cache_size
此参数表示 binlog 使用的内存大小,可以通过状态变量 binlog_cache_use 和 binlog_cache_disk_use 来帮助测试。
binlog_cache_use:使用二进制日志缓存的事务数量
binlog_cache_disk_use: 使用二进制日志缓存但超过 binlog_cache_size 值并使用临时文件来保存事务中的语句的事务数量
max_binlog_size
Binlog 最大值,最大和默认值是 1GB,该设置并不能严格控制 Binlog 的大小,尤其是 Binlog 比较靠近最大值而又遇到一个比较大事务时,为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有 SQL 都记录进当前日志,直到事务结束
sync_binlog
这个参数直接影响 mysql 的性能和完整性
sync_binlog=0
当事务提交后,Mysql 仅仅是将 binlog_cache 中的数据写入 Binlog 文件,但不执行 fsync 之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘,而让 Filesystem 自行决定什么时候来做同步,这个是性能最好的。
sync_binlog=n,在进行 n 次事务提交以后,Mysql 将执行一次 fsync 之类的磁盘同步指令,同志文件系统将 Binlog 文件缓存刷新到磁盘。
Mysql 中默认的设置是 sync_binlog=0,即不作任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。一旦系统绷 Crash,在文件系统缓存中的所有 Binlog 信息都会丢失
4)binlog 的删除
binlog 的删除可以手工删除或自动删除:
a)自动删除 binlog
通过 binlog 参数(expire_logs_days )来实现 mysql 自动删除 binlog
mysql> show binary logs;
mysql> show variables like 'expire_logs_days'; // 该参数表示 binlog 日志自动删除 / 过期的天数,默认值为 0,表示不自动删除
mysql> set global expire_logs_days=3; // 表示日志保留 3 天,3 天后就自动过期。
b)手工删除 binlog
mysql> reset master; // 删除 master 的 binlog,即手动删除所有的 binlog 日志
mysql> reset slave; // 删除 slave 的中继日志
mysql> purge master logs before '2012-03-30 17:20:00'; // 删除指定日期以前的日志索引中 binlog 日志文件
mysql> purge master logs to 'binlog.000002'; // 删除指定日志文件的日志索引中 binlog 日志文件
mysql> set sql_log_bin=1/0; // 如果用户有 super 权限,可以启用或禁用当前会话的 binlog 记录
mysql> show master logs; // 查看 master 的 binlog 日志列表
mysql> show binary logs; // 查看 master 的 binlog 日志文件大小
mysql> show master status; // 用于提供 master 二进制日志文件的状态信息
mysql> show slave hosts; // 显示当前注册的 slave 的列表。不以 --report-host=slave_name 选项为开头的 slave 不会显示在本列表中
mysql> flush logs; // 产生一个新的 binlog 日志文件
mysql binlog 日志自动清理及手动删除案例说明:
- 当开启MySQL数据库主从时,会产生大量如mysql-bin.00000* log的文件,这会大量耗费您的硬盘空间。
- mysql-bin.000001
- mysql-bin.000002
- mysql-bin.000003
- mysql-bin.000004
- mysql-bin.000005
- …
- 删除这些binlog日志有三种解决方法:
- 1.关闭mysql主从,关闭binlog;
- 实例操作如下:
- [root@huqniupc ~]# vim /etc/my.cnf //注释掉log-bin和binlog_format
- # Replication Master Server (default)
- # binary logging is required for replication
- # log-bin=mysql-bin
- # binary logging format - mixed recommended
- # binlog_format=mixed
- 然后重启数据库
- 2.开启mysql主从,设置expire_logs_days;
- 实例操作如下:
- [root@huqniupc ~]# vim /etc/my.cnf //修改expire_logs_days,x是自动删除的天数,一般将x设置为短点,如10
- expire_logs_days = x //二进制日志自动删除的天数。默认值为0,表示"没有自动删除"
- 此方法需要重启mysql
- 当然也可以不重启mysql,开启mysql主从,直接在mysql里设置expire_logs_days
- > show binary logs;
- > show variables like '%log%';
- > set global expire_logs_days = 10;
- 3.手动清除binlog文件,(比如Mysql> PURGE MASTER LOGS TO 'MySQL-bin.010′;)
- 实例操作如下:
- [root@huqniupc ~]# /usr/local/mysql/bin/mysql -u root -p
- > PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY); //删除10天前的MySQL binlog日志,附录2有关于PURGE MASTER LOGS手动删除用法及示例
- > show master logs;
- 也可以重置master,删除所有binlog文件:
- # /usr/local/mysql/bin/mysql -u root -p
- > reset master; //附录3有清除binlog时,对从mysql的影响说明
- ---------------------------------------------------------------
- PURGE MASTER LOGS手动删除用法及示例,MASTER和BINARY是同义词
- > PURGE {MASTER | BINARY} LOGS TO 'log_name'
- > PURGE {MASTER | BINARY} LOGS BEFORE 'date'
- 删除指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除MySQL BIN-LOG 日志,这样被给定的日志成为第一个。
- 实例:
- > PURGE MASTER LOGS TO 'MySQL-bin.010'; //清除MySQL-bin.010日志
- > PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00'; //清除2008-06-22 13:00:00前binlog日志
- > PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); //清除3天前binlog日志BEFORE,变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。
- -----------------------------------------------------
5)清除 binlog 时,对从 mysql 的影响
如果有一个活跃的 slave 从属服务器,该服务器当前正在读取您正在试图删除的日志之一,则本语句不会起作用,而是会失败,并伴随一个错误;不过如果 slave 从属服务器是关闭的(或 master-slave 主从关系关闭),并且碰巧清理了其想要读取的日志之一,则 slave 从属服务器启动后不能复制;当从属服务器正在复制时,本语句可以安全运行,不需要停止它们。
6)binglog 的查看
通过 mysqlbinlog 命令可以查看 binlog 的内容
[root@localhost ~]# mysqlbinlog /home/mysql/binlog/binlog.000003 | more
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#120330 16:51:46 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.45-log created 120330 1
6:51:46
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
# at 196
#120330 17:54:15 server id 1 end_log_pos 294 Query thread_id=3 exec_time=2 error_code=0
SET TIMESTAMP=1333101255/*!*/;
insert into tt7 select * from tt7/*!*/;
# at 294
#120330 17:54:46 server id 1 end_log_pos 388 Query thread_id=3 exec_time=28 error_code=0
SET TIMESTAMP=1333101286/*!*/;
alter table tt7 engine=innodb/*!*/;
解析 binlog 格式:
位置
位于文件中的位置,"at 196" 说明 "事件" 的起点,是以第 196 字节开始;"end_log_pos 294" 说明以第 294 字节结束
时间戳
事件发生的时间戳:"120330 17:54:46"
事件执行时间
事件执行花费的时间:"exec_time=28"
错误码
错误码为:"error_code=0"
服务器的标识
服务器的标识 id:"server id 1"
注意下面几点:
1.mysql 的日志切不可想象是可以恢复到任何时间的状态,这个恢复是有前提的!
至少得有一个从日志记录开始后的数据库备份,通过日志恢复数据库实际上只是一个对以前操作的回放过程而已,不用想得太复杂。
既然是回放操作,那么就得注意了,如果是执行了两次恢复那就相当于是回放了两次,后果可想而知。
所以:
1)恢复前务必先备份数据.
2)由于二进制文件多, 并且需要恢复的数据跨度大, 可以考虑将日志文件合并在恢复.
2. 开启 binlog 日志功能
要想通过日志恢复数据库,必须首先在 my.cnf 文件里定义,log-bin=mysql-bin,这样产生的 binlog 日志名就是以 mysql-bin 命名的
3. 什么时候会生成新的 binlog 文件
1)在备份的时候加入 --flush-logs
2)重新启动 mysql 服务的时候
特别提示,mysql 每次启动都会重新生成一个类似 mysql-bin.00000n 的文件,如果你的 mysql 每天都要重新启动一次的话,这时候你就要特别注意不要选错日志文件了。
二、binlog 日志格式介绍
(1)Mysql binlog 日志有三种格式,分别是 Statement、MiXED、ROW
1)Statement:每一条会修改数据的 sql 都会记录在 binlog 中
优点:不需要记录每一行的变化,减少了 binlog 日志量,节约了 IO,提高性能。(相比 row 能节约多少性能与日志量,这个取决于应用的 SQL 情况,正常同一条记录修改或者插入 row 格式所产生的日志量还小于 Statement 产生的日志量,但是考虑到如果带条件的 update 操作,以及整表删除,alter 表等操作,ROW 格式会产生大量日志,因此在考虑是否使用 ROW 格式日志时应该跟据应用的实际情况,其所产生的日志量会增加多少,以及带来的 IO 性能问题。)
缺点:由于记录的只是执行语句,为了这些语句能在 slave 上正确运行,因此还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在 slave 得到和在 master 端执行时候相同 的结果。另外 mysql 的复制, 像一些特定函数功能,slave 可与 master 上要保持一致会有很多相关问题 (如 sleep() 函数, last_insert_id(),以及 user-defined functions(udf)会出现问题).
使用以下函数的语句也无法被复制:
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
同时在 INSERT ...SELECT 会产生比 RBR 更多的行级锁
2)Row: 不记录 sql 语句上下文相关信息,仅保存哪条记录被修改
优点: binlog 中可以不记录执行的 sql 语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以 rowlevel 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或 function,以及 trigger 的调用和触发无法被正确复制的问题
缺点: 所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容, 比如一条 update 语句,修改多条记录,则 binlog 中每一条修改都会有记录,这样造成 binlog 日志量会很大,特别是当执行 alter table 之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。
3)Mixedlevel: 是以上两种 level 的混合使用,一般的语句修改使用 statment 格式保存 binlog,如一些函数,statement 无法完成主从复制的操作,则采用 row 格式保存 binlog,MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式,也就是在 Statement 和 Row 之间选择一种. 新版本的 MySQL 中队 row level 模式也被做了优化,并不是所有的修改都会以 row level 来记录,像遇到表结构变更的时候就会以 statement 模式来记录。至于 update 或者 delete 等修改数据的语句,还是会记录所有行的变更。
Mixed 日志说明:
在 slave 日志同步过程中,对于使用 now 这样的时间函数,MIXED 日志格式,会在日志中产生对应的 unix_timestamp()*1000 的时间字符串,slave 在完成同步时,取用的是 sqlEvent 发生的时间来保证数据的准确性。另外对于一些功能性函数 slave 能完成相应的数据同步,而对于上面指定的一些类似于 UDF 函数,导致 Slave 无法知晓的情况,则会采用 ROW 格式存储这些 Binlog,以保证产生的 Binlog 可以供 Slave 完成数据同步。
(2)binlog 基本配制与格式设定
1)基本配制
binlog 日志格式可以通过 mysql 的 my.cnf 文件的属性 binlog_format 指定。如以下:
binlog_format = MIXED //binlog 日志格式
log_bin = 目录 / mysql-bin.log //binlog 日志名
expire_logs_days = 7 //binlog 过期清理时间
max_binlog_size 100m //binlog 每个日志文件大小
binlog-do-db = 需要备份的数据库名,如果备份多个数据库,重复设置这个选项即可
binlog-ignore-db = 不需要备份的数据库苦命,如果备份多个数据库,重复设置这个选项即可
2)Binlog 日志格式选择
Mysql 默认是使用 Statement 日志格式,推荐使用 MIXED.
由于一些特殊使用,可以考虑使用 ROWED,如自己通过 binlog 日志来同步数据的修改,这样会节省很多相关操作。对于 binlog 数据处理会变得非常轻松, 相对 mixed,解析也会很轻松 (当然前提是增加的日志量所带来的 IO 开销在容忍的范围内即可)。
3)mysqlbinlog 格式选择
mysql 对于日志格式的选定原则: 如果是采用 INSERT,UPDATE,DELETE 等直接操作表的情况,则日志格式根据 binlog_format 的设定而记录, 如果是采用 GRANT,REVOKE,SET PASSWORD 等管理语句来做的话,那么无论如何 都采用 SBR 模式记录
(3)Mysql Binlog 日志分析
通过 MysqlBinlog 指令查看具体的 mysql 日志,如下:
- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
- SET TIMESTAMP=1350355892/*!*/;
- BEGIN
- /*!*/;
- # at 1643330
- #121016 10:51:32 server id 1 end_log_pos 1643885 Query thread_id=272571 exec_time=0 error_code=0
- SET TIMESTAMP=1350355892/*!*/;
- Insert into T_test….)
- /*!*/;
- # at 1643885
- #121016 10:51:32 server id 1 end_log_pos 1643912 Xid = 0
- COMMIT/*!*/;
- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
1. 开始事物的时间:
SET TIMESTAMP=1350355892/*!*/;
BEGIN
2.sqlevent 起点
#at 1643330 : 为事件的起点,是以 1643330 字节开始。
3.sqlevent 发生的时间点
#121016 10:51:32: 是事件发生的时间,
4.serverId
server id 1 : 为 master 的 serverId
5.sqlevent 终点及花费时间,错误码
end_log_pos 1643885: 为事件的终点,是以 1643885 字节结束。
execTime 0: 花费的时间
error_code=0: 错误码
Xid: 事件指示提交的 XA 事务
三、mysql 日志(重点 binlog 日志)的优化说明
MySQL 系统的伸缩性很强,既可以在充足的硬件资源环境下高效运行,也可以在极少资源环境下很好的运行,
但不管怎样,尽可能充足的硬件资源对 MySQL 的性能提升总是有帮助的。
下面着重分析一下 MySQL 的日志(主要是 Binlog)对系统性能的影响,并根据日志的相关特性得出相应的优化思路。
1)日志产生的性能影响
由于日志的记录带来的直接性能损耗就是数据库系统中最为昂贵的 IO 资源。
MySQL 的日志主要包括错误日志(ErrorLog),更新日志(UpdateLog),二进制日志(Binlog),查询日志(QueryLog),慢查询日志(SlowQueryLog)等。
特别注意:更新日志是老版本的 MySQL 才有的,目前已经被二进制日志替代。
在默认情况下,系统仅仅打开错误日志,关闭了其他所有日志,以达到尽可能减少 IO 损耗提高系统性能的目的。
但是在一般稍微重要一点的实际应用场景中,都至少需要打开二进制日志,因为这是 MySQL 很多存储引擎进行增量备份的基础,也是 MySQL 实现复制的基本条件。
有时候为了进一步的 mysql 性能优化,定位执行较慢的 SQL 语句,很多系统也会打开慢查询日志来记录执行时间超过特定数值(由我们自行设置)的 SQL 语句。
一般情况下,在生产系统中很少有系统会打开查询日志。因为查询日志打开之后会将 MySQL 中执行的每一条 Query 都记录到日志中,会该系统带来比较大的 IO 负担,而带来的实际效益却并不是非常大。一般只有在开发测试环境中,为了定位某些功能具体使用了哪些 SQL 语句的时候,才会在短时间段内打开该日志来做相应的分析。
所以,在 MySQL 系统中,会对性能产生影响的 MySQL 日志(不包括各存储引擎自己的日志)主要就是 Binlog 了。
2)Binlog 相关参数及优化策略
我们首先看看 Binlog 的相关参数,通过执行如下命令可以获得关于 Binlog 的相关参数。
当然,其中也显示出了 "innodb_locks_unsafe_for_binlog" 这个 Innodb 存储引擎特有的与 Binlog 相关的参数:
- mysql> show variables like '%binlog%';
- +-----------------------------------------+----------------------+
- | Variable_name | Value |
- +-----------------------------------------+----------------------+
- | binlog_cache_size | 16777216 |
- | binlog_checksum | CRC32 |
- | binlog_direct_non_transactional_updates | OFF |
- | binlog_error_action | IGNORE_ERROR |
- | binlog_format | MIXED |
- | binlog_gtid_simple_recovery | OFF |
- | binlog_max_flush_queue_time | 0 |
- | binlog_order_commits | ON |
- | binlog_row_image | FULL |
- | binlog_rows_query_log_events | OFF |
- | binlog_stmt_cache_size | 32768 |
- | binlogging_impossible_mode | IGNORE_ERROR |
- | innodb_api_enable_binlog | OFF |
- | innodb_locks_unsafe_for_binlog | OFF |
- | max_binlog_cache_size | 18446744073709547520 |
- | max_binlog_size | 1073741824 |
- | max_binlog_stmt_cache_size | 18446744073709547520 |
- | simplified_binlog_gtid_recovery | OFF |
- | sync_binlog | 1 |
- +-----------------------------------------+----------------------+
- 19 rows in set (0.00 sec)
"binlog_cache_size":在事务过程中容纳二进制日志 SQL 语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志 (—log-bin 选项) 的前提下为每个客户端分配的内存,注意,是每个 Client 都可以分配设置大小的 binlogcache 空间。如果读者朋友的系统中经常会出现多语句事务的华,可以尝试增加该值的大小,以获得更有的性能。当然,我们可以通过 MySQL 的以下两个状态变量来判断当前的 binlog_cache_size 的状况:Binlog_cache_use 和 Binlog_cache_disk_use。
"max_binlog_cache_size":和 "binlog_cache_size" 相对应,但是所代表的是 binlog 能够使用的最大 cache 内存大小。当我们执行多语句事务的时候,max_binlog_cache_size 如果不够大的话,系统可能会报出 "Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage" 的错误。
"max_binlog_size":Binlog 日志最大值,一般来说设置为 512M 或者 1G,但不能超过 1G。该大小并不能非常严格控制 Binlog 大小,尤其是当到达 Binlog 比较靠近尾部而又遇到一个较大事务的时候,系统为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有 SQL 都记录进入当前日志,直到该事务结束。这一点和 Oracle 的 Redo 日志有点不一样,因为 Oracle 的 Redo 日志所记录的是数据文件的物理位置的变化,而且里面同时记录了 Redo 和 Undo 相关的信息,所以同一个事务是否在一个日志中对 Oracle 来说并不关键。而 MySQL 在 Binlog 中所记录的是数据库逻辑变化信息,MySQL 称之为 Event,实际上就是带来数据库变化的 DML 之类的 Query 语句。
"sync_binlog":这个参数是对于 MySQL 系统来说是至关重要的,他不仅影响到 Binlog 对 MySQL 所带来的性能损耗,而且还影响到 MySQL 中数据的完整性。对于 "sync_binlog" 参数的各种设置的说明如下:
sync_binlog=0,当事务提交之后,MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘,而让 Filesystem 自行决定什么时候来做同步,或者 cache 满了之后才同步到磁盘。
sync_binlog=n,当每进行 n 次事务提交之后,MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。
在 MySQL 中系统默认的设置是 sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统 Crash,在 binlog_cache 中的所有 binlog 信息都会被丢失。而当设置为 "1" 的时候,是最安全但是性能损耗最大的设置。因为当设置为 1 的时候,即使系统 Crash,也最多丢失 binlog_cache 中未完成的一个事务,对实际数据没有任何实质性影响。从以往经验和相关测试来看,对于高并发事务的系统来说,"sync_binlog" 设置为 0 和设置为 1 的系统写入性能差距可能高达 5 倍甚至更多。
另:
MySQL 的复制(Replication),实际上就是通过将 Master 端的 Binlog 通过利用 IO 线程通过网络复制到 Slave 端,然后再通过 SQL 线程解析 Binlog 中的日志再应用到数据库中来实现的。所以,Binlog 量的大小对 IO 线程以及 Msater 和 Slave 端之间的网络都会产生直接的影响。
MySQL 中 Binlog 的产生量是没办法改变的,只要我们的 Query 改变了数据库中的数据,那么就必须将该 Query 所对应的 Event 记录到 Binlog 中。那我们是不是就没有办法优化复制了呢?当然不是,在 MySQL 复制环境中,实际上是是有 8 个参数可以让我们控制需要复制或者需要忽略而不进行复制的 DB 或者 Table 的,分别为:
Binlog_Do_DB:设定哪些数据库(Schema)需要记录 Binlog;
Binlog_Ignore_DB:设定哪些数据库(Schema)不要记录 Binlog;
Replicate_Do_DB:设定需要复制的数据库(Schema),多个 DB 用逗号(",")分隔;
Replicate_Ignore_DB:设定可以忽略的数据库(Schema);
Replicate_Do_Table:设定需要复制的 Table;
Replicate_Ignore_Table:设定可以忽略的 Table;
Replicate_Wild_Do_Table:功能同 Replicate_Do_Table,但可以带通配符来进行设置;
Replicate_Wild_Ignore_Table:功能同 Replicate_Ignore_Table,可带通配符设置;
通过上面这八个参数,我们就可以非常方便按照实际需求,控制从 Master 端到 Slave 端的 Binlog 量尽可能的少,从而减小 Master 端到 Slave 端的网络流量,减少 IO 线程的 IO 量,还能减少 SQL 线程的解析与应用 SQL 的数量,最终达到改善 Slave 上的数据延时问题。
实际上,上面这八个参数中的前面两个是设置在 Master 端的,而后面六个参数则是设置在 Slave 端的。虽然前面两个参数和后面六个参数在功能上并没有非常直接的关系,但是对于优化 MySQL 的 Replication 来说都可以启到相似的功能。当然也有一定的区别,其主要区别如下:
如果在 Master 端设置前面两个参数,不仅仅会让 Master 端的 Binlog 记录所带来的 IO 量减少,还会让 Master 端的 IO 线程就可以减少 Binlog 的读取量,传递给 Slave 端的 IO 线程的 Binlog 量自然就会较少。这样做的好处是可以减少网络 IO,减少 Slave 端 IO 线程的 IO 量,减少 Slave 端的 SQL 线程的工作量,从而最大幅度的优化复制性能。当然,在 Master 端设置也存在一定的弊端,因为 MySQL 的判断是否需要复制某个 Event 不是根据产生该 Event 的 Query 所更改的数据
所在的 DB,而是根据执行 Query 时刻所在的默认 Schema,也就是我们登录时候指定的 DB 或者运行 "USEDATABASE" 中所指定的 DB。只有当前默认 DB 和配置中所设定的 DB 完全吻合的时候 IO 线程才会将该 Event 读取给 Slave 的 IO 线程。所以如果在系统中出现在默认 DB 和设定需要复制的 DB 不一样的情况下改变了需要复制的 DB 中某个 Table 的数据的时候,该 Event 是不会被复制到 Slave 中去的,这样就会造成 Slave 端的数据和 Master 的数据不一致的情况出现。同样,如果在默认 Schema 下更改了不需要复制的 Schema 中的数据,则会被复制到 Slave 端,当 Slave 端并没有该 Schema 的时候,则会造成复制出错而停止。
而如果是在 Slave 端设置后面的六个参数,在性能优化方面可能比在 Master 端要稍微逊色一点,因为不管是需要还是不需要复制的 Event 都被会被 IO 线程读取到 Slave 端,这样不仅仅增加了网络 IO 量,也给 Slave 端的 IO 线程增加了 RelayLog 的写入量。但是仍然可以减少 Slave 的 SQL 线程在 Slave 端的日志应用量。虽然性能方面稍有逊色,但是在 Slave 端设置复制过滤机制,可以保证不会出现因为默认 Schema 的问题而造成 Slave 和 Master 数据不一致或者复制出错的问题。
3)慢查询日志 Query Log 相关参数及使用建议
再来看看 SlowQueryLog 的相关参数配置。有些时候,我们为了定位系统中效率比较地下的 Query 语句,则需要打开慢查询日志,也就是 SlowQueryLog。我们可以如下查看系统慢查询日志的相关设置:
- mysql> show variables like 'log_slow%';
- +------------------+-------+
- | Variable_name | Value |
- +------------------+-------+
- | log_slow_queries | ON |
- +------------------+-------+
- 1 row in set (0.00 sec)
- mysql> show variables like 'long_query%';
- +-----------------+-------+
- | Variable_name | Value |
- +-----------------+-------+
- | long_query_time | 1 |
- +-----------------+-------+
- 1 row in set (0.01 sec)
"log_slow_queries" 参数显示了系统是否已经打开 SlowQueryLog 功能,而 "long_query_time" 参数则告诉我们当前系统设置的 SlowQuery 记录执行时间超过多长的 Query。在 MySQLAB 发行的 MySQL 版本中 SlowQueryLog 可以设置的最短慢查询时间为 1 秒,这在有些时候可能没办法完全满足我们的要求,如果希望能够进一步缩短慢查询的时间限制,可以使用 Percona 提供的 microslow-patch(件成为 mslPatch)来突破该限制。mslpatch 不仅仅能将慢查询时间减小到毫秒级别,同时还能通过一些特定的规则来过滤记录的 SQL,如仅记录涉及到某个表的 SlowQuery 等等附加功能。
打开 SlowQueryLog 功能对系统性能的整体影响没有 Binlog 那么大,毕竟 SlowQueryLog 的数据量比较小,带来的 IO 损耗也就较小,但是,系统需要计算每一条 Query 的执行时间,所以消耗总是会有一些的,主要是 CPU 方面的消耗。如果大家的系统在 CPU 资源足够丰富的时候,可以不必在乎这一点点损耗,毕竟他可能会给我们带来更大性能优化的收获。但如果我们的 CPU 资源也比较紧张的时候,也完全可以在大部分时候关闭该功能,而只需要间断性的打开 SlowQueryLog 功能来定位可能存在的慢查询。
MySQL 的其他日志由于使用很少(QueryLog)或者性能影响很少,在此就不做过多分析了。
以上这篇 Mysql 数据库之 Binlog 日志使用总结 (必看篇) 就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持 PHPERZ。
来源: http://www.phperz.com/article/17/0812/339356.html