As a complete solutions provider for our clients we have extensive experience hosting the websites we build for these clients. Ranging from shared webservers, over cloud and dedicated servers, to multi server setups to provide the maximum performance and reliability. While preparing the setup for a new client I thought I’d start a series on how to setup some of the more complex systems. Starting today with mySQL replication.

We have two servers, both should end up containing the same, in sync database. The first one will be hcs01.kunstmaan.be and will be the master database, hcs02.kunstmaan.be will be the slave database.

Let’s start by installing the mySQL database server. Open a terminal to both servers and become root (I consider these commands to be common knowledge so i won’t go into detail here) and run:

apt-get install mysql-server

While replicating, these two servers need to stay in contact to each other. In cases such as these you don’t want to rely on a DNS server, so you can either use an IP address or add the hostnames to your /etc/hosts file. Since I cannot remember IP address, hostnames it is. So add the two servers to the /etc/hosts file on both servers. My lines would look like:

78.46.128.114  hcs01
78.46.128.115  hcs02

On Ubuntu mySQL is configured to only listen on 127.0.0.1. If our slave database wants to connect to the master, we need to change this in the master config. Open /etc/mysql/my.cnf and find the bind-address statement. Comment it out so it looks like:

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
#

In this same file we will activate the replication options. The server-id statement is an unique number assigned to each server. It defaults to 0 and it will not allow replication it it is. So the we change the master server-id to 1. The log_bin statement lists the file the server will log into. This log is used by the slave to replicate it’s content. We will expire this log in 10 days or if it is larger than 100MB. It should look like:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M

Restart the mySQL server to load the changes.

service mysql restart

Last step on the master, we will need a replication user. Connect to your mySQL server and create this user and add the REPLICATION SLAVE permissions to it.

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;

Run the following command to show the status:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      627 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Up next, the slave.

Edit the /etc/my.cnf and set the server-id statement. We are using 2, different from 0 and the one (1) we used for the master server. The section should look like:

server-id=2
master-connect-retry=60

Restart the mySQL server to load the changes.

service mysql restart

Now, connect to the mySQL server and let link it to our master server. The values are the user we created before, and the info shown when we ran “show master status;”

mysql> CHANGE MASTER TO MASTER_HOST='hcs01', MASTER_USER='repl', MASTER_PASSWORD='slavepassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=627;
mysql> START SLAVE;
mysql> SLAVE STATUS \G

You will see something like:

************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: hcs01
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 627
               Relay_Log_File: hcs02-relay-bin.000002
                Relay_Log_Pos: 772
        Relay_Master_Log_File: mysql-bin.000001
             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: 627
              Relay_Log_Space: 927
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

Your setup is working if Slave_IO_Running and Slave_SQL_Running are both Yes. If you create a new database, table or row in the master, it will appear within seconds on the slave.

We’re always looking to improve our setup, so if you implemented this on your servers and have valuable insights to share, please do so in the comments!