MySQL Replication

30% OFF - 9th Anniversary discount on Entity Framework Extensions until December 15 with code: ZZZANNIVERSARY9


Replication is used to copy[Backup] data from one MySQL database server to one or more MySQL database servers.

Master -- The MySQL database server, which is serving data to be copied

Slave -- The MySQL database server, copies data which is served by Master

With MySQL, replication is asynchronous by default. This means slaves do not need to be connected permanently to receive updates from the master. For example, if your slave is switched OFF or not connected with master and you are switching slave ON or connect with Master at a later time, then it will automatically synchronize with the Master.

Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Replication Formats

There are two core types of replication formats

Statement Based Replication (SBR) -- which replicates entire SQL statements. In this, the master writes SQL statements to the binary log. Replication of the master to the slave works by executing that SQL statements on the slave.

Row Based Replication (RBR) -- which replicates only the changed rows. In this, the master writes events to the binary log that indicate how individual table rows are changed. Replication of the master to the slave works by copying the events representing the changes to the table rows to the slave.

You can also use a third variety, Mixed Based Replication (MBR). In this, both statement-based and row-based logging is used. Log will be created depending on which is most appropriate for the change.

Statement-based format was the default in MySQL versions older than 5.7.7. In MySQL 5.7.7 and later, row-based format is the default.

Got any MySQL Question?