CentOS7，MySQL主从配置和读写分离（MySQL主从、MySQL读写分离、MySQL分布式、数据库读写分离、主从配置）

末蓝、 2022-05-27 13:14 180阅读 0赞

**一、实验目标**

搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作。

**二、测试环境**

主数据库： CentOS7， MySQL5.6.38 ， 10.100.50.27

从数据库： CentOS7， MySQL5.6.38 ， 10.100.50.32

**三、主从配置步骤**

**1、确保主数据库与从数据库里的数据一样**

例如：主数据库里的a的数据库里有b，c，d表，那从数据库里的就应该有一个模子刻出来的a的数据库和b，c，d表

我这里在两台MySQL上都创建了个名为“test”的数据库来测试，如图：

![70][]

**2、在主数据库里创建一个同步账号**

1）每个从数据库会使用一个MySQL账号来连接主数据库，所以我们要在主数据库里创建一个账号，并且该账号要授予 REPLICATION SLAVE 权限，你可以为每个从数据库分别创建账号，当然也可以用同一个！）

2）你可以用原来的账号不一定要新创账号，但你应该注意，这个账号和密码会被明文存放在master.info文件中，因此建议单独创一个只拥有相关权限的账号，以减少对其它账号的危害！）

3）创建新账号使用“CREATEUSER”，给账号授权使用“GRANT”命令，如果你仅仅为了主从复制创建账号，只需要授予REPLICATION SLAVE权限。

4）下面来创建一个账号（我这里使用的root账号），账号名：root，密码：123456，只允许10.100.50.的IP段登录，如下：

`mysql>CREATE USER '``root``'@'1``0``.1``00``.``50``.%' IDENTIFIED BY '``123456``';`

`mysql>GRANT REPLICATION SLAVE ON *.* TO '``root``'@'1``0``.1``00``.``50``.%';`

5）如果开放防火墙，可能要配置下端口，如下：

`firewall-cmd--zone=public --add-port=3306/tcp --permanent`

`firewall-cmd--reload`

**3、配置主数据库**

1）要主数据库，你必须要启用二进制日志（binary logging），并且创建一个唯一的Server ID，这步骤可能要重启MySQL。

2）主服务器发送变更记录到从服务器依赖的是二进制日志，如果没启用二进制日志，复制操作不能实现（主库复制到从库）。

3）复制组中的每台服务器都要配置唯一的ServerID，取值范围是1到(2**32**)−1，你自己决定取值。

4）配置二进制日志和Server ID，你需要关闭MySQL和编辑my.cnf或者my.ini文件，在 \[mysqld\] 节点下添加配置。

5）下面是启用二进制日志，日志文件名以“mysql-bin”作为前缀，Server ID配置为1，如下：

`[mysqld]`

`log-bin=mysql-bin`

`server-id=1`

`#``网络上还有如下配置`

`#binlog-do-db=mstest//``要同步的mstest数据库,要同步多个数据库，就多加几个replicate-db-db=数据库名`

`#binlog-ignore-db=mysql //要忽略的数据库`

提示1：如果你不配置server-id或者配置值为0，那么主服务器将拒绝所有从服务器的连接。

提示2：在使用InnoDB的事务复制，为了尽可能持久和数据一致，你应该在my.cnf里配置innodb\_flush\_log\_at\_trx\_commit=1 和 sync\_binlog=1；

For the greatest possible durabilityand consistency in a replication setup using `InnoDB` with transactions, you shoulduse`innodb_flush_log_at_trx_commit=1` and `sync_binlog=1` in the master `my.cnf` file.

提示3：确保主服务器里的skip-networking选项未启用，如果网络被禁用，你的从服务器将不能与主服务器通信并且复制失败。

文件在 /etc/my.cnf 目录，如下：

![70 1][]

在 /etc/my.cnf 增加相关配置，如下：

![70 2][]

重启MySQL，如图：

![70 3][]

查看主服务器状态，mysql> show master status;， 如图：

![70 4][]

（注意：记录好File和Position，后面要用）

**4、配置从数据库**

1）从服务器，同理，要分配一个唯一的ServerID，需要关闭MySQL，修改好后再重启，如下：

`[mysqld]`

`server-id=2`

`#``可以指定要复制的库`

`replicate-do-db= test #`在master端不指定binlog-do-db，在slave端用replication-do-db来过滤

`replicate-ignore-db= mysql #``忽略的库`

`#``网上还有下面配置`

`#relay-log=mysqld-relay-bin`

![70 5][]

提示1：如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。

提示2：一般你不需要在从服务器上启用二进制日志，如果你在从服务器上启用二进制日志，那你可用它来做数据备份和崩溃恢复，或者做更复杂的事情（比如这个从服务器用来当作其它从服务器的主服务器）。

2）配置连接主服务器的信息

mysql>stop slave;
    mysql>CHANGE MASTER TO
    ->MASTER_HOST='10.100.50.27',
    ->MASTER_USER='root',
    -> MASTER_PASSWORD='123456',
    ->MASTER_LOG_FILE='mysql-bin.000001',
    ->MASTER_LOG_POS=120;
    mysql> start slave;

3）查看从服务器状态

`mysql>show slave status \G;`

![70 6][]

**5、测试数据同步**

测试，连接主服务器 10.100.50.27，添加了表 aaa，

然后再连接上 10.100.50.32，发现也自己同步创建了表 aaa，如图：

![70 7][]

然后在主数据库添加一条记录，从数据库也自动添加了记录，如图：

![70 8][]

![70 9][]

至此，主从的配置已经完成了， 目前是在从库里面配置复制“test”这个库，

如果要添加其它库，可以在主服务器中添加“binlog-do-db”配置，或者在从服务器中添加“replicate-do-db”配置。

**遇到的问题：**

Fatal error: The slave I/O thread stops because master and slavehave equal MySQL server UUIDs; these UUIDs must be different for replication towork.

原因分析：

mysql 5.6的复制引入了uuid的概念，各个复制结构中的server\_uuid得保证不一样，但是查看到直接copy  data文件夹后server\_uuid是相同的，show variables like'%server\_uuid%';

解决方法：

找到data文件夹下的auto.cnf文件，修改里面的uuid值，保证各个db的uuid不一样，重启db即可，或者删掉auto.cnf文件,重启mysql，会自动生成新的anto.cnf文件

![70 10][]

参考1：

最近在部署MySQL主从复制架构的时候，碰到了"Last\_IO\_Error: Fatal error: The slave I/O thread stops becausemaster and slave have equal MySQL server UUIDs;  these UUIDs must bedifferent for replication to work." 这个错误提示。即主从架构中使用了相同的UUID。检查server\_id系统变量，已经是不同的设置，那原因是？接下来为具体描述。

1、错误消息  
mysql> show slave staus;  
   
Last\_IO\_Error: Fatal error: The slave I/O thread stops because master and slavehave equal MySQL server UUIDs;  
these UUIDs must be different for replication to work.  
   
2、查看主从的server\_id变量  
master\_mysql> show variables like 'server\_id';  
\+---------------+-------+  
| Variable\_name | Value |  
\+---------------+-------+  
| server\_id    | 33    |  
\+---------------+-------+

3、解决故障  
\#\#\#查看auto.cnf文件  
\[root@dbsrv1 ~\] cat /data/mysqldata/auto.cnf  \#\#\# 主上的uuid  
\[auto\]  
server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026

\[root@dbsrv2~\]\# more /data/mysqldata/auto.cnf \#\#\#从上的uuid，果然出现了重复，原因是克隆了虚拟机，只改server\_id不行  
\[auto\]  
server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026

\[root@dbsrv2~\]\# mv /data/mysqldata/auto.cnf  /data/mysqldata/auto.cnf.bk  \#\#\#重命名该文件  
\[root@dbsrv2 ~\]\# service mysql restart          \#\#\#重启mysql  
Shutting down MySQL.\[  OK  \]  
Starting MySQL.\[  OK  \]  
\[root@dbsrv2 ~\]\# more /data/mysqldata/auto.cnf  \#\#\#重启后自动生成新的auto.cnf文件，即新的UUID  
\[auto\]  
server-uuid=6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9

\#\#\#再次查看slave的状态已经正常  
\[root@dbsrv1 ~\]\# mysql -uroot -pxxx -e "show slave status\\G"|grepRunning  
Warning: Using a password on the command line interface can be insecure.  
            Slave\_IO\_Running: Yes  
            Slave\_SQL\_Running: Yes  
      Slave\_SQL\_Running\_State: Slave has read all relay log;waiting for the slave I/O thread to update it

\#\#\#主库端查看自身的uuid  
master\_mysql> show variables like 'server\_uuid';  
\+---------------+--------------------------------------+  
| Variable\_name | Value                               |  
\+---------------+--------------------------------------+  
| server\_uuid  | 62ee10aa-b1f7-11e4-90ae-080027615026 |  
\+---------------+--------------------------------------+  
1 row in set (0.00 sec)

\#\#\#主库端查看从库的uuid  
master\_mysql> show slave hosts;  
\+-----------+------+------+-----------+--------------------------------------+  
| Server\_id | Host | Port | Master\_id | Slave\_UUID                         |  
\+-----------+------+------+-----------+--------------------------------------+  
|        33 |      | 3306 |       11 | 62ee10aa-b1f7-11e4-90ae-080027615030 |  
|        22 |      | 3306 |       11 | 6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9 |  
\+-----------+------+------+-----------+--------------------------------------+

参考2：

主从同步出现以下错误：

Slave\_IO\_Running: Connecting

Slave\_SQL\_Running: Yes

解决方法：

导致lave\_IO\_Running 为connecting 的原因主要有以下 3 个方面：

1、网络不通

2、密码不对

3、pos不对

我敢肯定的并且排除的两个原因

1. 网络原因：在从库上拷贝主库上的备份文件的时候并没有出现任何的错误，因此可以排除。

2. POS 不对：这个我也可以排除，因为在主库上 show maste status 时我记录过了 POS，所以也将这个原因排除了。

很显然的问题是出现在 password 上面，只是我在从库上执行

CHANGE MASTER **TO**

MASTER\_HOST='192.168.223.101',

MASTER\_USER='liyao',

MASTER\_PASSWORD='liyao',

MASTER\_LOG\_FILE='mysql-bin.000008',

MASTER\_LOG\_POS= 261;

的时候 MASTER\_PASSWORD='liyao', 中的 'liyao' 写成了 'liyao '，因此导致了从库上的 Slave\_IO\_Running: Connecting。

修改过后再来查看从库的状态，问题已经解决

mysql> show slave status\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting **for** master to send event

Master\_Host: 192.168.223.101

Master\_User: liyao

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000008

Read\_Master\_Log\_Pos: 261

Relay\_Log\_File: template-relay-bin.000002

Relay\_Log\_Pos: 253

Relay\_Master\_Log\_File: mysql-bin.000008

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

Replicate\_Do\_DB:

Replicate\_Ignore\_DB:

Replicate\_Do\_Table:

Replicate\_Ignore\_Table:

Replicate\_Wild\_Do\_Table:

Replicate\_Wild\_Ignore\_Table:

Last\_Errno: 0

Last\_Error:

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 261

Relay\_Log\_Space: 412

Until\_Condition: None

Until\_Log\_File:

Until\_Log\_Pos: 0

Master\_SSL\_Allowed: **No**

Master\_SSL\_CA\_File:

Master\_SSL\_CA\_Path:

Master\_SSL\_Cert:

Master\_SSL\_Cipher:

Master\_SSL\_Key:

Seconds\_Behind\_Master: 0

Master\_SSL\_Verify\_Server\_Cert: **No**

Last\_IO\_Errno: 0

Last\_IO\_Error:

Last\_SQL\_Errno: 0

Last\_SQL\_Error:

Replicate\_Ignore\_Server\_Ids:

Master\_Server\_Id: 255

1 row in set (0.00 sec)

由此得出的结论，在实验过程中还是需要细心，避免为自己的实验过程造成不必要的麻烦。

[70]: /images/20220527/79179c9a17f848e7ac776f01ecaab1ba.png
[70 1]: /images/20220527/87de8113235b4d28bf546a75e332e009.png
[70 2]: /images/20220527/dbbe7079083f4391aebbed4eef1ade0b.png
[70 3]: /images/20220527/cc7ee230e73541988e34753fe556027c.png
[70 4]: /images/20220527/62b9ffe14aef40aeb145b7612094c874.png
[70 5]: /images/20220527/cc71995ee77f44939fa8d77e2b64d2d9.png
[70 6]: /images/20220527/26ed8aa129ca484186a613a821a862ef.png
[70 7]: /images/20220527/77bf7b889a5840eb91344dadf5277894.png
[70 8]: /images/20220527/e954fe354dc6473ca9adae05538dda5f.png
[70 9]: /images/20220527/2a0a457681944479a7c350a7d7d8ffda.png
[70 10]: /images/20220527/1645e8417cbe4359b3171ab9e654dd9c.png