Replication of MySQL databases
Backup Software / Features / 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:
Our software can help you do all of the above, and much more (see below).
Version 7.9.9, built on 26 January, 2018
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:
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.
Version 7.9.9, built on 26 January, 2018. 65.7 MBBackup Software from Novosoft LLC. 249 USD per license.
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.
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 replication configuration
Out of the box, MySQL comes with storage engines, most popular of which are MyISAM and InnoDB.
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 replication configuration
Configuration of Slave Servers can be performed in the following way:
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.
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 data using Slave replication server
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:
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:
Note: To make a MySQL backup, use our software – download it right now, if you still don’t have it!
To back up and restore MySQL, you need to have one of the business editions of our software.
Back up, restore and replicate MySQL with Small Server and Server Network editions
To learn about different purchase options, please refer to the Order section.
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.
Handy Backup is a great solution when you need to set up backup, recovery or replication of your MySQL databases!
Loading comments, please wait...
+1 (707) 703-13-11
Upgrade to version 7
See all volume order prices
2018/01/26 - Version 7.9.9: Improved Network Agents Utility
2017/12/25 - Version 7.9.8: Quick Disk Image Plug-In
Medical Database Backup - back up medical data with MySQL through the hot backup technology
MySQL Education Backup Service - perform backups for educational-related MySQL databases
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
© Vladimir Vaschenko
Copyright © 2004-2018 Novosoft LLC. All rights reserved. Handy Backup software.
United States, P.O.Box 535, Louisville, OH 44641 Tel.: +1 (707) 703-13-11
Back to Top