1. How to solve the problem of master-slave replication delay? (1) Principle of master-slave copying
Http://www.cnblogs.com/jenvid/p/8410922.html
- 1.salve execute slave start,salve service IO thread will connect to master via authorized user and request master to send Bin-log log content after the specified file and location
- After the 2.master server receives an IO thread request from the slave server, the IO thread on the master server sends the contents of the specified Bin-log log according to the slave server and returns the IO thread to slave. The returned information is out of the Bin-log log content wait, and this time the return log content on the master server side of the new Binlog file name is already in Binlog the next specified update bit
- After the 3.slave IO thread receives the information, the received log content is added to the end of the Relay-log file on the salve side, and the file name and location of the Bin-log read to the master side are recorded in the Master-info file. So that the next time you read it, you can clearly tell master "I need to start from somewhere in the Bin-log log content, please send Me",
- The 4.slave SQL thread detects that new content in Relay-log will immediately parse the contents of Relay-log into the executable content of the real execution of the master side and execute it on its own.
(2) Causes of delay
- 1. Master-slave replication is a single-threaded operation, asynchronous
- The Binlog of 2.master is sequential read-write, high efficiency, high throughput, DDL/DML is concurrent operation
- When the 3.slave Relay-log is implemented, DML and DLL are randomly operated and the slave_sql_running is single-threaded, and if there is lock contention, the cost is higher.
- 4.master TPS When concurrency peaks, the number of DDL generated exceeds the range that slave a SQL thread can withstand, and delays produce
- 5.
max_allowed_packet Master-Slave inconsistency
- 6. Self-increment ID starting position is not the same
- 7. The main height is inconsistent from the low version, the new feature is incompatible
(3) Resolve latency issues
- 1. The main library DDL performs DDL operations quickly
- 2. Main Library Settings
双1 ,sync_binlog=1 innodb_flush_log_at_trx_commit=1
- 3. From the library can be set to
双0
- 4.
slave_net_timeout slave How long it will take to reestablish the connection and get the data after it fails to read the log data
- 5.
master-connect-retry when a master-slave connection is re-established, the retry wait time if the connection fails
- 6. Use better hardware from the library
- 7. Dedicated slave server, dedicated read server, dedicated backup server
- 8. Using the Semi_sync plugin for semi-synchronization, loss of performance
2. How to determine whether master-slave replication is synchronized? (1) Show slave status\g
Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0
Seconds_behind_master is the difference between the timestamp of the event performed by the comparison Sql_thread and the timestamp (abbreviated TS) of the Io_thread copy good event.
(2) if
Relay_master_Log_File==
Master_Log_File
Read_Master_Log_Pos= = Exec_Master_Log_Pos , it is considered to be synchronous
The main library is show master status\G Position equal to the slave library and Read_Master_Log_Pos is also synchronized
(3) Use of third-party tools
Mk-heartbeat,maatkit
3. The following parameter is set to 1 how to understand?
Set global sql_slave_skip_counter=1;
Https://dev.mysql.com/doc/refman/5.6/en/set-global-sql-slave-skip-counter.html
- (1) From the library need to skip an unenforceable command, set in the state of slave stop
global sql_slave_skip_counter=N , skip the next n events
An event group contains a number of ordered events
An insert consists of three Event,begin/insert/commit
For transactional tables, an event group represents a transaction
For non-transactional tables, an event group represents a separate SQL statement
- (2) N=1, a number of event jumps are skipped until the current transaction ends
- (3) N>1, you have to n--every event you skip.
http://dinglin.iteye.com/blog/1236330
MySQL Small white series _10 MySQL master-slave copy principle
