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.


Recommended Solution

Handy Backup Small Server

Version 7.19.0, built on 8 November, 2018. 164 MB
Backup Software from Novosoft LLC. 249 USD per license.

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.

Version 7.19.0, built on 8 November, 2018
164 MB

See also:

See all terms from Databases...

Backup Terms Glossary

Contact Us

support@handybackup.net

Term of the Day

  • Glossary Category: Email Management
    “The “Email Management” category contains various terms from the area of e-mail messaging. In “Email Management” you can find...”

Backup Software Glossary

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

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

The glossary contains 179 terms. The current section Databases contains 18 terms.

Other “Databases” Terms

  • phpMyAdmin Auto Backup
    “”
  • Foreign Key
    “Foreign key is a field in a “child” table of a database that is used to refer to a record in a “parent” table of the same...”
  • Database Storage Engine
    “Database storage engine is a mechanism employed by database management software that defines how it stores and handles data. Different DBMS have...”

Who uses our backup software?