This tutorial describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help in othe situations
- protect against hardware failures as part of your database failover strategy.
- Reducing load on production master server by running expensive report queries on the slave server. In these setup, you should only allow accept write queries on the master server, other wise those changes made on a specific slave server won't be replicated across all instances of replication servers.
Consider a very simple example of mysql replication—one master will send information to a single slave.
Open up the mysql configuration file on the master server, edit /etc/mysql/my.cnf file
sudo nano /etc/mysql/my.cnf
Define the log file to be used (these logs are used by the slave to see what has changed on the master) and specify that this MySQL server is the master.
### uncomment this line (set it to 1)
server-id = 1
### define binary log location
log_bin = /var/log/mysql/mysql-bin.log
Save and restart MYSQL
sudo service mysql restart
Log into MySQL on the Master MySQL server and create the replication user(the user who will connect to the master server to copy it)
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%' IDENTIFIED BY 'swpassword';
FLUSH PRIVILEGES;
Open up the mysql configuration file on the replica server, edit /etc/mysql/my.cnf file
sudo nano /etc/mysql/my.cnf
Define the log file to be used (these logs are used by the slave to see what has changed on the master) and specify a unique server id (I usually just increment), set the relay logm set the binary log
### uncomment this line (set it to 1)
server-id = 2
# define the relay log
relay-log = /var/log/mysql/mysql-relay-bin.log
### define binary log location
log_bin = /var/log/mysql/mysql-bin.log
Save and restart MYSQL
sudo service mysql restart