mysql 主从 server id_MySQL 5.6.19主从server-id不生效，The server is not configured as slave

2014.10.10

在网上看过至少20余篇的MySQL主从安装文档，我觉得MySQL主从其实很简单。于是欣然的决定自己搭建一个，可终究还是出错了，在start 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';”。

-------从机上面查看端口

mysql> mysql> SHOW VARIABLES LIKE 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id     | 0     |

+---------------+-------+

1 row in set (0.00 sec)

我就纳闷呢，本来设置的是2，此时却是0，

-------主机上面查看

mysql> SHOW VARIABLES LIKE 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id     | 1     |

+---------------+-------+

1 row in set (0.00 sec)

跟设置的一样。

既然参数文件不生效，就试试在数据库命令里面设置：

在从机 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)