MySQL Master Server

MySQL Slave Server is a server configured for MySQL replication. It constantly monitors and replicates all changes made to the database on the Master Server. Slave Servers are never directly modified by MySQL clients. Instead, all changes are initially made to the Master database and then replicated to the Slave servers using MySQL′s built-in replication mechanism.

Each time a website or an application interacts with the Master database, the relevant records are logged and stored in files. The MySQL Slave Server analyzes the respective logs and automatically repeats any statements it finds that modify the data. This scheme implies that prior to enabling replication, the contents of the MySQL Slave must match those of the original database. To achieve synchronization, Handy Backup enables you to restore a backup of a MySQL Master Server onto the MySQL Slave Server. For more information, please refer to MySQL Replication.

Setting up MySQL Slave Server

To enable replication on a Slave Server, you must configure three parameters:

Specify the host name or IP address of the Master Server
Specify the name of the log file that contains the recorded changes to be read (you can find more information about the different types of replication logs below)
Specify the position within the log file to determine what precisely needs to be synchronized

To optimize the performance of your application, it is also recommended to enhance the server′s reading speed by modifying its storage engine. MySQL Slave Server is expected to be read-only, it receives data copies from the Master Server and does not require transaction safety. If you are using MySQL as your DBMS, the optimal solution for this specific purpose would be to utilize MyISAM. To switch the storage engine of the destination database server, you can easily modify the corresponding SQL statement within the dump file. Here’s an example:

ALTER TABLE your_table_name ENGINE = new_storage_engine;

For instance, if you want to switch the storage engine of the “computers” table to MyISAM:

ALTER TABLE computers ENGINE = MyISAM;

As mentioned earlier, the synchronization between the Master and Slave Servers can be accomplished by creating and restoring a backup. Our software creates backups that are stored as dump files, utilizing a highly intuitive format that can be easily edited using any text editor.

To discover more about this, explore our MySQL Restore webpage for valuable insights and details.

MySQL Database Backup and Recovery with Handy Backup Software - Video Tutorial

Discover how to effectively backup and restore MySQL databases with Handy Backup through our comprehensive video tutorial.

Note: The instructions provided in this video assume that you have already installed Handy Backup on your computer. If you haven′t done so yet, we strongly encourage you to download and install it to ensure you can fully leverage the benefits and features outlined in the tutorial.

Download for Free

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

Different Types of Replication Logs

There are different types of replication logs used in database replication. Two commonly used types are:

Binary Logs: Binary logs contain a record of all data changes made on the master database server. They store the actual SQL statements or low-level binary representation of the changes. These logs are used by the replication process to replicate the changes to the slave servers.
Relay Logs: Relay logs are specific to the slave database server in a replication setup. They store the data changes received from the master server and are used to replay those changes on the slave. Relay logs help ensure that the replication process is accurately and efficiently carried out on the slave server.

Two Types of Replication

MySQL offers two primary forms of replication: statement-based replication (SBR) and row-based replication (RBR).

In a statement-based configuration, the Master Server logs all SQL statements that modify the database′s contents. The replication engine then replays these statements on Slave Servers, ensuring that the databases maintain consistent data.

For example, if a user executes the following SQL statement on the Master Server:

INSERT INTO products (name, price) VALUES ('Product A', 10.99);

As a result of SBR, the same record ('Product A', 10.99) will be inserted into the respective table on each Slave Server.

In row-based replication, databases are synchronized by tracking changes at the individual row level. Whenever a new row is inserted or an existing row is updated, these changes are automatically replicated and copied to the database Slave Server.

For example, if a new row is inserted into the “users” table on the Master Server with ID 3:

INSERT INTO users (ID, Name, Age) VALUES (3, 'Jane', 25);

That entire row will be automatically copied to the database Slave Server.

While row-based replication (RBR) generates more logs and tends to be slower compared to the statement-based replication (SBR) method, it offers greater precision and accuracy in data replication. For instance, in a statement-based replication (SBR) scheme, inserting a random value using the RANDOM() function will yield different records on the Master and Slave Servers due to the nature of statement replication.

To switch from Statement-Based Replication (SBR) to Row-Based Replication (RBR) in MySQL, modify the binlog_format setting in the MySQL configuration file (my.cnf or my.ini) to set it to ROW (or STATEMENT if switching vice versa), as shown in the following example:

binlog_format = ROW,

and then restart the MySQL server.

It is crucial to recognize that while replication serves as a valuable security measure, it cannot serve as a substitute for regular backups. Backups, including a dedicated backup slave, remain an essential practice to ensure data integrity and provide a comprehensive data recovery solution in case of unforeseen circumstances. If you accidentally delete something from the Master Database, the same information will also be deleted from the Backup Slave. Unfortunately, there is no way to reverse or undo these changes once they have been made – unless you have a backup.