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
[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.
[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.
