MySql database replication (master/slave)

In this tutorial we'll create a simple one-way master/slave database replication. You must have at least one master and one slave but you can use multiple slaves.

Master

1. Configure master to listen on all ip addresses (pico /etc/mysql/my.cnf)

#bind-address = 127.0.0.1


Comment out this line or remove it

2. Configure server id, log file location and which databases are allowed to be replicated (pico /etc/mysql/my.cnf)

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = {database}


Replace {database} with the one you would like to replicate

3. Restart MySql

/etc/init.d/mysql restart


4. Create a user and allow it to act as slave for this server (mysql -u root -p)

GRANT REPLICATION SLAVE ON *.* TO {username}@'{ip}' IDENTIFIED BY '{password}';
FLUSH PRIVILEGES;


{username} = Your preferred username
{password} = Your password
{ip} = IP address of the slave system or % to allow all ip addresses

5. Show current log file and position (mysql -u root -p)

SHOW MASTER STATUS;


This will return something like this:

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 2751 | {database} | |
+------------------+----------+--------------+------------------+


Keep the file name and position. It will be used later on the slave

6. Transfer data from the master to the slave

You can do this using various methods including exporting and importing using phpMyAdmin, creating a database dump from the master and import to the slave and "LOAD DATA FROM MASTER".

Slave

1. Configure this server to be a slave for the master MySql server (pico /etc/mysql/my.cnf)

server-id = 2
master-host = {master_ip}
master-user = {username}
master-password = {password}
master-connect-retry = 60
replicate-do-db = {database}


{master_ip} = The ip of the master server
{username} = The username you provided earlier on the master server
{password} = The password you provided earlier on the master server
{database} = The database you want to replicate

2. Restart MySql

/etc/init.d/mysql restart


3. Final configurations to make the slave replicate with the master (mysql -u root -p)

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='{master_ip}', MASTER_USER='{username}', MASTER_PASSWORD='{password}', MASTER_LOG_FILE='{log_file}', MASTER_LOG_POS={log_position};
SLAVE START;


{master_ip} = The ip of the master server
{username} = The username you provided earlier on the master server
{password} = The password you provided earlier on the master server
{log_file} = Log file name from the master (ex. mysql-bin.000004)
{log_position} = Log position from the master (ex. 2751)