ComputersDatabase

MySQL replication. Short excursion

Sooner or later, before any administrator, there is a problem when the capacity of his servers is not enough to perform the tasks set, and the purchase 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 is not superfluous to learn about such a concept as database replication. Such knowledge will greatly simplify the life of any person involved in servicing highly loaded systems and will create an optimal working hardware configuration. In general, database replication involves pooling the power of a set of computers to improve the system's performance and fault tolerance. The first effect can be achieved by tossing a part of the requests to the standby server, and the second one by creating a local copy of the database, which, if necessary, will replace the main database.

The most simple and common way to organize replication is MySQL replication. And MySQL supports this functionality with version 3.23.15 and only one-way replication. In this case, a special "master-slave" configuration is created, where the master can also be a slave server.

MySQL replication is currently implemented as follows. A main server is created, whose responsibility is to monitor the changes in the binary file, which displays all changes to the database and the slave machine or machines that read and execute queries from this file. The slave server is constantly connected to the master, so all the changes that occurred on the master go to the slave, and there is no situation of divergence and incorrectness of the data.

MySQL Replication Is established in several stages.

  1. The system administrator must make sure that all his machines have the latest version of MySQL installed.
  2. You must create a new user on the master server, under which MySQL replication will be logged (it must have a FILE privilege level and the right to communicate with slaves).
  3. Next, you need to stop MySQL on the master and slave servers and copy all data related to replication. On Unix systems, this can be done with the tar command, which makes a backup archive of the entire directory. WinZip is suitable for Windows users.
  4. 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 overloaded.
  5. On slaves in the same file, you need to add the following code snippet:

Master-host =

Master-user =

Master-password =

Master-port =

Server-id =

* In triangular brackets you need to enter your data, not the above text.

At the end, copy all the databases to the slave servers and restart all 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 in case of failure of any table on it it can easily be restored from another computer. As a result, it is possible to forget for several years about buying new hardware for highly loaded systems and to be satisfied with the established work of existing equipment.

Similar articles

 

 

 

 

Trending Now

 

 

 

 

Newest

Copyright © 2018 en.atomiyme.com. Theme powered by WordPress.