Sooner or later, any administrator faces a problem when the capacity of his servers is not enough to carry out the tasks, and the acquisition of new equipment is almost impossible due to financial difficulties, or just such a purchase raises doubts about its payback in the future.
In the light of such events, it will not be out of place to learn about such a concept as database replication. Such knowledge will greatly simplify the life of any person involved in the maintenance of highly loaded systems and will allow you to create the optimal working hardware configuration. In general terms, database replication involves combining the power of a set of computers to increase the system's performance coefficient and its fault tolerance. The first effect can be achieved by throwing part of the requests to the backup server, and the second - by creating a local copy of the database, which, if necessary, will replace the main database.
The simplest and most common way to organize replication is with MySQL replication. Moreover, MySQL supports this functionality from 3.23.15 version and only one-way replication. In this case, a special “master-slave” configuration is created, and the main server (master) can also be a slave server at the same time.
MySQL replication is currently implemented as follows. A master server is created, whose responsibility is to monitor changes to the binary file, which displays all database changes and the slave machine or machines that read and execute requests from this file. The slave server constantly communicates with the main one, therefore all changes that have occurred on the master go to the slave, and there is no situation of divergence and incorrect data.
MySQL replication installed in several stages.
- The system administrator must make sure that all of his machines have the latest version of MySQL installed.
- It is necessary to create a new user on the main server, under whose login MySQL replication will occur (he must have the FILE privilege level and the right to communicate with slave machines).
- Next, you need to stop MySQL on the commander and slave servers and copy all the data related to replication. On Unix systems, this can be done with the tar command, which makes a backup archive of the entire directory. Windows users will use WinZip.
- Add the following lines to the mysqld section: server-id = unique number, log-bin. All changes are made in the My.conf file on the master server, after which it must be necessarily overloaded.
- On slaves, the following code fragment needs to be added to a similar file:
master-host = <name of your master host>
master-user = <user login>
master-password = <user secret password>
master-port = <port on which slave servers will connect to the master>
server-id = <id of this slave machine>
* in triangular brackets you need to enter your data, not the aforementioned text.
At the end, copy all the databases to the slave servers and restart all the machines.
After such actions, replication can be considered installed and configured, which means that now your main server will not suffer from high load and if any table on it fails, it can easily be restored from another computer. As a result, you can forget for several years about buying new hardware for highly loaded systems and be content with the established work of existing equipment.