mysql主从复制，读写分离-优快云博客

本文链接：https://blog.youkuaiyun.com/wangyijie11/article/details/88874065

本文介绍Mysql主从复制的配置过程，包括主服务器Master和从服务器Slave的配置步骤，以及Mycat中间件的配置方法。此外，还提供了读写分离及主从复制状态绑定的测试案例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、 Mysql主服务器Master配置

1、修改主服务器配置:

#vi /etc/my.cnf

binlog-do-db=db1 #根据具体要复制的库进行添加

binlog-do-db=db2

binlog-do-db=db3

binlog-ignore-db = mysql #可以不写

log-bin=mysql-bin #启用二进制日志

server-id=1 #服务器唯一ID

2、重启MySQL

Service mysql restart

3、建立帐户并授权slave:

#/usr/local/mysql/bin/mysql -uroot -p123456 –h127.0.0.1

mysql>GRANT FILE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';

mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'slave'@'%' IDENTIFIED BY '123456';

#一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.137.20，加强安全。

刷新权限

mysql> FLUSH PRIVILEGES;

查看mysql现在有哪些用户

mysql>select user,host from mysql.user;

4、登录主服务器的mysql，查询master的状态

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

Master 重启后会修改mysql-bin（序号加1）

二、 MySQL从服务器Slave 配置

1、修改从服务器配置:

#vi /etc/my.cnf

replicate-do-db=db1 #跟主服务器Master中的内容相同

replicate-do-db=db2

replicate-do-db=db3

replicate-ignore-db=mysql #可以不写

server-id=2 #每个Slave的server-id必须不同

#master-host=192.168.137.11 #如果Slave启动失败，注释掉该内容，从Slave的Mysql手动添加

#master-user=slave

#master-password=123456

#master-port=3306

#master-connect-retry=60

#log-slave-updates

skip-slave-start #防止复制随着mysql启动而自动启动。即slave端的mysql服务重启后需手动来启动主从复制（slave start），最好加上，slave端数据库服务重启后手动启动slave比较安全

2、重启MySQL

Service mysql restart

3、登录mysql并停止slave服务

mysql -uroot –p123456 –h127.0.0.1

mysql> stop slave;

设置与master服务器相关的配置参数

mysql>change master to master_host='192.168.137.11', master_user='slave', master_password='123456',MASTER_LOG_FILE='mysql-bin.000015',MASTER_LOG_POS=106;

注意：Master重启后slave 要修改MASTER_LOG_FILE，106无单引号。

启动从服务器复制功能

Mysql>start slave;

ERROR 1201 (HY000):Could not initialize master info structure的问题

解决方案是：运行命令 stop slave;

成功执行后继续运行 reset slave;

查看server_id:

Mysql> SHOW VARIABLES LIKE 'server_id';

4、检查从服务器复制功能状态

mysql> show slave status\G

以下两个参数必须为YES：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

返回如下：

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.1.176.158

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000010

Read_Master_Log_Pos: 106

Relay_Log_File: bogon-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000010

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: db1,db2,db3

Replicate_Ignore_DB: mysql

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: 106

Relay_Log_Space: 406

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:

1 row in set (0.00 sec)

三、数据库中间件Mycat配置

跳过第1部分，看第二部分，配置Mycat的schema.xml文件

1、不使用Mycat托管MySQL主从服务器，简单使用如下配置

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<writeHost host="hostM" url="192.168.11:3306" user="root"

password="123456">

</writeHost>

</dataHost>

2、Mycat 1.4 支持MySQL主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"

writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">

<heartbeat>show slave status</heartbeat>

<writeHost host="hostM" url"10.1.176.158:3306" user="root"

password="123456">

<readHost host="hostS" url="10.1.176.78:3306" user="root"

password="123456" />

</writeHost>s

</dataHost>

(1) 设置 balance="1"与writeType="0"

Balance参数设置：

1. balance=“0”, 所有读操作都发送到当前可用的writeHost上。

2. balance=“1”，所有读操作都随机的发送到readHost。

3. balance=“2”，所有读操作都随机的在writeHost、readhost上分发

WriteType参数设置：

1. writeType=“0”, 所有写操作都发送到可用的writeHost上。

2. writeType=“1”，所有写操作都随机的发送到readHost。

3. writeType=“2”，所有写操作都随机的在writeHost、readhost分上发。

“readHost是从属于writeHost的，即意味着它从那个writeHost获取同步数据，因此，当它所属的writeHost宕机了，则它也不会再参与到读写分离中来，即“不工作了”，这是因为此时，它的数据已经“不可靠”了。基于这个考虑，目前mycat 1.3和1.4版本中，若想支持MySQL一主一从的标准配置，并且在主节点宕机的情况下，从节点还能读取数据，则需要在Mycat里配置为两个writeHost并设置banlance=1。”

(2) 设置 switchType="2" 与slaveThreshold="100"

“Mycat心跳检查语句配置为 show slave status ，dataHost 上定义两个新属性： switchType="2" 与slaveThreshold="100"，此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制。Mycat心跳机制通过检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。“