Backup Software / Backup Terms Glossary / Databases / MySQL Master Server

MySQL Master Server

MySQL Master Server is a server in MySQL replication configuration that hosts Master Database and acts as a primary source of data. Whenever a website or a MySQL-driven application adds new rows and update existing records on the Master Server, all changes are automatically reproduced on one or several Slave Servers.

In MySQL, there are two main types of replication:

Statement-based replication (SBR) records all SQL statements that are run on the Master Server and repeats them on the Slaves. This method is not very accurate, because some MySQL functions may return different results on different servers. For example, if your Master and Slave servers are running different versions of MySQL, the VERSION() function will return different values.
Row-based replication (RBR) tracks changes made to the MySQL Master Server on row level, and then runs the respective updates and inserts on Slave Servers. This approach is much slower than statement-based replication (because one SQL query can affect hundreds and thousands of rows) but ensures 100% accuracy and concurrency.

Since MySQL replication is based on change log, it is required to synchronize Master Server and Slave Server databases before running it. This can be made with the help of Handy Backup: simply back up your MySQL Master database, restore it to MySQL Slave – and then you can start it. To learn more, please refer to MySQL Replication.

Handy Backup Small Server

To back up your MySQL servers you need the MySQL Backup plug-in which is available in small business server backup edition of our software. Download it right now, and try free for 30 days!

Using MySQL Replication for Backup Purposes

MySQL Master Server and MySQL Slave Server are not required to be set up similarly. Furthermore, there are applications in which they need to have different configurations. Particularly, it is recommended to make Slave Server read-only, and use it as a source of data for backups, while leaving all writing and processing to the Master Server. The logic is as follows:

Reading massive amounts of data from a MySQL database concurrently with updating it significantly slows down its performance.
Replication is a built-in feature of MySQL which doesn"t read from the very database, but uses change logs.
By changing storage engine of the Slave Server to the one providing maximum speed of data retrieval, you can make the procedure of backing up as fast and unnoticeable as it can be.

With all things considered, the idea of assigning different roles to MySQL Master and Slave servers becomes very clear.

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.

Modifying MySQL Master Server Backup to Change Storage Engine

Handy Backup provides an easy way of changing storage engine to set up a replication configuration. When you create a backup of your Master database, it is created as a dump file, i.e. a complete set of SQL commands that need to be run to re-create all data on an empty server.

Format of the backup is human-readable and can be modified without fear. Specifically, you can find the CREATE TABLE statements and supplement them with ENGINE=MyISAM. When you restore this backup, the respective tables will have MyISAM as a storage engine. To learn more, please see MySQL Backup Software.

Free Download

Version 8.5.0, built on September 19, 2023
116 MB

Contact Us

support@handybackup.net

Term of the Day

Disk: What types of disks are there? How is the data recorded?
“A disk is a round plate on which data can be encoded. There are two basic types of disks: magnetic disks and optical disks. On magnetic disks...”

Backup Software Glossary

Current glossary explains various terms that you may come across while working with Handy Backup or other backup software.

Download

Handy Backup is a backup utility for Windows Servers and PCs that is famous for its flexibility and ease of use.

The glossary contains 107 terms. The current section Databases contains 13 terms.

Other “Databases” Terms

Logical backup: What is logical backup? Features of logical backup.
“Logical backup is a copy of objects used in an application (e.g. database tables), that is made by accessing them through the application’s...”
MariaDB Database Engine - a Successor of MySQL
“”
Database Replication
“ Database replication is a process of synchronization in which data added to one database is automatically copied to the other. Replication should...”