centos7.2 mysql数据库同步时数据一致性的配置优化
时间:2018-01-16 15:38 来源:linux.it.net.cn 作者:IT
mysq的同步复制的，不管时主从，还是主主，都时利用的mysql自身的复制机制，而这个同步是有可能造成主从数据不一致的，master宕机也有可能导致数据丢失，而在生产环境中， 每一数据都是要尽量不等丢失，所以，为了提高主从数据一致性和稳定性，降低丢失数据的可能，就需要进行一些配置优化，使用一些特别的设定，使得数据安全更好，但是同时也会影响到mysql数据库的性能，所以就需要在安全和性能之间进行取舍。根据使用的具体环境，选择最适合自身的数据库安全设置。
下面就一一介绍和列举几个对主从数据复制安全比较重要的几个配置参数：
MySQL版本为5.6
1.master主库上设置：
innodb_flush_log_at_trx_commit
=0： log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提 交的时候，不会主动触发写入磁盘的操作。
=1： 每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.
=2： 每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该 模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。
注意：由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。
sync_binlog
=0 ，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
=N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志 binary log同步到磁盘中去。
注:如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。
最好的安全设置为：
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
但这样的性能也是最低，每次写入都会造成大量的磁盘IO，适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统等。
反之，如果压力较大，推荐的做法是 innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache，防止系统断电异常。
binlog_format=row
数据库复制的模式，主要有三种模式：
① STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
② ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
③ MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
在生产环境中，对数据安全比较看重，普遍使用row模式，任何情况都可以被复制，这对复制来说是最安全可靠
双主互为主备的情况下：
log_slave_updates=1
默认是关闭的，这样在双主的情况下会出现复制问题：
M01同步从M02同步数据过来的时候，log_slave_updates参数用来控制M01是否把所有的操作写入到binary log，默认的情况下mysql是关闭的;
R01数据的更新需要通过读取到M01的binary log才能进行更新，这个时候M01是没有写binary log的，所以当数据从M02写入的时候，R01也就没有更新了。。
2.slave从库上设置：
master_info_repository = "TABLE"
relay_log_info_repository = "TABLE"
relay_log_recovery = 1
这样设置，保证了从机不管怎么宕机，重启后都能保准数据一致性，不会导致从库已经插入数据，但还没写入到relay-log.info文件记录，而导致重启后重新重复插入数据的出错的情况。
最后，总结配置：
master：
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
binlog_format=row
slave：
master_info_repository = "TABLE"
relay_log_info_repository = "TABLE"
relay_log_recovery = 1
上面的配置是以数据安全为主进行的配置，如果要更改，请根据实际情况进行与性能的取舍。
(责任编辑：IT)
| mysq的同步复制的，不管时主从，还是主主，都时利用的mysql自身的复制机制，而这个同步是有可能造成主从数据不一致的，master宕机也有可能导致数据丢失，而在生产环境中， 每一数据都是要尽量不等丢失，所以，为了提高主从数据一致性和稳定性，降低丢失数据的可能，就需要进行一些配置优化，使用一些特别的设定，使得数据安全更好，但是同时也会影响到mysql数据库的性能，所以就需要在安全和性能之间进行取舍。根据使用的具体环境，选择最适合自身的数据库安全设置。 下面就一一介绍和列举几个对主从数据复制安全比较重要的几个配置参数： MySQL版本为5.6 1.master主库上设置： innodb_flush_log_at_trx_commit =0： log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提 交的时候，不会主动触发写入磁盘的操作。 =1： 每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去. =2： 每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该 模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。 注意：由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。 sync_binlog =0 ，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。 =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志 binary log同步到磁盘中去。 注:如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。 最好的安全设置为： innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 但这样的性能也是最低，每次写入都会造成大量的磁盘IO，适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统等。 反之，如果压力较大，推荐的做法是 innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache，防止系统断电异常。 binlog_format=row 数据库复制的模式，主要有三种模式： ① STATEMENT模式（SBR） 每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题) ② ROW模式（RBR） 不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。 ③ MIXED模式（MBR） 以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。 在生产环境中，对数据安全比较看重，普遍使用row模式，任何情况都可以被复制，这对复制来说是最安全可靠 双主互为主备的情况下： log_slave_updates=1 默认是关闭的，这样在双主的情况下会出现复制问题： M01同步从M02同步数据过来的时候，log_slave_updates参数用来控制M01是否把所有的操作写入到binary log，默认的情况下mysql是关闭的; R01数据的更新需要通过读取到M01的binary log才能进行更新，这个时候M01是没有写binary log的，所以当数据从M02写入的时候，R01也就没有更新了。。 2.slave从库上设置： master_info_repository = "TABLE" relay_log_info_repository = "TABLE" relay_log_recovery = 1 这样设置，保证了从机不管怎么宕机，重启后都能保准数据一致性，不会导致从库已经插入数据，但还没写入到relay-log.info文件记录，而导致重启后重新重复插入数据的出错的情况。 最后，总结配置： master： innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 binlog_format=row slave： master_info_repository = "TABLE" relay_log_info_repository = "TABLE" relay_log_recovery = 1 上面的配置是以数据安全为主进行的配置，如果要更改，请根据实际情况进行与性能的取舍。 (责任编辑：IT) |