Mysql双主双从搭建步骤

拼搏现实的明天。 2023-07-07 03:58 12阅读 0赞

# 背景：   需要搭建Mycat商用环境，使用4台虚拟机操练一下，最终的目标是： #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5_size_16_color_FFFFFF_t_70][]

## 服务器情况： ##

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:138.25pt;"> 机器IP </td> 
 <td style="vertical-align:top;width:138.25pt;"> 机器名 </td> 
 <td style="vertical-align:top;width:138.3pt;"> 安装组件 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:138.25pt;"> 192.168.239.144 </td> 
 <td style="vertical-align:top;width:138.25pt;"> douzi9 </td> 
 <td style="vertical-align:top;width:138.3pt;"> Master1 mycat1 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:138.25pt;"> 192.168.239.145 </td> 
 <td style="vertical-align:top;width:138.25pt;"> douzi10 </td> 
 <td style="vertical-align:top;width:138.3pt;"> Slave1 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:138.25pt;"> 192.168.239.146 </td> 
 <td style="vertical-align:top;width:138.25pt;"> douzi11 </td> 
 <td style="vertical-align:top;width:138.3pt;"> Master2 mycat2 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:138.25pt;"> 192.168.239.147 </td> 
 <td style="vertical-align:top;width:138.25pt;"> douzi12 </td> 
 <td style="vertical-align:top;width:138.3pt;"> Slave2 </td> 
 </tr> 
 </tbody> 
</table>

## 四台服务器安装mysql参考： ##

[https://blog.csdn.net/qq\_16116549/article/details/104106070][https_blog.csdn.net_qq_16116549_article_details_104106070]

## 一主一从请参考： ##

[https://blog.csdn.net/qq\_16116549/article/details/104418150][https_blog.csdn.net_qq_16116549_article_details_104418150]

# 正文： #

## 1.书接一主一从上文，删除主数据库中建立的testdb； ##

## 2.M1服务器douzi9 的mysql配置, douzi add config start 和douzi add config end之间替换为下方配置 ##

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:414.8pt;"> [mysqld] # douzi add config start ## 启用二进制日志 log-bin=mysql-bin ## 主服务器唯一id server-id=1 ## 设置不要复制的数据库(可配多个) binlog-ignore-db=mysql ## 设置需要复制的数据库(可配多个) binlog-do-db=testdb ## 设置logbin格式 （STATEMENT、ROW、MIXED） binlog_format=STATEMENT &nbsp; ## 在作为从数据库时，也要更新二进制文件 log-slave-updates ## 表示自增长时每次递增的量，指自增字段的起始值，默认为1，范围是 1-65535 auto-increment-increment =2 ## 表示自增长字段从哪个值开始，范围1-65535 auto-increment-offset =1 # douzi add config end </td> 
 </tr> 
 </tbody> 
</table>

## 3.M2服务器douzi11 的mysql配置, douzi add config start 和douzi add config end之间替换为下方配置 ##

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:414.8pt;"> [mysqld] # douzi add config start ## 启用二进制日志 log-bin=mysql-bin ## 主服务器唯一id server-id=3 ## 设置不要复制的数据库(可配多个) binlog-ignore-db=mysql ## 设置需要复制的数据库(可配多个，多行配置) binlog-do-db=testdb ## 设置logbin格式 （STATEMENT、ROW、MIXED） binlog_format=STATEMENT &nbsp; ## 在作为从数据库时，也要更新二进制文件 log-slave-updates ## 表示自增长时每次递增的量，指自增字段的起始值，默认为1，范围是 1-65535 auto-increment-increment =2 ## 表示自增长字段从哪个值开始，范围1-65535 auto-increment-offset =1 # douzi add config end </td> 
 </tr> 
 </tbody> 
</table>

## 4.S1服务器douzi10 的mysql配置 ##

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:414.8pt;"> [mysqld] # douzi add config start ## 主服务器唯一id server-id=2 ## 启动中继日志 relay-log=mysql-relay # douzi add config end </td> 
 </tr> 
 </tbody> 
</table>

## 5.S2服务器douzi12 的mysql配置 ##

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:414.8pt;"> [mysqld] # douzi add config start ## 主服务器唯一id server-id=4 ## 启动中继日志 relay-log=mysql-relay # douzi add config end </td> 
 </tr> 
 </tbody> 
</table>

## 6.重启所有mysql服务器，并查看状态: ##

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:414.8pt;"> systemctl restart mysqld systemctl status mysqld </td> 
 </tr> 
 </tbody> 
</table>

## 7.M1、M2主机上建立账户并授权从机： ##

访问主机mysql服务器：mysql -uroot -p123456

执行授权sql：grant replication slave on \*.\* to 'slave'@'%' identified by '123456';

注：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

执行

set global validate_password_policy=0;

set global validate_password_length=1; 即可

## 8.停止从机S1、S2复制服务：stop slave; ##

## 9.重置主机M1、M2：reset master; ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5_size_16_color_FFFFFF_t_70 1][]

## 10.搭建双主从复制M1->S1 M2->S2,与单个主从相同； ##

*  S1命令：

<table style="margin-left:18pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:414.8pt;"> change master to master_host='192.168.239.144', master_port=3306,master_user='slave', master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154; </td> 
 </tr> 
 </tbody> 
</table>

*  S2命令：

<table style="margin-left:18pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:414.8pt;"> change master to master_host='192.168.239.146', master_port=3306,master_user='slave', master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154; </td> 
 </tr> 
 </tbody> 
</table>

## 11.启动从机S1、S2复制服务：start slave; 查看从机状态（列展现）：show slave status\\G; ##

## 12.同时M1也要复制M2，M2也要复制M1，互相作为对方的从机： ##

*  M1命令：

<table style="margin-left:18pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:414.8pt;"> change master to master_host='192.168.239.146', master_port=3306,master_user='slave', master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154; </td> 
 </tr> 
 </tbody> 
</table>

*  M2命令：

## 13.启动从机M1、M2复制服务：start slave; 查看从机状态（列展现）：show slave status\\G; ##

## 14.验证双主双从复制是否成功：M1或M2执行 ##

主库执行： Create database testdb; 查看从库是否有testdb；

CREATE TABLE \`mytbl\` (

\`id\` int(11) NOT NULL,

\`name\` varchar(255) DEFAULT NULL,

PRIMARY KEY (\`id\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5_size_16_color_FFFFFF_t_70 3][] ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5_size_16_color_FFFFFF_t_70 4][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200220212913352.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_qq_16116549_article_details_104106070]: https://blog.csdn.net/qq_16116549/article/details/104106070
[https_blog.csdn.net_qq_16116549_article_details_104418150]: https://blog.csdn.net/qq_16116549/article/details/104418150
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200220213247886.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200220213247960.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200220213247914.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200220213247916.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MTE2NTQ5,size_16,color_FFFFFF,t_70