Setting up MySQL replication is an easy process to do. First you need to edit your my.cnf files on your two servers and add the following:

Server 1

server-id=1
auto_increment_offset=1
auto_increment_increment=3
log-bin=mysql_log

Server 2

server-id=2
auto_increment_offset=1
auto_increment_increment=3
log-bin=mysql_log

Make sure that the server id’s in the my.cnf file are unique for each server and the auto_increment_increment is n+1 more than the total amount of servers in your environment. This way your slave servers will update correctly. Once that is complete, restart the MySQL Service

service mysqld restart

To configure your slave user, log into the master and type the following:

mysql> create user slaveuser@’slavehost.example.com’ identified by ‘somepassword’;
mysql> grant replication slave on *.* to slaveuser@’slavehost.example.com’
mysql> flush privileges;

The next step is to dump the database from your primary server and import it on the slave server. To dump the database:

mysqldump -u root -p –lock-tables database > database.sql

Import the database on the slave server:

mysql -u root -p database < database.sql

We need to get the log file and position information from the master server in order to sync it with the slave. First lock the tables so no changes can be made and then show the status.

mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
+——————+————–+———————-+—————————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+————–+———————-+—————————+
| mysql_log.000006 | 156005305 | | |
+——————+————–+———————-+—————————+
1 row in set (0.00 sec)

Configure the slave server to attach to the master with the correct credentials. Note the MASTER_LOG_FILE and the MASTER_LOG_POS information need to be identical as the master.

mysql> CHANGE MASTER TO
-> MASTER_HOST=’masterhost.example.com’,
-> MASTER_USER=’slaveuser’,
-> MASTER_PASSWORD=’somepassword’,
-> MASTER_LOG_FILE=’mysql-bin.000006′,
-> MASTER_LOG_POS=156005305;

Next start the replication

mysql> START SLAVE;

Unlock the tables on the master

mysql> UNLOCK TABLES;

Check to make sure that it is running properly

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: masterhost.example.com
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_log.000006
Read_Master_Log_Pos: 156005305
Relay_Log_File: mysqld-relay-bin.000146
Relay_Log_Pos: 107097880
Relay_Master_Log_File: mysql_log.000006
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: 155994592
Relay_Log_Space: 107097880
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
1 row in set (0.00 sec)

To set up a master-master replication, repeat the process on the second MySQL server.

That’s it!