MySQL Replication and Different Aspects of Using MySQL Backups

Comments: 0

MySQL Replication

MySQL Replication lets you keep several databases in sync, and use them simultaneously to improve your MySQL-based application’s performance, security, and other critical parameters.

While replication is available in MySQL out of the box, it cannot be simply turned on, but requires certain configuration before use.

There are several areas related to MySQL replication where Handy Backup can be very helpful:

  • Backing up Master Server database. Before enabling replication, you need to duplicate Master database to a different physical or virtual server that will host the Slave database.
  • Configuring Slave Server database. To improve performance of your website or application, it is recommended to change certain settings of Slave databases: specifically, increase its reading speed by changing data storage engine.
  • Setting up backup on Slave Servers. It is important to understand that MySQL replication cannot replace backups: an effective approach is combining them, and creating backups of MySQL Slave servers.

Our software can help you do all of the above, and much more (see below).

Version 7.19.0, built on 8 November, 2018
164 MB

Contents [hide]

Setting up MySQL Replication

When setting up master-to-slave replication configuration, it is important to acknowledge how the respective mechanism works. In MySQL there are two main types of replication:

  • Statement-based replication records all SQL queries, and reproduces them on Slave servers. This method is fast, but is not very accurate: for example, if you insert a random value using MySQL’s RAND() function, the results stored in the databases will be different.
  • Row-based replication tracks all changes made to rows of Master database tables, and automatically updates Slave databases each time there are new or modified values.

    This approach is noticeably slower than statement-based MySQL replication, but provides higher concurrency and has less negative effect on the Slave’s performance.

As you can see, both types of replication suggest that you already have two identical databases that you want to keep synchronized. You can achieve this with the help of our program by backing up Master Server databases and restoring them to Slave Servers.

For more details, please see MySQL Backup.

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, restore and set up replication for MySQL databases, you need to use the Small Server edition of our software adopted for small business backup needs.

Configuring MySQL Slave Databases

Besides creating two identical databases, it is important to set appropriate configuration options for Slave Servers - to make them read-only, so the load can be distributed across several servers.

Master-to-Slave MySQL replication

Master-to-Slave replication configuration

Out of the box, MySQL comes with storage engines, most popular of which are MyISAM and InnoDB.

  • MyISAM is very fast and resource-friendly, but doesn’t support transactions and has table-level locking. It was a default storage engine prior to MySQL version 5.5, and its main disadvantage is that it doesn’t allow making any simultaneous operations while updating tables.
  • InnoDB is a transaction-safe storage engine featuring row-level locking, support for commits and rollbacks, index caching, and much more. In most aspects it outperforms all other engines available in MySQL.

Master-to-Slave replication configuration suggests that you use Slave databases for reading only. For this purpose, MyISAM appears a better option, so your Slave Servers must be configured to use it.

Setting up Master-to-Slave MySQL replication

Setting up Master-to-Slave replication configuration

Configuration of Slave Servers can be performed in the following way:

  1. The MySQL Backup plug-in of Handy Backup creates a set of dump files, one for each MySQL table. Each dump file contains all SQL queries that are needed to create the respective table and fill it up with data.
  2. The format of backups is very understandable, and you can modify it by hands. Just find the CREATE TABLE statement in the beginning of the file and change ENGINE=InnoDB to ENGINE=MyISAM.
  3. Then you can restore this backup to the Slave Server.

Version 7.19.0, built on 8 November, 2018
164 MB

It is important to write down storage engines used in database tables of the Master Server. If it crashes, you will need to repeat the procedure in reverse order (for more information, see Recovery Considerations below).

After restoring the backup, you will need to run the server as a MySQL Slave. This is done with the help of the CHANGE MASTER TO statement, which you can learn about in the official tutorial.

Setting up Backup on Slave Servers

A common problem with backing up MySQL databases is that the process of backup has negative effect on performance. This is where MySQL replication comes into play: it not only optimizes database load, but also enables you to create backups in a very fast and efficient manner.

In a Master-to-Slave replication configuration, Slave Servers are configured in a way to provide maximum reading speed. And since backup is a read-only operation, it can be performed on a Slave database without affecting the Master.

Backing up MySQL Slave Servers

Backing up MySQL data using Slave replication server

Recovery Considerations

Restoring MySQL databases in a Master-to-Slave replication configuration can be tricky. It is different than common MySQL Restore, because it involves several databases instead of one.

If you experience a crash of a Slave Server, then you can simply repeat configuration of Slave databases described above. Make a backup of your Master database, change the storage engine, restore it to the Slave, enable replication – and you’re done.

Recovering after a crash of a Master is a more complicated procedure. In many cases you can’t afford running your website or application in read-only mode, so you will need to temporarily change one of the Slaves to Master. To do it, follow these steps:

  1. Choose a MySQL Slave that will become a new Master.
  2. Use the STOP SLAVE and RESET MASTER statements to make it run as a standalone database.
  3. On all other Slave Servers, use the STOP SLAVE IO_THREAD statement to finish all unprocessed synchronization commands that they received from the old Master.
  4. Then, use CHANGE MASTER TO with the correct parameters, and START SLAVE to run replication from the new Master to Slave Servers.

Running Slave as a Master is only a temporary measure, because MyISAM storage engine is optimized for reading and may be very slow for inserts and updates. It is recommended to restore the old Master server as soon as possible:

  1. Make a fresh backup from one of the MySQL Slaves. For performance considerations, it should not be taken from the new Master Server (but if you had only one Slave, it’s certainly better than downtime).

Note: To make a MySQL backup, use our software – download it right now, if you still don’t have it!

  1. Modify the backup file by changing storage engine to the original engine used in the Master database.
  2. Restore the backup.
  3. Repeat steps 1-4 as if the Master (ex-Slave) had crashed and you needed to set up restored database to be a new Master.

Licensing Information

To back up and restore MySQL, you need to have one of the business editions of our software.

Handy Backup Small Server
Handy Backup Server Network

Back up, restore and replicate MySQL with Small Server and Server Network editions

  • If you need to be able to back up and restore multiple servers at once (which is very likely if you have MySQL replication configuration), then you need our client-server solution that allows managing backups on up to 5 networked servers.

    Please see product page of Handy Backup Server Network
  • To back up databases on only one computer, use Handy Backup Small Server

To learn about different purchase options, please refer to the Order section.

Video Example

In this video example you can see how to back up and restore MySQL database with Windows version of Handy Backup.

It is not exactly about MySQL replication, but is a good illustration of the general concept.

See Also

Handy Backup is a great solution when you need to set up backup, recovery or replication of your MySQL databases!

Version 7.19.0, built on 8 November, 2018
164 MB


    Contact Us

    +1 (707) 703-13-11

    Handy Backup Small Server

    $249 per license

    Upgrade to version 7

    Volume Discounts
    1 license$249 per unit
    2-9 licensescontact us$249 per unit
    10-49 licensescontact us$249 per unit

    See all volume order prices

    Read after MySQL Replication

    Medical Database Backup - back up medical data with MySQL through the hot backup technology

    MS SQL Backup - learn about backing up Microsoft SQL Server data

    Oracle Backup - read how Handy Backup can help you to back up Oracle databases and Oracle-based software

    PostgreSQL Backup - check out how to perform PostgreSQL backup and recovery

    Database Backup - protect your databases with different plug-ins available in our software

    MariaDB Backup - save a copy of MariaDB database server to protect important data

    Who uses our backup software?