mysql 主从搭建_「干货」如何正确搭建MySQL主从

随着业务不断的增长，一台MySQL服务器已经很难满足业务上的需求，此时我们首先考虑的就是对MySQL搭建主从并实现MySQL的读写分离，来负担业务上的压力，今天给大家分享如何正确的搭建MySQL主从。

在分享之前，我们先了解下MySQL5.6开始增加的GTID(Global Transaction ID，全局事务ID)这个新特性，GTID是用来强化数据库的主备一致性， 故障恢复， 以及容错能力，用于取代过去基于BingLog偏移量的主从复制(即：基于binlog和position的异步复制)。

借助GTID，在发生主备切换的情况下，MySQL的其他slave可以自动在新主上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制position发生误操作的风险。另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。

GTID的组成：

GTID是由server_uuid和事务id组成的，即GTID=server_uuid:transaction_id。

server_uuid，是在MySQL第一次启动时自动生成并持久化到auto.cnf文件(存放在数据目录下，每台机器的server_uuid都不一样。

transaction_id，是一个从1开始的自增计数，表示在这个主库上执行的第n个事务。MySQL会保证事务与GTID之间的1:1映射，如：b6af5b5c-666f-11e9-bed3-000c29b85ea6:1

表示在以b6af5b5c-666f-11e9-bed3-000c29b85ea6为唯一标志的MySQL实例上执行的第1个数据库事务。

一组连续的事务可以用 "-" 连接的事务序号范围表示。例如：b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-5

GTID复制的好处：

基于BinLog偏移量复制，当连接发生故障时，需要重新连接到master主机，需要找到binlog和position，然后change master to 连接到master主机，此过程需要人工来做，比较麻烦，也容易出错，尤其是master写操作较多时，更不容易确定position，如果flush table with read lock，势必会影响到线上业务。而GTID复制方式不需要找master的binlog和position，只需要知道master的ip、端口、账号密码，即可进行复制，MySQl会通过内部机制自动找点同步(MASTER_AUTO_POSITION=1)，大大降低了复制时的工作。

GTID复制的工作原理：

1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中

2、slave端的I/O线程将变更的binlog，写入到本地的relay log中

3、SQL线程从relay log中获取GTID，然后对比slave端的binlog是否有记录(所以MySQL5.6 slave端必须开启binlog)

4、如果有记录，说明该GTID的事务已经执行，slave会忽略

5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog

6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描

使用GTID复制需要注意：

1、不允许在同一个事务内对事务表和非事务进行DML操作，例如：在同一个事务内先update innodb表，然后update myisam表。因为GTID强制每一个GTID对应一个事务，而在同一个事务内既操作innodb表又操作myisam，就会产生两个GTID；

2、不允许CREATE TABLE … SELECT语句，此语句是将查询结果转存到一个新表中，首先这种语句对于statement格式的binlog是不安全的；而对于row格式的binlog，这种语句在binlog实际是分成两个event进行记录的，一个记录create创建操作，一个记录insert操作，那么就有可能这两个操作是对应到同一个GTID上，而当将这两个拥有相同GTID的event传到从库时，从库就会忽略拥有相同GTID的insert操作，造成数据丢失；

3、CREATE TEMPORARY TABLE和DROP TEMPORARY TABLE不允许在事务内执行，只有在事务以外并且autocommit=1才能正常执行；

4、不支持sql_slave_skip_counter，如果需要跳过事务，可以用以下方法：

set @@session.gtid_next='需要跳过的事务gtid'

begin;

commit;

set session gtid_next=automatic;

基于GTID搭建一主两从的主从架构：

1、准备三台服务器，我这里用的是虚拟主机

A主服务器(主服务器192.168.8.128)创建test数据库和test表

B从服务器(从服务器192.168.8.129)创建test数据库

C从服务器(从服务器192.168.8.130)创建test数据库

2、配置主从参数

主从设置如下参数(直接在配置文件my.cnf下直接添加)：

A主库配置：

server_id=1

gtid_mode=on

enforce_gtid_consistency=on

skip_slave_start=1

log_bin=master-bin.log

log-slave-updates=1

binlog_format=row

binlog_do_db=test #设置只记录主库的test库的二进制日志，多个请写多行

B从库配置：

server_id=2

gtid_mode=on

enforce_gtid_consistency=on

skip_slave_start=1

log_bin=slave-bin.log

log-slave-updates=1

binlog_format=row

C从库配置：

server_id=3

gtid_mode=on

enforce_gtid_consistency=on

skip_slave_start=1

log_bin=slave-bin.log

log-slave-updates=1

binlog_format=row

3、在A主服务器上建立同步用的账号

#创建用户(密码可以改成自己的)

[mysql]>create user 'repluser'@'192.168.8.%' identified by '123456';

#分配权限

[mysql]> grant replication slave on *.* to 'repluser'@'192.168.8.%';

#刷新权限

[mysql]> flush privileges;

4、导出主服务器上的test表(不像BingLog偏移量的主从复制那样需要锁住主库防止主库发生写操作)

用Navicate、mysqldump命令或者其他工具导出都可以，这里不再阐述。

5、将主服务器上test表导入从服务器

6、在B、C从库上建立主库同步

CHANGE MASTER TO MASTER_HOST='192.168.8.128',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_AUTO_POSITION = 1;

7、启动从库

start slave；

8、查看slave状态

show slave status;

9、在主库插入数据，此时各个从库已经可以完成同步了

一主两从我们就配置成功了，下一期我给大家分享，如何配置读写分离。欢迎大家持续关注我~~

最后给大家推荐一本不错的书，希望对想学习架构的同学有所帮助。