mysql配置文件不生效以及配置同步复制报错“The server is not configured as slave”解决办法...

最新推荐文章于 2022-12-15 16:43:03 发布

weixin_34150830

最新推荐文章于 2022-12-15 16:43:03 发布

阅读量908

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/weixin_34150830/article/details/85083817

版权

今晚给2台mysql数据库配置主从同步，因为驾轻就熟，所以很快就配置到最后一步了，谁知道执行最后一个命令“slave start”时给我来了个报错“ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO”。

我就纳闷了，我明明配置了的，为什么说我没配置呢？

首先环境大致是这样的：

MYSQL MASTER:192.168.182.149

MYSQL SLAVE:192.168.182.150

排查开始：

-----------------先还原配置过程----------------------

1、182.149上的配置：

1）在my.cnf添加了以下内容（加粗）

[root@YMngSrv01 doc]# cat /etc/my.cnf

[mysqld]

event_scheduler=1

innodb_rollback_on_timeout=1

innodb_lock_wait_timeout=600

max_connections=350

log-bin=mysql-bin

binlog-format='ROW'

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

event_scheduler=1

innodb_rollback_on_timeout=1

innodb_lock_wait_timeout=600

max_connections=350

log-bin=mysql-bin

binlog-format='ROW'

server-id=149

binlog-do-db=cloud;cloud_usage;cloudbridge;internetwarecloud

binlog-ignore-db=mysql

2）创建同步帐号

GRANT REPLICATION SLAVE ON *.* TO'bona'@'%' IDENTIFIED BY 'password';GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO'bona'@'%' IDENTIFIED BY 'password';

3）重启mysql

2、182.150上的配置：

1）在my.cnf添加了以下内容（加粗）

[root@YMngSrv02 ~]# cat /etc/my.cnf
[mysqld]
event_scheduler=1
innodb_rollback_on_timeout=1
innodb_lock_wait_timeout=600
max_connections=350
log-bin=mysql-bin
binlog-format='ROW'
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
event_scheduler=1
innodb_rollback_on_timeout=1
innodb_lock_wait_timeout=600
max_connections=350
log-bin=mysql-bin
binlog-format='ROW'

server-id=150
replicate-do-db=cloud;cloud_usage;cloudbridge;internetwarecloud
replicate-ignore-db=mysql

2）重启mysql

3、在182.149上查看并记录File 和Position的值

4、在182.150上执行

1）slave stop;

2）change master to master_host='192.168.182.149', master_user='bona',master_password='password',MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=106;

3）slave start;

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

#开始报错了~

--------------------开始排错-------------------------------

1.查看SLAVE182.150上的Mysql报错日志，有这么一句：

140527 22:56:24 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: 1593

大概意思是，master和slave的server-id重复了。

那就奇怪了，我明明在配置文件里面指定了server-id的了，并且有重启mysql服务，难道不起效？

分别在182.149和182.150上执行命令“show variables like 'server_id';”，竟然server_id都是1。

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)

在182.149上执行命令“SET GLOBAL server_id=149;”，在182.150上执行命令“SET GLOBAL server_id=150;”

再次在182.150上执行slave start和show slave status，成功了。

但高兴的有点早了，因为“SET GLOBAL server_id=;”命令会在mysql服务重启后丢失，所以一定要写到配置文件里面。

所以剩下的问题就是，为什么修改了my.cnf文件不起效？回头检查配置文件有没有错误。

仔细排查，发现配置里面有[mysqld]和[mysqld_safe]，新增的配置文件放的位置不一样也有关系？于是我尝试把配置文件改成这样再尝试一下

182.149上：

[mysqld]
event_scheduler=1
innodb_rollback_on_timeout=1
innodb_lock_wait_timeout=600
max_connections=350
log-bin=mysql-bin
binlog-format='ROW'
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=149
binlog-do-db=cloud;cloud_usage;cloudbridge;internetwarecloud
binlog-ignore-db=mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
event_scheduler=1
innodb_rollback_on_timeout=1
innodb_lock_wait_timeout=600
max_connections=350
log-bin=mysql-bin
binlog-format='ROW'

182.150上：

[mysqld]
event_scheduler=1
innodb_rollback_on_timeout=1
innodb_lock_wait_timeout=600
max_connections=350
log-bin=mysql-bin
binlog-format='ROW'
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=150
replicate-do-db=cloud;cloud_usage;cloudbridge;internetwarecloud
replicate-ignore-db=mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
event_scheduler=1
innodb_rollback_on_timeout=1
innodb_lock_wait_timeout=600
max_connections=350
log-bin=mysql-bin
binlog-format='ROW'