HOW DOES THE MASTER SLAVE REPLICATION WORK?

When any changes are made onto your databases on the Master server, will be logged and saved on you server location. The logs can be configured as where you want them to be saved. You can specify the log location in your my.cnf file which is most commonly found in /etc directory.

These logs are read by the slave server and the changes mentioned in the master log are replicated onto the databases lying on the slave machine.

The replication between master and slave sounds very simple, however there are several challenges you will face when you want it to start working as per your expectations.

STEP BY STEP CONFIGURATION

ON MASTER:

At the Prompt> vi /etc/my.cnf (Mysql does not create a my.cnf at this location, if not created go ahead and create it with the same command)

https://dbperf.files.wordpress.com/2010/03/master.jpeg

This image is a screen shot of a sample replication, my.cnf can be optimized as per you preference.

Here you can declare few setting in you my.cnf, which are defined as follow,

log-bin’: location where the master log files is saved, whenever there is a change to the database, the log is updated with the changes. These logs are read by the slave who will replicate the same changes onto its server.

You need to specify the location for these logs to be saved on Master; the default location is /var/lib/mysql.

‘binlog-do-db’: As the name suggest, it needs input of a databases name for which you want to create a binary log and save it to above mentioned location for ex: /var/log/mysql. You can specify any number of databases.

It is a good practice to specify a binlog-do-db separately for the number of databases you want to replicate. However there are dba’s who would mention the database names to a single binlog-do-db by use commas as separators.

‘server-id’: You tell the server what is it?  Master or a Slave, id 1 would suggest that it’s a master and id 2 will suggest it is a slave.

These are the few configuration properties which are required to create a replication Master, you can set many more variables in this my.cnf, and at this point the above variables are enough to go ahead with the setup.

After you save and exit the my.cnf, at the mysql prompt grant the access privileges to the server which will be configured as Slave

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’ @ 10.X.X.X   IDENTIFIED BY ‘password’;

Now it is very important to restart the mysql daemon, since you can defined all your variables under [mysqld] in your /etc/my.cnf file, the changes will be picked up only after you restart you daemon.

Note: I would prefer to restart the mysql daemon rather than mysql service is because the service does not load the changes defined under mysqld. Hence incase if you are unable to notice the changes by just restarting you /etc/init.d/mysql service, go ahead and start you daemon.

At the prompt> mysqld –u root &

ON SLAVE:

At the Prompt> vi /etc/my.cnf

https://dbperf.files.wordpress.com/2010/03/slave.jpeg

‘Server-id’: As mentioned earlier will define master or slave.

‘master-host’: IP address of the Master server.

‘master-connect-retry’: Retries slave need to try connecting to Master.

‘master-user’: User name which was granted replication slave privilege on Master.

‘master-password’: Password provided by using identified by clause to connect to Master.

‘relay-log’: Relay log save events that describes database changes.

‘relay-log-index’: Index file contains the names of all used relay log files.

Restart the daemon on the Slave server for the configuration to take effect.

As above we have mentioned two databases SampleDB and My_database for replication hence make sure the databases are present on both Master and Slave.

On Master run the following command at mysql prompt

FLUSH TABLES WITH READ LOCKS;

SHOW MASTER STATUS;

Show master status should return the Master bin-log file number and bin-log position along with the databases names which are configured for replication.

Status will look similar to this,

https://dbperf.files.wordpress.com/2010/03/binlog.jpeg

On the Slave server use the following commands at the mysql prompt,

SLAVE STOP;

CHANGE MASTER TO MASTER_HOST=’10.X.X.X’, MASTER_USER=’slave_user’, MASTER_PASSWORD=’slave_password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=20097900;

SLAVE START;

SHOW SLAVE STATUS\G;

Show slave status command should return the following.

https://dbperf.files.wordpress.com/2010/03/slavestatus.jpeg

Ensure the Slave_IO_Running and Slave_SQL_Running is YES, and Slave_IO_State is waiting for master to send event.

If any of these status is No or blank, the replication will not function.

After starting the slave run the following command on Master,

UNLOCK TABLES;

Check creating a test table on Master and see if the same table is getting displayed when you run SHOW TABLES;

If everything goes well your Master-Slave Replication is configured.

Some challenges you might face if your replication goes due to server crash,

Slave_IO_Running will show the status as NO, and no replication is done.

Solution: Clear the relay logs and master.info files from /var/lib/mysql and repeat the above steps. Do not forget to restart the daemons if any changes are made to the my.cnf file.

Wait for the Next topic Master-Master Replication.

Advertisements