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';”。

在從機 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)

MySQL 5.6.19主從server-id不生效，The server is not configured as slave

相關文章