垂直拆分 + 水平拆分（分库分表） + 读写分离（主从复制）

垂直拆分概述

垂直拆分即业务拆分。

例如，电商数据库(shop)中有用户表(t_user)、商品表(t_goods)、订单表(t_order)，现在采用三个数据库分别只存放自己业务相关的表。如 用户数据库(user)中存放用户表(t_user)，货物数据库(goods)中存放货物表(t_goods)，订单数据库(order)中存放订单表(t_order)。

水平拆分（分库分表）概述

某张表数据过大，比如说超过100万条，则可以用五个数据库中的五个表均摊100万条数据，每个库的表中存储20万条数据。

读写分离（主从复制）概述

在实际生产中，数据的重要性不言而喻。

如果我们的数据库只有一台服务器，那么很容易产生单点故障的问题，比如这台服务器访问压力过大而没有响应或者崩溃，那么服务就不可用了，再比如这台服务器的硬盘坏了，那么整个数据库的数据就全部丢失了，这是重大的安全事故.

为了避免服务的不可用以及保障数据的安全可靠性，我们至少需要部署两台或两台以上服务器来存储数据库数据，也就是我们需要将数据复制多份部署在多台不同的服务器上，即使有一台服务器出现故障了，其他服务器依然可以继续提供服务.

MySQL提供了主从复制功能以提高服务的可用性与数据的安全可靠性.

主从复制是指服务器分为主服务器和从服务器，主服务器负责读和写，从服务器只负责读，主从复制也叫 master/slave，master是主，slave是从，但是并没有强制，也就是说从也可以写，主也可以读，只不过一般我们不这么做。

主从复制可以实现对数据库备份和读写分离

原理

  1. 当 master 主服务器上的数据发生改变时，则将其改变写入二进制事件日志文件中，混合类型的文件复制，默认采用基于语句的赋值，一旦发现基于语句无法精确地复制时，就会采用基于行的复制。
  2. salve 从服务器会在一定时间间隔内对 master 主服务器上的二进制日志进行探测，探测其是否发生过改变，如果探测到 master 主服务器的二进制事件日志发生了改变，则开始一个 I/O Thread 请求 master 二进制事件日志，同时 master 主服务器为每个 I/O Thread 启动一个dump Thread，用于向其发送二进制事件日志
  3. slave 从服务器将接收到的二进制事件日志保存至自己本地的中继日志文件中
  4. salve 从服务器将启动 SQL Thread 从中继日志中读取二进制日志，在本地重放，使得其数据和主服务器保持一致；
  5. 最后 I/O Thread 和 SQL Thread 将进入睡眠状态，等待下一次被唤醒
  6. ![](/Users/KXY/work/70.ITAssets/imgs/mysql master slave procedure.png)

1、用Docker部署MySQL集群(一主一从)

第一步、在同一台机器上运行两个mysql容器，host端口分别为3306和3307

# 主数据库
docker run -d --name mysql -p 3306:3306 \
-v /data/docker/mysql/conf:/etc/mysql/conf.d \
-v /data/docker/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=password \
--privileged=true --restart=always mysql:5.7

docker run -d --name mysql_3307 -p 3307:3306 \
-v /data/docker/mysql_3307/conf:/etc/mysql/conf.d \
-v /data/docker/mysql_3307/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=password \
--privileged=true --restart=always mysql:5.7

# 主库master配置文件my.cnf
[mysqld]
log-bin = mysql-bin
server-id = 3306

# 从库master配置文件my.cnf
[mysqld]
server-id = 3307

第二步、主库配置。连接主库后执行。

# 新建一个用户专门用来同步master的用户
CREATE USER 'backup'@'%' IDENTIFIED BY '123456';

# 给backup用户分配备份的权限，该语句可完成授权、创建用户、修改密码操作
GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';

# 主库配置完成，查看主库状态
show master status;
# 记住查询结果，后面会用。
File: mysql-bin.000001
Position: 688

第三步、从库配置。连接从库后执行。

# 从库通过IO线程连接master，所以需要指定master的信息，包括host, port, user, password
change master to master_host='10.10.225.117', 
    master_port=3306, 
    master_user='backup', 
    master_password='123456', 
    master_log_file='mysql-bin.000001', 
    master_log_pos=688;
start slave;

# 查看从库状态
show slave status;
#如果输出一下信息，说明连接成功。
Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 688
               Relay_Log_File: 6b0f3668aa62-relay-bin.000002
                Relay_Log_Pos: 913
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
 # 解决 Slave_SQL_Running:  NO 问题，（方案：数据库的备份）。https://blog.csdn.net/lilygg/article/details/98187015

第四步、查看主从复制binlog日志文件内容

# 在主服务器客户端执行：
show binlog events in 'mysql-bin.000001';

2、用Docker部署MySQL集群(两主两从)

一主多从，可以缓解读的压力，但是一旦主宕机了，就不能写了,所以我们可以采用双主双从架构来改进它的不足。

所谓双主，就是两台master互为主从

架构规划：

主master 3306 —> 从slave 3307

主master 3206 —> 从slave 3207

3306 <—> 3206 互为主从

2个写节点，每个写节点下又是1个读节点

第一步、在一主一从的基础上增加如下配置

# 主数据库3306的my.cnf中追加如下内容
auto_increment_increment=2
auto_increment_offset=1 #不一样的点 相当于起始值
log-slave-updates
sync_binlog=1

# 主数据库3206
docker run -d --name mysql_3206 -p 3206:3306 \
-v /data/docker/mysql_3206/conf:/etc/mysql/conf.d \
-v /data/docker/mysql_3206/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=password \
--privileged=true --restart=always mysql:5.7

# 从数据库3207
docker run -d --name mysql_3207 -p 3207:3306 \
-v /data/docker/mysql_3207/conf:/etc/mysql/conf.d \
-v /data/docker/mysql_3207/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=password \
--privileged=true --restart=always mysql:5.7

# 主库master_3206配置文件my.cnf
[mysqld]
log-bin = mysql-bin
server-id = 3206

auto_increment_increment=2
auto_increment_offset=2 #不一样的点 相当于起始值
log-slave-updates
sync_binlog=1

# 从库master_3207配置文件my.cnf
[mysqld]
server-id = 3207

配置项说明

（1） auto_increment_increment

控制主键自增的自增步长，用于防止Master与Master之间复制出现重复自增字段值，通常auto_increment_increment=n，有多少台主服务器，n 就设置为多少

（2） auto_increment_offset

设置自增起始值，这里设置为1，这样Master的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID

注意auto_increment_offset的设置，不同的master设置不应该一样，否则就容易引起主键冲突，比如master1的offset=1，则master2的offset=2，master3的offset=3

（3） log-slave-updates

在双主模式中，log-slave-updates 配置项一定要配置，否则在master1（3306）上进行了更新数据，在master2（3206）和slave1（3307）上会更新，但是在slave2（3207）上不会更新

（4） sync_binlog

表示每几次事务提交，MySQL把binlog缓存刷进日志文件中，默认是0，最安全的是设置为1

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

（5） *注意*

从库只开启log-bin功能，不添加log-slave-updates参数，从库从主库复制的数据不会写入log-bin日志文件里。

开启log-slave-updates参数后，从库从主库复制的数据会写入log-bin日志文件里。这也是该参数的功能。

直接向从库写入数据时，是会写入log-bin日志的

在自动生成主键的时候，会在已生成主键的基础上按照规则生成，即比存在的值大

第二步、主库3206配置。

# 新建一个用户专门用来同步master的用户
CREATE USER 'copy'@'%' IDENTIFIED BY '123456';

# 给backup用户分配备份的权限，该语句可完成授权、创建用户、修改密码操作
GRANT REPLICATION SLAVE ON *.* to 'copy'@'%' identified by '123456';

# 主库配置完成，查看主库状态
show master status;
# 记住查询结果，后面会用。
File: mysql-bin.000001
Position: 397531

第三步、从库配置。连接从库后执行。（现在相当于所有的库都是从库，两个master主库3306和3206互为主从）

# 连接从库3207、主库3306，设置其主库为3206
change master to master_host='10.10.225.117', 
    master_port=3206, 
    master_user='copy', 
    master_password='123456', 
    master_log_file='mysql-bin.000001', 
    master_log_pos=397531;
start slave;

# 连接主库3206，设置其主库为3306
change master to master_host='10.10.225.117', 
    master_port=3306, 
    master_user='backup', 
    master_password='123456', 
    master_log_file='mysql-bin.000002', 
    master_log_pos=450;
start slave;


# 查看主库状态
show master status;
# 查看从库状态
show slave status;

# 如果主服务状态不是初始状态，需要重置状态
reset master;

# 如果某机器设置过从库，现在要当做主库使用，需要执行如下命令
stop slave;
reset slave;

Q.E.D.

行走在天地间自由的灵魂