MySQL Replication configuration example

Database replication will synchronize databases between two different servers.
Should any record change on primary server, it will be automatically sent to secondary (slave) server.

Edit MySQL config file /etc/my.cnf

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

log_bin – specifies the location of MySQL binary log, which is used for replication.

You can limit the binary logging to only one database by specifying parameter binlog_do_db=database .
However this is not recommended, see (dev.mysql.com )

Save changes and restart MySQL server.

service mysql restart

Now we should grant privileges on slave user which will be used for replcation.

mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;

Now lock and dump DB to the file to transfer it to the second server.

FLUSH TABLES READ LOCK;
SHOW MASTER STATUS;

Save “Position” field.

If you do something in this window database will automatic unlock,
you should open new window and do it there:

mysqldump -u root -p -f db1 > db1.sql

db1 – name of database, that you want to dump

Unlock database.

UNLOCK TABLES;

Now it’s time to configure secondary server.

CREATE DATABASE db1;

Import dump from first server:

mysql -u root -p -f db1 < db1.sql

Edit MySQL config file /etc/my.cnf

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

Restart MySQL:

service mysql restart

Server configured and ready for replication.
To activate replication on slave server execute following command in mysql console:

CHANGE MASTER TO
MASTER_HOST='IP-address or primary server',
MASTER_USER='slave_user',
MASTER_PASSWORD='pass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=XXX;

Remember “Position” field from “SHOW MASTER STATUS” command and write value in place of XXX.

And start replacation:

START SLAVE;

Look at status::

SHOW SLAVE STATUS\G

Slave_IO_State: Waiting for master to send event

To stop replication use:

STOP SLAVE

PS: MySQL uses TCP port 3306 for communication, so it should be open between them.