MySQL Server Replication

Recently had cause to check out MySQL's replication capabilities. One of the programmers wanted a wiki for his department's to use. We already used Mediawiki on a Debian box for another department so I thought we could beef up the box with some RAM then add a new wiki to it, change the backup scripts and we would be done. However I got thinking that this might be an opportunity to provide a more resilient set up where the MySQL server running the wiki could replicate to an offsite location. This could then provide a read-only back up to the existing machine in case of failure. I will of course still need to take backups (just in case some Flump deletes the lot).

The following is how I set up replication between a production mediawiki server with the MySQL server installed locally.

It kind of goes without saying that you will need at least two machines each running MySQL, in my set up both machines were Debian 6 (Squeeze).

On the Production machine we will need to to configure MySQL so it knows it is going to be part of a replication group. Edit my.cnf and add or modify the following:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

We also need to get MySQL to bind to the network address of the NIC

bind-address = 0.0.0.0

On the Offsite machine we need to tell it to be a slave:

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log

We need to create an account on the master (production) machine for the slave to be able to log on to the master for replication.

$ mysql -u root -p

mysql>  CREATE USER 'replicationos'@'%' IDENTIFIED BY 'password'; 

Query OK, 0 rows affected (0.10 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicationos'@'%';

Query OK, 0 rows affected (0.00 sec)

We now need to get the Replication master binary log components. If this is done on a production machine then I suggest you do it quickly because you will stop certain transactions from occurring while the tables are locked.

open two sessions to mysql, in the first run:

mysql> FLUSH TABLES WITH READ LOCK;

In the second session run:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |    21381 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.02 sec)

Make a note of the"File" and "Postition":

Exit mysql client in the second session (still leaving the first logged in). Now dump the data out ready to be imported by the slave with:

$ mysqldump -p --all-databases --lock-all-tables >/root/dbdump.db

Copy the dbdump.db to the slave machine with scp then on the slave machine start mysql with the --skip-slave-start option:

mysqld --skip-slave-start

Import the data with:

mysql -p < /root/dbdump.db

Now we need to tell the slave machine about the master using the "File" and "Position" we noted in a earlier step with the following command:

mysql> CHANGE MASTER TO MASTER_HOST='wiki.company.com', MASTER_USER='replicationos', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=21381;
Now start the slave process:
mysql> start slave;


Wait for a few moments then check the status of replication with the following:

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: wiki.company.com
                  Master_User: replicationos
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000027
          Read_Master_Log_Pos: 37742
               Relay_Log_File: mysqld-relay-bin.000511
                Relay_Log_Pos: 7333
        Relay_Master_Log_File: mysql-bin.000027
             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: 37742
              Relay_Log_Space: 7489
              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)

The \G in the "SHOW SLAVE STATUS\G" is short hand for "ego" in the MySQL client and it means "Send command to mysql server, display result vertically." Check that the "Slave_IO_State" is "Waiting for master to send event" and that "Seconds_Behind_Master" is 0 and that means that your slave is synchronised with your server (unless you have only just lost network connection to the master).