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 ThreadMySQL'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. The Binary Log for MySQL can be opened by using the "-log-bin" parameter option in the process of starting MySQL Server, or by adding "Log-bi" in the mysqld parameter group in the MY.CNF configuration file (the Parameters section after the [mysqld] identity) N "parameter entry.
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.After 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.After the Slave IO thread receives the information, it 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 that the content in the Relay log is newly added, 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, you can do this in the following three modes: – 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. NDB3 is enabled. The current session tries RBR mode, and the temporary table is open if Binlog takes MIXED mode, In the following cases, the Binlog mode is automatically changed from SBR mode to RBR mode: 1. When a DML statement updates a NDB table 2. When the function contains a UUID (), 3.2 and more tables containing auto_increment fields are updated 4. Line any INSERT DELAYED statement 5. When using UDF 6. When RBR must be required in a view, for example, establishing a view is using 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 BINL Og_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:sbr : A long history, mature skills Binlog file Small binlog contains all the database modification information, which can be used to audit the security of the database, etc. binlog can use in real-time restore, And not only for the copy master and slave versions can be different, from the server version can be higher than the main server version sbr disadvantage: Not all UPDATE statements can be copied, especially when the operation is indeterminate. When calling a UDF with an indeterminate factor, replication can also be problematic. Statements that use the following functions cannot be copied: * Load_file () * UUID () * USER () * found_rows () * sysdate () (unless enabled at startup – Sysdate-is-now option) insert The SELECT produces more row-level locks than RBR, which requires a full-table scan (which is not applied to the index in the WHERE statement), and the INSERT statement will block the InnoDB table with auto_increment fields. Plug other INSERT statements for some complex statements, the consumption of resources from the server will be more serious, and in RBR mode, only the change of the record will affect the storage function (not the storage process) is called at the same time will also execute the now () function, This could be a bad thing, or maybe a good thing. The UDF that is determined must also be executed on the server from a data table that is almost identical to the primary server, which may cause replication errors to execute complex statements If an error occurs, it consumes more resources RBR Advantage: Any situation can be replicated, which is the most secure for replication and the replication skills of most other database systems. In most cases, if the table has a primary key from the server, the replication will be much faster. There are fewer row locks when copying the following statements: * INSERT ... select* the insert* containing the Auto_increment field does not have a condition or the UPDATE or DELETE statement that does not modify many records is executed Insert,update,delete When a statement is less locked from the server using multithreading to perform replication is possible rbr disadvantage:binlog a lot of complex rollback when binlog will contain a lot of data when the UPDATE statement is executed on the primary server, all the changed records are written to Binlog, and SBR will only write once, which causes frequent binlog of concurrent write queries UThe large BLOB value produced by the df causes the replication to slow and cannot be seen from the binlog. What statements are copied (encrypted) when executing a stack of SQL statements on a non-transactional table, it is best to use SBR mode, otherwise it is very easy to cause the data inconsistency of the master-slave server to occur in addition, For the system library MySQL inside the table changes when the processing criteria are as follows: If the use of Insert,update,delete direct operation of the table, the log format according to the Binlog_format settings and records if the use of Grant,revoke,set PASSWORD such as 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. Example: For INSERT INTO Db_allot_ids SELECT * from Db_allot_ids this statement: in binlog_format=statement mode: 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=0set TIMESTAMP=12 44793942/*!*/;insert to Db_allot_ids select * from db_allot_ids/*!*/; ————————————— – in Binlog_format=row mode: BINLOG log information is: ————————————— –binlog ' ha0yshmbaaaamwaaaoaaaaaaaa8aaaaaaaaaa1nouwamzgjfywxsb3rfawrzaaibawaaha0yshcbaaaanqaaabubaaaqaa8aaaaaaaeaav/ 8aqeaaad8aqeaaad8aqeaaad8aqeaaaa= '/*!*/; ————————————— –
4, MySQL master-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, if you do this work independently through a single thread, copy the Binary log logs from the Master side and parse the logs. And then in the process of self-execution become a serial process, the performance will naturally be subject to a large limit, the structure of the Replication delay 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 MySQL Cluster know when I write this part of the content, still is a memory database solution, that is, all the data needs to be included in the index Load into memory, so that the memory requirements are very large, for the general popularity of the application is not too big 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 filteringThere are 2 ways to filter replication: 1, 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. Replication optimizations: In MySQL replication environment, there are 8 parameters that let us control, need to replicate or need to ignore the DB or table that does not replicate: The following two items need to be set on master: binlog_do_db: Set which databases need to be recorded binlogbinlog_ignore_db: set where the database does not need to log binlog The advantage is that the Binlog record on the master side reduces the IO volume, the network IO decreases, and the slave side of the IO thread, the SQL thread is reduced , which greatly improves replication performance, the drawback 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 where 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: Set the database to be replicated, multiple DB separated by commas replicate_ignore_db: Set the database that can be ignored. Replicate_do_table: Set the tablereplicate_ignore_table to be copied: Set the tablereplicate_wild_do_table that can be ignored: function with Replicate_do_ Table, but you can set it with a wildcard character. Replicate_wild_ignore_table: function with replicate_do_table, function with replicate_ignore_table, can carry wildcard characters. The advantage is to set the replication filtering mechanism on the slave side, which guarantees that the slave and MAS will not occur because of the default database problems.ter data inconsistency or replication error. The disadvantage is that the performance is worse than the master end. The reason is that the event is read by the IO thread to the slave side, whether or not it is required, which not only increases the network IO, but also increases the IO thread of the slave side relay The amount of log writes. 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 configurationMain Library ip:192.168.1.2 Add a user for master-slave synchronization from the library ip:192.168.1.3: GRANT REPLICATION SLAVE on * * to ' repl ' @ '% ' identified by ' 1q2w3e4r '; If you monitor MySQL master and slave, 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 versionsModify the main library MySQL configuration profile and add the following in the [Mysqld] segment: Server-id = 1log-bin=/home/mysql/logs/binlog/bin-logmax_binlog_size = 500mbinlog_ Cache_size = 128kbinlog-do-db = Adbbinlog-ignore-db = Mysqllog-slave-updates
1.2. Configuration of mysql5.0 or laterModify the main library MySQL configuration profile and add the following in the [Mysqld] segment: Server-id = 1log-bin=/home/mysql/logs/binlog/bin-logmax_binlog_size = 500mbinlog_ Cache_size = 128kbinlog-do-db = Adbbinlog-ignore-db = mysqllog-slave-updatesexpire_logs_day=2binlog_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 multiple, 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 VersionsModify the MySQL configuration profile from the library, and add the following in the [Mysqld] segment: server-id=2master-host=192.168.1.2master-user=replmaster-password= 1q2w3e4rmaster-port=3306master-connect-retry=30slave-skip-errors=1062replicate-do-db = Adbreplicate-ignore-db = Mysqlslave-skip-errors=1007,1008,1053,1062,1213,1158,1159master-info-file =/home/mysql/logs/ Master.inforelay-log =/home/mysql/logs/relay-binrelay-log-index =/home/mysql/logs/ Relay-bin.indexrelay-log-info-file =/home/mysql/logs/ Relay-log.info If you modify the connection main library related information, must delete the Master.info 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 versionsMysql5.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/ Master.info file, if the storage changes, directly on 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 notesHere 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 databaseCheck 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 informationGo to 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) records file and position information;
3.3. Executing the synchronization statement from the libraryEnter MySQL and execute the following statement: Slave stop;change master tomaster_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 statusGo to MySQL and execute show slave status\g; The results are as follows (MySQL versions differ from one query to another, but the important indicators are the same): the previous version of Mysql5.0 is as follows:
The previous versions of Mysql5.5 are as follows:
The Mysql5.5 version is as follows:
The important indicators are: Slave_io_running:yesslave_sql_running:yesmaster_log_file:bin-log.003relay_master_log_file: Bin-log.003read_master_log_pos:4exec_master_log_pos:4seconds_behind_master: 0 (prior to 5.0 version does not have this option) the above options are 22 corresponding, as long as the results are consistent, the master-slave synchronization is successful.
3.5. Common errors and handling in synchronization1, the phenomenon: show slave status\g from the top of the library;, Slave_io_running:yes slave_sql_running:no Seconds_behind_master:null reasons: A. The program may have been 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 mastermysql> show master status;+----------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+----------------------+----------+--------------+------------------+| mysql-bin.000040 | 324 |ADB | mysql|+----------------------+----------+--------------+------------------+ and then perform manual sync slave on slave server Stop;change Master tomaster_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 status\g;2, symptom: Unable to sync from database, show slave status show: 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|+------------------+----------+--------------+------------------+ enter 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 restarting the slave can solve this problem, this problem occurs, You must restart the master database.
Iv. MySQL Primary and primary master cluster
1, the realization of the main master of MySQLIn 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 monitoringIn 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 ideasMySQL master-slave monitoring, which is mainly to monitor some important parameters from the library: Slave_io_running:yesslave_sql_running:yesmaster_log_file:bin-log.003relay_master_ Log_file:bin-log.003read_master_log_pos:4exec_master_log_pos:4seconds_behind_master: 0 (prior to 5.0 version 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 Whether MySQL is that version, which is from the library of Exec_master_log_pos, Exec_master_log_pos, master on the main library on the log_file, position, These four parameters can be used to determine the current master-slave state. Below is a master-slave monitoring shell script for all versions of MySQL: #/bin/shuser=replpasswd=123415master_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 status\g;" | Egrep "file| Position "' #mysql 4.0 slave= '/usr/local/mysql/bin/mysql-u$user-p$passwd-h127.0.0.1-e" show slave status\ G; "| Egrep "relay_master_log_file| Exec_master_log_pos "' #mysql 5.0 #slave = ' mysql-u$user-p$passwd-e" show slave status\g; "| Egrep "relay_master_log_file| Exec_master_log_pos "' #取主库上的bin-log and the current log location written master_log= ' echo $master |awk ' {print $ 2} ' |awk-f '. " ' {print $} ' master_log_pos= ' echo $master |awk ' {print $4} ' #取从库上当前同步主库的位置 relay_master_log_file= ' echo $slave |awk ' {print $} ' |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<=10;i++));d O echo "#################################" >> $log value time= ' date + "%Y- %m-%d%h:%m:%s "' if [$Master _log-eq $Relay _master_log_file];then A= ' expr $ master_log_pos-$Exec _master_log_pos ' if [$A-lt 0];then a= ' expr 0-$A ' fi echo $A >> $log if [$A-lt 10000];then & nbsp; echo "$time Master-slave is OK." >> $log #echo "$i" break Else if [$i GE 3];then echo "$time warning:slave-master lag $A" >> $log echo "$i" fi Sleep 30 continue fi else Sleep 60 fi If [$i-eq];then echo "$i" echo "$time error:slave-master must be check!" >> $log Fidone after the mysql5.0 version, MySQL master and slave has been quite mature, can only monitor Slave_io_running,slave_sql_running,seconds_behind_ The master state is all right, and this is no longer a description.
MySQL master-slave copy of an article (reprinted)
