Mysql database master-slave experience _ MySQL

Mysql database master-slave experience management mysql master-slave for more than two years, management of more than 200 groups of mysql master-slave, involving almost all versions of master-slave, this blog is summative, part of which is from the network, most of them are based on their own management experiences and experiences. I have sorted out and shared them with colleagues. I hope they can help and communicate with each other.

I. mysql master-slave principle

1. Replication thread

Mysql Replication is an asynchronous Replication process (Mysql 5.1.7 and later versions are divided into two modes: asynchronous Replication and semi-synchronization), from a Mysql instace (we call it a Master) copy to another Mysql instance ). The entire replication process between the Master and Slave is mainly completed by three threads, two of which (SQL thread and IO thread) are on the Slave side, and the other (IO thread) on the Master side.

To implement Replication for MySQL, you must first enable the Binary Log (mysql-bin.xxxxxx) function on the Master side, otherwise it cannot be implemented. The whole replication process is actually because Slave obtains the log from the Master end and then executes the operations recorded in the log in full order on itself. To enable the Binary Log of MySQL, you can use the "-log-bin" parameter option during MySQL Server startup, or. the "log-bin" parameter item is added to the mysqld parameter group (the parameter section marked by [mysqld]) in the cnf configuration file.

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

2.1 .The IO thread on the Slave connects to the Master and requests the log content after the specified location (or from the beginning) of the log file;

2.2.After the Master receives a request from the Slave IO thread, the IO thread responsible for replication reads the log information after the specified log location based on the request information and returns it to the Slave IO thread. In addition to the information contained in the Log, the returned information also includes the name of the Binary Log file on the Master end and its location in the 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) at the Slave end in sequence, and record the file name and location of the bin-log on the Master end to the master-info file, so that the next read can clearly show the high-speed Master "I need to start from the location of a bin-log, please send it to me"

2.4.After the Slave SQL thread detects the newly added content in the Relay Log, it will immediately parse the content in the Log file into the executable Query statements during actual execution on the Master end, and execute these queries on your own. In this way, the same Query is actually executed on the Master and Slave ends, so the data at both ends is exactly the same.

3. Mysql replication modes

. From MySQL 5.1.12, you can use the following three modes:

-SQL statement-based replication ),

-Row-based replication (RBR ),

-Mixed-based replication (MBR ).

Correspondingly, there are three binlog formats: STATEMENT, ROW, and MIXED. In MBR mode, the default version is "SBR.

You can dynamically change the binlog format at runtime, except for the following situations:

1. intermediate storage process or trigger

2. enable NDB

3. the RBR mode is used in the current session and a temporary table is enabled.

If binlog adopts the MIXED mode, the binlog mode is automatically changed from the SBR mode to the RBR mode in the following situations:

1. when the DML statement updates an NDB table

2. when the function contains UUID ()

When 3.2 or more tables with AUTO_INCREMENT fields are updated

4. when you execute any insert delayed statement

5. when using UDF

6. when RBR must be used in a view, for example, the UUID () function is used to create a view.

. Set the master-slave replication mode:

Log-bin = mysql-bin # binlog_format = "STATEMENT" # binlog_format = "ROW" binlog_format = "MIXED" can also dynamically modify the binlog format at runtime. 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 ';

Log-bin = mysql-bin # Binlog_format = "STATEMENT" # Binlog_format = "ROW" Binlog_format = "MIXED" You can also dynamically modify the binlog format at runtime. 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. Advantages and disadvantages of the two modes:

Advantages of SBR:

Long history and mature skills

The binlog file is small.

Binlog contains all database modification information, which can be used to audit database security and other information.

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

The master/slave versions can be different. the slave server version can be higher than the master server version.

Disadvantages of SBR:

Not all UPDATE statements can be copied, especially when there are uncertain operations.

Copying a UDF with uncertainties may also cause problems.

Statements that use the following functions cannot be copied:

* LOAD_FILE ()

* UUID ()

* USER ()

* FOUND_ROWS ()

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

INSERT... SELECT will generate more row-level locks than RBR

When a full table scan (index is not used in the WHERE statement) is performed for replication, more row-level locks are required than RBR requests.

For InnoDB tables with AUTO_INCREMENT fields, the INSERT statement blocks other INSERT statements.

For some complex statements, resource consumption on the slave server is more serious, while in RBR mode, only the changed record will be affected.

When a stored function (not a stored procedure) is called, it also executes the NOW () function. this can be said to be a bad thing or a good thing.

The identified UDF must also be executed on the slave server.

Data tables must be basically the same as those on the master server. Otherwise, a replication error may occur.

Execution of complex statements consumes more resources if an error occurs.

Advantages of RBR:

It can be copied in any situation, which is the most secure and reliable for replication.

Similar to the replication skills of most other database systems

In most cases, if a table on the slave server has a primary key, replication will be much faster.

When you copy the following statements, there are fewer row locks:

* INSERT... SELECT

* INSERT containing the AUTO_INCREMENT field

* The UPDATE or DELETE statements for many records are not attached with conditions or modified.

Less locks when executing INSERT, UPDATE, and DELETE statements

It is possible to use multiple threads to execute replication on the server.

Disadvantages of RBR:

Binlog is much larger

Complex rollback binlog contains a large amount of data.

When an UPDATE statement is executed on the master server, all the changed records will be written to the binlog, while the SBR will only write once, which will lead to frequent concurrent writes of the binlog.

The large BLOB value generated by the UDF causes the replication to slow down.

What statements have been copied (encrypted) cannot be seen from binlog)

When executing a stacked SQL statement on a non-transaction table, it is best to use the SBR mode. Otherwise, it is easy to cause data inconsistency between the master and slave servers.

In addition, the processing rules for table changes in the mysql database are as follows:

If INSERT, UPDATE, and DELETE are used to operate the table directly, the log format is recorded according to the binlog_format setting.

If you use management statements such as GRANT, REVOKE, and set password, you can record them in the SBR mode in any case.

Note: The RBR mode can be used to handle many primary key duplication problems. Instance:

For the insert into db_allot_ids select * from db_allot_ids 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=1244793942/*!*/;insert into db_allot_ids select * from db_allot_ids/*!*/;

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:

BINLOG log information is:

--------------

BINLOG 'hA0yShMBAAAAMwAAAOAAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRzAAIBAwAAhA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA='/*!*/;

Binlog' Bytes Ha0yshcbaaaanqaaabubaaaqaa8aaaaaeaav/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA = '/*! */;

--------------

4. Advantages and disadvantages of Mysql master/slave

MySQL master-slave synchronization is a mature architecture with the following advantages: ① the query can be performed on the slave server (that is, the read function we often call) to reduce the pressure on the master server; ② back up data from the master server to avoid affecting the services of the master server during the backup process; ③ When the master server encounters problems, you can switch to the slave server. Therefore, this solution is often used in project deployment and implementation. In view of the rigor of mysql in the production environment.

In fact, in the old version, MySQL replication implementation on the Slave end is not completed by collaboration between the SQL thread and the IO thread, instead, a single thread is used to complete all the work. However, MySQL engineers quickly discovered that there were great risks and performance problems, mainly as follows:

First, if you use a single thread to independently implement this operation, you can copy the Master end, Binary Log, and parse the logs, then, the execution of this process becomes a serial process, and the performance will naturally be greatly limited. the Replication delay in this architecture is naturally longer.

Second, after the replication thread on the Slave side obtains the Binary Log from the Master side, it needs to parse the content, restore it to the original Query executed by the Master side, and then execute it on its own. In this process, the Master end may have produced a lot of changes and generated a lot of Binary Log information. If the Master-side storage system encounters an irreparable fault at this stage, all changes made at this stage will be lost forever and cannot be recovered. This potential risk is especially prominent when the Slave side is under great pressure, because if the Slave is under great pressure, it takes longer to parse logs and apply these logs, more data may be lost.

Therefore, in the later transformation, in order to minimize this risk and improve the replication performance, the new version of MySQL will replace the replication on the Slave end with two threads, that is, the SQL thread and IO thread mentioned above. Yahoo! was the first to propose this improvement solution! Jeremy Zawodny, an engineer in the project ". This transformation not only solves the performance problem to a large extent, shortens the asynchronous delay time, but also reduces the potential data loss.

Of course, even after two threads are switched to the current mode for collaborative processing, there is still the possibility of Slave data latency and data loss. after all, this replication is asynchronous. As long as data changes are not in a transaction, these problems exist.

To completely avoid these problems, you can only use the MySQL Cluster to solve them. However, when I write this part of MySQL Cluster, it is still a memory database solution, that is, to Load all data, including indexes, to the memory, in this way, the memory requirements are very large, and the implementation of general popular applications is not too large. MySQL is constantly improving its Cluster implementation. one of the major changes is to allow data not all to be loaded into the memory, but only to Load all the indexes to the memory, I believe that after the transformation, the MySQL Cluster will be more popular and more feasible.

5. Mysql semi-sync mode (Semisynchronous Replication)

We know that before MySQL 5.5, MySQL replication was actually an asynchronous operation, rather than synchronization, which means that the data between the master and slave nodes was allowed to have a certain delay, the original design of mysql may also be based on availability considerations. in order to ensure that the master is not affected by the slave, and asynchronous replication puts the master in an optimal state of performance: after writing the binlog, you can submit it without waiting for the slave operation to complete. This poses a risk. when you use slave as a backup, if the master fails, some committed transactions may fail to be transmitted to slave, which means data is lost!

In MySQL, the Semi-synchronous Replication mode (Semi-synchronous Replication) is introduced to prevent the above data loss risks. In this mode, the master will wait until the binlog is successfully transferred and at least one slave relay log is written. Otherwise, the master will wait until timeout (10 s by default ). When a timeout occurs, the master automatically switches the semi-sync mode to asynchronous mode until at least one slave receives and sends Acknowledge, and the master switches back to the semi-sync mode. With this new function, we can ensure the absolute security of synchronization data on the premise that the transaction throughput is allowed to be lost, because when you set timeout to a value large enough, any submitted data is securely sent to slave.

Mysql5.5 supports Semisynchronous Replication, but not native. it is supported by plugin and is not installed by default. This plug-in is generated by default for both binary release and source code compilation. one is for the master node and the other is for the slave node. you need to install these two plug-ins before use.

II. filtering of Mysql master-slave replication

There are two filtering methods for replication:

1. filter events out from binary logs on the master server. the related parameters are binlog_do_db and binlog_ignore_db.

2. filter events from the relay log on the slave server. the related parameter is replicate _*.

Replication only supports extended reading, but does not support extended writing. you can perform extended writing on data partitions.

Replication optimization:

In the mysql replication environment, there are eight parameters for us to control. we need to copy or ignore the databases or tables that are not copied as follows:

The following two items must be set on the Master:

Binlog_Do_DB: set which databases need to record Binlog

Binlog_Ignore_DB: set where the database does not need to record Binlog

The advantage is that the Binlog record on the Master end reduces the I/O volume and network I/O, and reduces the I/O threads and SQL threads on the slave end, greatly improving the replication performance,

The disadvantage is that mysql does not determine whether to copy an event based on the DB where the query that generates the event is located, instead, it is based on the default database (that is, the database name specified during logon or the database specified in "use database") where the query is executed ), the IO thread will read the event to the slave IO thread only when the default DB is exactly the same as the DB set in the configuration. therefore, if the data in a Table in the database to be copied is changed when the default database and the database to be copied are different, the event will not be copied to Slave, in this way, the Slave data is inconsistent with the Master data. similarly, if the data in the database that does not need to be copied is changed under the default database, it will be copied to the slave end. when the slave end does not have the database, this will cause a replication error and stop.

The following six items must be set on slave:

Replicate_Do_DB: specifies the database to be copied. multiple databases are separated by commas (,).

Replicate_Ignore_DB: sets the databases that can be ignored.

Replicate_Do_Table: specifies the Table to be copied.

Replicate_Ignore_Table: sets the Table that can be ignored.

Replicate_Wild_Do_Table: The function is the same as Replicate_Do_Table, but can be set with wildcards.

Replicate_Wild_Ignore_Table: The function is the same as Replicate_Do_Table. the function is the same as Replicate_Ignore_Table and can contain wildcards.

The advantage is that the replication filtering mechanism is set on the slave side to ensure that the data inconsistency between the Slave and Master is not caused by the default database problems or the replication error occurs.

The disadvantage is that the performance is worse than that on the Master side. the reason is: no matter whether replication is required or not, the event will be read by the IO thread to the Slave end. This not only increases the network IO volume, but also increases the Relay Log write volume for the IO thread on the Slave end.

Note: in actual production applications, we found that in mysql versions earlier than mysql, the filtering settings of mysql are almost useless: whether you ignore a database or table in the master database or slave database, it will still be synchronized. Therefore, when performing master-slave synchronization for versions earlier than 5.0, you must maintain the consistency of the master-slave database, some databases or tables on the master database must be available from the top. otherwise, errors may occur during synchronization.

III. configuration of Mysql master-slave synchronization

Master Database IP address: 192.168.1.2

Slave Database IP: 192.168.1.3

Add a user for master-slave synchronization:

Grant replication slave on *. * TO 'repl' @ '%' identified by '1q2w3e4r ';

If you are monitoring mysql master/slave, add a super permission:

Grant super, replication slave on *. * TO 'repl' @ '%' identified by '1q2w3e4r ';

1. master database configuration

Configuration of versions earlier than 1.1.mysql5.0

Modify the mysql configuration file of the master database and add the following content in the [mysqld] section:

server-id = 1log-bin=/home/mysql/logs/binlog/bin-logmax_binlog_size = 500Mbinlog_cache_size = 128Kbinlog-do-db = adbbinlog-ignore-db = mysqllog-slave-updates

Server-id = 1 Log-bin =/home/mysql/logs/binlog/bin-log Max_binlog_size = 500 M Binlog_cache_size = 128 K Binlog-do-db = adb Binlog-ignore-db = mysql Log-slave-updates

1.2. configuration of MySQL or later versions

Modify the mysql configuration file of the master database and add the following content in the [mysqld] section:

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"

Server-id = 1 Log-bin =/home/mysql/logs/binlog/bin-log Max_binlog_size = 500 M Binlog_cache_size = 128 K Binlog-do-db = adb Binlog-ignore-db = mysql Log-slave-updates Expire_logs_day = 2 Binlog_format = "MIXED"

1. 3. meanings of parameters and related precautions:

Server-id = 1 # server flag number. Note that there cannot be multiple such identifiers in the configuration file. if there are multiple identifiers, take the first one as the standard, in a group of Masters, this ID number cannot be repeated.

Log-bin =/home/mysql/logs/binlog/bin-log # enable bin-log and specify the file directory and file name prefix.

Max_binlog_size = 500 M # maximum size of each bin-log. when the size is M, a new log file is automatically generated. A record is not written in two log files, so sometimes the log file size exceeds this limit.

Binlog_cache_size = 128 K # log cache size

Binlog-do-db = adb # Name of the database to be synchronized. if there are multiple database names, write a row in this format.

Binlog-ignore-db = mysql # Name of the database that does not need to be synchronized. if there are multiple database names, write a row in this format.

Log-slave-updates # When Slave reads logs from the Master database, it is updated and written to the log, if only log-bin is started, but log-slave-updates is not started, Slave only records updates for operations on its own database.

Expire_logs_day = 2 # set the number of days for storing bin-log files. this parameter is not supported by MySQL or earlier versions.

Binlog_format = "MIXED" # set the bin-log file format to MIXED to prevent duplicate primary keys.

2. slave database configuration

Versions earlier than 2.1.mysql5.1.7

Modify the mysql configuration file of the slave database and add the following content in the [mysqld] section:

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

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,100 8, 1053,106 2, 1213,115 8, 1159 Master-info-file =/home/mysql/logs/master. info Relay-log =/home/mysql/logs/relay-bin Relay-log-index =/home/mysql/logs/relay-bin. index Relay-log-info-file =/home/mysql/logs/relay-log. info

If you modify the information related to the connection to the master database, you must delete the master.info file before restarting. Otherwise, the synchronization fails because the connection information changes from the slave database and the master database is not automatically connected. This file stores information about the connected master database.

2.2.mysql5.1.7 or later

MySQL 5.1.7 is rarely configured on the cluster database. it mainly adopts a new synchronous information record method. it does not support configuring related information to connect to the master database in the configuration file, the connection and other related information are recorded in the master-info-file =/home/mysql/logs/master.info file, you can change the connection information directly on the mysql command line to take effect, which is more flexible, instead of restarting mysql. Modify the configuration file of the slave mysql database and add the following content in the [mysqld] Section: slave-skip-errors = 1007,1008, 1053,1062, 1213,1158, 1159

2.3. meanings of parameters and related precautions

Here we will only talk about two parameters. the other parameters are the information for connecting the master database from the slave database and the relay-log settings of the intermediate log.

Master-connect-retry = 30 # this option controls the retry interval. the default value is 60 seconds.

Slave-skip-errors = 1007,1008, 1053,1062, 1213,1158, 1159 # This is an error that is ignored during synchronization. these errors do not affect data integrity and may occur frequently, general settings are ignored. 1062 indicates the primary key repetition error.

3. implement master-slave synchronization

. Database unification

Check the configuration file of the master-slave database to check whether the configuration is correct. For the first synchronization, you need to back up the database to be synchronized on the master database, and then import the database to the slave database. Note: mysql versions earlier than mysql involve replication and filtering issues. you need to back up all databases and import them to the cluster database to maintain them.

3. 2. view and record the bin-log information of the master database

Go to the mysql master database and run: show master status. the following information is displayed:

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. execute the synchronization statement on the slave database

Go to 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;

Slave stop; Change master Master_host = '1970. 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 the master-slave synchronization status

Log on to mysql and run show slave status/G. the following information is displayed: (the query results vary with mysql versions, but the important indicators are the same ):

Versions earlier than Mysql5.0 are as follows:

Versions earlier than Mysql5.5 are as follows:

The version of Mysql5.5 is as follows:

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 (this option is not available for versions earlier than 5.0)

The above options correspond to each other. if the results are consistent, the master-slave synchronization is successful.

3. 5. common errors and handling in synchronization

1. symptom: show slave status/G on the slave database; the following situations occur,

Slave_IO_Running: Yes

Slave_ SQL _Running: No

Seconds_Behind_Master: NULL

Cause:

A. The program may write data on slave;

B. It may also be caused by transaction rollback after the server load balancer instance restarts;

C. It may be that an error occurs during synchronization. this will cause an error when you view the status of the Slave Database. The most common error is that the primary key repeats the error 1062.

Solution:

Enter master

Mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| Mysql-bin.000040 | 324 | adb | mysql |

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

Then execute Manual synchronization on the slave server.

slave 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;

Slave stop; Change master 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 status/G;

2. symptom: The slave Database cannot be synchronized. The show slave status displays:

Slave_IO_Running: No          Slave_SQL_Running: Yes          Seconds_Behind_Master: NULL

Slave_IO_Running: No Slave_ SQL _Running: Yes Seconds_Behind_Master: NULL

Solution: First, check the database's err log, check the error message, and check whether the IP address, user, password, and other information related to the connection from the database to the master database is incorrect. If yes, re-execute the synchronization; if no error is found, restart the primary database.

Mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

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

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

Go to the slave database mysql and execute:

slave stop;change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;slave start;

Slave stop; Change master to Master_Log_File = 'MySQL-bin.000001 ', Master_Log_Pos = 98; Slave start;

Or:

stop slave;set global sql_slave_skip_counter =1;start slave;

Stop slave; Set global SQL _slave_skip_counter = 1; Start slave;

This problem mainly occurs when the master database is connected due to incorrect information about the master database, failure of the master database, and common errors, in actual operations, I need to restart the master and then restart the slave to solve this problem. in this case, you must restart the master database.