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

36 Replies

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 [email protected]'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.

36 thoughts on “MySQL: How do you set up master-slave replication in MySQL? (CentOS, RHEL, Fedora)”

  1. HC

    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

    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. CrazyToon Post author

    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.

  5. Pingback: MySQL: How do you set up master/master replication in MySQL? (CentOS, RHEL, Fedora) | Technology: Learn and Share

  6. Michal

    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

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

  9. Bestie

    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

    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

    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. Bestie

    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

    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

    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?

  16. Pingback: MySQL Replication Series: How does MySQL replication works? | Technology: Learn and Share

  17. claudia

    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. Chris Chandler

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

    [[email protected] 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
    [[email protected] mysql]#

    Thanks – Chris

  19. Sunny Walia Post author

    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. imsoft

    [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. Pingback: HyperDB Explained Part 2 « life by way of media

  23. HoHum

    * 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

    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

    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

  27. Steven

    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

  30. Pingback: Where can I find documentation on my.cnf directives? - Admins Goodies

  31. Pingback: moeblog » Mysql master-slave

  32. Pingback: Setting up Replication for MySQL data « KaixersofT { ScriptBlocK } Weblog

  33. 95Mabel

    Hello admin, i must say you have hi quality content here. Your page can go viral.
    You need initial traffic boost only. How to get it?
    Search for; Mertiso’s tips go viral

Leave a Reply

Your email address will not be published. Required fields are marked *