mysql复制参数讲解

43212gg

mysql复制参数讲解
1、master上的参数
log-bin                                          #必须
server-id #1-pow(2,32)-1        #必须 (端口+ip)
server-uuid  #路径为$datadir/auto.cnf
log-bin-index                                #必须
binlog-format
binlog_cache_size
max_binlog_size
sync_binlog
expire_logs_days
log_bin_trust_function_creators

2、配置参数(IGNORE/DO规则的炕)
log-bin
        binlog文件名前缀，可以全路径
        log-bin=mybinlog
        log-bin=/data/mysql/mysql3306/mybinlog
        不可动态修改
       
server-id
        唯一区别ID，同一个集群内不可重复
        可动态修改从mysql5.6开始

log-bin-index
        binlog索引文件前缀名,和log-bin一样，也可以全路径
        不可动态修改
       
binlog-format
        binlog日志格式：statement|row|mixed 三种
        可动态修改
binlog_cache_size  建议1M到2M
        binlog写入buffer
        可动态修改参数
       
max_binlog_size 默认是1G 推荐128M 或256M
        限制单个binlog大小
        可动态修改
       
sync_binlog=n
        #交易系统为了安全 sync_binlog=1
        多少个sql之后，调用fdatasync函数刷新binlog到disk
        fsync()/fdatasync区别是什么？
        fsync是完全伤心到磁盘，fdatasync只刷新数据，不刷新metadata
        可动态修改

expire_logs_days=n  推荐保存7天
        n天后自动删除binlog
        可动态修改
       
log_bin_trust_function_creators=n  一般情况下为1就行
        开启binlog时,是否允许创建存储过程
        可动态修改
       
3、特殊说明:

log_warnings=n 最好为1
不然会在错误日志看到异常断开的连接(Aborted connection)
MySQL5.6.5 MySQL5.7.5以后 binlog v2格式 GTID binlog_checksum

迁移报错 5.6 -> 5.5  5.5 -> 5.6
binlog_checksum=none
log_bin_use_v1_row_events=on

binlog_row_image5.6.2后出现
默认为full

binlog_rows_query_log_events

binlog_gtid_recovery_simpliefied

Crash Recovery
基于MySQL5.6GTID的crash recovery 是有mysql-5.6.21支持到5.6.23稳定
传统复制里面也是可以支持的
root@localhost [test]>show global variables like "%repo%";
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| master_info_repository    | FILE  |
| relay_log_info_repository | FILE  |

FILE------>table



友好的报错信息设置
binlog_error_action                     | ABORT_SERVER
binlogging_impossiable_mode----->binlog_error_action

root@localhost [test]>show global variables like "%binlog%";
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 1048576              |
| binlog_checksum                         | CRC32                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_error_action                     | ABORT_SERVER         |
| binlog_format                           | ROW                  |
| binlog_group_commit_sync_delay          | 0                    |
| binlog_group_commit_sync_no_delay_count | 0                    |
| binlog_gtid_simple_recovery             | ON                   |
| 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                |
| innodb_api_enable_binlog                | OFF                  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_statements_unsafe_for_binlog        | ON                   |
| max_binlog_cache_size                   | 1048576              |
| max_binlog_size                         | 268435456            |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
20 rows in set (0.00 sec)

4、master其他参数
auto_increment_increment
auto_increment_offset
bin-do-db
bin-ignore-db
max_binlog_cache_size
binlog_stmt_cache_size
max_binlog_stmt_cache_size
biglog_direct_non_transactional_updates

5、具体配置参数的炕：
auto_increment_increment：每次自增的步长
auto_increment_offset：自增起始的偏移量  pxc

bin-do-db：记录db，其他db的binlog都不记录,存在风险  不建议设置
配置案例：
bin-do-db=mydb1
bin-do-db=mydb2

bin-ignore-db
bin-ignore-db=test 表示test这个库的binlog记录都不被记录
建议：所有过滤规则不建议在主库中设置(bin-do-db、bin-ignore-db)

max_binlog_cache_size 默认就行

biglog_direct_non_transactional_updates默认不动

6、gtid相关参数
show global variables like "%gtid%";
Current database: test

+----------------------------------+------------------------------------------+
| Variable_name                    | Value                                    |
+----------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                       |
| enforce_gtid_consistency         | ON                                       |
| gtid_executed                    | 6e5d664c-c7e5-11e6-88b1-000c296ae445:1-9 |
| gtid_executed_compression_period | 1000                                     |
| gtid_mode                        | ON                                       |
| gtid_owned                       |                                          |
| gtid_purged                      |                                          |
| session_track_gtids              | OFF                                      |
+----------------------------------+------------------------------------------+
如果开启了gtid(用于控制只有是事务安全的语句才能被记录到log中)
gtid_mode用于控制是否启用GTID
如果设置为on,则必须是log-bin ,log-salve-updates,enforce_gtid_consistency也要启用

gtid_purged：        用于指定那个事务从binlog忽略了。reset master 这个值会被清空。
gtid_executed：        执行到的GTID号及所有执行的gtid号。和(show master status,show slave status)输出值Executed Gtid set意思一样。
rest master ：        这个值会被清空。

总结：主库开启最少参数
server-id
server-uuid
log-bin
gtid_mode
enforce_gtid_consistency
max_binlog_size
expire_logs_days
binlog-format

#############################################################################################################################################

7、slave上的参数
server-id
server-uuid
relay-log  #io thread读取过来存到本地文件
relay-log-index
read-only  #从库只读，但对super权限的用户不起作用   这个设置意义不大，一般不要设置。

8、具体配置参数的炕：
server-id:
relay-log:relaylog文件名前缀，可以是全路径,不可以动态更改
relay-log-index:relaylog 索引文件前缀名 和relay-log一样，可以全路径；不可以动态更改。
read-only：除非有super权限，否则无法修改数据，不过不影响自主创建临时表(temporary table ,analyze table,optimize table) 可以动态修改。


9、salve其他参数
log-slow-salve-statements
log_slave_updates  #建议这个参数无论如何都要设置。作用：主库更新，从库会记录日志。
max_relay_log_size
relay_log_info_file
relay_log_purge
relay_log_recovery
replicate-same-server-id #不建议使用
skip-slave-start=1  #不要自动同步   最好设置为1
slave_load_tmpdir
slave_transaction_retries #默认就行，重试
slave_parallel_workers #默认是关闭的


#例子
log_slave_updates  已经开启
root@localhost [wu]>show global variables like "%log%"
root@localhost [test]>show master status;
/usr/local/mysql/bin/mysqlbinlog -v--base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.000002 >1.log


relay_log_space_limit 默认没有设置的。有问题才设置

下面参数不建议设置：
show global variables like "%report%";
| report_host     |       |
| report_password |       |
| report_port     | 3306  |
| report_user


log-slow-slave-statements:slave复制产生的slow log 是否需要记录;建议打开。

max_relay_log_size 建议128M

master_info_repository relay_log_info_repository参数设置如下：
master-info-file/relay-log-info-file/relay-log-info
----->master_info_repository relay_log_info_repository
root@localhost [wu]>show global variables like "%info%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    17
Current database: wu

+--------------------------------+----------------+
| Variable_name                  | Value          |
+--------------------------------+----------------+
| master_info_repository         | FILE           |
| relay_log_info_file            | relay-log.info |
| relay_log_info_repository      | FILE           |
| session_track_transaction_info | OFF            |
| sync_master_info               | 10000          |
| sync_relay_log_info            | 10000          |
+--------------------------------+----------------+
6 rows in set (0.00 sec)

发展趋势 file将来被换成table

sync_master_info=n ：一般情况下不要动   除非安全性要求非常高才设置。

relay_log_purge ：relay应用完。是否需要自动马上删除

relay_log_recovery： salve崩溃或者正常启动时,未应用的relay log 会被删除，重新从master请求binlog,再次生成relay log

10、slave过滤参数
一般情况下，过滤规则在从库设置。不要在主库设置。
replicate-do-table
replicate-ignore-table
replicate-rewrite-db        =db1->db2
replicate-wild-do-table
replicate-wild-ignore-table
replicate-do-db
replicate-ignore-db
slave_net_timeout
slave_skip_errors
sql_slave_skip_count