Mysql Replication运维与实施（三）

QING____

浏览: 2250105 次
性别:
来自: 北京

最近访客更多访客>>

wusuosuo

sd_zf

lingxiajiudu

a123abc123abc

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

MYSQL

十三、Replication部署准备

我们在三台机器上构建Replication架构，使用MySQL 5.7.10：

1）三个mysql实例分别为“mysql-1”、“mysql-2”、“mysql-3”

2）IP分别为“192.168.1.100”、“192.168.1.101”、“192.168.1.102”，其中mysql-1为初始master。

3）mysql安装目录为“/usr/local/mysql”，即“basedir”。

4）配置文件为my.cnf，放置在安装目录下。

5）他们的datadir为“/data/mysql”，在构建replicaiton架构之前，我们首先准备实例化三个mysql实例：

1、简要配置文件

[mysqld]
server_id=10
bind_address=0.0.0.0  
#绑定端口  
port=3306  
socket=/tmp/mysql.socket  
#mysql的安装路径  
basedir=/usr/local/mysql  
#数据文件保存的目录  
datadir=/data/mysql

#binlog
log_bin=my-binlog  
binlog_checksum=CRC32  
binlog_format=MIXED  
sync_binlog=1  
binlog_row_image=FULL    
max_binlog_size=2G  
binlog_order_commits=1  
##binlog保存的时间，单位“天”  
##自动删除早期的binlog文件  
expire_logs_days=180  
#开启GTID，强烈建议开启  
gtid_mode=ON
enforce_gtid_consistency=ON

#replication模式下选项  
relay_log_purge=1  
##当前实例的IP地址  
report_host=192.168.1.100  
##当前实例的端口  
report_port=3306  
##replication的用户账号名  
report_user=rpl_sys  
##slave上是否将执行的变更操作写入binlog  
log_slave_updates=0  
master_verify_checksum=1  
relay_log=relay-bin  
relay_log_info_repository=TABLE  
sync_relay_log=1  
master_info_repository=TABLE  
#slave与master链接超时时间  
slave_net_timeout=3600  
##建议为database个数 + 1，或者简单为2  
slave_parallel_workers=2  
slave_sql_verify_checksum=1

配置文件内容在三个节点上基本保持一致，全部配置信息请参考【MySQL配置】，如果使用“伪分布式”部署，那么需要将datadir等参数调整以防止冲突。

2、依次初始化三个mysql：

#mysql
>sudo ./mysqld --datadir=/data/mysql --initialize --user=mysql

3、用户权限：

因为“mysql”这个系统默认的数据库，通常不能（严格来说绝对不能）被replication；因此，我们在master上创建的用户权限，也不会被replication到slaves上，为了便于管理，我们需要在每个节点上都增加如下用户：

1）root，最高级权限

2）test，一个模拟的用户，具有指定数据库的所有操作权限（需要master与slaves上必须严格一致，否则application无法对reads操作进行load balance）

3）rpl_sys，用于master与slaves之间replication的特殊用户

按照如下指令依次在三个实例上执行：

##以无权限验证模式，启动mysql
>sudo ./mysqld_safe --defaults-file=../my.cnf --skip-grant-tables &

#访问mysql
>./mysql --host=127.0.0.1 --port=3306
#初始化root密码
>use mysql;
>UPDATE user SET authentication_string = PASSWORD('root'),
password_expired = 'N'
WHERE User = 'root' AND Host = 'localhost';
>FLUSH PRIVILEGES;

#关闭mysql，并以授权认证的方式重新启动，并增加两个用户
>./mysqladmin --host=127.0.0.1 --port=3306 shutdown;
#重新启动
>./mysqld_safe --defaults-file=../my.cnf &
#使用root用户登录mysql
>./mysql --host=127.0.0.1 --port=3306 -u root -p
>#输入密码登录
>CREATE DATABASE mydb;##貌似需要首先创建DB，才能授权
>GRANT ALL ON mydb.* TO 'test'@'%' IDENTIFIED BY 'test';
#新增一个rpl_sys，用于在replication模式中可以复制binlog数据
>GRANT REPLICATION SLAVE ON *.* TO 'rpl_sys'@'%' IDENTIFIED BY 'replication';
>FLUSH PRIVILEGES;

在初始化root密码之后，关闭mysql需要指定密码：

>./mysqladmin --host=127.0.0.1 --port=3306 -u root -p shutdown;

最终我们达成，三个mysql实例持有相同的用户授权信息。

十四、架构过程与要求

1、架构模式

本文使用三个mysql实例构建replicaiton，全部采用“半同步”模式，我们暂且选定mysql-1为master，其他两个实例为slaves。为了避免一些不必要的错误或者bug，我们在任何时候都需要尽可能保证，master与slaves上的配置文件基本一致，且master和slaves的数据库引擎设置和模式一致，而且不会在slaves上直接变更数据或者修改table的引擎类型、字段类型等。

#常用架构方式
|---------|                  |---------|
| master  |------semisync----| slave   |
|---------|                  |---------|
          |      (2~5个)                               (任意多个)
          |                  |---------|              |---------|
          -------semisync----|   slave |-----aysnc----|  slave  |
                             |---------|              |---------|

2、安装semi插件：

semi插件有2个，分别为“semisync_master”、“semisync_slave”，默认情况下“plugin_dir”为“<basedir>/lib/plugin”，我们可以查看这个目录下是否有这两个插件，我们则在三个mysql实例上均安装此插件：

>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
>INSTALL PLUGIN rpl_semi_sync_SLAVE SONAME 'semisync_slave.so';
>use mysql;
>select * from plugin;

需要在三个节点上都执行上述命令，无论master还是slaves；即使你的环境为“伪分布式”，仍然需要在每个实例上执行，插件数据将会写入各自的“mysql”数据库中。

3、设定semisync全局变量：

在三个mysql实例上均执行如下命令，设置semisync变量：

#只在master上执行，
#或者为了便于Failover，可以在所有节点都执行
>SET GLOBAL rpl_semi_sync_master_enabled=ON;
#可以只在slave上执行
>SET GLOBAL rpl_semi_sync_slave_enabled=ON;

如果你觉得使用全局变量的方式可读性不强，也可以在配置文件中增加如下配置：

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=10000
rpl_semi_sync_master_wait_no_slave=0
rpl_semi_sync_slave_enabled=1
replicate_ignore_db=mysql

修改配置文件后，需要重启mysql实例。

4、备份Master数据（可选）

为了测试，我们首先在在master上的“mydb”数据库中插入一些测试数据，来模拟“单点架构扩展成replicaiton架构”的场景。

1）锁定数据库

>./mysql -h 127.0.0.1 -P3306 -u root -p;
>FLUSH TABLES WITH READ LOCK;
>SET GLOBAL read_only=1;

2）dump数据

>./mysqldump -h 127.0.0.1 -P3306 -u root -p -B mydb>/tmp/mydb.sql

3）释放锁

>UNLOCK TABLES;
>SET GLOBAL read_only=0;

5、将数据导入到slaves（可选）

#首先把master上的mydb.sql文件同步到各个slaves
#在slave上逐个执行，不过需要首先启动slave实例
>mysql -h 127.0.0.1 -P3306 -u root -p >/tmp/mydb.sql
#登陆mysql
>show databases;
#将会列出mydb数据库

6、初始化replication

到目前为止，我们已经将master和slaves单点准备完毕，且master上先前的数据也已经导入到slaves中，接下来我们将初始化replicaiton集群：

#slaves上执行
>SET GLOBAL read_only=1;
>CHANGE MASTER TO MASTER_HOST='192.168.1.100',MASTER_PORT=3306, MASTER_USER='rpl_sys', MASTER_PASSWORD='replication', MASTER_AUTO_POSITION=1;
>START SLAVE;

千万别忘了最后执行“START SLAVE”，否则将无法执行replicaiton。在一个slave上执行“START SLAVE”之后，即使此slave 重启，它仍然保持自己的“slave”角色，且不需要再次执行“CHARGE MASTER TO”，因为这些信息已经被保存在内部数据库中。我们可以使用“STOP SLAVE”在停止replicaiton，此后slave可以作为master或者执行“CHARGE MASTER TO”跟进其他的master。

我们可以在master上执行“SHOW SLAVE HOSTS”指令，查看slaves列表：

mysql> SHOW SLAVE HOSTS;
+-----------+---------+------+-----------+--------------------------------------+
| Server_id | Host    | Port | Master_id | Slave_UUID                           |
+-----------+---------+------+-----------+--------------------------------------+
|        11 | mysql-2 | 3306 |        10 | 4694924e-b818-11e5-b9ad-ccddf019aa0c |
|        12 | mysql-3 | 3306 |        10 | 26baac30-b820-11e5-ad9c-933c718547d4 |
+-----------+---------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

7、数据测试

我们可以在master上写入数据一些数据测试，然后在slaves上查看，发现数据已经正确的被replication，目前架构基本达成了我们的需要。

十五、要点描述

1、配置部分

1）binlog_format：replication时使用的格式，为了数据一致性和性能，我们选用“MIXED”模式，让mysql在合适的时机选用“row”、“statement”模式。在后续的版本中，我们已经不建议使用“statement”模式。

2）log_slave_updates：slave是否将变更操作也写入自己的binlog，如果此slave后端没有slave跟进，则建议关闭。

3）gtid_mode，enforce_gtid_consistency：这两个参数需要配合，同时开启“ON”，在replicaiton模式中GTID特性强烈建议开启。

4）report_port，report_host，report_user：需要在slave上配置，主要在master上使用“SHOW SLAVE HOSTS”查看slaves列表时展示。

5）slave_parallel_workers：是否开启并发replication，默认为“0”表示不开启，此时slave只有一个SQL线程，用于读取relay log并执行statements；如果此值大于0，表示开启worker线程的个数，此时SQL线程只负责从relay log读取statements，然后转发给workers线程，有worker线程负责执行。通常workers线程的个数与database的个数一样，或者简单设置为2。

6）binlog_checksum，master_verify_checksum，slave_sql_verify_checksum：建议开启，对binlog开启校验和验证。

7）server_id：这个配置项必备，每个节点都不同，数字类型。

8）sync_binlog=1、innodb_flush_log_at_trx_commit=1，这两个参数配合用于控制binlog刷新磁盘的时机，“1”表示每次事务提交都立即刷新磁盘，效率较低但是可以尽可能的保证数据完整性；如果你的数据库是一些社交内容的数据（而非订单数据），可以为了考虑并发能力，而稍微降低binlog刷新频率。

2、数据与replication：首先“mysql”这个特殊的系统数据库是不能被replication的，我们需要在配置文件中通过“replicate_ignore_db=mysql”来忽略；然后在master上授权一个具有“REPLICATION SLAVE”权限的用户，比如本文中的“rpl_sys”用户，slaves可以使用此用户与master建立链接并进行数据同步；如果你希望Failover的话，需要在所有的节点上都添加此用户，而且所有的节点上的配置把包括root用户都应该一样，因为任何节点都有可能被提升为master。

3、GTID是一个新的特性，它保证了master事务在replication的一致性、有序性，可以有效的简化replication的复杂度和人工干预，我们应该开启此特性。（无论是修改MyISAM，还是InnoDB，无论是否手动开始事务还是自动提交的事务，均会生成GTID，每个事务一个ID，自动提交模式下每个操作一个GTID）

4、多线程replication，即“slave_parallel_workers”控制，这个是个需要权衡的配置项，如果你的系统中writes操作比较密集、databases个数较多、大事务较多，那么你应该考虑开启此参数来提高relay log的执行效率，线程数不建议特别多，通常设置为“2”或者根据和databases的个数一致。

5、semisync特性，即“半同步”也是新特性，官方推荐使用特性以降低数据丢失的可能性，半同步的核心就是在master与slaves之间有一次同步 + 消息确认的过程，会降低master事务并发提交，但是它保证master与slave之间binlog复制的同步能力。通常，我们将集群中“大多数”slaves开启半同步，部分slaves仍然采用异步同步，这是在数据完整性和性能上做权衡。

6、在从master上dump数据时，一定要首先对master实例进行LOCK，我们期望dump的数据是完整的（而非变动、增量的），即使用“FLUSH TABLES WITH READ LOCK;”，此指令将导致binlog、数据文件立即刷新磁盘，且阻止writes操作提交，在dump结束后，我们通过“UNLOCK TABLES”释放锁。上文中已经提到“mysql”这个系统数据通常不能参与replicaiton，所以我们也不能将“mysql”数据dump到其他slaves中。

7、关于是否在slave上开启read_only参数，存在一些权衡，按照规范，我们应该在slave上开启read_only，以避免slave上的数据被客户端意外变更，而导致replication集群中数据无法对齐的问题。即slave上的数据是可以直接修改的。

此外slave原则上，仍然可以创建其他databases，这些databases与replication master没有关系，application可以直接操作slaves并修改数据。为了避免这种纠缠不清的问题，我们建议slaves统一开启“read_only”。read_only并不会影响replication进程对数据的变更，也不会影响SUPER用户对“系统”状态的变更（比如SET GLOBAL ...）

8、在普通的replication模式下，通常master失效后，优先恢复master，而不是failover到其他slaves上。

如果master无法恢复：

1）将某个slave提升为master，这种方式下可能需要调整客户端Connector的URL配置（因为jdbc没有角色感知的功能，需要开发额外的代码）。

2）将slave提升为master，且此slave也使用master的IP（VIP方式，或者硬性路由），此后再重新搭建一个slave即可（可以使用原slave的IP），此种方式需要修改客户端Connector URL配置。

十六、运维操作

1、mysqldump脚本：

./mysqldump -h 127.0.0.1 -P 3306 -u root -p -B mydb >/home/data/mydb.sql

这是mysqldump脚本常用的模式，它还有几个有用的参数选项，全部参数请参考【mysqldump】：

1）--add-drop-database：默认情况下，dump文件中会写入“CREATE DATABASE”语句，如果slave上已经有了此database，在导入数据时可能会发生错误；此选项，就是在“CREATE DATABASE”语句之前首先添加一行“DROP DATABASE”，这样在导入数据时不会发生冲突。

2）--no-create-db：简写为“-n”，是否添加“CREATE DATABASE”语句。

3）--ignore-table=<db>.<table>：指定忽略的数据库表，此参数可以重复出现多次。

4）--no-data：简写“-d”，只写入“CREATE TABLE”不写入表数据，即只dump表结构。

5）--default-character-set=<character-set>：使用的字符集，默认为utf8。

6）--all-databases：简写“-A”，dump所有的数据库。

7）--databases：简写“-B”，指定需要dumpl的数据库列表，以空格分隔。

2、show processlist：我们可以在master或者slave上执行“SHOW PROCESSLIST”查看与replication有关的进程。

3、show slave hosts：在master执行此命令，用于查看跟进master的所有slaves列表以及相应的report信息。

4、show master status：用于查看master的状态，主要是查看master上GTID的状况。

5、show slave status：查看slave的状态，涉及到的内容很多，【参见详情】。我们需要关注两项：Retrieved_Gtid_Set 和 Executed_Gtid_Set，前者表示IO线程已经读取的GTID的范围，后者表示SQL线程已经执行的GTID的范围，我们可以根据这两个参数来判断slave与master上数据的距离。

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rpl_sys
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: my-binlog.000008
##IO线程已读取的master binlog位置
##如果没有开启GTID，在“CHANGE MASTER TO”中
##可以使用此POS
Read_Master_Log_Pos: 2403
Relay_Log_File: relay-bin.000006
Relay_Log_Pos: 789
Relay_Master_Log_File: my-binlog.000008
##slave IO线程状态
Slave_IO_Running: Yes
##slave SQL线程状态
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
...
##SQL线程已执行的binlog位置
Exec_Master_Log_Pos: 2403
Relay_Log_Space: 1030
...
##slave与master的步差
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
...
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 8a542af6-b79d-11e5-b2cf-c699ad96e9cc
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
...
##GTID模式下
##已经从master读取的GTID范围
Retrieved_Gtid_Set: 8a542af6-b79d-11e5-b2cf-c699ad96e9cc:1-7
##SQL线程已经执行的GTI范围
Executed_Gtid_Set: 8a542af6-b79d-11e5-b2cf-c699ad96e9cc:1-7
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

6、show status like “rpl_semi_sync%”：用于查看semisync的各项变量的值。

7、slave有2种线程类型：IO线程、SQL线程，可以通过“START(或STOP) SLAVE IO_THREAD”，“START(或STOP) SLAVE SQL_THREAD”对两种线程单独控制。IO线程就是与master建立链接读取binlog、写入relay log的线程，通常一个slave只有一个；SQL线程，负责从relay log中读取变更操作并执行语句，默认为单线程，我们可以通过“slave_parallel_workers”来开启多线程方式。

十七、Failover

如果master失效或者人工干预，可以将slave提升为master；在实际环境中，如果master失效，我们建议优先将master重新上线，而不是急于Failover，除非master无法短时间内恢复。因为在普通的replication架构下，failover机制尚不是非常健全，而且还涉及到客户端Connection相关策略问题。

1、终止Master节点（人工干预）：

如果希望将一个活跃的master节点角色迁移成slave，那么需要首先中断master的写服务：

#在master上执行
>FLUSH TABLES WITH READ LOCK;
>SET GLOBAL read_only=1;

2、将slave提升为master：

1）如果master已经失效，那么就需要将逐个查看slaves的状态，并确定一个数据最完整的slave（Retrieved_Gtid_Set范围最大的那个），并将其选定为master候选；且等待此slave数据复制完成之后，再切换成master，即“show slave status”中“Exec_Master_Log_Pos”=“Read_Master_Log_Pos”。

2）如果人工终止Master，如1、，master上终止writes服务之后，可以通过“show master status”查看当前master已经执行的GTIDs；当所有的slaves的“Retrieved_Gtid_Set”值与master相等时，再将此slave切换为master。

3、在所有的slaves执行（包括候选master候选）：“STOP SLAVE”。在候选master上，还需要并关闭read_only。

4、在其他slaves上执行（新master除外）：“CHANGE MASTER TO”指令，与新master跟进，然后执行“START SLAVE”。

5、如果是人工终止Master，别忘了释放锁。与1、对应。

>UNLOCK TABLES;
>SET GLOBAL read_only=0;

6、如果旧的master需要再次成为master，它首先需要与当前master跟进并保持数据完整（其实还需要将当前master处于read_only状态），然后在旧master上执行“RESET MASTER”，此指令会导致旧master上原有的binlog清除，以避免slaves跟进时带来问题。

十八、Replication模式问题

1、模式一致：在replicaiton架构中，Master与slaves核心配置项应该保持一致，所有的database的引擎保持一致，表结构保持一致等。我们不应该在replication架构中，人为将数据的模式修改为不同，比如手动修改slaves的引擎类型，将Innodb改为MyISAM，有可能你是基于“slaves上使用MyISAM引擎可以提供更好的read效率”考虑，但是这种调整将会导致很多意外的问题。而且我们通常在slalves上设定全局的“read_only=1”以避免人为的、或者客户端意外的在slaves上修改数据。

2、限定binlog的数据库：我们可以在master端设定“binlog_do_db”、“binlog_ignore_db”配置项，来指定master端将那些数据库的操作写入binlog，以及忽略哪些数据库的binlog。默认情况下，所有的databases中的数据变更都将写入binlog。如果指定了“binlog_do_db”，那么binlog中只会写入相应数据库的操作日志；如果指定了“binlog_ignore_db”，那么此数据库的操作记录将不会被写入binlog。

不过，在SBR和RBR两种replication不同的格式下，binlog的写入行为有些不同，这一点需要非常小心。

binlog_do_db：

1）SBR：根据“默认”数据库（即USE指令选择）名来判断是否写入binlog，而不是根据updates的目标数据库判断，比如我们指定“binlog_do_db=sales”，下面两个SQL语句的binlog行为将是不同的：

1、
> USE prices;
> UPDATE sales.january SET amount = amount + 1000;

2、
>USE sales;
>UPDATE prices.discounts SET percentage = percentage + 10;

通过“USE”选择的数据库为“默认数据库”，因为“binlog_do_db”值检测“默认数据库”是否匹配，而不关注实际的updates语句中的目标数据库（即跨数据库操作的），因此对于1、情况将不会写入binlog，但是2、会写入binlog。

2）RBR：基于row-based格式的binlog模式，binlog的写入似乎更加合乎逻辑，它不在单纯的只判断“默认数据库”，而是判断updates实际的目标数据库是否匹配，如果匹配则写入binlog，即使此时“默认数据库”不匹配。比如“binlog_do_db=sales”，上例中1、语句仍然会被写入binlog，但是2、语句尽管“默认数据库”为“sales”但是其updates的目标数据库不匹配，将不会被写入binlog。

binlog_ignore_db：

1）SBR：基本思路同上，即根据“默认数据库”来判断是否忽略此statements，比如“binlog_ignore_db=sales”，那么上例中2、语句将不会写入binlog（被忽略）。

2）RBR：思路同上，根据updates实际的目标数据库来判断是否忽略此statements，比如“binlog_ignore_db=sales”，那么上例中1、语句将不会写入binlog，而2、语句会被写入。

3、限定replicate数据库：binlog_do_db和binlog_ignore_db是在master端生效，那些写入binlog的内容将会被全权发送给slaves，当slave的SQL线程读取binlog变更操作后，slave还可以判断是否继续执行此变更操作，有两个配置项来决定“replicate_do_db”、“replicate_ignore_db”。它们基本思路同2、部分，请参看“binlog_do_db”、“binlog_ignore_db”。

4、log_slave_updates：本文一再强调，如果你的slave后端没有其他的slaves跟进，即二级slaves，那么建议关闭此选项。如果开启，那么slave的SQL线程在执行变更操作时也会写入binlog日志，在failover时，如果此slave被选举为master，其他slaves与它跟进时，其本地的binlog会再次发给其他slaves，那么也意味着其他slaves接收了2次相同的binlog内容。（GTID开启时应该不会接受2次，未测试！如果slave上开启了此特性，那么当次slave被提升为master后，需要执行一次“RESET MASTER”指令）

5、AUTO_INCREMENT：基于SBR的replication格式下，AUTO_INCREMENT、LAST_INSERT_ID()、TIMESTAMP的值都可以正确的被复制，但是需要注意如下几个问题（RBR不需要考虑）：

1）Master与slaves的存储引擎必须一致，表的schema必须一致。即master和slave上AUTO_INCREMENT的字段必须一一对应。

2）如果slave端有trigger，且在trigger中修改了AUTO_INCREMENT的字段值，这种方式有可能导致replication下数据不一致问题。

3）如果是组合主键（composite primary key），那么AUTO_INCREMENT字段必须是组合主键的首个字段。（InnoDB）

4）对于“INSERT ... SELECT ...”语句，可能导致在master与slaves上执行时得到的数据顺序不同，连带导致AUTO_INCREMENT的值不同。所以，我们必须在SELECT语句中使用“ORDER BY”来完全保障顺序。

6、字符集：为了避免乱码，我们应该在master和slaves上配置相同的字符集类型，而且尽可能在create表时指定字符集，而不是使用默认字符集，这样可以避免replication过程中产生乱码。

7、NOW()：这个函数，可能是我们用的最多的，表示当前系统的时间；在SBR模式下，大家肯定会疑惑，如果slave与master的时间不同步怎么办？会不会导致数据不一致？无论是SBR还是RBR，NOW()总是在解析时计算并替换成“TIMESTAMP”，所以在replication到slave端时已经是计算后的值，而不是NOW()语句。但是最大的问题是“time_zone”，因为timestamp的转换成日期时将会受到时区的影响，这也就要求master和slaves上必须配置相同的time_zone，而不是使用默认的系统时区。

> SHOW VARIABLES LIKE '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
> SELECT @@GLOBAL.time_zone;

我们可以使用如下几种方式来设置time_zone：

##配置文件中
default_time_zone=+8:00
##全局变量
SET GLOBAL time_zone=+8:00

#查看binlog内容
>./mysqlbinlog binlog.000001
...
SET TIMESTAMP=1453473604/*!*/;
INSERT INTO test(created) value(now())
...

注意time_zone值采用UTC格式，中国采用“+8:00”，可参考列表【timezone】

8、LIMIT：在SRB模式下，delete、updates、insert...select批量操作且使用了LIMIT时，如果操作不能有效限定特定的行（比如指定主键ID），那么将可能导致语句在slave与master上执行的结果不同，所以，对于上述语句，指定LIMIT的同时还要使用order by，以保证被操作的行是一致性的。（底层存储，大家都知道“holes”）

9、REPAIR：当MySQL实例异常中断后，或者希望对表进行repair，那么需要首先暂停所有slaves上的IO线程，即中断binlog复制。然后在master上执行“repair table”执行，这个过程会导致部分“不完整的行”被删除，但是这种删除是基于底层文件存储，而不会记录在binlog中；所以在repair结束之后，需要将master上相应table的frm、ibd文件数据copy到slaves上，并在master上执行“REST MASTER”。

（表空间优化指令“OPTIMIZE TABLE”则不需要上述过程，只需要相应的节点处于只读状态即可。）

10、系统数据库mysql：我们都知道，MySQL实例在初始化时会创建mysql数据库，这是一个系统数据库，mysql数据库中保存了当前实例的很多状态信息。在replication模式下，mysql数据库的数据变更仍然会被写入binlog，对于有些statements比如“GRANT”、“REVOKE”等将会以SBR格式写入binlog，进而同步给slaves。直观来说，如果你在master上新增了系统用户或者授权操作，也将会被同步给slaves，这种情况存在利弊，或许你希望这么做，但或许你不希望。所以，我们建议“mysql”数据库不能被replicate，在slaves端使用“replicate_ignore_db=mysql”来限定。

11、事务：最大的问题就在于一个事务中同时修改了非事务表和事务表，这种情况我们应该避免，将会导致replication模式下数据不一致问题。首先非事务性表的修改是立即生效，而且无法rollback；当事务中，穿插执行“事务性表”和“非事务性”表更新的操作时，任何一个事务性表操作都可能发生异常而rollback，进而那些非事务性表的修改将变得不可确定，那么最终数据一致性（逻辑上）将是无法保证的。

1）如果在一个事务中，在修改事务性表数据之前，执行任何修改非事务性表的操作，都将立即被写入binlog，此后的修改事务性表的操作将首先被cached，并在COMMIT时写入binlog（COMMIT和ROLLBACK指令也会写入binlog）。

2）如果配置中开启了GTID且指定“enfore_gtid_consistency=ON”时，那么在事务中，执行修改事务性表数据之后，再尝试修改非事务性表时将会抛出异常。通常我们建议非事务性表则不需要再事务中执行。

ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.

为了避免不必要的问题，我们建议在所有数据库表均采用InnoDB引擎，对于目前而言，InnoDB引擎在读写效率上已经和MyISAM没有太大性能差别。

12、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，能够确保每个DATABASE中的事务顺序严格一致，但是无法保证所有databases的事务执行顺序也是严格一致的（gap），比如两个事务依次操作了2个DB：A和B，尽管事务A、B分别被worker X、Y线程接收，但是因为线程调度的问题，有可能导致A的执行时机落后于B。如果你的事务经常是“跨DB”操作，那么可以考虑使用此参数限定顺序。当此参数开启时，这要求任何worker线程执行的事务时，只有当前事务中此之前的所有事务都执行后（被其他worker线程执行），才能执行和提交。（每个事务中，都记录了当前GTID的privious GTID，只有privious GTID被提交后，当前GTID事务才能提交）

13、TRUNCATE：清理表数据，并重建表的schema。这个指令其实是个DML指令，不过replication模式中，它将作为DDL语句写入binlog，无论是SBR、RBR还是MIXED模式。

【上一篇：MySQL Replication配置项】

【下一篇：MySQL Replication与Connector原理】

分享到：