ShardingSphere分库分表实战之MySQL主从集群搭建

🍀 一.ShardingSphere分库分表实战之MySQL主从同步原理

读写分离是建立在MySQL主从复制基础之上实现的，所以必须先搭建MySQL的主从复制架构。

🥦 1.1 主从复制的用途

• 实时灾备，用于故障切换

• 读写分离，提供查询服务

• 备份，避免影响业务

🥦 1.2 主从部署必要条件

• 主库开启binlog日志（设置log-bin参数）
• 主从server-id不同
• 从库服务器能连通主库

🥦 1.3 主从复制的原理

• Mysql 中有一种日志叫做 bin 日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL 语句（insert,update,delete,create/alter/drop table, grant 等等）。
• 主从复制的原理其实就是把主服务器上的 bin 日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。

1. 主库db的更新事件(update、insert、delete)被写到binlog
2. 主库创建一个binlog dump thread，把binlog的内容发送到从库
3. 从库启动并发起连接，连接到主库
4. 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
5. 从库启动之后，创建一个SQL线程，从relay log里面读取内容，执行读取到的更新事件，将更新内容写入到slave的db

🍀 二.ShardingSphere分库分表实战之MySQL主从集群搭建

🥦 2.1 创建俩个MySQL服务

如果你看过了之前的文章，并且都进行了实操，那么这个步骤你肯定完成了，如果你是刚刚学习，那么也不要紧，可以先去学习之前的文章，再来学习就会更加容易。

🥦 2.2 主数据库上创建需要同步的数据库以及表

-- 创建数据库
CREATE DATABASE test CHARACTER SET utf8;

-- 创建表
CREATE TABLE user (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20) DEFAULT NULL,
  age INT(11) DEFAULT NULL
); 

🥦 2.3 修改主数据库的配置文件my.cnf

直接在原来的配置文件内容下方添加即可！

# ...
# 省略原来的配置内容
# .....


# Master
# 开启日志
log-bin=mysql-bin
# 日志格式按行同步
binlog-format=ROW
# 设置服务id，主从不能一致
server-id=1
# 设置需要同步的数据库
binlog-do-db=test
# 设置忽略系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

• server-id=1 中的1可以任定义，只要是唯一的就行。
• log-bin=mysql-bin 表示启用binlog功能，并制定二进制日志的存储目录，
• binlog-do-db=test 是表示只备份test 数据库。
• binlog_ignore_db=mysql 表示忽略备份mysql。
• 不加binlog-do-db和binlog_ignore_db，那就表示备份全部数据库。

🥦 2.4 修改从数据库的配置文件my.cnf

同理， 配置我们从数据库的配置文件，最重要的就是将此时从库的server-id改为2！

# ...
# 省略原来的配置内容
# .....


# Slave
# 开启日志
log-bin=mysql-bin
# 日志格式按行同步
binlog-format=ROW
# 设置服务id，主从不能一致
server-id=2
# 设置需要同步的数据库
binlog-do-db=test
# 设置忽略系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

• server-id=2 中的1可以任定义，只要是唯一的就行。
• log-bin=mysql-bin 表示启用binlog功能，并制定二进制日志的存储目录，
• binlog-do-db=test 是表示只备份test 数据库。
• binlog_ignore_db=mysql 表示忽略备份mysql。
• 不加binlog-do-db和binlog_ignore_db，那就表示备份全部数据库。

🥦 2.5 登陆主数据上创建主从复制的账号

在执行下面命令的之前，俩个服务器上重启我们的MySQL服务。

docker restart mysql  

进入mysql容器内容，登陆mysql

docker exec -it mysql /bin/bash

mysql -uroot -proot

创建一个允许从数据库来访问的用户账号

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.10.133' IDENTIFIED BY '123456';

查询验证：

🥦 2.6 数据库中查询表获取文件名MASTER_LOG_FILE和点位MASTER_LOG_POS

执行如下的命令：

show master status \G;

🥦 2.7 从库登陆，从库向主库同步信息

进入容器，登陆mysql

停止同步

stop slave;

修改从库指向到主库，使用上一步记录的文件名以及位点

CHANGE MASTER TO 
master_host='192.168.10.132',
master_port=3306,
master_user='slave',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=154,
MASTER_CONNECT_RETRY=10;

启动同步

start slave;

查看运行状态

show slave status \G;

🥦 2.8 主库插入数据，从库查询信息

主库插入数据：

从库中查询对应的数据

🍀 三.MySQL集群主从同步启动后常见的错误

常见错误是Slave_IO_Running： No 或者 Connecting 的情况

🥦 3.1 解决方案1：

1. 首先停掉Slave服务

stop slave;

2. 到主服务器上查看主机状态, 记录File和Position对应的值

SHOW MASTER STATUS;

3. 然后到slave服务器上执行手动同步：

CHANGE MASTER TO 
master_host='192.168.10.132',
master_port=3306,
master_user='slave',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=1181,
MASTER_CONNECT_RETRY=10;

🥦 3.2 解决方案2

1. 程序可能在slave上进行了写操作；
2. 也可能是slave机器重起后，事务回滚造成的.；
3. 一般是事务回滚造成的,解决办法；

stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;

🍀 四.总结

本篇文章主要讲解了ShardingSphere分库分表实战之MySQL主从集群搭建，实操过程非常重要，大家一定要动手亲自实践一下，必须掌握。下节预告，ShardingSphere分库分表实战之Sharding-JDBC实现读写分离，敬请期待。

💬 五.共勉