导航：首页 > 数据库 >

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

发表于：2024-11-22 作者：千家信息网编辑

千家信息网最后更新 2024年11月22日，这篇文章主要介绍"MySQL搭建主从报错"The server is not configured as slave"怎么解决"，在日常操作中，相信很多人在MySQL搭建主从报错"The server

千家信息网最后更新 2024年11月22日MySQL搭建主从报错“The server is not configured as slave“怎么解决

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

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