I am trying to do Master Slave Replication for MySQL. When i am typing the following command:
CHANGE MASTER TO MASTER_HOST='10.1.100.1', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.0开发者_高级运维00001', MASTER_LOG_POS=451228;
mysql> START SLAVE;
it throws the following error:
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
Any help would be greatly appreciated.
TRY TO RESET IT, IT DOES MAGIC! ON SLAVE THE SLAVE MYSQL COMMAND TYPE:
RESET SLAVE;
THEN TRY AGAIN:
CHANGE MASTER TO MASTER_HOST='10.1.100.1', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=451228;
mysql> START SLAVE;
Please check several things:
1) Make sure the Master's /etc/my.cnf has server_id actually set
Here is why: Replication relies on the server_id. Whenever a query is executed and is recorded in the master's binary log, the server_id of the master is recorded with it. By default, if a server_id is not defined in /etc/my.cnf, the server_id is defaulted to 1. However, the rules MySQL Replication demand that a server_id be explicitly defined in the master's /etc/my.cnf. In addition, for any given slave, mysqld checks the server_id of the SQL statement as it reads it from the relay log and makes sure it is different from the slave's server_id. That is how MySQL Replication knows it is safe to execute that SQL statement. This rule is necessary in the event Circular (Master-Master,MultiMaster) Replication is implemented.
use select @@server_id;
in sql command line to check config really on server.
2) Make sure the Slave's /etc/my.cnf has server_id actually set
Here is why: Same reason as in #1
3) Make sure the server_id in the Master's /etc/my.cnf is different from the server_id in the Slave's /etc/my.cnf
Here is why: Same reason as in #1
As a side note : If you setup multiple slaves, please make sure each slave has a different server_id from its master and its sibling slaves.
Here is why : Example
A master with 2 slaves
MASTER has server_id 1
SLAVE1 has server_id 2
SLAVE2 has server_id 2
Replication will become agressively sluggish on SLAVE2 because a sibling slave has the same server_id. In fact, it will steadily fall behind, catch a break, process a few SQL statements. This is the master's fault for having one or more slaves with identical server_ids. This is a gotcha that is not really documented anywhere. I've seen this dozens of times in my life time.
I had something very close to that and got same error messages. Replication run fine, mariadb restart -> "cannot open relay log"
Solution from Neo helped in the first place.
But the root cause it seems were to small open file limits.
Try a lsof | wc and increase DefaultLimitNOFILE to 65535 in /etc/systemd/system.conf and /etc/systemd/user.conf
If nothing else helps and you are convinced everything is set correctly you will have to remove this file:
/var/lib/mysql/<relay_logname>-<connection>.info
after that perform the 'CHANGE MASTER' command as stated above
精彩评论