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.
Primary server
Edit MySQL config file /etc/my.cnf
[code]
server-id=1
log_bin=/var/log/mysql/mysql-bin.log
[/code]
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.
[code]service mysql restart[/code]
Now we should grant privileges on slave user which will be used for replcation.
[code]mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘pass’;
FLUSH PRIVILEGES;
[/code]
Now lock and dump DB to the file to transfer it to the second server.
[code]
FLUSH TABLES READ LOCK;
SHOW MASTER STATUS;
[/code]
Save “Position” field.
If you do something in this window database will automatic unlock,
you should open new window and do it there:
[code]mysqldump -u root -p -f db1 > db1.sql[/code]
db1 – name of database, that you want to dump
Unlock database.
[code]UNLOCK TABLES;[/code]
Now it’s time to configure secondary server.
Secondary server
[code]CREATE DATABASE db1;[/code]
Import dump from first server:
[code]mysql -u root -p -f db1 < db1.sql[/code]
Edit MySQL config file /etc/my.cnf
[code]server-id=2
relay-log=/var/log/mysql/mysql-relay-bin.log
log_bin=/var/log/mysql/mysql-bin.log
[/code]
Restart MySQL:
[code]service mysql restart[/code]
Server configured and ready for replication.
To activate replication on slave server execute following command in mysql console:
[code]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;[/code]
Remember “Position” field from “SHOW MASTER STATUS” command and write value in place of XXX.
And start replacation:
[code]START SLAVE;[/code]
Look at status::
[code]SHOW SLAVE STATUS\G[/code]
Slave_IO_State: Waiting for master to send event
To stop replication use:
[code]STOP SLAVE[/code]
PS: MySQL uses TCP port 3306 for communication, so it should be open between them.
