Replikacija MySQL

Replikacija it can be used what to increase oshibkoustojchivost` and speed. For oshibkoustojchivosti you can have two systems and use the reserve server if you have problems with the main server.

Additional speed can be achieved, sending a part of searches about sample (select) the data on the reserve server (where to be stored{kept} the exact copy of the data).

Since version 3.23.15, MySQL supports built - in unilateral replikaciju. One server operates as main (master), while another as reserve. Pay attention, that one server can be, as well as main, and reserve (in different chains). The main server saves full magazine of changes of the data (binary log of updates) see hands 4.9.4 and an index file for rotation of dens.

The reserve server at connection with the main thing, informs from what stage to begin updating and looks forward to hearing from the main thing about new obnovlenij.

Pay attention - if you copy a database, all updatings to this database should be made on the main server! Other benefit of use replikacii - it is possible to receive alive copies from system, doing{making} a copy on the reserve server, instead of the main thing.

Full magazine of changes of the data (Binary Update Log)

In the future the full magazine of changes of the data (binary update log) will replace magazine obnovlenij (update log) so we recommend you to proceed{pass} to use of this magazine as soon as possible! The full magazine of changes of the data (binary update log) contains the information, which contains magazine obnovlenij (update log), but in more effective format. He contains also the information on that, each search which modified a database was how long carried out.

You can use options binlog-do-db=database_name the specified database binlog-ignore-db=database_name Will to be processed only Will be ignored the specified databases

At use of mysqladmin refresh mysqladmin flush-logs SQL commands the operator " FLUSH LOGS " or restart of the server to expansion of a name of a file of magazine will be added inkremental`nyj number{room}.

To take into account what files of magazines were used Mysqld creates an index file at which the name coincides with full magazine of changes of the data (binary update log), and has expansion '.index '. You can change a name of this file, having specified an option - log-bin-index = [filename].

If you use replikaciju, you should not remove old full magazines of changes of the data (binary update log) while you are not sure, that any reserve server will not use them. One of variants to do{make} mysqladmin flush-logs once a day and then to delete any files of registration at which date is more senior, than 3 days. You can see{overlook} full magazine of changes of the data (binary update log), mysqlbinlog. mysqlbinlog log-file command | mysql-h server_name you can learn{find out} Additional keys, having started mysqlbinlog command - help

If you use BEGIN [WORK] or SET AUTOCOMMIT=0, you should use full magazine of changes of the data (binary update log) instead of magazine obnovlenij (update log). Recording of magazine occurs at once, after performance of search, before performance of possible{probable} blocking. It guarantees authentic recording magazine All updating (UPDATE, DELETE or INSERT) kehshirujutsja before operation COMMIT if you use transactions (table BDB). If you do not use transaction - changes are saved immediately. Each stream at start fills in the buffer in the size (binlog_cache_size) for recording searches. If the search is more than the size of this buffer, is used a temporary file which will be removed at closing a stream. To limit the maximal size of the buffer, use an option max_binlog_cache_size to guarantee, that you can freshen an exact copy of your tables, applying a file of registration on reservation (copy).

IMPORTANT! The full magazine of changes of the data (Binary Update Log) begins with the certain moment of time. Your reserve the server should have exact copies of the data at the moment of start of magazine. In future versions MySQL 4.0 it will be eliminated, but by the current moment it is necessary for you to block the server for recording, and will put blocking on reading when you will do{make} slepok the current databases

At a correct configuration the reserve server incorporates to the basic and waits for the data on updating. If connection interrupts, the reserve server tries to incorporate again, everyone master-connect-retry sek. The reserve server marks the moment of disconnect. The preferred server does not define{determine}, how much is connected to it{him} of reserve servers.