Shulou(Shulou.com)05/31 Report--

This article mainly introduces "MySQL master slave error report" The server is not configured as slave "how to solve". In daily operation, I believe that many people have doubts about how to solve the problem of MySQL master slave error report "The server is not configured as slave". The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods, hoping to help you answer the doubts of "MySQL builder slave error report" The server is not configured as slave "how to solve"! Next, please follow the editor to study!

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

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

MySQL version: 5.6.19

Master: master IP: 172.17.210.199

From: slave IP: 172.17.206.138

First take a look at the my.cnf of the slave 172.17.206.138

[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

# the following is the configuration for adding master and slave

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

This is the my.cnf of the MySQL host 172.17.210.199

[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

Tried to restart the master and slave many times or reported an error

After manual chang to, the error is still reported.

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

-slightly

So check the log.

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

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

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

That's strange, I clearly specified server-id in the configuration file, and there is a restart of the mysql service, does it not work?

Execute the command "show variables like 'server_id';" on the master and slave, respectively.

-View the port from above

Mysql > mysql > SHOW VARIABLES LIKE 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 0 | |

+-+ +

1 row in set (0.00 sec)

I wondered, it was set to 2, but now it is 0.

-check on the mainframe.

Mysql > SHOW VARIABLES LIKE 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 1 | |

+-+ +

1 row in set (0.00 sec)

It's the same as set.

Since the parameter file does not take effect, try setting it in the database command:

Execute the command on the slave 172.17.206.138

Mysql > SET GLOBAL server_id=2

Execute slave start and show slave status again on the slave 172.17.206.138, which is a success.

Attention! Since the "SET GLOBAL server_id=;" command is lost after the mysql service is restarted, be sure to write it to the configuration file.

But why didn't it work when I modified the my.cnf file before?

After careful inspection, it is found that there are [mysqld] and [mysqld_safe] in the configuration. Does it matter that the new configuration files are placed in different locations? So I tried to change the configuration file like this:

"/ 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

After the modification, synchronize again, it is a success!

Mysql >

Mysql > STOP slave

Query OK, 0 rows affected (0.05 sec)

Mysql > START slave

Query OK, 0 rows affected (0.00 sec)

At this point, the study on "MySQL builder reports an error" The server is not configured as slave "how to solve" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.