MySQL主从实践篇：超详细版读写分离、双主热备架构搭建教学

在上篇《主从原理篇》中，基本上把主从复制原理、主从架构模式、数据同步方式、复制技术优化.....等各类细枝末节讲清楚了，本章则准备真正对聊到的几种主从模式落地实践，但实践的内容通常比较枯燥乏味，因为就是调整各种配置、设置各种参数等步骤。

本章中就搭建最基本的两种常用搭建，一主一从/多从架构、双主/多主多写架构，其他例如级联复制架构、多主一从架构的配置过程大致相同，因此各位小伙伴可以举一反三，这里就不再详细展开实践。

一、CentOS7安装MySQL8.0

其实基于Docker容器去构建会更简单，基本上就敲几行命令，开放一下端口映射即可，但那种方式似乎缺少一些原汁原味，因此这里还是基于原生的Linux-CentOS7系统，完成MySQL主从复制集群的搭建工作，但在此之前先安装一个MySQL8.0版本。

❶首先创建MySQL的目录并进入：

[root@localhost]# mkdir /soft && mkdir /soft/mysql/
[root@localhost]# cd /soft/mysql/

❷下载MySQL的安装包，可以通过FTP工具上传离线环境包，也可通过wget命令在线获取安装包：

[root@localhost]# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz

没有wget命令的可通过yum命令安装：

[root@localhost]# yum -y install wget

❸解压MySQL的压缩包，并对解压后的目录重命名：

[root@localhost]# tar -xvJf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
[root@localhost]# mv mysql-8.0.20-linux-glibc2.12-x86_64 mysql8.0

❹Linux-CentOS内核会自带MariaDB这个开源版的MySQL，因此在正式安装前，一定要先检测是否一下，如果存在则卸载一下：

[root@localhost]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64

[root@localhost]# rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

❺卸载掉MariaDB后，进入重命名后的目录并创建一个data文件夹，用于存放数据：

[root@localhost]# cd mysql8.0 && mkdir data

❻配置系统的环境变量，配置好后记得刷新配置文件：

[root@localhost]# vi /etc/profile
# 在最后面加一行：
#   export PATH=$PATH:/soft/mysql/mysql8.0/bin:/soft/mysql/mysql8.0/lib
# 按ESC键，输入 :wq 保存退出

[root@localhost]# source /etc/profile

其实这一步不配置也行，MySQL8.0的启动文件中，会自动去配置系统的PATH变量。

❼接着要创建一个mysql的用户组，并创建一个用户和分配密码、权限：

[root@localhost]# groupadd mysql
[root@localhost]# useradd -g mysql mysql
[root@localhost]# chown -R mysql.mysql /soft/mysql/mysql8.0

这一步千万要做，有人也许会想：我可以直接用root用户呀，的确可以用，但后续会产生天多的麻烦，因为你需要手动更改很多很多的配置文件，所以这一步千万别省略，不要图简单！

❽接着进入MySQL解压目录下的bin文件夹，初始化MySQL的基本信息：

[root@localhost]# cd /soft/mysql/mysql8.0/bin
[root@localhost]# ./mysqld --user=mysql --basedir=/soft/mysql/mysql8.0 --datadir=/soft/mysql/mysql8.0/data/ --initialize

# 如果执行报错，请先执行 yum install -y libaio 安装一下依赖库

这一步的命令略微有些长，但实际上的作用就是指定MySQL的系统用户、安装目录、数据存放目录等信息，这行命令执行完成后会得到一个临时密码，也就是在最后一行输出的结尾，如下：

这个密码各位小伙伴可以手动先复制出来，因为后续需要通过这个临时密码连接MySQL。

❾接着需要配置一下MySQL的配置文件，启动MySQL时会默认读取/etc/my.cnf这个文件，因此配置如下：

[root@localhost]# vi /etc/my.cnf
[mysqld]
basedir=/soft/mysql/mysql8.0
datadir=/soft/mysql/mysql8.0/data

port=3306
socket=/tmp/mysql.sock
character-set-server=UTF8MB4

上述最主要的是配置安装目录和数据目录，这里仅是基础配置，后续还会做更改~！

同时，MySQL的默认目录是/usr/local/mysql/，如果你和我一样，没有在这个目录下安装MySQL，那也记得一定要修改MySQL的启动文件，如下：

[root@localhost]# cd /soft/mysql/mysql8.0
[root@localhost]# vi support-files/mysql.server

# 其中安装目录和数据目录默认为空，启动时会去加载/usr/local/mysql/目录
basedir=
datadir=

# 记得将其修改为你自定义的安装目录和数据目录（最后不要加斜杠）
basedir=/soft/mysql/mysql8.0
datadir=/soft/mysql/mysql8.0/data

❿修改完成后，接着先尝试通过MySQL启动脚本，运行MySQL程序：

[root@localhost]# ./support-files/mysql.server start
Starting MySQL.Logging to '/soft/mysql/mysql8.0/data/localhost.localdomain.err'.
.. SUCCESS!

当出现SUCCESS后表示启动成功，接着可以查询一下后台进程：

[root@localhost]# ps aux | grep mysql

⓫后台有进程则表示安装成功，接着可以连接一下MySQL并重置密码：

[root@localhost]# mysql -uroot -p
Enter password: (这里输入前面得到的临时密码)

# 密码输入正确后会进入MySQL终端，此时可以尝试简单查询
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER ...

此时你会看到一个信息提示，也就是必须要通过ALTER USER重置密码后，才能进行操作：

mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';

重置密码后再次进行查询，此时就能查询出结果啦~：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.03 sec)

mysql> quit

⓬对MySQL-root账号的密码做了重置后，为了后续方便启动，此时可将MySQL添加到系统服务：

# 先停止MySQL服务
[root@localhost]# ./support-files/mysql.server stop

# 将MySQL启动文件复制到系统etc目录下
[root@localhost]# cp -a ./support-files/mysql.server /etc/init.d/mysql

# 给予启动文件的执行权限
[root@localhost]# chmod +x /etc/init.d/mysql

# 通过系统etc目录下的启动文件创建系统服务
[root@localhost]# chkconfig --add mysql

为MySQL创建好系统服务后，后续可通过service命令来快捷管理MySQL，指令如下：

# 查看MySQL服务的运行状态
[root@localhost]# service mysql status

# 启动MySQL服务
[root@localhost]# service mysql start

# 停止MySQL服务
[root@localhost]# service mysql stop

# 重启MySQL服务
[root@localhost]# service mysql reload

# 平滑重启MySQL服务（先处理完已接收的SQL后再重启）
[root@localhost]# service mysql force-reload

⓭通过服务命令启动MySQL，并使用更改后的密码连接：

[root@localhost]# service mysql start
[root@localhost]# mysql -uroot -p
Enter password: 123456
Welcome to the MySQL monitor.  Commands end with ;
.......

能够看到上述欢迎信息，即表示一切无误！

⓮MySQL8.0版本中，root账号默认是不接受远程连接的，因此这里还需要修改一下远程连接的权限：

# 进入mysql系统库
mysql> use mysql;

# 直接通过update修改系统用户表
mysql> update user set host="%" where user="root";

# 刷新一下所有用户的权限信息
mysql> flush privileges;

到这里，root用户也支持远程连接了，但MySQL8.0的密码认证套件换掉了，所以需要通过SSL加密方式去连接，这里需要牢记！

上述这个流程，是经过本人两次实验的，所以绝对可靠，大家需要安装时，只需无脑复制命令并执行即可~

二、一主一从架构搭建实践

    经过第一阶段后，安装好了一个最基本的`MySQL`裸机，但想要搭建主从集群，最少也需要两个节点，所以还需要再开一个节点，如果为了图简单，也可以直接克隆上一台机器，这样最省时省力，我这里就不重复再安装一次了，现有两台机器的信息如下：

192.168.12.128、CentOS7、MySQL8.0：后续将作为主节点（主库）使用。
192.168.12.129、CentOS7、MySQL8.0：后续将作为从节点（从库）使用。

大家也可以选择更多的节点来作为从库，方法基本上一样，也就是再开一个节点，配置和129的一样即可。

2.1、配置MySQL主节点（master）

首先一起来搭建一下MySQL的主节点，大体可分为三步：

①修改主节点的配置文件。
②开放主节点的端口。
③创建一个用于同步数据的账号。

2.1.1、修改主库的核心配置

主从集群的配置实则非常简单，只需修改前面安装时的my.cnf文件即可，如果服务器是Windows系统，则找到对应的my.ini文件做修改，在修改前记得先停掉MySQL服务，然后将主库的/etc/my.cnf文件修改成如下内容：

[root@localhost]# service mysql stop
[root@localhost]# vi /etc/my.cnf

[mysqld]
basedir=/soft/mysql/mysql8.0
datadir=/soft/mysql/mysql8.0/data

port=3306
socket=/tmp/mysql.sock
character-set-server=UTF8MB4

# ------接下来的是主从集群配置-------

# 主库在主从集群中的唯一标识
server-id=1
# 开启bin-log日志，并为bin-log日志取个前缀名（有默认值可不写）
log-bin=mysql-bin-log
# 同步复制时过滤的库（主要将一些不需要备份/同步库写进来）
# 也可以通过binlog-do-db=xx1,xx2... 来指定要复制的目标库
binlog-ignore-db=mysql
# 指定bin-log日志的格式为混合模式（默认为statement）
binlog_format=mixed
# 设置单个binlog日志文件的最大容量
max_binlog_size=1024M

2.1.2、开放主节点的端口

更改/etc/my.cnf文件后，因为后续从库会通过网络实现数据同步，因此还需要对外开放相应的3306端口，同时也要记得刷新防火墙，如下：

[root@localhost]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@localhost]# firewall-cmd --reload
[root@localhost]# firewall-cmd --zone=public --list-ports

如果嫌这样比较麻烦，也可以直接将防火墙关闭：

[root@localhost]# systemctl stop firewalld.service

但最好不要这样做，因为保持系统防火墙的正常运转，在线上情况时，往往比装N多个杀毒软件都管用，系统自带的防火墙能够有效的协助咱们拦截大量网络攻击和木马植入。

2.1.3、创建用于数据同步的账号

开放对应的数据库端口后，接着重启MySQL服务并使用root登录，稍后从库需要一个账号作为同步账号，这里可以用root账号，但最好再创建一个用户做区分，同时新的账号也可以做精准的权限控制：

[root@localhost]# service mysql start
[root@localhost]# mysql -uroot -p

mysql> create user 'zhuzi'@'%' identified with mysql_native_password by '666666';
mysql> grant replication slave on *.* to 'zhuzi'@'%';

上面我为了图简单，因此直接为新用户zhuzi分配了所有权限，但线上环境最好根据自己的需求取分配权限！

这里有个小细节要牢记，我在创建用户时通过with指定密码套件为mysql_native_password，这是早版本中，默认的密码认证套件，而在MySQL8.0中则将默认套件换成了caching_sha2_password，如果基于默认的认证套件创建用户，后续通过该账号连接时，则需要以SSL加密形式连接。

2.1.4、查看主库的运行状态

最后来简单查看一下master主节点的状态，如下：

mysql> show master status;

目前主库的bin-log日志正在使用mysql-bin-log.000001这个文件，Position也就是《主从原理篇》中提到的POS点，更具体一点的说，它代表当前日志文件的光标位置，每个MySQL节点都有可能不一致，因此MySQL5.7之后的版本并不会使用这种最基本的数据复制方式，这里先做简单演示，后续会使用GTID复制。

2.2、配置MySQL从节点（slave）

前面配置好了MySQL的主库，接着再来弄一下MySQL从库，配置步骤也大致相同，但这里需要牢记一点：如果你的单/多个从节点是直接克隆出来的，一定要记得更改的一下server_uuid，因为克隆会导致每个节点的server_uuid相同，这种情况下是无法搭建出主从集群的。

更改server_uuid的方式也很简单，MySQL在启动时会随机生成一个UUID放到数据目录下的auto.cnf文件中，如果是克隆出来的机器，直接rm -rf移除auto.cnf文件，再次重启MySQL后又会生成一个新的UUID。

2.2.3、更改从库的核心配置

同样得先修改一下从库的/etc/my.cnf文件，将其配置为如下格式：

[root@localhost]# service mysql stop
[root@localhost]# vi /etc/my.cnf

[mysqld]
basedir=/soft/mysql/mysql8.0
datadir=/soft/mysql/mysql8.0/data

port=3306
socket=/tmp/mysql.sock
character-set-server=UTF8MB4

# ------接下来的是主从配置-------

# 从库在主从集群中的唯一标识
server-id=2

# 开启bin-log日志（为了主从切换时使用，不开启bin-log的从机只能当备库使用）
log-bin=mysql-bin-log
# 同步复制时要过滤的库
binlog-ignore-db=mysql
# 指定bin-log日志的格式为混合模式
binlog_format=mixed
# 设置单个binlog日志文件的最大容量
max_binlog_size=1024M

# 开启relay-log日志（同样可以指定前缀名）
relay_log=mysql-relay-log
# 开启存储过程、函数、触发器等内容的同步功能
log_bin_trust_function_creators=true
# 同步执行跳过一些错误码（防止同步写入时出现错误导致复制中断）
slave_skip_errors=1062

如果你的从库只打算作为备库使用，那则无需再开启从库的bin-log日志，因为这会让从库的性能下降，毕竟写数据时还需要多写一次日志，相较于只写数据而言，性能开销近乎降低一倍。但如若从库还打算用于热备切换，那则必须也要开启bin-log日志，否则这个从节点升级为主库后，无法接收客户端SQL写入数据，也无法给其他从库提供数据复制的基础。

2.2.2、开放从库的端口和防火墙

不仅仅只有主库需要开放3306端口，从库所在的机器同样需要，因为主从之间是基于TCP/IP协议来构建网络连接，从而实现传输数据的，TCP协议是一种对端协议，一方的端口未开放都有可能导致数据传输出现问题：

[root@localhost]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@localhost]# firewall-cmd --reload
[root@localhost]# firewall-cmd --zone=public --list-ports

2.2.3、从库连接账号实现数据同步

想要配置主库的连接账号，从库也需要先登录root账号，或者其他具备权限的账号也行，接着执行下述命令即可：

[root@localhost]# service mysql start
[root@localhost]# mysql -uroot -p

mysql> change master to master_host='192.168.12.128',
       master_user='zhuzi',
       master_password='666666',
       master_port=3306,
       master_log_file='mysql-bin-log.000001',
       master_log_pos=653;

也就是基于原本主库上创建的zhuzi账号，以此来建立网络连接，在主库数据更新后，从库会以客户端的方式请求主库的bin-log日志，接着写入到自身的relay-log日志中，后续从库上的SQL线程会读取relay-log日志解析执行。

这里额外提一句：如果你主库上创建的远程账号，使用了8.0中默认的caching_sha2_password套件，那在这里连接时，也仅仅只需要在最后加上一行master_ssl=1即可！

2.2.4、启动从库线程、观察从库状态

从库上连接好账号之后，接着需要启动一下从库线程：

mysql> start slave;
mysql> show slave status\G;

上述命令执行完成后，会出现下面这一堆信息：

在其中必须要看到Slave_IO_Running、Slave_SQL_Running两个线程的状态都为Yes时，这才意味着主从集群搭建完成。

2.2.5、测试主从数据同步的效果

前面手把手带着诸位将最基本的主从集群搭建出来了，如果你想要为主节点搭载更多的从库，只需要再多开几个机器/节点，重复前面的从库配置即可，接着来实际测试看看主从同步的效果，先看看主从节点各自的数据情况：

# 查询主节点的库信息
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

# 查询从节点的库信息
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

从上述信息中可看出，目前主、从节点都属于裸机状态，其中没有任何数据，接着先在主节点上创建一个名为db_zhuzi的库：

mysql> create database db_zhuzi;

从上图的效果中，也能够明显看出，虽然从节点上未主动创建库，但从节点会直接将主节点上创建的db_zhuzi库直接同步过来，接着再创建一张zz_users看看：

mysql> create table `zz_users`  (
  `user_id` int(8) not null,
  `user_name` varchar(255) not null,
  `user_sex` varchar(255) not null,
  `password` varchar(255) not null,
  `register_time` datetime
) 
engine = InnoDB 
character set = utf8 
collate = utf8_general_ci 
row_format = Compact;

从效果图上看，不仅仅之前创建库的操作会同步，创建表的记录依旧会通过bin-log日志同步过来，这也就意味着DDL语句都会做同步，那接下来再试试DML语句，也就是增、删、改语句的效果是怎么样呢？

mysql> insert into `zz_users` values(1,"男人","女","1111",now()),(2,"啊啊","男","1234",now());

效果依旧十分明显，咱们只在master节点上执行了数据插入动作，但从节点上依旧可以查询出数据，这也就意味着主库上插入的数据，也被同步过来啦~，对于修改、删除操作则不再重复演示，大家可以自己玩一玩，效果依旧是相同的！

三、GTID复制、无损复制落地实操

经过第二阶段的内容讲解后，就已经完成了最传统的主从集群搭建，但这种集群有一个令人十分难受的点，也就是当主节点宕机后，假设从节点成为了新主，如果这里还有其他从节点，就需要指向新主，但POS定位又会成为一个问题，毕竟各个从库之间的复制进度也许不一致，所以需要开发/运维人员手动介入，才能寻找到合适的同步点。

也正是由于上述这个问题，所以在MySQL5.6中引入了一种名为GTID复制的技术，专门用于处理从库寻点的问题，在前面配置从节点时，需要咱们手动指定master_log_pos=653同步点，而开启GTID复制后则可根治该问题，但具体如何操作呢？接着咱们来实操一下。

3.1、GTID复制实操

因为之前咱们从库已经配置好了同步，因此这里需要停止一下从库的同步线程，如下：

mysql> stop slave;

停止从库的同步线程，接着主从节点都需要退出连接，然后停止MySQL服务：

mysql> quit
[root@localhost]# service mysql stop

接着需要再次更改两个节点的/etc/my.cnf文件，增加如下内容：

# 开启GTID复制
gtid_mode=on
# 跳过一些可能导致执行出错的SQL语句
enforce-gtid-consistency=true

接着再重启两个节点上的MySQL服务，然后登录从节点，再通过下述命令建立与主库的连接：

mysql> change master to master_host='192.168.12.128',
       master_user='zhuzi',
       master_password='666666',
       master_port=3306,
       master_auto_position=1;

从这里可以看到，原本需要通过master_log_file='mysql-bin-log.000001', master_log_pos=653指定同步的日志文件和同步点，但在GTID复制模式中，只需要设置一个master_auto_position=1即可，接下来它会自动寻找同步点，接着来试试效果，但不要忘了在从机上启动slave线程：

mysql> start slave;

测试结果如下：

①首先分别在主从节点上查询表数据，此时各自用户表中都只有两条数据。
②启动从节点上的同步线程，并在主节点上插入一条ID=3的数据。
③从上图也能明显看到从节点上有数据，配置了GTID复制后，数据同步机制依旧正常工作。
④最后查看一下主节点的状态，可以明显看到目前Executed_Gtid_Set已经有值啦！

如果未开启GTID复制，主从节点的数据同步必须要求手动指定POS同步点，从库会依照指定同步点开始同步数据，而开启GTID复制模式后，MySQL会自动寻找数据的同步点。

3.2、无损复制实操

MySQL5.7中对原有的半同步复制模式做了增强，也就是引入了无损复制的模式，具体的概念可参考上篇《MySQL主从原理篇-无损复制》，MySQL的无损复制是插件式实现的，这也就意味着想要使用还需要先安装插件，如下：

主节点执行：INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
从节点执行：INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

分别在主从节点上安装后，接着依旧要停止同步线程、退出连接、关闭MySQL服务，接着再次修改主从的/etc/my.cnf配置文件，内容如下：

# 主节点上新增配置：
# 开启无损复制
rpl_semi_sync_master_enabled=on
# 等待从节点ACK的时长（不指定默认为10s）
rpl_semi_sync_master_timeout=3000

# 从节点上新增配置：
rpl_semi_sync_slave_enabled=on

在各自的文件中新增上述配置后，再次重启MySQL服务就能将复制模式改为无无损复制方案，但线上最好不要使用这种无损复制，因为会导致MySQL整体性能直线下滑，如果对数据实时性要求较高，可以引入Canal中间件来监控主节点的Bin-log日志。

四、多主架构搭建实战

经过第三阶段的学习后，咱们掌握了一些主从集群中的高级特性，接着再来搭建一下另一种十分流行的主从架构方案，也就双主/多主多写架构，其实这种多主的思想十分简单，好比目前我这两个节点组成的集群，两个互为各自的主节点，也互为各自的从节点，这里就不对这些概念做重复叙述了，不了解的可参考《主从原理篇-多种主从架构模型》中的详细说明，这里就直接上实战。

4.1、修改第一个节点的配置文件

这里也就是将原本的128节点，再在其配置文件中加上从库的配置信息，然后再设置一下自增步长和初始值即可，完整配置文件如下：

[mysqld]
basedir=/soft/mysql/mysql8.0
datadir=/soft/mysql/mysql8.0/data

port=3306
socket=/tmp/mysql.sock
character-set-server=UTF8MB4

# ------主节点配置-------
# 当前节点在集群中的唯一标识
server-id=1
# 开启bin-log日志，并为bin-log日志取个前缀名（有默认值可不写）
log-bin=mysql-bin-log
# 同步复制时过滤的库（主要将一些不需要备份/同步库写进来）
# 也可以通过binlog-do-db=xx1,xx2... 来指定要复制的目标库
binlog-ignore-db=mysql
# 指定bin-log日志的格式为混合模式（默认为statement）
binlog_format=mixed
# 设置单个binlog日志文件的最大容量
max_binlog_size=1024M

# ------GTID配置-------
# 开启GTID复制
gtid_mode=on
# 跳过一些可能导致执行出错的SQL语句
enforce-gtid-consistency=true

# ------从节点配置-------
# 开启relay-log日志（同样可以指定前缀名）
relay_log=mysql-relay-log
# 开启存储过程、函数、触发器等内容的同步功能
log_bin_trust_function_creators=true
# 同步执行跳过一些错误码（防止同步写入时出现错误导致复制中断）
slave_skip_errors=1062

# ------自增序列配置-------
# 设置自增初始值为1
auto_increment_offset=1
# 设置自增步长为2，自增序列为{1、3、5、7、9.....}
auto_increment_increment=2

4.2、修改第二个节点的配置文件

上面完成了第一个节点配置后，接着再来配置第二个节点的信息，基本上大致相同，主要有两点不同的地方：

[mysqld]
basedir=/soft/mysql/mysql8.0
datadir=/soft/mysql/mysql8.0/data

port=3306
socket=/tmp/mysql.sock
character-set-server=UTF8MB4

# ------主节点配置-------
# 当前节点在集群中的唯一标识
server-id=2
# 开启bin-log日志，并为bin-log日志取个前缀名（有默认值可不写）
log-bin=mysql-bin-log
# 同步复制时过滤的库（主要将一些不需要备份/同步库写进来）
# 也可以通过binlog-do-db=xx1,xx2... 来指定要复制的目标库
binlog-ignore-db=mysql
# 指定bin-log日志的格式为混合模式（默认为statement）
binlog_format=mixed
# 设置单个binlog日志文件的最大容量
max_binlog_size=1024M

# ------GTID配置-------
# 开启GTID复制
gtid_mode=on
# 跳过一些可能导致执行出错的SQL语句
enforce-gtid-consistency=true

# ------从节点配置-------
# 开启relay-log日志（同样可以指定前缀名）
relay_log=mysql-relay-log
# 开启存储过程、函数、触发器等内容的同步功能
log_bin_trust_function_creators=true
# 同步执行跳过一些错误码（防止同步写入时出现错误导致复制中断）
slave_skip_errors=1062

# ------自增序列配置-------
# 设置自增初始值为1
auto_increment_offset=2
# 设置自增步长为2，自增序列为{1、3、5、7、9.....}
auto_increment_increment=2

第二个节点的配置信息和第一个节点的信息，只有两点不同：

server-id=2：这个是各节点在集群中的唯一标识，不能重复。
auto_increment_offset=2 :自增初始值从1变为2,最终效果会变为：
- 192.168.12.128：1、3、5、7、9、11、13、15、17.......
- 192.168.12.129：2、4、6、8、10、12、14、16、18......

接着需要先启动一下原本作为从库的192.168.12.129节点，同样需要创建一个同步账号，如下：

[root@localhost]# service mysql start
[root@localhost]# mysql -uroot -p

mysql> create user 'zhuzi'@'%' identified with mysql_native_password by '666666';
mysql> grant replication slave on *.* to 'zhuzi'@'%';

创建好了账号后，接着再启动192.168.12.128节点的MySQL服务，两个节点都通过root账号登录，接着执行下述命令，都将对方指定为自己的主节点：

# 192.168.12.128节点上执行，将192.168.12.129设为自己的主节点
mysql> change master to master_host='192.168.12.129',
       master_user='zhuzi',
       master_password='666666',
       master_port=3306,
       master_auto_position=1;

# 192.168.12.129节点上执行，将192.168.12.128设为自己的主节点
mysql> change master to master_host='192.168.12.128',
       master_user='zhuzi',
       master_password='666666',
       master_port=3306,
       master_auto_position=1;

最后再分别启动各自的同步线程：

mysql> start slave;

然后再在任意一个节点上创建一张zz_user用户表：

mysql> create table `zz_user`  (
  `user_id` int(8) not null auto_increment,
  `user_name` varchar(255),
  primary key (`user_id`) using btree
) 
engine = InnoDB 
character set = utf8 
collate = utf8_general_ci 
row_format = Compact;

上述表中将user_id设置为了主键，并具备自增特性，同时因为两个节点互为主从，所以在一个节点上创建表之后，就会自动同步给另一个节点，因此无需在两个节点上都创建表，测试一下：

①在192.168.12.128节点上，插入了一条名为“竹子”的用户数据。
②在192.168.12.129节点上，可以查询出名为“竹子”的这条用户数据。
③在192.168.12.129节点上，插入了一条名为“熊猫”的用户数据。
④在192.168.12.128节点上，也可以查询出名为“熊猫”的这条用户数据。

从上述结果中可明显看出效果，这也就意味着咱们的双主双写架构就搭建完成啦！两台节点都支持客户端读写数据，同时两个节点都具备完整数据，这也就是所谓的双主双写架构，大家也可以用更多的节点来搭建环形多主架构，但之前《MySQL优化篇-架构优化》中聊过，搭建双主以上的多主架构则没有必要了，可以直接将架构升级到分库分表。

五、主从实践篇总结

前面的步骤中实现了数据库的主备、主主搭建，但这仅仅只是数据库的基础架构，如果想要将这些架构应用到业务中，还得解决客户端的连接问题，毕竟现在从原有的单库变为了多库，因此多数据源的连接需要再通过新的手段处理，业内一般有三种做法：

①基于Spring、MyBatis提供的接口和类，拦截SQL语句动态选择对应的数据源。
②使用Sharding-JDBC这类驱动层框架，配置多个数据源，路由改写的工作交给框架完成。
③使用MyCat、Sharding-Proxy等这类代理中间件，业务系统连接中间件，路由改写的工作交给中间件完成。

基于上述三种方案，配置好多数据源的管理后，业务系统在线上运行时，会自行根据SQL类型分发请求，比如主从搭建读写分离架构，就是将SQL以insert、delete、update、alter...这类关键字开头的语句，分发到主节点处理，对于select这类关键字开头的语句，分发到从节点处理。

对于双主双写的架构而言，就只需要取模分发或随机分发即可，无需区分读写操作，毕竟两个节点都会相互同步数据，因此最终都会具备完整的数据，所以集群内的任意节点，都具备处理读写请求的能力。

PS：虽然本章中只搭建了一主多从、多主多写两种架构，但对于多主一从、级联复制这两种架构，也仅仅只是节点数量的分配不同，因此大家有兴趣可自行搭建，这里就不再重复赘述~

同时，为了确保数据库集群的高可用，一般也会结合Keeplived+VIP来对各节点做虚拟IP映射和宕机检测，这样能够确保MySQL出现宕机时，能够第一时间将流量切换到新主上，并且也能够快速重启宕机的程序，但Keeplived+VIP在之前关于《Nginx高可用》的文章详细讲过，大家感兴趣可以去看看，这里同样不做重复赘述。

作者：竹子爱熊猫

链接：https://juejin.cn/post/7170202668013453319

来源：稀土掘金

mysql

Loading

龙行博客

龙行博客