Mysql – Using thesqldump to initialize slave database

backupMySQLmysqldumpreplication

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 ?

Best Answer

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.

Related Solutions

Thesql replication deadlock slave stopped

This query fails on slave: 'insert into archieved(eid,at,rtime,scode) select id,at,avg(rtime),scode from tstatus where date(at)="2011-10-29" group by id'

You report a strange Error_code: 0, but try to run this query on slave? When it run succesfully then run as root:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
SLAVE START;

Mysql – Create a thesql slave database from master database

Going by your question, I will like to review what I believe you did thus far:

You stopped mysql on the Master
You copied Master's /var/lib/mysql to the Slave's /var/lib/mysql
I surmise the binlogs on the Master were copied as well

Look at the Slave's last binlog. From the question, it should be

mydbm1-bin.008524
Filesize 1330529

Believe it or not, you have to do a few things:

1) On the Master, create a replication user like this:

GRANT REPLICATION SLAVE,REPLICATION CLIENT
ON *.* TO replicator@'%'
IDENTIFIED BY 'r3plic4t0R';

2) Make /var/lib/mysql on the Slave owned by mysql user

chown -R mysql:mysql /var/lib/mysql

3) Make sure Master's server_id is explicitly set in my.cnf

[mysqld]
server_id = 1

4) Make sure Slave's server_id is explicitly set in my.cnf

[mysqld]
server_id = 2

5) Startup mysql on the Slave

service mysql start

6) Setup replication by running this on the Slave

CHANGE MASTER TO
MASTER_HOST='IPAddressOfMaster',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mydbm1-bin.008524',
MASTER_LOG_POS=1330529;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

You will see something like this:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            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: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

If Slave_IO_Running and Slave_SQL_Running are both Yes, CONGRATULATIONS !!!

I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with essentially the same steps.

I wanted to add additional posts I made for setting up Circular Replication should you decide to setup the two DB servers as Master/Master

Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology?
May 07, 2012 : Setting Circular Replication in mysql

Best Answer

YOUR ACTUAL QUESTION

Related Solutions

Thesql replication deadlock slave stopped

Mysql – Create a thesql slave database from master database

Related Question