How to replicate a MySQL database on multiple servers (Windows / Linux)

MySQL allows you to "copy" the database managed by a single master server on multiple secondary servers: a great way to distribute the load or to prepare a backup process efficient and automatic.

When the load on the infrastructure starts to become higher, alongside a second server to the main one is almost an obligation to remain operational.

The version of "community" of MySQL (ie one free) offers a convenient feature that allows replication of "copy" in a completely automatic data placed on the first server on the other (or "the other", in case the machines are used more numerous)

In addition, all works asynchronously, which means that in circumstances in which the secondary servers are unable to update for some time (for example, due to a hardware failure or a temporary lack of connectivity), the database will automatically re-aligned as soon as the problem will be solved.

The operation is called replication (replication) and in this article, we'll prepare it.

Terminology: "master" and "slave"

Before you begin, pointed out the terminology. In the context of replication, it comes to server "master" server "slave".

The "master" is the database that can be defined as "primary" is on it, and only on it!, To be carried out write operations (INSERT / UPDATE / DELETE etc.). Any changes made to data stored on the master will then be replicated on other servers.

The secondary servers are called "slaves": their task is to deliver the data in read-only and automatically retrieve all the news released on the associated master.

There may be multiple slaves connected to a single master, but not more than one master connected to each slave. In other words, you can replicate data across multiple secondary servers, but each of them can remain aligned to a single master.

What you need

Before starting the configuration you need to check that the port of MySQL running on the master (this is the TCP 3306 , if you kept the default settings) is open and accessible from the slaves. You can check this by trying to connect from outside via the command line client (mysql- uNomeUtente-pMiaPassword-hIndirizzoMaster , recalling that, by default, the user root is enabled to dial-up) or with a tool like Nmap port-scanning.

You must also have at least two separate servers (one master and one slave), and have full access to both.

On each of them will then be running an instance of MySQL. Here, I'll assume that the program is already installed and configured: for more information about the setup of Windows, see "Turning your PC into an HTTP server with Apache, PHP, MySQL and Perl."

It is not necessary that the server use the same platform: you can easily replicate a master slave Linux on a Windows (or vice versa), even if a computer system is 32-bit and one 64-bit (or vice versa).

It is not even a requirement that the version of MySQL is the same: the replication tolerate one or two minor releases (the number after the point) difference. However, it is highly recommended to always use the latest build available on both systems.

See Pictures and Read more : How to replicate a MySQL database on multiple servers (Windows / Linux)