MySQL: How do you set up master-slave replication in MySQL? (CentOS, RHEL, Fedora)


Before we go into how to set up master-slave replication in MySQL, let us talk about some of the reasons I have set up master-slave replication using MySQL.

1) Offload some of the queries from one server to another and spread the load: One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.

2) Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump, site lags when table locks happen. For some sites, this could mean that site goes down for few secs to minutes. If you have slave, you just take slave out of rotation (should be built into code) and run backups off the slave. You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.

Ok let us dive into how to setup master-slave replication under MySQL. There are many configuration changes you can do to optimize your MySQL set up. I will just touch on very basic ones to get the replication to work. Here are some assumptions:

Master server ip: 10.0.0.1
Slave server ip: 10.0.0.2
Slave username: slaveuser
Slave pw: slavepw
Your data directory is: /usr/local/mysql/var/

Put the following in your master my.cnf file under [mysqld] section:

# changes made to do master
server-id = 1
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
log-bin = /usr/local/mysql/var/mysql-bin
# end master

Copy the following to slave’s my.cnf under [mysqld] section:

# changes made to do slave
server-id = 2
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
# end slave setup

Create user on master:
mysql> grant replication slave on *.* to slaveuser@'10.0.0.2' identified by 'slavepw';

Do a dump of data to move to slave
mysqldump -u root --all-databases --single-transaction --master-data=1 > masterdump.sql

import dump on slave
mysql < masterdump.sql

After dump is imported go in to mysql client by typing mysql. Let us tell the slave which master to connect to and what login/password to use:
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='slaveuser', MASTER_PASSWORD='slavepw';

Let us start the slave:
mysql> start slave;

You can check the status of the slave by typing
mysql> show slave status\G

The last row tells you how many seconds its behind the master. Don’t worry if it doesn’t say 0, the number should be going down over time until it catches up with master (at that time it will show Seconds_Behind_Master: 0) If it shows NULL, it could be that slave is not started (you can start by typing: start slave) or it could be that it ran into an error (shows up in Last_errno: and Last_error under show slave status\G).

Related posts:

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.


32 Responses to “ MySQL: How do you set up master-slave replication in MySQL? (CentOS, RHEL, Fedora) ”

  1. HC
    February 1st, 2008 | 2:38 pm

    Thanks. Will give it a try. I would be very helpful if you could also explain what the configuration options mean/do.
    Regards,
    HC

  2. Andrew
    February 3rd, 2008 | 8:22 pm

    nice entry, was wondering if you can answer this question. you mentioned that doing master/slave replication, you can have insert in master, and select in slave. does it do that automatically by following your tutorial or these’s something else needs to be done? and can i tell which goes to which?

  3. February 4th, 2008 | 2:24 pm

    You would do that in code. Just remember that after you do insert, it might take a sec or two to show up on slave. Best thing to do is to do a insert, and leave user on master for a while. Amount of time you leave user on master depends on what kind of setup you have ie. using innodb vs myisam, how many inserts are happening, etc.

  4. Andrew
    February 5th, 2008 | 7:16 am

    I have a lot more to learn, interesting.

  5. February 29th, 2008 | 1:30 pm

    [...] document.write(“”); } )() Setting up master/master replication in MySQL is very similar to how we set up master/slave replication. You can read up about how to setup master/slave replication in my previous post: How to set up [...]

  6. Michal
    March 5th, 2008 | 1:54 am

    Hi, i set last day the master/slave replication – it works! The next step – i would like to replicate different DBs from more masters to one slave. Is it possible?

  7. Robert
    March 24th, 2008 | 3:57 pm

    I wish to have two slaves. Is this possible by using the same config but with “server-id = 3″?

  8. March 27th, 2008 | 10:26 pm

    Yes. Just follow the same thing you use to setup first slave to setup n numbers of slaves. Just keep incrementing the id.

  9. April 4th, 2008 | 1:51 am

    if table locks are a problem try converting your tables from MyISAM to InnoDB and and set innodb_table_locks off in your my.cnf if you don’t have access to that I think you can change the setting with SET GLOBAL innodb_table_locks OFF.

    one of the superior features of InnoDB is that it can do row level locking rather than the table level.

  10. homebrew
    April 7th, 2008 | 1:18 pm

    I don’t have a MY.CNF I have a MY.INI though and it looks like that’s the place to edit ??
    I’ve seen various instructions about editing these files, and they’re all different !! So I can’t figure out what’s right.

  11. ultrabill
    April 8th, 2008 | 6:08 am

    homebrew > this method is ok.

    I used it with two servers : master on linux, slave on windows 2K3

    With my.ini, be carreful about the paths. I.e.:
    relay-log = “X:/mysql/mysql-relay-bin”

    Yes, use slashes like Unix (not backslashes as usual with windows) ans put your path under quotes.

  12. April 9th, 2008 | 9:15 am

    Looks like my.ini is a Windows specific (probably why I didn’t know about it) thing from the link below it looks like on windows mysql will accept configuration options from either a my.ini or a my.cnf (neither MUST exist) and they may be located in a number of places with different effects, either global or user specific.

    From a brief read of the page it seems like there is no difference between a my.ini or a my.cnf and the same options can be placed in both including the one I mentioned previously.

    I have in my ‘my.cnf’:
    innodb_table_locks = 0

    http://dev.mysql.com/doc/refman/5.0/en/option-files.html

    Also there are loads more fun InnoDB options to play with here:

    http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html

  13. Norberto
    April 11th, 2008 | 7:37 am

    Hello. Is it possible to replicate using mysql server version 3.x as master and mysql server version 5 as a replication host? Thanks in advance. Norberto

  14. Elektron
    April 13th, 2008 | 3:46 pm

    Hi,

    I was wondering if the following scenario would be possible.

    Server 1: Database X (master)
    Server 2: Database Y (master)

    - replicating to -

    Server 3: Database X, Database Y (slave)

    The databases are independent, each master:slave relationship is still 1:1. However, as the slave server only seems to accept configuration details for one master server I can’t figure out if this is possible.

    Does anyone have any experience of this?

  15. April 13th, 2008 | 11:54 pm

    Electron,

    You can run 2 instances on server 3 to accomplish this.

  16. April 17th, 2008 | 9:36 am

    [...] You can learn setting up two type of replication MySQL offers here:  master-master replication and master-slave replication. Once you have replication set up, you can start playing with it without doing any more changes to [...]

  17. claudia
    May 23rd, 2008 | 5:44 am

    I would like to know in case that the master does not respond and if for that reason i want to use the slave as the master (until the original master server is alive again) what steps should i do for allowing this

  18. July 16th, 2008 | 8:03 am

    Slave Replication Errors. Can you tell me what is wrong?

    [root@dhi015 mysql]# tail mysql.err
    080716 8:24:30 InnoDB: Started; log sequence number 0 43665
    080716 8:24:30 [Note] /usr/libexec/mysqld: ready for connections.
    Version: ‘5.0.45-log’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Source distribution
    080716 10:57:05 [Note] Slave SQL thread initialized, starting replication in log ‘FIRST’ at position 0, relay log ‘/var/log/mysql/mysql-relay-bin.000001′ position: 4
    080716 10:57:05 [Note] Slave I/O thread: connected to master ‘[email protected]:3306′, replication started in log ‘FIRST’ at position 4
    080716 10:57:05 [ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
    080716 10:57:05 [Note] Slave I/O thread exiting, read up to log ‘FIRST’, position 4
    [root@dhi015 mysql]#

    Thanks – Chris

  19. July 16th, 2008 | 9:35 am

    Chris it’s caused by this: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work

    So in your my.cnf on either master or slave, change the: server-id = # <- to another number.

    Let me know if it works or not.

  20. August 10th, 2008 | 2:26 am

    [quote]
    Hi,

    I was wondering if the following scenario would be possible.

    Server 1: Database X (master)
    Server 2: Database Y (master)

    - replicating to -

    Server 3: Database X, Database Y (slave)

    The databases are independent, each master:slave relationship is still 1:1. However, as the slave server only seems to accept configuration details for one master server I can’t figure out if this is possible.

    Does anyone have any experience of this?
    [/quote]

    Maybe the following is one of the solutions.
    http://www.ziddu.com/download/1886062/mm-single-slave-repl.pdf.html

  21. May 8th, 2009 | 2:07 pm

    [...] Crazytoon – Another article that I referenced [...]

  22. March 11th, 2010 | 2:21 pm

    Hi,

    :)
    Thanks Man…..
    You saved me!
    _^_
    AnoopL

  23. HoHum
    April 21st, 2010 | 7:02 pm

    * Scenerio: Master/multiple slaves
    * Problem: 50GB ibdata1 file
    * Goal: reduce the size of ibdata1
    * Non replication method: dump the master database, drop all dbs, import dump
    * Question: what wil doing this do to the slaves in a replication scenerio? How would you do this and can you explain your answer?

  24. Sundar
    April 27th, 2011 | 3:19 am

    After doing this setup, by default, only Information_schema and test databases are accessible from slave and hence only those two databases are getting replicated onto slave.

  25. Sundar
    April 27th, 2011 | 3:19 am

    Good writeup.

    After doing this setup, by default, only Information_schema and test databases are accessible from slave and hence only those two databases are getting replicated onto slave.

    How to we provide permission for other databases?

    Thanks & Regards
    Sundar

  26. April 27th, 2011 | 11:58 am

    Sundar: check your my.cnf to make sure you are replicating all of the db’s.

  27. Steven
    May 5th, 2011 | 11:39 pm

    Hi there,

    Can anyone help me with this ? I am trying to do replication of Centos 5.6

    when I tried

    mysqldump -u root–all-databases –single-transaction –master-data=1 > masterdump.sql

    It shows

    mysqldump: Got error: 1045: Access denied for user ‘root’@'localhost’ (using password: NO) when trying to connect

    When I tried

    mysqldump -u root -p –all-databases –single-transaction –master-data=1 > masterdump.sql

    It shows

    mysqldump: Error: Binlogging on server not active

    Even, I already added binlogging inside my.cnf file

    THanks You so Much

  28. Steven
    May 6th, 2011 | 2:52 am

    My bad, I fixed. Thanks anyway

  29. Doro
    July 5th, 2011 | 12:24 am

    Thanks for this, man. I will definitely give it a shot.

  30. January 7th, 2012 | 10:30 pm

    [...] I’m skimming through how to set up a basic master/slave replication in mysql. http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel... [...]

  31. January 13th, 2012 | 8:01 am
  32. February 14th, 2012 | 7:10 pm

Leave a reply

*
To prove that you're not a bot, enter this code
Anti-Spam Image