The master (production server) needs to have binary logging turned on to facilitate replication. To turn on binary logging, we need to take the following steps:

• Using phpMyAdmin, add a new user called replication_user which only has REPLICATION SLAVE access rights

• Add the following lines to the /etc/my.cnf file:

[mysqld]
log-bin=mysql-bin
server-id=1

• Restart mysql (as root)

[user@server]# service mysqld restart

• Stop the production server, and obtain its state information. Most importantly, note the File and Position values:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 240      | test         | manual,mysql     |
+---------------+----------+--------------+------------------+

• While the database has the READ LOCK applied, export the data using mysqldump, including master data information:

mysqldump --all-databases --master-data > dbdump.db

• Then unlock the master database:

mysql> UNLOCK TABLES;

To initialise the slave server, the following steps need to be taken:

• Add the following to the slave servers config file /etc/my.cnf:

# Replication settings
log-bin
replicate-ignore-db=mysql
relay-log=triton-relay-bin
log-slave-updates
log-warnings
server-id=2
# Slave details
report-host=slave.mydomain.com  # YOUR SLAVE'S HOSTNAME

# Master Host details
master-host=master.mydomain.com  # YOUR MASTER'S HOSTNAME
master-user=replication_user
master-password=mypasswordhere   # YOUR REPLICATION USERS PASSWORD
master-port=3306

• Transfer the data (dbdump.db) file from Master and load it into the database:

shell> mysql < fulldb.dump

• Set the file information – this needs to match the values you observed on the master when you locked the tables above. In this example the filename was mysql-bin.003 and the log position was 240:

mysql> change master to
MASTER_LOG_FILE='mysql-bin.003',
MASTER_LOG_POS=240;

• Start the slave:

mysql> start slave;

It’s important to check that replication is working once you set it up. Some information can be gathered from the mysql log (usually found in /var/log/mysqld.log), however the following steps are more conslusive:

• On the production server, issue the following command to see which slaves are connected:

mysql> show slave hosts;
+-----------+------------------------------+------+-------------------+-----------+
| Server_id | Host                         | Port | Rpl_recovery_rank | Master_id |
+-----------+------------------------------+------+-------------------+-----------+
|         2 | slave.mydomain.com           | 3306 |                 0 |         1 |
+-----------+------------------------------+------+-------------------+-----------+
1 row in set (0.02 sec)

• On the backup server, issue the following command to see the slave status:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: master.mydomain.com
                Master_User: replication_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 525088794
             Relay_Log_File: slave-relay-bin.000004
              Relay_Log_Pos: 250751844
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB: mysql
         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: 525088794
            Relay_Log_Space: 250751844
            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

More Information

More information can be found online at the Mysql documentation Replication Guide.