MySQL搭建主从报错“The server is not configured as slave“怎么解决

39次阅读

共计 3675 个字符，预计需要花费 10 分钟才能阅读完成。

这篇文章主要介绍“MySQL 搭建主从报错“The server is not configured as slave“怎么解决”，在日常操作中，相信很多人在 MySQL 搭建主从报错“The server is not configured as slave“怎么解决问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL 搭建主从报错“The server is not configured as slave“怎么解决”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
这里说，我没有配置为从机，但我明明配置过的啊。

MySQL 版本：5.6.19
主：master IP : 172.17.210.199
从：slave IP：172.17.206.138

先看看从机 172.17.206.138 的 my.cnf
[root@testmysql ~]# vi /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
skip-grant-tables
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
tmpdir=/tmp
[mysqld_safe]
log-error=/usr/local/data/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid
############### 以下是添加主从的配置
server_id = 2
log-bin = /usr/local/mysql/log/solve-bin.log
master-host = 172.17.210.199
master-user = my
master-pass = 123456
master-port = 3306
master-connect-retry = 60

这是 MySQL 主机 172.17.210.199 的 my.cnf
[oracle@newbidb ~]$ cat /etc/my.cnf
[mysqld]
log-bin = /u01/mysql/log/masters-bin.log
read-only = 0
basedir = /u01/mysql
datadir = /u01/mysql/data
port = 3306
server_id = 1
socket = /tmp/mysql.sock
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

尝试过多次重启主从还是报错;
接着手动 chang to 后还是一样报错
mysql CHANGE MASTER TO
MASTER_HOST= 172.17.210.199 ,
MASTER_USER= my ,
MASTER_PASSWORD= 123456 ,
MASTER_LOG_FILE= masters-bin.000003 ,
MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql START slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
mysql SHOW slave STATUS \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.17.210.199
Master_User: my
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: masters-bin.000003
Read_Master_Log_Pos: 120
Relay_Log_File: testmysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: masters-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
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: 120
—————- 略

于是查看日志
1. 查看 SLAVE172.17.206.138 上的 Mysql 报错日志，有这么一句：
141009 6:06:29 [ERROR] Server id not set, will not start slave

意思是，slave 的 server-id 没有设置。
那就奇怪了，我明明在配置文件里面指定了 server-id 的了，并且有重启 mysql 服务，难道不起效？
分别在主从上执行命令“show variables like server_id”。

在从机 172.17.206.138 上执行命令
mysql SET GLOBAL server_id=2;
再次在从机 172.17.206.138 上执行 slave start 和 show slave status，成功了。
注意！！！由于“SET GLOBAL server_id=;”命令会在 mysql 服务重启后丢失，所以一定要写到配置文件里面。
但为什么我之前修改了 my.cnf 文件不起效？
仔细排查，发现配置里面有 [mysqld] 和[mysqld_safe]，新增的配置文件放的位置不一样也有关系？于是我尝试把配置文件改成这样：
/etc/my.cnf 24L, 467C written
[root@testmysql mysql]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
skip-grant-tables
user=mysql
symbolic-links=0
tmpdir=/tmp
server_id = 2
log-bin = /usr/local/mysql/log/solve-bin.log
master-host = 172.17.210.199
master-user = my
master-pass = 123456
master-port = 3306
master-connect-retry = 60

[mysqld_safe]
log-error=/usr/local/data/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid

修改之后，再次同步，成功了！
mysql
mysql STOP slave;
Query OK, 0 rows affected (0.05 sec)

mysql START slave;
Query OK, 0 rows affected (0.00 sec)

到此，关于“MySQL 搭建主从报错“The server is not configured as slave“怎么解决”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！

正文完