MySQL database master and slave experience finishing

Management MySQL master and slave has more than 2 years, managed more than 200 sets of MySQL master and slave, almost related to each version of the master and slave, this blog is summarized, part of it is from the network, most of it is based on their own management of experiences and experience written, collated a bit, to share to colleagues, I hope to have help, mutual exchange.

First, the principle of MySQL master and slave

1. Replication Thread

MySQL's Replication is an asynchronous copy process (mysql5.1.7 above is divided into asynchronous and semi-synchronous modes), copied from one MySQL instace (what we call Master) to another MySQL instance (we call it Slave). The entire replication process between Master and Slave is done primarily by three threads, of which two threads (SQL thread and IO thread) are on the Slave side, and another thread (IO thread) on the master side.

To implement MySQL Replication, you must first turn on the binary Log (mysql-bin.xxxxxx) function on the Master side, otherwise it will not be possible. Because the entire replication process is actually a variety of operations that are logged in the execution log that slave obtains the log from the master side and then executes it in its own full sequence. Open MySQL Binary Log by using the "-log-bin" parameter option in the process of starting MySQL Server, or by adding the "Log-bin" parameter to the MYSQLD parameter group in the F configuration file ([mysqld] Item

2, the basic process of MySQL replication is as follows:

2.1. Slave the above IO line thread attached the Master and requests the log content from the specified location (or from the beginning of the log) to the designated log file;

2.2. Master receives a request from an IO thread from Slave, the IO thread that is responsible for the replication reads the log information from the specified log at the specified location based on the requested information and returns the IO thread to the Slave side. In addition to the information contained in the log, the return information includes the name of the binary log file on the Master side of the returned information and its location in binary log;

2.3. Slave the IO thread receives the information, writes the received log content to the end of the relay log file (mysql-relay-bin.xxxxxx) on the Slave side, And the read to the master side of the Bin-log's file name and location to the Master-info file, so that the next time you read the high-speed master "I need to start from somewhere in the Bin-log log content, please send me"

2.4. Slave's SQL thread detects a new addition to the Relay log, it immediately resolves the contents of the log file into those executable query statements that were executed at the Master end, and executes the query itself. In this way, the same Query is actually executed on the Master and Slave ends, so the data on both ends is exactly the same.

3. Several modes of MySQL replication

3.1. Starting with MySQL 5.1.12, the following three modes can be implemented:

– SQL statement-based replication (statement-based replication, SBR),

– Row-based replication (row-based replication, RBR),

– Mixed Mode replication (mixed-based replication, MBR).

Accordingly, there are three types of Binlog: statement,row,mixed. In MBR mode, the SBR mode is the default.

You can dynamically change the format of the Binlog at run time, in addition to the following scenarios:

1. Storage process or trigger intermediate

2. NDB enabled

3. Current session trial RBR mode, and temporary table is open

If the Binlog uses the MIXED mode, the Binlog mode is automatically changed from SBR mode to RBR mode in the following cases:

1. When a DML statement updates a NDB table

2. When the function contains a UUID ()

When 3.2 or more tables containing the Auto_increment field are updated

4. When you line any INSERT DELAYED statement

5. When using UDF

6. The view must require the use of RBR, such as establishing the view is the use of the UUID () function

3.2. Set master-slave copy mode:

Log-bin=mysql-bin

#binlog_format = "STATEMENT"

#binlog_format = "ROW"

binlog_format= "MIXED"

You can also dynamically modify the format of the Binlog at run time. For example

mysql> SET SESSION binlog_format = ' STATEMENT ';

mysql> SET SESSION binlog_format = ' ROW ';

mysql> SET SESSION binlog_format = ' MIXED ';

mysql> SET GLOBAL binlog_format = ' STATEMENT ';

mysql> SET GLOBAL binlog_format = ' ROW ';

mysql> SET GLOBAL binlog_format = ' MIXED ';

3.3. The advantages and disadvantages of the two modes:

Advantages of SBR:

long history, mature skills

Binlog files are small

Binlog contains all the database modification information, which can be used to audit the security of the database, etc.

Binlog can be used for real-time restores, not just for replication

Master-slave version can be different from server version can be higher than the primary server version

Disadvantages of SBR:

Not all UPDATE statements can be duplicated, especially if they contain indeterminate operations.

Replication may also be problematic when invoking a UDF with an indeterminate factor

Statements that use the following functions cannot be duplicated:

* Load_file ()

* UUID ()

* USER ()

* Found_rows ()

* Sysdate () (unless the –sysdate-is-now option is enabled at startup)

INSERT ... SELECT produces more row-level locks than RBR

Replication requires more row-level locks than RBR to perform a full-table scan (an UPDATE that is not applied to an index in a WHERE statement)

For InnoDB tables with auto_increment fields, INSERT statements block other INSERT statements

For some complex statements, the consumption of resources from the server will be more serious, and RBR mode will only affect the changed records.

A stored function (not a stored procedure) executes the now () function once it is called, which can be a bad thing or a good thing.

The identified UDF also needs to be executed from the server.

The data table must be almost consistent with the primary server, or it may cause replication to fail.

Executing complex statements consumes more resources if something goes wrong.

Advantages of RBR:

Any situation can be replicated, which is the safest and most reliable for replication

Same replication skills as most other database systems

In most cases, replication will be much faster if you have a primary key from a table on the server.

There are fewer row locks when copying the following statements:

* INSERT ... SELECT

* INSERT containing the auto_increment field

* UPDATE or DELETE statements with no strings attached or changes to many records

Fewer locks when executing insert,update,delete statements

Possible to perform replication from a server with multithreading

Disadvantages of RBR:

Binlog a lot bigger.

Complex rollback with a large amount of data in the Binlog

When an UPDATE statement is executed on the primary server, all changed records are written to Binlog, and SBR is written only once, which results in frequent binlog of concurrent write queries

Large BLOB values produced by UDFs can cause replication to slow

Cannot see from Binlog what statements are copied (encrypted)

When executing a stacked SQL statement on a non-transactional table, it is best to use SBR mode, otherwise it can easily result in data inconsistency of the master-slave server.

In addition, for the system library MySQL inside the table changes when the processing criteria are as follows:

If you are using the Insert,update,delete Direct Action table, the log format is recorded according to the Binlog_format setting

If the use of Grant,revoke,set PASSWORD and other management statements to do, then in any case, the use of SBR mode record.

Note: After using RBR mode, it can handle many original key duplication problems. Instance:

For INSERT INTO Db_allot_ids SELECT * from Db_allot_ids this statement:

In binlog_format=statement mode:

The Binlog log information is:

—————————————–

BEGIN

/*!*/;

# at 173

#090612 16:05:42 Server ID 1 end_log_pos 288 Query thread_id=4 exec_time=0 error_code=0

SET timestamp=1244793942/*!*/;

INSERT INTO Db_allot_ids select * from Db_allot_ids

/*!*/;

—————————————–

In Binlog_format=row mode:

The Binlog log information is:

—————————————–

BINLOG '

Ha0yshmbaaaamwaaaoaaaaaaaa8aaaaaaaaaa1nouwamzgjfywxsb3rfawrzaaibawaa

Ha0yshcbaaaanqaaabubaaaqaa8aaaaaaaeaav/8aqeaaad8aqeaaad8aqeaaad8aqeaaaa=

‘/*!*/;

—————————————–

4, MySQL master and slave advantages and disadvantages

MySQL master-slave synchronization is a very mature architecture, the advantage is: ① in the slave server can perform query work (that is, we often say that the read function), reduce the primary server pressure; ② in the backup from the primary server, to avoid affecting the primary server service during backup, ③ when the primary server is having problems, you can switch to the slave server. So I often use this approach in project deployment and implementation, given the rigor of MySQL in a production environment.

In fact, in the old version, the replication implementation of MySQL was done on the Slave side not by the two threads of the SQL thread and IO thread, but by a single thread to do all the work. But MySQL engineers quickly found that there were a lot of risks and performance issues, mainly as follows:

First of all, if the work is done independently by a single thread, it makes it possible to replicate the Binary log log on the Master side and parse the logs, and then perform this process as a serial process, and the performance is naturally subject to greater limitations, which The delay of the Replication is naturally longer.

Second, after the copy thread from the Slave side gets the Binary Log from the master, it needs to parse the content back into the original Query executed by the master and execute it on its own. In this process, the master side is likely to have generated a lot of changes and generated a lot of Binary Log information. If the storage system at the Master end of this stage fails to repair, all changes made at this stage will be lost forever and cannot be found again. This potential risk is particularly pronounced when the Slave is at a high pressure ratio, because if the Slave pressure is large, the time taken to parse the logs and apply them will naturally be longer, and more data may be lost.

Therefore, in the late transformation, the new version of MySQL in order to minimize this risk, and improve the performance of replication, the Slave end of the replication to two threads to complete, that is mentioned earlier SQL thread and IO thread. The first to propose this improvement is Yahoo! 's engineer, "Jeremy Zawodny". Through such transformation, this solves the performance problem to a large extent, shortens the asynchronous delay time and reduces the potential data loss.

Of course, even with the two threads that are now working together, there is also the possibility of Slave data latency and data loss, after all, the replication is asynchronous. These problems exist as long as the data changes are not in one transaction.

If you want to completely avoid these problems, you can only use the MySQL Cluster to solve. But the MySQL Cluster know the author writes this part of the time, still is a memory database solution, namely need to all the data including index all Load into memory, so the memory requirements are very large, for the general popularity of the application is not too large to implement. MySQL is now constantly improving the implementation of its Cluster, and one of the big changes is to allow the data not all load into memory, but just index all load into memory, I believe that after the completion of the transformation of the MySQL Cluster will be more popular, The implementation will be even greater.

5. mysql semi-synchronous mode (semisynchronous Replication)

We know that before 5.5, MySQL replication is actually asynchronous operation, rather than synchronization, it means to allow a certain delay between the master and slave data, MySQL originally designed for the purpose may also be based on usability considerations, in order to ensure that the master is not affected by slave, and asynchronous replication so that master in a The state of optimal performance: After writing the Binlog, you can submit without waiting for slave to complete the operation. This is a hidden problem, when you use slave as a backup, if master hangs, then there will be some committed transactions failed to successfully transfer to slave, which means data loss!

In the MySQL5.5 version, the introduction of the semi-synchronous replication mode (semi-synchronous Replication) can successfully (but only relatively) avoid the pitfalls of the above data loss. In this mode: Master waits until Binlog successfully transmits and writes at least one slave of relay log before committing, otherwise waiting until timeout (default 10s). When a timeout occurs, master automatically switches the half-sync to asynchronous until at least one slave is successfully received and sent Acknowledge,master will then switch back to semi-synchronous mode. In combination with this new feature, we can ensure the absolute security of the synchronized data by allowing the loss of a certain amount of transaction throughput, since any submitted data will safely reach slave when you set timeout to a value that is large enough.

The mysql5.5 version supports the semi-synchronous replication feature (Semisynchronous Replication), but it is not native supported, is supported by plugin, and is not installed by default. Whether it is a binary release, or its own source code compiled, will default to generate this plug-in, one for the master is for slave, you need to install the two plugins before use.

Second, MySQL master-slave replication filtering

There are 2 main ways to filter replication:

1, in the main server in the event from the binary log filter out, the relevant parameters are: binlog_do_db and binlog_ignore_db.

2, the event from the server to filter out from the log, the relevant parameters are replicate_*.

Replication can only extend reads, cannot extend writes, and partitions data for extended writes.

Optimizations for replication:

In the MySQL replication environment, there are 8 parameters that allow us to control the need to copy or to ignore the DB or table that does not replicate, respectively:

The following two items need to be set on master:

BINLOG_DO_DB: Set which databases need to be logged Binlog

BINLOG_IGNORE_DB: Set where the database does not need to be logged Binlog

The advantage is that the Binlog record on the master side reduces the IO volume, the network IO decreases, and the IO thread on the slave side, the SQL thread is reduced, which greatly improves the replication performance.

The disadvantage is that MySQL determines whether an event needs to be replicated rather than the DB of the query that generated the event, depending on the default database at which the query was executed (that is, the library name specified at login or the db specified in "Use database"). The IO thread will read the event to the IO thread of slave only if the current default DB and the DB set in the configuration exactly match. So, if you change the data in a table in the DB that needs to be copied if the default db is not the same as the DB that needs to be copied, This event is not copied to slave, which causes the data in the slave end to be inconsistent with the master data. Similarly, the data in the database that is not required to replicate is changed under the default database, and is copied to the slave side, when the slave side does not have the database. will cause replication errors to stop.

The following six items need to be set on slave:

REPLICATE_DO_DB: Sets the database to be replicated, with multiple DB separated by commas

REPLICATE_IGNORE_DB: Sets the database that can be ignored.

Replicate_do_table: Sets the Table to be copied

Replicate_ignore_table: Setting a Table that can be ignored

Replicate_wild_do_table: Functions with replicate_do_table, but can be set with wildcard characters.

Replicate_wild_ignore_table: function with replicate_do_table, function with replicate_ignore_table, can carry wildcard characters.

The advantage is that the replication filtering mechanism is set at the slave end, which guarantees that there will be no inconsistency or replication error between the slave and master data due to the default database problem.

The disadvantage is that the performance is worse than the master end. The reason is that, regardless of whether or not replication is required, the event is read by the IO thread to the slave side, which not only increases the amount of network Io, but also increases the write volume of relay log for the IO thread on the slave side.

Note: In the actual production application found that In previous versions of mysql5.0, this filtering setting for MySQL was virtually nonexistent, and did not work: regardless of whether you set a database or table in the main library or from the library, he will still synchronize, so in the 5.0 version of the master-slave synchronization, we must maintain the consistency of the master-slave database, the Lord some libraries or tables must have, Otherwise, there will be an error in the synchronization process.

Third, MySQL master-slave synchronization configuration

Main Library ip:192.168.1.2

From library ip:192.168.1.3

Add a user for master-slave synchronization:

GRANT REPLICATION SLAVE on * * to ' repl ' @ '% ' identified by ' 1q2w3e4r ';

If you monitor MySQL master and slave, please add a super privilege:

GRANT SUPER, REPLICATION SLAVE on * * to ' repl ' @ '% ' identified by ' 1q2w3e4r ';

1, the configuration of the main library

1.1. mysql5.0 configuration for the following versions

Modify the main library MySQL configuration profile and add the following to the [Mysqld] segment:

Server-id = 1

Log-bin=/home/mysql/logs/binlog/bin-log

Max_binlog_size = 500M

Binlog_cache_size = 128K

Binlog-do-db = adb

binlog-ignore-db = MySQL

Log-slave-updates

1.2. Configuration of mysql5.0 or later

Modify the main library MySQL configuration profile and add the following to the [Mysqld] segment:

Server-id = 1

Log-bin=/home/mysql/logs/binlog/bin-log

Max_binlog_size = 500M

Binlog_cache_size = 128K

Binlog-do-db = adb

binlog-ignore-db = MySQL

Log-slave-updates

expire_logs_day=2

binlog_format= "MIXED"

1.3. The meaning of each parameter and the relevant note:

Server-id = 1 #服务器标志号, note that in the configuration file can not appear more than one such identity, if there are multiple words, MySQL first, a group of the master from which this identification number cannot be duplicated.

Log-bin=/home/mysql/logs/binlog/bin-log #开启bin-log and specifies the file directory and filename prefix.

Max_binlog_size = 500M #每个bin-log maximum size, a new log file is automatically generated when this size equals 500M. A record is not written in 2 log files, so sometimes the log file exceeds this size.

Binlog_cache_size = 128K #日志缓存大小

binlog-do-db = adb #需要同步的数据库名字, if it is more than one, write a line in this format.

binlog-ignore-db = MySQL #不需要同步的数据库名字, if it is more than one, write a line in this format.

Log-slave-updates #当Slave从Master数据库读取日志时更新新写入日志中, if you only start Log-bin and do not start Log-slave-updates, slave only records updates for your own database operations.

expire_logs_day=2 #设置bin The number of days that the-log log file was saved, this parameter mysql5.0 the following version is not supported.

binlog_format= "MIXED" #设置bin the-log log file format is: MIXED, you can prevent the primary key from repeating.

2, from the configuration of the library

2.1.mysql5.1.7 Previous Versions

Modify the MySQL configuration profile from the library and add the following in the [Mysqld] segment:

server-id=2

master-host=192.168.1.2

Master-user=repl

master-password=1q2w3e4r

master-port=3306

Master-connect-retry=30

slave-skip-errors=1062

Replicate-do-db = adb

replicate-ignore-db = MySQL

slave-skip-errors=1007,1008,1053,1062,1213,1158,1159

Master-info-file =/home/mysql/logs/

Relay-log =/home/mysql/logs/relay-bin

Relay-log-index =/home/mysql/logs/relay-bin.index

Relay-log-info-file =/home/mysql/logs/

If you modify the connection main library related information, must delete the file before restarting, otherwise the synchronization fails because the connection information changes from the library and does not automatically connect to the main library after the reboot. This file is to save the connection Master library information.

2.2.mysql5.1.7 later versions

Mysql5.1.7 version in the cluster above the configuration of a few, mainly using a new synchronization information logging, he does not support the configuration file to configure the connection to the main library information, but the connection and other related information recorded in master-info-file =/home/mysql/logs/ File, if the storage changes, directly in the MySQL command line to perform the connection information changes can take effect, more flexible, and not to restart MySQL. Modify the MySQL configuration profile from the library and add the following in the [Mysqld] segment:

slave-skip-errors=1007,1008,1053,1062,1213,1158,1159

2.3. The meaning of each parameter and the relevant notes

Here are just a few 2 parameters, all the rest are from the library to connect the main library information and intermediate log relay-log settings.

Master-connect-retry=30 #这个选项控制重试间隔, default is 60 seconds.

slave-skip-errors=1007,1008,1053,1062,1213,1158,1159 #这个是在同步过程中忽略掉的错误, these errors do not affect the integrity of the data, there are frequent errors, general settings ignored. Where 1062 is the primary key repeat error.

3. Realize Master-Slave synchronization

3.1. Achieving a unified database

Check the configuration file of the master and slave database to see if it is configured correctly. Synchronize for the first time to back up the database that needs to be synchronized on the main library, and then complete the import to the From library. Note: The previous version of mysql5.0 involves the problem of MySQL itself replication filtering, which requires that all databases be backed up into Concou to be maintained.

3.2. View and record the main library bin-log information

Go to the main library MySQL, execute: Show Master status, display information as follows:

Mysql> Show master status;

+-------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+-------------+----------+--------------+------------------+

| bin-log.003 | 4 | adb | MySQL |

+-------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

Record file and position information;

3.3. Executing the synchronization statement from the library

Enter MySQL and execute the following statement:

Slave stop;

Change Master to

Master_host= ' 192.168.1.2 ',

Master_user= ' Repl ',

Master_password= ' 1q2w3e4r ',

master_port=3306,

Master_log_file= ' bin-log.003 ',

master_log_pos=4;

Slave start;

3.4. View master-Slave synchronization status

Go to MySQL and execute show slave statusg; The results are as follows (MySQL versions differ from one query to another, but the important indicators are the same):

　　

The important indicators are:

Slave_io_running:yes

Slave_sql_running:yes

master_log_file:bin-log.003

relay_master_log_file:bin-log.003

Read_master_log_pos:4

Exec_master_log_pos:4

Seconds_behind_master:0 (Previous version 5.0 does not have this option)

The above options correspond to 22, as long as the results are consistent, the master-slave synchronization is successful.

3.5. Common errors and handling in synchronization

1. Phenomenon: Show slave statusg from the top of the library;

Slave_io_running:yes

Slave_sql_running:no

Seconds_behind_master:null

Reason:

A. The program may be written on the slave;

B. It may also be the result of a transaction rollback after the slave machine is reset;

C It is possible to encounter some kind of error during synchronization, this will see the error prompt when viewing the state from the library, the most uncommon is the primary key repeat 1062 error.

Workaround:

Enter Master

Mysql> Show master status;

+----------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+----------------------+----------+--------------+------------------+

| mysql-bin.000040 | 324 |ADB | mysql|

+----------------------+----------+--------------+------------------+

Then perform a manual synchronization on the slave server

Slave stop;

Change Master to

Master_host= ' 10.14.0.140 ',

Master_user= ' Repl ',

Master_password= ' 1q2w3e4r ',

master_port=3306,

Master_log_file= ' mysql-bin.000040 ',

master_log_pos=324;

Slave start;

show slave statusg;

2, the phenomenon: from the database can not sync, show slave status display:

Slave_io_running:no

Slave_sql_running:yes

Seconds_behind_master:null

Workaround: First look at the Err log of the database, see what the error is, see whether the IP, user, password and other relevant information from the library connected to the main library is wrong, if it is wrong, re-perform the synchronization, if the confirmation is correct, restart the primary database.

Mysql> Show master status;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 98 | adb| mysql|

+------------------+----------+--------------+------------------+

Go to MySQL from library, execute:

Slave stop;

Change Master to master_log_file= ' mysql-bin.000001 ', master_log_pos=98;

Slave start;

or this:

Stop slave;

Set global sql_slave_skip_counter = 1;

Start slave;

This phenomenon is mainly the master database problems, due to the connection of the main library information error, the main database hanging off if the common mistake caused by, I in the actual operation restart master after restart slave can solve this problem, this problem, you must restart the master database.

Iv. MySQL primary and primary master cluster

1, the realization of the main master of MySQL

In the actual production application, in order to crash the main library or the main server has a serious failure in the rapid recovery of the business, will be directly switched to the library, when the main library failure to deal with the completion of his direct operation as a Concou, at this time the master is a good choice.

V. MySQL master-slave monitoring

In MySQL master-slave application, as long as the reasonable set up, basically do not have problems, but to his monitoring is essential to avoid the real problem and do not know the cause of unnecessary data loss.

1, MySQL master-slave monitoring of the main ideas

MySQL master-slave monitoring, which is mainly to monitor some important parameters from the library:

Slave_io_running:yes

Slave_sql_running:yes

master_log_file:bin-log.003

relay_master_log_file:bin-log.003

Read_master_log_pos:4

Exec_master_log_pos:4

Seconds_behind_master:0 (Previous version 5.0 does not have this option)

Through the above parameters can reflect the main library and from the library state is normal, from the library is lagging behind the main library and so on. It is worth mentioning that the previous version of mysql5.0, slave_io_running this status indicator is not reliable, will be in the main library directly hanging out of the case will not become the No,seconds_behind_master parameter does not exist. Monitoring the above parameters can monitor MySQL master and slave.

2, MySQL master-slave monitoring implementation

Regardless of whether MySQL is that version, which is from the library of Exec_master_log_pos, Exec_master_log_pos, the Master library on the master of Log_file,position, these four parameters can determine the current master-slave state. The following is a master-slave monitoring shell script for all versions of MySQL:

　　#/bin/sh user=repl passwd=123415 master_ip= "192.168.1.2" log= "/data3/check_repl.log" value () {master= '/usr/local/ Mysql/bin/mysql-u$user-p$passwd-h$master_ip-e "Show Master STATUSG;" | Egrep "file| Position "' #mysql 4.0 slave= '/usr/local/mysql/bin/mysql-u$user-p$passwd-h127.0.0.1-e" show slave statusg; "| Egrep "relay_master_log_file| Exec_master_log_pos "' #mysql 5.0 #slave = ' mysql-u$user-p$passwd-e" show slave statusg; "| Egrep "relay_master_log_file| Exec_master_log_pos "' #取主库上的bin-log and the current log location written master_log= ' echo $master |awk ' {print $} ' |awk-f '." ' {print $} ' master_log_pos= ' echo $master |awk ' {print $4} ' #取从库上当前同步主库的位置 relay_master_log_file= ' echo $slave |awk ' {PR int ' |awk-f} '. " ' {print $} ' exec_master_log_pos= ' echo $slave |awk ' {print $4} ' echo ' Master_log: ' $Master _log>> $log Echo ' Master _log_pos: "$Master _log_pos>> $log echo" relay_master_log_file: "$Relay _master_log_file>> $log echo" exec_ Master_log_pos: "$Exec _master_log_pos>> $log} for ((I=1;i

After the mysql5.0 version, MySQL master and slave has quite mature, can only monitor the Slave_io_running,slave_sql_running,seconds_behind_master status can be, here no longer do the explanation.

Original Details Address: http://www.roncoo.com/article/detail/126291

MySQL database master and slave experience finishing