day7-Profiling a replication thread

High Availability:
Two-center concept
Same city: With IDC: Enhanced semi-synchronous replication
Cross-City: Asynchronous replication

The third center of the two places: (Financial payment Bank)
The same city: cross-IDC, enhanced semi-synchronous
Cross-City: Asynchronous replication

Traditional replication Thread parsing
Gtid principle
Semi-synchronous replication thread resolution
Concurrent replication
Deferred replication implementation
Multi-source Replication
MySQL-5.7 Enhancements in replication threading

Master-Slave data validation:
Pt-table-checksum

GTID:
MySQL 5.6 Switch from non-gtid to Gtid, requires reboot
MySQL 5.7 Support Online enable Gtid
Switching process:
Gtid_modd=
1, master and slave all off_permissive.
Set global gtid_mode=off_permissive;
2, from the library on_permissive, the main library on_permissive
Set Global gtid_mode=on_permissive
3, confirmed that the entire group of binlog non-Gtid execution completed
4. Main library on, from library on
Set global gtid_modd=on;

MySQL 5.7 Gtid will be stored in the table.
Support slave without opening binlog (log_slave_updates)
mysql.gtid_executed

How to record Gtid
If Binlog is turned on, the current gtid is inserted into the gtid_executed table when the binlog switch.
If Binlog is not turned on, an insert is executed before each transaction commits

gtid_executed Table Compression
Control compression Frequency
Set global gtid_executed_compression_period=n; (default 1000)

Enforce-gtid-consistency
OFF: does not detect if there are statements/transactions that are not supported by Gtid
Warn: When discovery does not support statements/transactions, returns a warning and logs a warning message
On: Error returned when Discovery statement/transaction does not support Gtid

TIPS:
In the reality from the non-Gtid switching process, you can first set to: Warn

GTID Limit:
You cannot use the CREATE TABLE TB1 select * from TB2;
Can be changed to: Create table tb1 like TB2; INSERT INTO TB1 select * from TB2;
Updating non-transactional tables in a transaction:
Begin:update No_trx_tabe set col1 =xxx where xxx; Update TRX_TB set col1=xxx where ... commit;
Create/delete temporary tables in a transaction
Begin:update TRX_TB set xxx;create temporary table ...; Commit
Sql_slave_skip_counter not supported

Gtid Skip Error:

Stop slave;
SET @ @SESSION. gtid_next= ' 01eea86a-5da6-11e8-a709-c03fd5486d6f:282 ';
Begin;commit;
Set gtid_next= "Automactic"

Semi-synchronous replication:

The commit transaction occurs after master receives an ACK acknowledgement that slave has completed the transaction (5.6 is master after commit and waits for slave to answer)
Therefore, the concurrent transaction cannot see the data of the current transaction before the transaction is replicated to slave
When Master fails, all committed transactions are copied to slave
Default with no data loss response waiting mechanism, the user can choose to use 5.6 of the answer to
Set Rpl_semi_sync_master_wait_point=after_sync|after_commit

If the response time-out rpl_semi_sync_master_timeout (time-out), it is automatically degraded to asynchronous replication
Fix will automatically upgrade to semi-sync

Enhanced semi-synchronous:
5.7 After default: (production using enhanced semi-synchronous no problem)
Main Library write Binlog, transfer to from library, return ACK from library, main library commit; better performance
Set Rpl_semi_sync_master_wait_point=after_sync;
Create a separate answer receive thread
Become duplex default, send and receive do not affect each other

Master receives n Slave responses before commit transaction
Users can set the number of slave to answer
Set global rpl_semi_sync_master_wait_for_slave_count=2; (there are two slave corresponding re-commits;)

Special tips:
Less than 5.7 version, after a period of time from the library shut down, just start, pay attention to the asynchronous replication, replication after, and then semi-synchronous replication
Master
Set Global rpl_semi_sync_master_enabled=on| OFF;
Slave
Set Global rpl_semi_sync_master_enabled=on| OFF; (Confirm main Library on, then slave on)

Enhanced semi-synchronous Master Crash Recovery
1. Scan the last Binlog to extract the XID
2, Scan InnoDB maintenance status (Redo log) in the Prepare Transaction list, and Binlog XID comparison, if the binlog exists, then commit, no rollback.
Commits to InnoDB in prepare, and writes Binlog, the transaction can be resumed from the crash.

Concurrent replication
MySQL 5.6 Concurrent replication is based on the library level (because production is a multi-instance + library architecture, so the basic useless)

Essentially, you need:
Allow concurrent execution of transactions on master and concurrently on slave
Record information about transaction concurrency execution in Binlog
Slave on the basis of this information, so that the food in the slave on multiple threads concurrently execute

Concurrent replication of MySQL 5.7 is based on transaction level (logical Clock)

Lock-based parallel replication
The concurrency information is written in logical-time gtid_log_event with a total of 2 logical times:
Sequence_number
When a transaction is written to Binlog, the transaction's own logical time is incremented in the order in which the transaction records Binlog
Commit_parent
The maximum logical time for a transaction that has been committed when the food is in the prepare phase
Transaction execution in Slave
Commit_parent before the transaction has been committed before the execution

Parallel replication:
Preferably configure Binlog Group commit
Binlog_group_commit_sync_delay=20-300 Subtlety: Time
binlog_group_commit_sync_no_delay_count=10-30: Number of transactions

Deferred replication
To keep a fixed time delay from the library and the main library (Sql_thread)
Usage Scenarios
Using delayed replication to perform misoperation recovery
Using delayed replication to do statistical analysis environment processing
Stop slave Sql_thread
Change Master to Master_delay=n; #N单位是S

Multi-source Replication
Multiple m corresponds to one s
Scene:
Centralized backup
Data Analysis Aggregation
Shard Data Merging

Multiple channels (channel includes: Recevier thread,relay logs,applier threads) Each channel can run and stop independently
Status monitoring via the p_s table:
The Channel_name field is added to the following table, and information for different channel is displayed in different lines
Replication_applier_status_by_coordinator
Replication_applier_status_by_work
Replication_connection_status
Show Slave Status\g

Group replication
The essence is:
Binary log
Based on row format +gtid
A communication framework + transaction sequencing control
Enhanced semi-synchronous & parallel replication

is a trend
Backup's not good.
Xtrabackup backup, resulting in a serious loss of performance
The new node joining process has an impact on the original cluster performance

Practice:
1, enhanced semi-synchronous replication, semi-synchronous replication, asynchronous replication differences
2, test master-slave data check, pt-table-checksum

day7-Profiling a replication thread