CODEWITHSUNDEEP
mysql
user619684
user619684

Reputation: 581

Error: "could not initailize master info structure" while doing Master Slave Replication in MySQL

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.000001', 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.

Upvotes: 48

Views: 73336

Answers (5)

toni sukmana
toni sukmana

Reputation: 1

RESET SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

start slave;

Upvotes: 0

E de Groot
E de Groot

Reputation: 11

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

Upvotes: 1

Walter
Walter

Reputation: 11

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

Upvotes: 1

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44373

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.

Upvotes: 6

Neo
Neo

Reputation: 11587

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;

Upvotes: 199

Related Questions