搭建MySQL主从数据库，首先需要满足以下条件：

  • 至少两台以上服务器（可用虚拟机模拟）；
  • 两台服务器中MySQL的版本一致。

最好是使用刚安装的MySQL并且没有任何修改。

如果要配置从库的MySQL服务器中有数据库存在（或者是要给主库配置新的从库），还需要将该服务器中数据库的结构及其数据导出，并且导入到从库服务器中，保持主库和从库的状态一致（非常重要！！！）。

修改数据库配置

假设你已经满足了以上条件，准备开始配置主库。

修改主库的配置文件/etc/my.cnf：

vim /etc/my.cnf

增加（修改）以下内容：

[mysqld]
# 主库Master
log-bin=mysql-bin   # [必须] 启用二进制日志
server-id=100       # [必须] 服务器唯一ID（默认是1，一般取IP最后一段）

接着修改从库配置文件/etc/my.cnf（方法与上类似）：

[mysqld]
# 从库Slave
log-bin=mysql-bin   # [可选] 启用二进制日志
server-id=101       # [必须] 服务器唯一ID

分别重启两台服务器的MySQL：

service mysqld restart;
# 或
systemctl restart mysql;

授权

在主服务器上建立帐户并给从库授权：

mysql -uroot -p
# 登录你的主服务器MySQL

GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'%' IDENTIFIED BY '123456';  -- 一般不用root账号进行授权
  • %：表示所有客户端都可连接，只要账号、密码正确即可。可以使用具体的IP地址代替以加强安全。
  • mysync：表示使用客户端上mysync这个账号进行登录。
  • IDENTIFIED BY '123456'：表示使用123456作为密码登录。

授权成功后，使用以下命令查询Master的状态：

SHOW MASTER STATUS;

会返回如下信息：

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

注：执行完成后不要再操作主服务器MySQL，防止主服务器状态值变化。

配置从服务器Slave：

mysql -uroot -p
# 登录你的从服务器MySQL

CHANGE MASTER TO 
    master_host='192.168.145.100',      -- 主库IP地址
    master_port=3306,                   -- 主库MySQL端口号（默认就是3306，可以移除）
    master_user='mysync',               -- 使用主库的mysync用户登录
    master_password='123456',           -- 使用123456作为登录密码
    master_log_file='mysql-bin.000013', -- 主库的日志文件（与上方查询的结果要一致）
    master_log_pos=157;                 -- 主库的Position（与上方查询的结果要一致）

查询从库复制功能状态：

SHOW SLAVE STATUS\G

注：查询从库复制功能状态时，不要直接使用SHOW SLAVE STATUS;查询。这样查询的结果是一张用字符组织起来的表（十分冗长）。使用SHOW SLAVE STATUS\G输出更加易读。

如果查询结果中有以下内容，说明配置成功：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

使用 Docker 搭建

如果想要在一台主机上模拟MySQL分库。可以使用Docker。

拉取MySQL镜像：

docker pull mysql

可以根据你的需要指定MySQL镜像的版本，例如：

docker pull mysql:5.7

在Docker中，两个镜像相互连接需要使用Docker Network，我们可以创建一个让MySQL可以互相访问的Network：

docker network create mysql-network

首先创建一个MySQL容器，用于获取它的配置文件：

$ docker run -id --name mysql \
    -e TZ=Asia/Shanghai \                   # 设置时区
    -e MYSQL_ROOT_PASSWORD=123456 \         # 设置root用户的密码
    mysql \                                 # 创建时使用的镜像
    --character-set-server=utf8mb4 \        # 设置字符集
    --collation-server=utf8mb4_general_ci
$ docker cp mysql:/etc/my.cnf /home/mysql/my.cnf # 将mysql容器中的/etc/my.cnf复制到本地
$ mkdir /home/mysql/master
$ mkdir /home/mysql/slave_1
$ cp /home/mysql/my.cnf /home/mysql/master
$ cp /home/mysql/my.cnf /home/mysql/slave_1

创建Master容器：

docker run -id --name mysql-master \
    -p 3306:3306 \                          # 端口映射
    -v /home/mysql/master/data:/var/lib/mysql \
    -v /home/mysql/master/conf:/etc/mysql/conf.d \
    -v /home/mysql/master/log:/var/log/mysql \
    -v /home/mysql/master/my.cnf:/etc/my.cnf \
    --network mysql-network \               # 指定Network
    -e TZ=Asia/Shanghai \ 
    -e MYSQL_ROOT_PASSWORD=123456 \ 
    mysql \ 
    --character-set-server=utf8mb4 \ 
    --collation-server=utf8mb4_general_ci

注：上方命令在创建容器时，将容器中的/etc/my.cnf挂载到本地的/home/mysql/master/my.cnf文件上。在创建容器前需确保/home/mysql/master目录中有my.cnf这个文件。在挂载成功后，可以直接修改本地的my.cnf文件。

测试连接Master：

docker exec -ti mysql-master mysql -uroot -p123456

创建Slave容器的步骤与Master类似：

$ docker run -id --name mysql-slave-1 \
    -p 3316:3306 \                          # 端口映射（注意不要端口冲突）
    -v /home/mysql/slave_1/data:/var/lib/mysql \
    -v /home/mysql/slave_1/conf:/etc/mysql/conf.d \
    -v /home/mysql/slave_1/log:/var/log/mysql \
    -v /home/mysql/slave_1/my.cnf:/etc/my.cnf \
    --network mysql-network \               # 指定Network
    -e TZ=Asia/Shanghai \ 
    -e MYSQL_ROOT_PASSWORD=123456 \ 
    mysql \ 
    --character-set-server=utf8mb4 \ 
    --collation-server=utf8mb4_general_ci
$ docker exec -ti mysql-slave-1 mysql -uroot -p123456

最后按照上方配置和授权的步骤即可搭建成功。

读写分离

在一般的生产环境中，使用一台数据服务器进行写操作（一般很少进行写操作，所以一台就够了）。这台进行写操作的服务器就是主库。进行读操作则可以根据网站的浏览量配置对应数量的专门用于读取数据的数据服务器。这些进行读操作的服务器就是从库，所以需要配置多台从服务器。

使用过程中的一些问题

查询从库的状态，从库忽然Slave_SQL_Running: No。这是运行过程中的同步故障。出现该问题原因可能有：

  • 程序可能在Slave上进行了写操作（应该极力避免此种情况发生）。
  • 可能是Slave机器重起后，事务回滚造成的（一般是这种情况）。

解决事务回滚造成的Slave_SQL_Running: No，进入MySQL，运行：

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

如果还是Slave_SQL_Running: No，有可能是CHANGE MASTER时信息出现错误。可以回到主服务器中运行SHOW MASTER STATUS;查看日志文件和Position是否与从库的一致。然后再在从库中重新CHANGE MASTER。