MySql Replication主要配置项和通用配置项讲解

Replication主要配置项(配置文件)

1、log_bin：指定binlog文件的名称，同时也表示开启binlog功能，在replication模式下，master上必须开启log_bin，如果slave不需要failover，可以不开启。文件将会放置在“datadir”目录下。

2、binlog_checksum：是否开启binlog校验功能，在5.6.6+之后此值默认为“CRC32”，此前版本默认为“NONE”表示不开启校验和算法;用于控制master将每个变更操作写入binlog时是否携带checksum值，使用checksum可以在replication时，slave用于校验变更操作在网络传输时是否被破坏。

3、binlog_format：可选值“MIXED”、“ROW”、“STATEMENT”，在5.6版本之前默认为“STATEMENT”，5.6之后默认为“MIXED”;因为“STATEMENT”方式在处理一些“不确定”性的方法时会造成数据不一致问题，我们建议使用“MIXED”或者“ROW”。

4、sync_binlog：binlog文件刷新磁盘的时机，默认为“0”，表示binlog刷新磁盘的时机有操作系统决定，“1”表示每个变更操作写入binlog后立即刷新磁盘，这是最安全的方式，在crash时最多丢失一个“group”，当然也是磁盘效率最低的一种;其他任何大于0的数字，表示“多少次”变更操作写入binlog文件后执行flush。此值越大，对磁盘IO消耗越小，当回额外消耗内存，效率越高。

5、binlog_row_image：可选值“full”、“minimal”、“noblob”，默认值为“full”，对“STATEMENT”复制模式无效;每个变更操作发生时，都有2个“image”，“before”表示变更前此行所有列的值，“after”表示变更后此行所有列的值;“full”表示在binlog中将会记录before和after的全部信息，易于排错，但是会导致日志量很大，对磁盘空间和网络传输都有一定的影响;“minimal”只记录before中能够唯一标记此行的列值，比如主键或者唯一索引键，在after中只记录那些有值变更的列;“noblob”类似于full，但是不记录那些没有变更的blob或者text类型的列。

默认值为“full”，因为before和after均写入了binlog，所以通常不会带来问题;如果使用minimal或noblob，我们需要首先确保所有的表都有“主键”或者“唯一索引键”，且slave和master上的表结构完全一致，这样binlog只需要在before中记录能够唯一确定此行的的列即可，否则可能会引入数据不一致的问题。

6、log_slave_updates：默认值为“FALSE”，slave在读取relay log后执行变更操作时，是否也将变更操作再次保存在本地的binlog中，就像变更操作在本地发生一样，开启此特性时也需要同时开启binlog。如果你的slave后端仍有其他slave跟进，需要开启此特性;比如A<-B<-C链状模式，A是B的master，B为C的master，因此A和B需要同时开启binlog和log_slave_updates，否则C将无法正常replication。

7、max_binlog_size：binlog文件的大小，单位“字节”，默认为1G;即当binlog的尺寸大于此值时，将会滚动生成新的binlog文件，但是同一个事务将不会被分割在2个binlog文件中。如果没有设定“max_relay_log_size”配置项，那么此选项也会控制relay log的大小。

8、log_bin_index：binlog索引文件名称，此文件记录当前所有正在使用的binlog文件列表。默认与binlog文件名一样，只是后缀为“.index”。

9、expire_logs_days：binlog文件保存的天数，超过指定天数后，binlog历史文件将被删除;默认为“0”表示“不自动删除、永久保存”。通常我们建议不修改此值，即永久保存binlog日志，以备将来一旦数据操作失误时，可以用于恢复。如果磁盘空间有限，可以将binlog备份到其他存储平台，而在本地使用“PURGE BINARY LOGS”指令清除早期的binlog历史文件，释放磁盘空间。

10、master_verify_checksum：master在读取binlog时是否校验，与“binlog_checksum”对应，默认为“false”即关闭。

11、innodb_flush_log_at_trx_commit：innodb表中事务写入binlog后刷新磁盘的时机，配合“sync_binlog”参数使用，默认值为“1”表示每个事务提交后立即刷新binlog文件，是目前最安全的方式。“0”表示缓存事务，每隔1秒钟(innodb_flush_log_at_timeout控制)批量写入binlog并刷新一次磁盘，当crash时这种方式不能100%保证事务全部都写入磁盘，binlog中最多会丢失1秒内的事务。“2”表示事务提交后立即写入binlog文件，每隔一秒钟刷新一次磁盘，它和“1”并没有太大区别，只不过这个方式下操作系统也会影响文件的刷新时机。

12、binlog_order_commits：默认为“true”，事务提交的顺序与写入binlog的顺序保持一致，即在事务提交时会锁定binlog并写入;如果关闭此特性，事务将会并发的提交，那么binlog中事务的顺序有可能与其实际提交的顺序不同，恢复replication带来一定的影响，不过这种方式会提升一定的并发性。

13、server_id：当前mysql实例的id，整个replication中所有的实例的id都不能重复，而且每个server都必须配置此选项，此值是一个数字，integer型。

14、relay_log：relay log的文件名，建议在master和slave上均配置。

15、relay_log_index：relay log索引文件名称。

16、relay_log_info_repository：可选值为“FILE”、“TABLE”，用于保存slave读取relay log的位置信息，以便crash重启后继续恢复;“FILE”表示将信息写入relay-log.info文件，“TABLE”表示将信息写入mysql.slave_relay_log_info表中。

17、master_info_repository：可选值“FILE”、“TABLE”，将master的状态和链接信息保存何处，FILE则表示保存在master.info文件中，TABLE表示保存在mysql.slave_master_info表中。

18、slave_net_timeout：slave与master链接IO超时的时间，超时后将会中断read，默认为3600。

19、slave_parallel_workers：设置执行replication的worker线程的数量，默认为0，表示关闭多线程，此时slave只有一个SQL线程。如果开启多线程，slave中的SQL线程将作为worker线程池的“协调器”，SQL线程从relay log中读取变更操作记录，并以database为分离标准将操作分发给相应的workers线程，每个worker线程在一端时间内将持续执行一个database的变更操作，简单而言，就是不同的worker线程执行不同database的操作，多个database数据将在slave上被并行的执行，为了能够正确的工作，事务不能跨多个databases。

此值用于控制worker线程的个数，“协调器”线程不包含在内，我们在slave上通过“SHOW PROCESSLIST”指令可以查看到SQL线程、worker线程的数据和状态。

开启多线程时，并发执行会导致，在slaves上不同的databases被执行的时机可能与master上不同，归因于线程上线文的切换和CPU资源分配的不确定性;即一个事务执行后，不能保证此事务之前的其他事务(其他databases)也已经执行成功，这或许会对slave上binlog的记录、数据恢复和客户端数据访问带来影响。建议线程数为“database个数”，每个database一个worker线程;也有很多DBA将此值设置为2，即只用一个worker线程，这就不会带来事务顺序与master不一致的问题;需要强调的是，无论多少个线程，单个database的事务总是在一个worker中执行，单个database的事务顺序总是正确的。

20、slave_pending_jobs_size_max：仅对多线程有效，SQL线程将变更操作读取后分发给worker线程，每个worker线程以队列的方式缓存亟待执行的变更操作记录，此处用于控制队列的大小，默认为16M，单位为“字节”;此值不得大于“max_allowed_packet”。

21、slave_sql_verify_checksum：slave中SQL线程是否使用校验和检测relay log中的变更操作，与“binlog_checksum”配合，默认为“1”表示开启。

22、slave_transaction_retries：如果SQL线程在执行事务时发生InnoDB死锁且等待超时后，slave重试的次数，默认为10，如果超过此次数，slave将会抛出error且终止replication;此值在“slave_parallel_workers”开启时无效，即为0，不重试。

23、sync_relay_log：slave将多少条变更操作写入relay log后刷新磁盘文件，默认为10000;“0”表示刷新时机有操作系统决定，“1”表示每写入一条变更操作即立即刷新，是最安全的方式，不过因为relay log的安全级别与binlog还是要低一些，建议保持默认值，这通常也不会带来问题。

24、gtid_mode：是否开启GTID特性，可选值为“ON”、“OFF”;在replication模式下我们应该开启。

25、enforce_gtid_consistency：如果gtid_mode=ON，此值必须开启，可选值为“ON”、“OFF”，表示是否强制gtid的“一致性”避免并发操作。

26、binlog_ignore_db：binlog中忽略的db，master与slave均可以配置，通常在master端。如果需要ignore多个databases，此配置允许声明多次，每次指定一个db。

27、binlog_do_db：指定可以写入binlog的database名字，与binlog_ignore_db相反，只有指定的db才会写入binlog。此配置允许声明多次，每次指定一个db。(上述两个如果在slave上配置，可能还需要开启“log_slave_updates”，默认情况下，slaves端是不写binlog的)

28、replicate_do_db：同上，只是在slave端生效，slave中的SQL线程在读取relay log时，会检测此变更操作所属DB，SQL线程只会执行此配置参数指定的db。如果不声明replicate_do_db，表示所有的db都会执行。此配置允许声明多次，每次指定一个db。与此配置项对应的还有“replicate_ignore_db=”、“replicate_db_table=.

”、“replicate_ignore_table=.”。

29、read_only：当前实例是否为“只读”模式，在此模式下除了具有“SUPER”权限的用户外，其他权限用户均不可以修改数据。SUPER权限包括(举例)：CHANGE MASTER TO、KILL、SET GLOABL等。通常slave上，需要设定read_only=1以避免客户端意外修改数据，而造成数据错乱问题。默认为“0”(OFF)表示关闭。read_only并不会影响replication进程对数据的变更。

30、super_read_only：对于具有SUPER权限的用户，是否也只读，默认为“0”(OFF)表示关闭。super_read_onl开启时，也会隐式的开启“read_only”参数。

31、slave_preserve_commit_order：对于多线程slaves，来保障事务在slave上执行的顺序与relay log中的顺序严格一致，只有当“slave_parallel_workers”开启时有效;此时“log_bin”、“log_slave_updates”必须开启，而且“slave_parallel_type”值必须为“LOGICAL_CLOCK”(默认值为DATABASE)。即当多线程开启时，且根据relay log中事务的逻辑顺序执行statements，是否需要严格保持顺序，默认值为0表示并发执行忽略顺序。

slave_parallel_type默认为DATABASE，原理参见18，能够确保每个DATABASE中的事务顺序严格一致，但是无法保证所有databases的事务执行顺序也是严格一致的(gap)，比如两个事务依次操作了2个DB：A和B，尽管事务A、B分别被worker X、Y线程接收，但是因为线程调度的问题，有可能导致A的执行时机落后于B。

如果你的事务经常是“跨DB”操作，那么可以考虑使用此参数限定顺序。当此参数开启时，这要求任何worker线程执行的事务时，只有当前事务中此之前的所有事务都执行后(被其他worker线程执行)，才能执行和提交。(每个事务中，都记录了当前GTID的privious GTID，只有privious GTID被提交后，当前GTID事务才能提交)

通用配置项(配置文件)

下划线的参数表示系统变量，不能在配置文件中使用。

1、bind_address：绑定的网卡ip地址，默认为“0.0.0.0”，即绑定本机的所有网卡地址;每个机器至少包括内网IP和外网IP，如果此mysql只能被内网访问，那么请绑定在内网IP上。

2、port：绑定的端口，默认为3306。

3、pid_file：pid文件的名称。

4、autocommit：是否开启事务自动提交模式，默认为“1”表示开启;如果为“0”，客户端需要声明事务提交的时机，“START TRANSCATION”、“COMMIT”、“ROLLBACK”等。

5、bulk_insert_buffer_size：批量INSERT时的缓冲区大小，我们经常用“INSERT INTO …VALUES(…),(…),(…)…”使用一条INSERT插入多行记录，此值用于控制buffer的最大容量，如果超过此值insert将会被拒绝，默认为8M，单位“字节”。

6、character_set_server：为了避免乱码，建议上述值以及客户端使用的编码保持一致，我们通常选用兼容域更大的字符集，比如UTF8等。

7、concurrent_insert：仅对MyISAM引擎有效，表示是否支持并发插入。“0”表示禁用并发插入;“1”表示没有空洞时支持并发插入，如果有空洞，就首先填充;“2”表示总是支持并发插入，即使表文件有空洞(holes)，当表正在被其他线程执行INSERT时，新的INSERT将直接在表文件尾部插入，如果没有并发，才会填充空洞。所谓空洞就是表文件中因为数据删除，而遗留下的未被使用的空间。(因为innodb使用的是聚簇索引，所以也不存在并发插入的问题，insert总是在合适的位置被插入–通常为了提高性能，主键总是自增的，即insert总是在数据文件的尾部发生)

8、connect_timeout：链接超时时间，默认为10秒。

9、datadir：数据目录。

10、default_storage_engine：默认值为“InnoDB”，如果你的数据库不需要事务支持，可以使用“MyISAM”作为默认引擎。引擎的类型也可以在创建table时指定。

11、flush：每个变更操作执行后(即写入数据文件后)是否立即刷新磁盘，默认为“0”表示关闭，刷新时机有操作系统决定;如果你的数据库保存的数据非常重要，可以开启此值(值为1)。

12、interactive_timeout：链接空闲的最长时间，单位“秒”，默认为28800(即8个小时);通常链接有客户端关闭，我们可以不用调整此值。

13、key_buffer_size：MyISAM引擎中，内存中缓存索引的最大尺寸，单位“字节”，默认为8M;如果你的系统由较多的MyISAM表，且建立了索引，你可以通常增加此值来提高读取性能。

14、lock_wait_timeout：尝试获取metadata锁时等待的最长时间，比如DDL操作，或者在DML语句中使用“LOCK TABLES”、“FLUSH TABLES WITH READ LOCK”等。单位“秒”。

15、log_error：错误日志的文件名。

16、log_output：可选值为“FILE”、“TABLE”，表示日志输出在哪里。

17、long_query_time：如果一个query耗时超过此值，将被认定为“慢查询”，SQL语句将会被写入到慢查询log中。单位“秒”，默认值为10，通常这个值有些小，我们可以适度增加此值。

18、lower_case_table_names：表名是否是大小写敏感的，默认为“0”表示区分大小写，“1”表示表名将以小写方式保存且在比较时大小写不敏感，“2”表示表名原样保存但是比较时不区分大小写。因为表名的大小写经常会带来麻烦，建议使用“1”或者“2”。

19、max_allowed_packet：server与client单此交互所能接收、发送的最大数据集，单位“字节”，默认为4M。

20、max_connections：所能支撑的最大连接数，默认为151，如果超过此值，将会拒绝链接且反馈“Too many connections”错误，对于production环境，此值显然有些小，建议修改为“65535”等，server所能支撑的最大连接数仍受限于本地系统最大文件描述符的个数，可以通过“ulimit”等方式调整。

21、max_relay_log_size：原理基本同“max_binlog_size”，默认为“0”表示relay log的大小由“max_binlog_size”参数控制。单位“字节数”。

22、preload_buffer_size：索引文件预加载到内存的大小，默认为32K，单位“字节”;建议增大此值。

23、relay_log_purge：是否开启relay log自动清除功能，默认为“1”表示开启。

24、report_host：在replication模式中，slave向master注册时告知的host名称，此值通常为slave的ip地址(建议与bind_address保持一致)，仅供展示。

25、report_port：通上，此值与port保持一致，仅供展示。

26、report_user：replicaiton的用户名，此值可以不需要与实际使用的replication用户名一致，仅供展示。(在master上使用“SHOW SLAVE HOSTS”查看)

27、rpl_semi_sync_master_enabled：是否在master上开启“半同步”replication特性，默认为“0”表示关闭;如果replicaiton集群中需要采用半同步机制，那么master上必须开启。(需要安装semisync_master插件)

28、rpl_semi_sync_master_timeout：在半同步模式中，master将变更操作提交给“半同步”slave后，等待响应的最长时间，默认为10000，单位“毫秒”;如果超时，此master与此slave将转换成“异步”replication，直到此slave再次跟进为止。(需要安装semisync_master插件)

29、rpl_semi_sync_master_wait_no_slave：在timeout期间，所有的slave都离线时，master是否继续等待，默认为“1”表示继续等待，“0”表示master不再等待，转换成异步replication模式。(需要安装semisync_master插件)

39、rpl_semi_sync_slave_enabled：是否在slave上开启半同步模式，默认为“0”表示关闭。(需要安装semisync_slave插件)

31、slow_query_log：是否开启“慢查询”日志功能，配合“long_query_time”参数。

32、slow_query_log_file：指定慢查询日志文件的名称，默认为“–slow.log”。

33、socket：指定socket文件的路径和文件名，即unix socket file。

35、thread_pool_size：5.6新增参数，用于设定线程池的大小，默认为16，最大不得超过64;线程池中的线程用户并行的接收和执行客户端的SQL语句，合适的线程池大小对性能有很大的提升。(5.7中移除了此选项)

36、tx_isolation：事务的隔离级别，默认值为“REPEATABLE-READ”。(此参数为系统变量，不能在配置文件中声明)

37、innodb_buffer_pool_size：innodb用缓存数据和索引的内存大小，默认为128M，单位“字节”，通常设定为内存总量的20%~80%之间。

38、innodb_commit_concurrency：innodb允许多少个线程并发的提交事务，默认为“0”表示不限制线程并发数，允许的最大值为1000。(针对整个innodb层)

39、innodb_thread_concurrency：innodb允许的最大并发线程数，默认为“0”表示不限制，最大值不得超过1000;如果并发线程数到达限定值，其他线程将会被加入等待队列，正在等待lock的线程不计为并发线程数。如果你要修改此值，需要经过测试和性能对比才能决定，如果你无法断定，可以将此值暂且设定为CPU的核数或者保持默认值。(针对整个innodb层)

40、innodb_concurrency_tickets：innodb线程持有的tickets数量，默认值为5000。如果并发数达到上限，后续线程将会被添加到等待队列中(由innodb_thread_concurrency控制);当线程被允许进入innodb层后，会给此线程分配“innodb_concurrency_tickets”个tickets，线程可以*进入innodb层直到tickets消耗完毕(进入即消耗)，此后线程需要重新进行并发检查(innodb_thread_concurrency),有可能重新进入队列等待。

较小的innodb_concurrency_tickets，对于只处理较少个操作的小事务影响不大，如果一个事务中需要多个操作，那么它需要多次访问innodb层，有可能会耗尽tickets，进而需要多次进行并发检查，这将导致事务的处理时间较长;如果你要减小此值，需要慎重考虑。

41、innodb_doublewrite：是否开启“双写”特性，默认为“1”表示开启;双写就是数据变更首先写入“buffer”，然后再写入实际的数据文件(data files)，可以提高数据完整性;不建议关闭!

42、innodb_file_per_table：是否将不同的innodb表数据(数据和索引)保存在各自的.ibd文件中，而不是保存在系统表空间中;它的优点就是，当table被drop或者truncate时，此表的存储空间可以被回收;在5.6.6+版本之后，默认为“1”表示开启，此前版本默认为关闭。当关闭此特性时，InnoDB将所有的表和索引保存在系统表空间的idb文件中，这种存储方式对处理“drop”和“truncate”表时性能较低，因为删除表数据不会导致innodb数据文件的收缩，毕竟所有的innodb表共享底层存储文件空间。

43、innodb_flush_log_at_timeout：默认值为1，单位“秒”，当“innodb_flush_log_at_trx_commit”值为0或者2时，表示binlog刷新的时间间隔。

44、innodb_lock_wait_timeout：事务等待获取行锁的最长时间，默认为50，单位“秒”，一旦超时将会抛出timeout错误。这个参数不会应用在deadlock上，因为mysql有死锁检查，一旦发现死锁将立即回滚而不会等待。

45、innodb_read_io_thread、innodb_write_io_thread：IO线程的个数，此值建议保持默认，如果你的系统是基于固态硬盘等更高速的存储设备，可以适当调大此值，默认值为4，最大为64。

46、innodb_sort_buffer_size：排序时使用的内存量，默认为1m，单位“字节”，如果排序时内存需要量超过此值将会交换到文件中，对于线上环境，我们可以适度调大此值。

47、innodb_support_xa：是否支持XA事务，默认为1表示支持。