MySQL-master-slave server-id does not take effect sample code (picture)

The database has been set up and all configurations have been completed, but when starting slave ;, an error is reported:

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

It says here that I have not configured it as a slave, but I have clearly configured it.

MySQL version: 5.6.19

Master: master IP: 172.17.210.199

Slave: slave IP: 172.17.206.138
First look at the slave 172.17. my.cnf of 206.138

[root@liuyazhuang ~]# 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 = test
master-pass = 123456
master-port = 3306
master-connect-retry = 60

This is my.cnf of MySQL host 172.17.210.199

[root@liuyazhuang ~]$ 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

I have tried restarting the master and slave many times and still got an error;

Then manually change to Afterwards, the same error is still reported

mysql> CHANGE MASTER TO
MASTER_HOST='172.17.210.199',
MASTER_USER='test',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='masters-bin.000003',
MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

Execute the START slave; command on the slave server to start the slave

mysql> START slave;

Error report:

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

So check the log

1. Check the Mysql error log on SLAVE172.17.206.138, there is this sentence:

141009 6:06:29 [ERROR] Server id not set, will not start slave

It means that the slave's server-id is not set.

That’s strange. I clearly specified the server-id in the configuration file and restarted the mysql service. Doesn’t it work?
Execute the command "show variables like 'server_id';" on the master and slave respectively.

-------View from the slave machinePort

mysql> mysql> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 0     |
+---------------+-------+
1 row in set (0.00 sec)

The naming setting is 2, how can it become 0

-------Check on the host machine

mysql> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

is the same as the setting.

Since the parameter file does not take effect, try setting it in the database command: execute the command on the slave machine 172.17.206.138

mysql > SET GLOBAL server_id=2;

Execute slave start and show slave status on the slave machine 172.17.206.138 again, It worked.

Notice! ! ! Since the "SET GLOBAL server_id=;" command will be lost after the mysql service is restarted, it must be written into the configuration file. But why did the my.cnf file I modified before not work?
After careful investigation, I found that there are [mysqld] and [mysqld_safe] in the configuration. The modified configuration contents were basically placed under [mysqld_safe]. Does it matter if the newly added configuration files are placed in different locations? So I tried to put the modified part in the configuration file under [mysqld], so I changed it to this:

[root@liuyazhuang]# 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 = test
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

As shown in the picture, I changed the content marked in the picture from [mysqld_safe] to [ mysqld]After modifying

##, synchronize again and it is successful!

mysql>
mysql> STOP slave;
Query OK, 0 rows affected (0.05 sec)

mysql> START slave;
Query OK, 0 rows affected (0.00 sec)

The above is the detailed content of MySQL-master-slave server-id does not take effect sample code (picture). For more information, please follow other related articles on the PHP Chinese website!