How to Setup MySQL Replication

Create a MySQL user on the master which the slave will connect as. The user requires only SLAVE REPLICATION permissions. I used phpmyadmin for this and named my user slave.

Configure a server-id and binary logging on the MASTER in /etc/mysql/my.cnf:

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

Configure the server to flush writes to disk in real time for greater reliability:

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Restart MySQL:

/etc/init.d/mysql restart

Connect to MySQL on the MASTER and issue the following commands to delete any existing binary logs and then lock your tables:

mysql -h localhost -u username -p

reset master;
FLUSH TABLES WITH READ LOCK;

Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect.

Now use the SHOW MASTER STATUS statement to determine the current binary log name and offset on the master:

SHOW MASTER STATUS;

You should see output something like below:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 |              |                  |
+------------------+----------+--------------+------------------+

The File column shows the name of the log and Position shows the offset within the file. Record these values. You need them later when you are setting up the slave. They represent the replication coordinates at which the slave should begin processing new updates from the master.

If the master has been running previously without binary logging enabled, the log name and position values displayed by SHOW MASTER STATUS or mysqldump –master-data will be empty. In that case, the values that you need to use later when specifying the slave's log file and position are the empty string ('') and 4.

If you have existing data that needs be to synchronized with the slave before you start replication, leave the client running so that the lock remains in place and then proceed to transfer a dump of your databases as described below.

Connect to MySQL on the MASTER with a new shell and use mysqldump to create a dump of all databases:

mysqldump -h localhost -u username -p --all-databases --lock-all-tables > ~/dump.db

Configure a server-id and binary logging on the slave in /etc/mysql/my.cnf:

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

Configure the server to flush writes to disk in real time for greater reliability:

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Note that while binary logging is not required on the slave it may be useful for backup or in the event you need to recover from a failure.

Restart MySQL:

/etc/init.d/mysql restart

Import your data dump:

mysql -h localhost -u username -p < ~/dump.db

To set up the slave to communicate with the master for replication, you must tell the slave the necessary connection information. To do this, execute the following statement on the slave, replacing the option values with the actual values relevant to your system:

reset master;
mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

PS) I added reset master in case binary logging had been enabled on the slave previously. This will clear any existing binary logs.

Now start the slave:

START SLAVE;

After you have performed this procedure, the slave should connect to the master and catch up on any updates that have occurred since the snapshot was taken. You also find error messages in the slave's error log if it is not able to replicate for any reason.

Once you have a snapshot of the master, you can use it to set up other slaves by following the slave portion of the procedure just described. You do not need to take another snapshot of the master; you can use the same one for each slave.

To check the status of replication connect to MySQL on the SLAVE and issue the following command:

mysql -h localhost -u username -p

SHOW SLAVE STATUS\G

To pause and then restart replication in a slave:

STOP SLAVE;
START SLAVE;