MySQL Database Replication

What is Replication?

Replication enables data from one MySQL database server (master) to be copied to one or more MySQL database servers (Slaves).

Replication between servers in MySQL is based on the binary logging mechanism. The MySQL instance operating as the master (the source of the database changes) writes updates and changes as “events” to the binary log. The information in the binary log is stored in different logging formats according to the database changes being recorded. Slaves are configured to read the binary log from the master and to execute the events in the binary log on the slave’s local database.

How Does MySQL Replication Works?

MySQL Database Replication1. Users connect to the Master database and modify the data.
2. Every activity gets logged into the Binary log file.
3. Slave IO Thread connects to the Master DB Server to read the new event from the the Binary log file and writes down the all new events in the Relay log file which is on the Slave server.
4. Slave SQL Thread reads the all new events from the Relay log file and excute on the Slave Database.
I/O_THREAD – is responsible to connect to the Master, ask for new transaction and save them to his own log (relay log).
SQL_THREAD – is responsible for read all new transaction from relay log and apply them to the database server.

Why is MySQL replication needed?

MySQL master slave replication is carried out by many for more reasons than one. They could be:
To Speed up your application: One of the biggest points of interest to have master-slave set up in MySQL is to have the ability to utilize master for the inserts, update and delete queries and slave for select queries. This will generally likely to accelerate your website without needing to swooping into optimizing all the queries or purchasing more hardware.
• To Replicate your Database: Replication is the main innovation that can fulfill the necessities of the most requesting systems, as just replication can give moment access to information and zero information loss.
• To enable Disaster recovery strategy: A database can get unusable because of a wide assortment of hardware or software washouts. Master slave cloning will automatically create a copy of your primary database and thus you always have latest copy of your database.
• To keep the backup up to date: Data on the slave server is more likely to be up to date than on a periodical backup which is only performed periodically (eg: daily). If there is a complete failure of the Master Server, the Slave Server can be made available to take over the functionality, or it can be used to restore data with minimal loss.

Advantages of Replication

• Long-distance data distribution
• Automatic database backup
• Analyze the database without using the main database
• Automatically maintaining clone of the server.

Method of Replication

1. Statement Based Replication (SBR)
The replication was based on propagation of SQL statements from the master server to the slave server. This replication format is called statement-based replication (SBR) and is default in older versions of MySQL (<= 5.1.4). Just as a note that in later versions of MySQL, especially with the Innodb Plugin, you NEED to run your transaction_isolation with REPEATABLE-READ.

2. Row Based Replication (RBR)
The newer replication type is row-based replication (RBR), which logs changes in individual table rows to the binary log. Basically, logging the actual change and not the SQL statement itself.

MySQL also has the ability to change its binary logging format in real time depending on the type of event using the mixed format binary logging. When the mixed format is turned on, statement-based replication is on by default but will change to row-based replication is particular cases.

Click here to see Statement-Based vs Row-Based Replication

3. Global Transaction Identifiers Replication (GTIDs)
The is a newer method in MySQL 5.6 based on global transaction identifiers (GTIDs) which is transactional.

Click here to see Binary Logging Formats

Type of Replication

1. One-Direction (Mater-Slave Replication)

MySQL Database Replication
MySQL replication is the process by which a single data set, stored in a MySQL database, will be live-copied to a second server. This configuration, called “master-slave” replication, is a typical setup.

2. Bi-Direction (Master-Master Replication)

MySQL Database Replication
Master-Master replication allows data to be copied from either server to the other one. This subtle but important difference allows us to perform mysql read or writes from either server.

Click here to see  how to Set Up Master Slave Replication in MySQL 5.6

<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

1 comment on “MySQL Database Replication”

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.