MySQL Slave Server

MySQL Slave Server is a server in MySQL replication configuration that continuously tracks all modifications of the Master Server′s database and replicates them locally. Slave Servers are never modified by MySQL clients directly: all changes are firstly made to the Master database, and then reproduced on Slaves, via a built-in replication mechanism of MySQL.

Each time a website or an application interacts with the Master database, the respective records are saved into log files. MySQL Slave Server analyzes the respective logs and when it finds statements modifying the data, it automatically repeats them. This scheme suggests that before replication is enabled, contents of the MySQL Slave must be equal to the original database. Synchronization can be achieved with Handy Backup by restoring a backup of a MySQL Master Server to MySQL Slave Server. To learn more, please refer to MySQL Replication.

Setting up MySQL Slave Server

To enable replication on a Slave Server, you need to set three parameters:

Host name or IP address of the Master Server
Log file name to read changes from (learn about different types of replication logs below)
Position within log file to calculate what exactly needs to be synchronized

To achieve maximum performance of your application, it is also recommended to increase the server′s reading speed by changing its storage engine. MySQL Slave Server is supposed to be read-only; it copies data from the Master Server and doesn′t need to be transaction-safe. In case of MySQL DBMS, the best solution for this purpose is using MyISAM.

As it′s been said before, synchronization of Master and Slave Servers can be made by making and restoring a backup. Backups made with our software are saved as dump files which have a very understandable format and can be easily edited in any text editor. To change storage engine of the destination server, you can simply change the respective SQL statement in the dump file. To learn how to do it, please refer to MySQL Restore.

Video Example

In the following video tutorial you can learn how to back up and restore MySQL databases with Handy Backup.

Note: Instructions in this video suggest that you already have Handy Backup installed on your computer. If you still haven′t done so, don′t hesitate to download it.

Download for Free

Version 8.5.8 , built on October 31, 2024. 118 MB
30-day full-featured trial period

Two Types of Replication

MySQL comes with two main types of replication: statement-based replication (SBR) and row-based replication (RBR).

In statement-based configuration, Master Server logs all SQL statements that change contents of the database. The replication engine then repeats the same statements on MySQL Slave Servers, so the databases have similar contents.
Row-based replication synchronizes databases by tracking changes made on row level. Whenever there is a new or updated row, it is automatically copied to the Slave Server′s database.

While RBR replication writes a lot more logs and is generally slower than the SBR method, it provides higher accuracy. For example, in SBR replication scheme, inserting a random value through the RANDOM() function will result in different records.

It is important to understand, that though replication is a good security measure, it can′t be a replacement for backups. If you accidentally delete something from the Master Database, the same information will be removed from the Slave, and there will be no way to undo the changes – unless you have a backup.