MySQL Slave 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.
Version 8.5.0, built on September 19, 2023. 116 MB
In order to manage your MySQL databases, you will require the MySQL Backup plug-in, which is available in the Small Server edition of our program. Download it now and enjoy a free 30-day trial!
Setting up MySQL Slave Server
To enable replication on a Slave Server, you must configure three parameters:
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.
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.
Different Types of Replication Logs
There are different types of replication logs used in database replication. Two commonly used types are:
Two Types of Replication
MySQL offers two primary forms of replication: statement-based replication (SBR) and row-based replication (RBR).
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.
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.
Version 8.5.0, built on September 19, 2023