MySQL replication - Master/Slave


MySQL replication enables one database server (we'll call it the Master server) to be replicated to one or more database servers (we'll call those the Slave servers). With MySQL, replication is asynchronous. This means your Slave servers do not need to be connected permanently to receive updates from the Master - for example, you could stop the Slave thread on the Slave server and start it back up at a later time and it would automatically catch itself up to the Master. Pretty cool stuff.


There are many different ways to setup replication. This tutorial will cover a simple setup (single Master server replicating to a single Slave server) that replicates all databases from the Master to the Slave.

Contents

Pre-Requisites

For the sake of this article, we'll assume that your OS is CentOS and the mysql and mysql-server packages have already been installed. We'll also assume you want to configure replication on a brand new set of servers with no data (no databases yet, this is important as any existing data will throw off the replication). Please note that this article could be useful for other flavors of Linux as well. Now let's get to the fun part.

review for the purpose of this article:

  • CentOS -OS installed
  • mysql -installed
  • mysql-devel -installed
  • mysql-sever -installed

Collect IP Information

In this tutorial we're going to configure MySQL to replicate over the private IPs of your cloud server. As such, you'll need to note the private IP of each server.

[user@mysql-master ~]$ /sbin/ifconfig

You'll get output that looks something like this:

eth0      Link encap:Ethernet  HWaddr 40:40:51:B7:A4:2E  
          inet addr:67.23.9.185  Bcast:67.23.9.255  Mask:255.255.255.0
          inet6 addr: fe80::4240:51ff:feb7:a42e/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:28878 errors:0 dropped:0 overruns:0 frame:0
          TX packets:15147 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:37708534 (35.9 MiB)  TX bytes:1129533 (1.0 MiB)

eth1      Link encap:Ethernet  HWaddr 40:40:1A:AF:35:F2  
          inet addr:10.176.41.72  Bcast:10.176.63.255 Mask:255.255.224.0
          inet6 addr: fe80::4240:1aff:feaf:35f2/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:3 errors:0 dropped:0 overruns:0 frame:0
          TX packets:13 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:230 (230.0 b)  TX bytes:762 (762.0 b)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:0 errors:0 dropped:0 overruns:0 frame:0
          TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:0 (0.0 b)  TX bytes:0 (0.0 b)

You want to note the IP that is shown for 'eth1'. The IP address is listed right after 'inet addr:'. In this example, our Master server's private IP is 10.176.41.72. Repeat this on the Slave server and note the private IP there.

Slave:

[user@mysql-slave ~]$ /sbin/ifconfig
eth0      Link encap:Ethernet  HWaddr 40:40:BE:90:EB:1E  
          inet addr:67.23.10.69  Bcast:67.23.10.255  Mask:255.255.255.0
          inet6 addr: fe80::4240:beff:fe90:eb1e/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:29047 errors:0 dropped:0 overruns:0 frame:0
          TX packets:13527 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:37743828 (35.9 MiB)  TX bytes:1473375 (1.4 MiB)

eth1      Link encap:Ethernet  HWaddr 40:40:AE:5B:35:3A  
          inet addr:10.176.41.207  Bcast:10.176.63.255 Mask:255.255.224.0
          inet6 addr: fe80::4240:aeff:fe5b:353a/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:3 errors:0 dropped:0 overruns:0 frame:0
          TX packets:13 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:230 (230.0 b)  TX bytes:762 (762.0 b)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:0 errors:0 dropped:0 overruns:0 frame:0
          TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:0 (0.0 b)  TX bytes:0 (0.0 b)

The IP address for our Slave server in this example is 10.176.41.207. Once you have both private IPs noted somewhere, you're good to start configuring.

Configure your Servers

Master

You'll need to edit the my.cnf file on the Master server to enable binary logging and set the server's id.

[user@mysql-master ~]$ sudo vi /etc/my.cnf

Add these lines under the [mysqld] section:

log-bin=mysql-bin
server-id=1

This will enable binary logging and set the Master's server-id to '1'. Restart MySQL for the changes to take effect.

[user@mysql-master ~]$ sudo /etc/init.d/mysqld restart
  • Master my.cnf configuration done.

Slave

You'll need to edit the my.cnf file on the Slave server to set the server's id to something different than the master.

[user@mysql-slave ~]$ sudo vi /etc/my.cnf

Add these lines under the [mysqld] section:

server-id=2

This assigns the Slave a unique server-id. Now lets restart MySQL so these changs take effect.

[user@mysql-slave ~]$ sudo /etc/init.d/mysqld restart
  • Slave my.cnf configuration done.

Creating a Replication User

The Slave server will connect to the Master via a standard MySQL user and password. This user will need to have been granted the 'REPLICATION SLAVE' privilege. Let's create that user on the Master server now.

[user@mysql-master ~]$ sudo mysql

You should be at the mysql prompt on the Master server. Once there, let's create a new user and grant him the proper privilege.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.176.41.207' IDENTIFIED BY 'slavepass';

This statement is basically saying grant the repl user replication slave access to any database/table on this host from the Slave server's private IP. Now tell the server to reload the grant tables with the statement 'FLUSH PRIVILEGES'.

mysql> FLUSH PRIVILEGES;

At this point, it's a good idea to make sure you're able to access the Master from the Slave using the credentials you just created. Alright, now back to the Slave server.

[user@mysql-slave ~]$ mysql -h 10.176.41.72 -u repl -p'slavepass'

Be sure to use your private IP for your Master server. In this example, mine is 10.176.41.72. Do you get a MySQL prompt? If so, you're successfully logged into your Master server using the new 'repl' user you just created. Alright, we're about half way there.

Grab the Master Information

On the Master server, we'll need to find out the binary log it is currently using and the position it is at in that binary log. If you haven't logged out of MySQL from creating that replication user, you should be at the MySQL prompt already. Go ahead and lock the tables so we can safely grab that data.

  • Log into the master server.
mysql> FLUSH TABLES WITH READ LOCK;

Now we'll use the 'SHOW MASTER STATUS' command to get all the data we need.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      239 |              |                  | 
+------------------+----------+--------------+------------------+

Note the current binary log and position. In our example, the Master server is currently on the mysql-bin.00003 binary log and is at position 239. Now let's remove that lock.

mysql> UNLOCK TABLES;

Final Slave Config

Changing the Master Information on the Slave

Up to this point, you should have collected the following information:

  • Private IPs
  • replication username and password
  • Master server binary log and position.

Now it's time to use this information to connect the Slave server to the Master server. Let's start configuring the Slave.

[user@mysql-slave ~]$ sudo mysql

Once at the mysql prompt, we're going to change the Master information with the 'CHANGE MASTER TO' statement (so the Slave server knows where/how it's connecting).

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.176.41.72',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='slavepass',
    -> MASTER_LOG_FILE='mysql-bin.000003',
    -> MASTER_LOG_POS=239;

Note the values for each field. The MASTER_HOST is the private IP of the Master server, MASTER_USER is the user we created for replication, MASTER_PASSWORD is the password for the replication user, MASTER_LOG_FILE is the binary log that we recorded from the Master server status earlier, and MASTER_LOG_POS is the position the Master was in that we recorded.

Start the Slave Thread

Ok, now start the slave thread on the Slave server.

mysql> START SLAVE;

Lets make sure that replication is working with the 'SHOW SLAVE STATUS' statement.

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.176.41.72
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000003
        Read_Master_Log_Pos: 314
             Relay_Log_File: mysqld-relay-bin.000003
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000003
           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: 314
            Relay_Log_Space: 235
            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

If you see that Slave_IO_State is 'Waiting for master to send event' and Seconds_Behind_Master isn't 'NULL', your replication is working! If it does show NULL, go back and double check your 'CHANGE MASTER TO' statment to make sure all data is correct. That's it!

Rejoice

Wasn't too bad, was it? Now sit back and let your Slave server replicate from the Master. Be sure not to perform any writes to the Slave server as this will break replication! All writes performed on the Master will automatically be sent to the slave via the binary log and replication. For more information on MySQL replication, go here: https://dev.mysql.com/doc/refman/5.0/en/replication.html



Was this content helpful?




© 2014 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License


See license specifics and DISCLAIMER