How to add a new MySQL slave

28 thoughts on “How to add a new MySQL slave”

1. When editing the relay-log.info file, it’s not enough to only edit the name of the relay log, but the position should be changed as well (to 0). If you don’t, the slave will start reading at a later point in it’s relay log.

   But better then to edit the file it is to delete them both, and to initialize the slave with “CHANGE MASTER TO”, which values can be read from the master.info file.

2. Stijn van der Ree,
   I read once again MySQL manual pages Replication Relay and Status Files and you have right. Not only name should be changed but position too. On the other hand, I’m sure that I didn’t touch Relay_Log_Pos (second line) in relay-log.info file, only name was changed as I described in Edit relay-log.info file. Hmm, the question is how did I successfully create a new MySQL slave if position was not set to zero? I can only suppose that MySQL slave ignored non existing position in newly created new_slave_name-relay-bin.000001 file.

   Hope that someone can confirm my assumption.
   Cheers!

3. At first I also only changed the filename, and then discovered the slave was missing some data. In my case it did start reading later on, maybe because the position in the relay-log already existed (because enough data was already downloaded from the master). It’s likely that if the position does not exist, it starts at zero.

4. Pingback: How To Set Up Database Replication In MySQL « Narendra Dhami

5. I agree with Stijin. I do this kind of thing all the time, and find it much better to read the position from the stopped slave, and use CHANGE MASTER TO, to initialize the new slave.

   In step one, just do SHOW SLAVE STATUS \G
   and record Relay_Master_Log_File and Exec_Master_Log_Pos

   It’s very very important that it’s those two, and not some of the other fields that look like they might be the master position. Because those define the position on the master it’s actually exectued on the slave.

   Then just use CHANGE MASTER TO. That lets mysql manage it’s master.info and such w/out the chance for messing up those files. The other advantages are that it can be done through the mysql client, instead of mucking around in files on the new slave, and you don’t have to restart mysql on the new slave — assuming you have slave-id set before you start.

6. Thanks for the guide, really helped me along!

   My process was the same but I did a CHANGE MASTER TO as well.

   One thing I had to do after i did the GRANT REPLICATION … line was to do a FLUSH PRIVILEGES on the master as well.

7. hai friends pls help me, how can i solve this problem

   “Slave_IO_State”, “Master_Host”, “Master_User”, “Master_Port”, “Connect_Retry”, “Master_Log_File”,
   “Read_Master_Log_Pos”, “Relay_Log_File”, “Relay_Log_Pos”, “Relay_Master_Log_File”, “Slave_IO_Running”, “Slave_SQL_Running”, “Replicate_Do_DB”, “Replicate_Ignore_DB”, “Replicate_Do_Table”, “Replicate_Ignore_Table”, “Replicate_Wild_Do_Table”, “Replicate_Wild_Ignore_Table”, “Last_Errno”, “Last_Error”, “Skip_Counter”, “Exec_Master_Log_Pos”, “Relay_Log_Space”, “Until_Condition”, “Until_Log_File”, “Until_Log_Pos”, “Master_SSL_Allowed”, “Master_SSL_CA_File”, “Master_SSL_CA_Path”, “Master_SSL_Cert”, “Master_SSL_Cipher”, “Master_SSL_Key”, “Seconds_Behind_Master”, “Master_SSL_Verify_Server_Cert”,
   “Last_IO_Errno”, “Last_IO_Error”, “Last_SQL_Errno”, “Last_SQL_Error”

   “Connecting to master”, “192.168.0.226”, “hyden”, “3306”, “60”, “master-bin.000001”,
   “106”, “C70-relay-bin.000001”, “4”, “master-bin.000001”, “No”, “Yes”, “my_db”, “”, “”, “”, “”, “”, “0”, “”, “0”, “106”, “106”, “None”, “”, “0”, “No”, “”, “”, “”, “”, “”, NULL, “No”,
   “1045”, “error connecting to master ‘hyden@192.168.0.226:3306’ – retry-time: 60 retries: 86400”, “0”, “”

   regards

8. This error is due to privileges and authentication. Here is what I found:

   “Error No. 1045 indicates that the user trying to connect to the master is not properly authenticated. Check if user has been given sufficient privileges at the master server.”

   Hope this short info will give the right direction to solve the problem.

9. Pingback: a bisserl was geht immer » Blog Archive » MySQL-Slave zur Replikation hinzufügen

10. Thanks for the guide. As other people have commented, the stuff about editing files is unnecessary and it’s very easy to discover a chunk of data missing on your new slave, which can waste hours of time if you have a large database.

    If anyone else should stumble across this page, here’s what you need to do differently:

    At step 1, after you have stopped the original slave and used the show slave status\G command, make a note of the Relay_Master_Log_File and Exec_Master_Log_Pos. Ignore step 3 and step 6. At step 9, issue the following commands:

    reset slave;
    
    change master to master_host='[master host]',master_user='[slave username]',master_password='[slave password]',master_log_file='[Relay_Master_Log_File]',master_log_pos=[Exec_Master_Log_Pos];
    
    start slave;
    

    If you can’t remember your slave username or password, then it is on line 4 and 5 in the mysql-master.info file on the original slave.

11. @Dave – Thank you for your comment. I made several MySQL slave cloning and never had any problems. Next time I will try with change master to command. Anyway in step 6 is minor editing of relay-log.info file (beacuse many of parameters stay unchanged), on the other hand with change master to command you will have to type in a lot of parameters to set the slave to the right point of master MySQL database. Nevertheless change master to seems as preferred method.

    ;)

12. Originally I stumbled across this while trying to setup a slave of a slave of a master type setup. Where primary server is:

    Master ---> Slave1 ---> Slave2
    

    I’ve read its more practical to have the slaves replicate directly to the master for single-point of failure. However our network topology requires this for our disaster recover solution.

    So I already have Master to –> slave replication working, however to now replicate the slave1 to new slave2, would I simply follow these instructions and use master.info to reflect my slave1?

    -Furosh

13. @Furosh – This post is about adding a new slave to the existing MySQL replication. In short, Master and Slave1 already exist and how to add Slave2 without stopping Master and Slave1. Here is my simple topology:

     
     Slave1 <--- Master ---> Slave2
    

    So, this instructions for adding a new slave (Slave2) to the replication will not fit your case of daisy chained slaves. You will have configure Slave1 to log to its own binary log any updates that are received from a master server. Here is MySQL documentation for –log-slave-updates option. Slave1 in your case must be both, a master and a slave at the same time.

14. Thanks for your response. I figured this all out after multiple tries of trying to figure out which exact bin-log and position to add to Slave2. The little light finally turned on right before I saw your response and yes I came to the same exact conclusion! I accomplished this by essentially making Slave2 use Slave1 as its master.

    I think your simple topology where Master has 2 slaves is the best approach to have just one single-point of failure however due to our network topology slave1 is currently the only allowed server that can access our DMZ from inside our network. Master is in DMZ, Slave1 is inside our network, and Slave2 is in a separate DMZ for Disaster Recovery. Slave1 has access to both DMZs.

    I realize if Slave1 becomes corrupted for any reason so will Slave2, but until I can have both slaves access our Master, this current setup seemed to be the only solution…
    Thanks for your response!!!

15. Pingback: Start MySQL Replication on a slave | Web Hosting

16. Great Post, it worked FLAWLESSLY!!

17. Great post, thanks!

    I found out that you need to move the ibdata_0.log and ibdata_1.log out of the directory

18. hi,

    I am pretty much new in mysql database replication, in my system there are two 64bit machine, one is master and another is slave, now i need to add another slave into my system and i followed ur instruction step by step but still unsuccessful.

    this is what showed when i applied ‘show slave status \G’

    Slave_IO_State: Connecting to master
    Slave_IO_Running: No
    

    I checked and rechecked all configuration then i found my new system is 32bit machine.

    so,
    is this the main issue??

    i am trying for last two days… :(

    –mahi

19. @Ashqar Mahi – I think that 32bit slave should connect to the 64bit master without any problem (similar to reading HTML served by 64 bit server on 32bit Linux).

    Your error message maybe points to permission problems. Please check is it possible to access master server with mysql command line from new slave server. Something like this on mysql slave server:

    $ mysql -p -u replica -h master.server.com
    

    -p –> password to use when connecting to the server (if omitted, mysql prompts for one)
    -u –> MySQL user name to use when connecting to the server (in this example it’s “replica”)
    -h –> connect to the MySQL server on the given host

20. hi dbunic,

    Thank you for pointing me to the right direction.

    yes, i can not connect to my master server using the replication user from new slave.

    and it will be helpful if you suggest me any way to troubleshoot this issue..

    –mahi