Using mysqldump to initialize slave database

Question

I've facing an issue I don't really understand.

I wan't to synchronize a slave MariaDB database to a master one.

I try to use the mysqldump option --master-data=1 which is adding a "CHANGE MASTER TO" in the output file to set the bin log file and position for the slave. I'm assuming (as per the doc) than it should be enough for the slave to know where to start. When I import the file to the slave and start slave, the slave is desynchronized

If I do a "FLUSH TABLES WITH READ LOCK;", SHOW MASTER STATUS etc then at the end manually do a "CHANGE MASTER TO", all if fine and synchronized

I would assume the --master-data=1 would generate the same bin log information as the manual way, but it is not.

Where am I going wrong ?

Answer 1

When setting up replication on a new slave for the first time, you have to explicitly give it all the information it needs:

Suppose you have

• Master Host is 10.20.30.40
• replication user is 'repuser'
• replication password is 'reppass'

First, create the replication user on the Master like this:

CREATE USER 'repuser'@'%' IDENTIFIED BY 'reppass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Next, enable replication on the Slave like this:

CHANGE MASTER TO
master_host='10.20.30.40',
master_port=3306,
master_user='repuser',
master_password='reppass',
master_log_file='dummy',
master_log_pos=1;

Now, load the mysqldump into the Slave.

Line 23-25 of the dump file has the CHANGE MASTER TO command, but it specifies log file and position only. This will set those values on the Slave.

Finally, run these on the Slave

START SLAVE;
DO SLEEP(10);
SHOW SLAVE STATUS\G

Replication should now be running. You can verify it as running when you see

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master:

Keep running SHOW SLAVE STATUS\G on the Slave until Seconds_Behind_Master is 0

YOUR ACTUAL QUESTION

You initially said

I try to use the mysqldump option --master-data=1 which is adding a "CHANGE MASTER TO" in the output file to set the bin log file and position for the slave. I'm assuming (as per the doc) than it should be enough for the slave to know where to start.

To reiterate what I said earlier

Line 23-25 of the dump file has the CHANGE MASTER TO command, but it specifies log file and position only. This will set those values on the Slave.

To clarify, when a Slave is initialized with all 6 options for the first time, then you can give it a CHANGE MASTER TO command with just master_log_file and master_log_pos. Using --master-data does not supply host, port, username and password. Even with MySQL 5.7, CHANGE MASTER TO supplies master_host and master_port. Still, username and password are not.