MySQL一主一从

水深无声 2023-02-12 05:48 29阅读 0赞

linux系统环境下，每次开机都要重启mysql，并关闭防火墙（应该可以设置，这里不讲）

重启mysql服务：/usr/local/mysql/support-files/mysql.server restart
    打开防火墙：service iptables start
    关闭防火墙：service iptables stop
    查看防火墙状态：service iptables status

## ##

# 一、配置两台可用的mysql，一主一从 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70][]

# 二、配置主mysql #

2.1、配置/etc/my.cnf文件，并重启

server_id=135 ###服务器id
    log-bin=mysql-bin ###开启日志文件

2.2、在Navicat中查询（验证上一步）

show variables like '%server_id%'; #查看server_id配置属性
    
    show master status; #查看logbin文件

如果查不到请查看防火墙是否未关闭

*  打开防火墙：service iptables start
 *  关闭防火墙：service iptables stop
 *  查看防火墙状态：service iptables status

# 三、配置从mysql #

3.1、配置/etc/my.cnf文件

server_id=136 ###服务器id
    log-bin=mysql-bin ###开启日志文件
    binlog_do_db=test ###同步的数据库名

3.2、在Navicat中查询（验证上一步）

show variables like '%server_id%'; #查看server_id配置属性
    
    show master status; #查看logbin文件
    
    #以下命令一定要理解使用
    stop slave; #关闭同步
    reset slave; #重置同步
    change master to master_host='192.168.154.135',master_user='root',master_password='root',master_log_file='mysql-bin.000008',master_log_pos=154; #与主数据库开启连接
    start slave; #开始同步
    show slave status; #查看从服务器复制功能状态

3.3、建立连接（Navicat中查询）

change master to master_host='192.168.154.135',master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=154;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 1][]

3.4、启动同步（Navicat中）：

mysql> start slave;

3.5、查看从服务器复制功能状态（Navicat中）：

show slave status;

如果查不到请查看防火墙是否未关闭

*  打开防火墙：service iptables start
 *  关闭防火墙：service iptables stop
 *  查看防火墙状态：service iptables status

# 四、结果展示： #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 2][]

## ##

# 五、异常 #

### 5.1、SLAVE IO\_THREAD 为 NO ###

5.1.1、发现IO线程没有被开启

5.1.2、原因：服务器克隆时uuid产生重复

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 3][]

日志报错：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

5.1.3、解决办法：

查看配置文件my.cnf中datadir=/usr/local/mysql/data

找到目录/usr/local/mysql/data，删除auto.cnf文件

重启mysql服务
    [root@localhost wanhua]# /usr/local/mysql/support-files/mysql.server restart

在Navicat中查询show slave status;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 4][]

### 5.2、SLAVE IO\_THREAD 为 NO ###

发现IO线程没有被开启 SLAVE IO\_THREAD

This operation cannot be performed with a running slave io thread; run STOP SLAVE IO\_THREAD FOR CHANNEL '' first.

1.  进入slave服务器mysql执行命令：STOP slave;
2.  在执行下面CHANGE MASTER命令

change master to master_host='192.168.154.135',master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=154;

3.成功后再执行命令：START slave;

### 5.3、SLAVE IO\_THREAD 为 NO ###

Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'CREATE DATABASE \`test\`'

解决：

stop slave;

reset slave;

start slave;

### 5.4、Error 'Can't create database 'test'; database exists' on query. Default data ###

test表存在,把这张表删除重新就可以（因为这个是从库，但是这样应该会丢失数据，求大佬告知如何完善）

### [MySQL的读写分离的几种选择][MySQL] ###

其它：

1、配置主

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 5][]

2、配置从

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 6][]

\--------------------

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 7][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200524204127180.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200524235432843.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200525002300254.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200525000332760.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200525001955467.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz,size_16,color_FFFFFF,t_70
[MySQL]: http://jayluns.iteye.com/blog/2275690
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200524204829305.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200524204909322.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200524205003638.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz,size_16,color_FFFFFF,t_70