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)