Replication in MySQL - Part - 1

Replication is one of the high availability services offered by MySQL. In this article, I'm going to show how to set up replication in MySQL.

In MySQL, you can do everything with the configuration file only. Never mind whether it is on Windows or in Linux, configuration file is the heart of the product. By default, MySQL offers server to server replication.

To start setting up replication, we need to know some things. MySQL Replication terminology has two kinds of instances, one is Master and the other is Slave. All operations performed on the Master are written to a Binary Log File which are processed on the slave. To identify the Master and Slave uniquely, each should be assigned with a server_id. This is also done in the configuration file.

So, the first step in setting up replication is to ensure that binary logging is enabled in your instance and it has a server_id. If they are not enabled by default, stop the Master instance and include the following statements in its configuration file and start it again,

log-bin=yashwanth

server-id=1

The phrase "yashwanth" is the name of the binary log file and it can be anything. If you are using InnoDB Storage Engine then you need to include more three options in your configuration file, 

innodb_flush_log_at_trx_commit=1

sync_binlog=1

innodb-safe-binlog

If the "innodb_flush_log_at_trx_commit" is set to "1" then the log buffer of InnoDB is written out to the log file whenever a transaction is committed and the "flush to disk" operation is performed on the log file.

If "sync_binlog" is set to "1" then the log is flushed to the disk after 1 statement (if autocommit option is enabled) or one transaction. This is the safer option as there will be only one statement loss if there is a crash in the server. If it is set to "0" then logs are flushed based on the operating system's feasibility. It can be set to any value.

Though the "sync_binlog" is set to "1", if transactions are used then the log is synced with the disk only after its commit. If the crash occurs between starting the transaction and committing it then it is rolled back at InnoDB but not at the log file. This causes an inconsistency in the transaction. To avoid this innodb-safe-binlog is enabled to maintain consistency among the transactions. But this option is not needed in MySQL higher than 5.0.3.

Now, assign a server_id to your Slave instance by stopping it and including the following statement in its configuration file, 

server-id=2

The values assigned to the server_ids is as per your wish but just ensure that both do not have the same values.

There are different formats to write statements to the Binary log file which are called Replication Formats. There are three Replication Formats namely,

• Statement Based Replication (SBR) - If this format is enabled, the SQL statements are written to the Binary Log which are propagated from Master to Slave. Those SQL statements are executed at the Slave.
• Row Based Replication (RBR) - Unlike SBR, the events of how the rows are changed at the Master are recorded in the Binary Log rather than recording the SQL statements regarding the changes.
• Mixed Format Replication (MFR) - This is the combination of both the above formats. MySQL takes Statement Based format by default but switches to Row Based format automatically based on the situation.

To know the advantages and disadvantages of above Replication Formats, click here.

To set the desired replication format, include the following option in your configuration file, 

binlog-format=STATEMENT

This is the pre-setup of replication and now comes the actual part. To have the data replicated, the slave should get connected to the master often to get its binary log data. For this, the slave needs a user account of the master. Any existing account can be used if it has a REPLICATION SLAVE privilege. But the suggestion is to create a new account in the master and grant the above privilege to it because the binary log of master is in text format and whenever the slave gets connected to it, the username and password are written in the text format over there. This may be harmful if an existing user account is used for replication. So, now create a user for slave in the master, 

create user 'replication'@'<slave_ip_address>' identified by '<password>'; 

grant replication slave on *.* to 'replication'@'<slave_ip_address>';

Please note that like other privileges, MySQL doesn't allow giving replication slave privilege on a single database. It should be given on the whole instance.

After enabling the binary logging and creating a user at the master, its binary coordinates should be obtained which contain name of the master's binary log file and its position, needed for the slave. It keeps on changing with the changes made at the master. So, pause your operations for a moment with this command,

FLUSH TABLES WITH READ LOCK;

This command doesn't allow any updates on tables. Now execute the below command, 

SHOW MASTER STATUS;

Record the values appeared in the first and second columns as they are useful for the slave. By now, the master configuration is done completely.

Next comes slave configuration out of which the first step is to create a database snapshot of master at the slave. This can be done in two ways depending upon your database size. One is creating using mysqldump and the other is to create using raw data files.

If your database size is lesser then you can have its dump restored at the slave, using mysqldump utility. If the database is larger then you have to copy the raw data files from master to slave using either cold backup or warm backup or hot backup, depending on your application. For more information about backup strategies, click here.

After creating a snapshot in your slave, run execute the following command on it,

CHANGE MASTER TO

MASTER_HOST='master_host_name',

MASTER_USER='replication_user_name',

MASTER_PASSWORD='replication_password',

MASTER_LOG_FILE='recorded_log_file_name',

MASTER_LOG_POS=recorded_log_position;

Now start the slave with the following command to have the replication started,

START SLAVE;

The last step of the replication is to unlock the tables at master for write operations with the following command, 

UNLOCK TABLES;

So your MySQL instances have been configured with replication. Test it by making some changes at Master and observing them getting replicated at the Slave. What we've done is Server level Replication. I don't want to test your patience by making this post much lengthier, I'll write about more replication options in my next post.