Master - Slave Replication Setup
Consider 2 MySQL Servers for replication setup, one is a Master and the other is a Slave.
We are going to configure the Master that it should keep a log of every action performed on it. We are going to configure the Slave server that it should look at the log on the Master and whenever changes happens in log on the Master, it should do the same thing.
First of all, we need to create a user on the Master. This user is going to be used by Slave to create a connection with the Master.
Change user_name and user_password according to your Username and Password.
Now my.inf (my.cnf in Linux) file should be edited. Include the following lines in [mysqld] section.
server-id = 1
log-bin = mysql-bin.log
binlog-do-db = your_database
The first line is used to assign an ID to this MySQL server.
The second line tells MySQL to start writing a log in the specified log file. In Linux this can be configured like log-bin = /home/mysql/logs/mysql-bin.log. If you are starting replication in a MySQL server in which replication has already been used, make sure this directory is empty of all replication logs.
The third line is used to configure the database for which we are going to write log. You should replace your_database with your database name.
Make sure skip-networking has not been enabled and restart the MySQL server(Master)
my.inf file should be edited in Slave also. Include the following lines in [mysqld] section.
The first line is used to assign an ID to this MySQL server. This ID should be unique.
The second line is the I.P address of the Master server. Change this according to your Master system I.P.
The third line is used to set a retry limit in seconds.
The next two lines tell the username and password to the Slave, by using which it connect the Master.
Next line set the database it needs to replicate.
The last two lines used to assign relay-log and relay-log-index file names.
Make sure skip-networking has not been enabled and restart the MySQL server(Slave)