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

今晚给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'

然后在182.150上再尝试一下之前配置同步的步骤

slave stop;slave start;

成功了~

至此,所有问题得以解决。

最后应该还有个疑问,my.cnf里面的[mysqld]和[mysqld_safe]有什么区别?

链接来了~~

http://ronaldbradford.com/blog/be-sure-to-know-your-my-cnf-sections-2010-01-26/


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值