十三、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、简要配置文件
Java代码 收藏代码
[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:
Java代码 收藏代码
#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的特殊用户
按照如下指令依次在三个实例上执行:
Java代码 收藏代码
##以无权限验证模式,启动mysql
>sudo ./mysqld_safe --defaults-file=../my.cnf --skip-grant-tables &
Java代码 收藏代码
#访问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;
Java代码 收藏代码
#关闭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需要指定密码:
Java代码 收藏代码
>./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的引擎类型、字段类型等。
Java代码 收藏代码
#常用架构方式
|---------| |---------|
| master |------semisync----| slave |
|---------| |---------|
| (2~5个) (任意多个)
| |---------| |---------|
-------semisync----| slave |-----aysnc----| slave |
|---------| |---------|
2、安装semi插件:
semi插件有2个,分别为“semisync_master”、“semisync_slave”,默认情况下“plugin_dir”为“<basedir>/lib/plugin”,我们可以查看这个目录下是否有这两个插件,我们则在三个mysql实例上均安装此插件:
Java代码 收藏代码
>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变量:
Java代码 收藏代码
#只在master上执行,
#或者为了便于Failover,可以在所有节点都执行
>SET GLOBAL rpl_semi_sync_master_enabled=ON;
#可以只在slave上执行
>SET GLOBAL rpl_semi_sync_slave_enabled=ON;
如果你觉得使用全局变量的方式可读性不强,也可以在配置文件中增加如下配置:
Java代码 收藏代码
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)锁定数据库
Java代码 收藏代码
>./mysql -h 127.0.0.1 -P3306 -u root -p;
>FLUSH TABLES WITH READ LOCK;
>SET GLOBAL read_only=1;
2)dump数据
Java代码 收藏代码
>./mysqldump -h 127.0.0.1 -P3306 -u root -p -B mydb>/tmp/mydb.sql
3)释放锁
Java代码 收藏代码
>UNLOCK TABLES;
>SET GLOBAL read_only=0;
5、将数据导入到slaves(可选)
Java代码 收藏代码
#首先把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集群:
Java代码 收藏代码
#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列表:
Java代码 收藏代码
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脚本:
Java代码 收藏代码
./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上数据的距离。
Java代码 收藏代码
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的写服务:
Java代码 收藏代码
#在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、对应。
Java代码 收藏代码
>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行为将是不同的:
Java代码 收藏代码
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,而不是使用默认的系统时区。
Java代码 收藏代码
> SHOW VARIABLES LIKE '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
> SELECT @@GLOBAL.time_zone;
我们可以使用如下几种方式来设置time_zone:
Java代码 收藏代码
##配置文件中
default_time_zone=+8:00
##全局变量
SET GLOBAL time_zone=+8:00
Java代码 收藏代码
#查看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”时,那么在事务中,执行修改事务性表数据之后,再尝试修改非事务性表时将会抛出异常。通常我们建议非事务性表则不需要再事务中执行。
Java代码 收藏代码
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模式。
转载于:https://blog.51cto.com/xsunday/2049681