Author: wuXing
QQ:1226032602
E-mail:1226032602@qq.com
https://dev.mysql.com/doc/refman/5.7/en/replication-implementation-details.html
- mysql replication
- working principle
replication is a high availability solution provided by mysql.
1. The master records the data changes in the binary log
2. Request the master library to establish a connection from the slave I/O thread, obtain the binlog of the master and record it into the local relay log
3. Read and apply the relaylog from the slave sql thread
- purpose
1. Load balancing using slave database to improve the ability of reading data
2. Use the slave database as the master failover server for high availability
3. Using slave library as backup server
4. Backup of delayed data using slave database
5. mysql upgrade using replication
6. Using slave database as query server
- Copy mode
mysql5.6 start support:
Traditional replication
1. Statement based replication binlog_format = statement (SBR)
2. Copy binlog based on rows_ format = row (RBR)
3. Hybrid replication binlog_format = mixed
GTID replication
Semi synchronous replication is a technical implementation of replication security
- Row based replication
binlog_format = row
advantage:
1. Secure replication mode
2. In some cases, the replication speed is fast (the sql is complex, the table has a primary key, and the updated target record is copied directly)
3. System functions can also be copied
4. Fewer locks
Disadvantages:
1. binary log file is large
2. A single statement, such as updating, deletes too many records, which will produce a large number of binary log s
3. The specific statement content cannot be seen from the log, (binlog row query log events = 1 can add this part of information)
Note: DDL statements will still be recorded as statements
- View the order of fields in the table
mysql> select TABLE_NAME,COLUMN_NAME from columns where TABLE_SCHEMA='oldboy' limit 10; +------------+-------------+ | TABLE_NAME | COLUMN_NAME | +------------+-------------+ | backupstep | id | | backupstep | step | | student | id | | student | name | | student | age | | student | dept | | test | id | | test | name | +------------+-------------+ 8 rows in set (0.01 sec)
- mixed based replication
binlog_format = mixed
Mix the two modes
Innodb table, the transaction level is read uncommitted, and the read committed log type can only be row
Conditions for converting to row mode:
1. The storage engine of the table is NDB engine table. At this time, DML operations on the table will be recorded in row format
2. UUID (), user (), current are used_ USER(),FOUND_ROWS(),ROW_ Uncertain functions such as count()
3. Two or more with auto_ When the table of the increment field is updated
4. INSERT DELAY statement used
5. User defined functions are used
6. Temporary table used
- Convert to row mode condition
When there are grant,revoke,set password,rename user,create(all forms except create table... select),alter(all forms),and drop (all forms), the binlog will be recorded at the statement level, ignoring the system settings
The select statement and the create statement are defined as one data set. His create table part uses the statement method, while the select part uses the system defined method
- Copy common commands
show slave status\G; show master status; start slave; start slave IO_THREAD; start slave SQL_THREAD; start slave [SQL_THREAD] until master_log_file = 'log_name' master_log_pos = log_pos stop slave IO_THREAD; stop slave SQL_THREAD; reset master/slave;
- Skip error
stop slave; set global sql_slave_skip_counter=1; start slave;
- Configure master-slave synchronous execution commands from the slave Library
change master to master_host = '10.0.0.51', master_port=3306, master_user = 'replication', master_password ='slave', master_log_file = 'mysqld-bin.006572', master_log_pos=514294150;
- Basic principles of replication creation
1. A mysql slave database can only have one master database (after mysql 5.7, it can be copied from multiple sources)
2. Each master database and slave database must have a unique server id
3. A master library can have multiple slave libraries
4. If cascade replication needs to open log_slave_updates: record the relaylog from the database to the binlog for its replication from the database
- One master and many slaves
It is applicable to a small amount of writing and a large amount of reading, and can realize:
1. Set different roles for different slave Libraries
2. Take a slave database as the master database to be used, and there is no other data query except replication
3. Put one from the library to the remote data center for disaster recovery
4. Delay a standby database for disaster recovery
- Double master
Active active mode replication consists of two servers, each of which is set as the master database and slave database of the other party. Applicable to the case where both sides need to be written
Question: Conflict of self addition
auto_increment_increment=2# self increment ID interval, such as 1 3 5 interval is 2
Master A: auto_increment_offset=1 # distribution 1 3 5 7 9
Master B: auto_increment_offset=2 # distribution 2 4 6 8 10
The difference between active and passive mode is that one of them is a read-only passive server. This method makes it very convenient to switch active and passive servers repeatedly.
Failover and recovery are easy. You can maintain, optimize and upgrade the operating system without shutting down the server
Test a single master library
create database oldboy CHARACTER SET utf8 COLLATE utf8_general_ci; use oldboy; CREATE TABLE `student` ( `id` bigint(12) NOT NULL auto_increment COMMENT 'Primary key', `name` varchar(12) NOT NULL COMMENT 'full name', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
- Dual master with slave Library
- cascading replication
In cascade replication, add the log slave updates parameter, and the data copied from the slave database to the master database will be written to the log bin log file
- Master slave mysql replication configuration
- Configure replication
Preparation for replication
1. The master creates a special replication account for replication
replication slave replication client
2. master and slave have different server IDs
3. Perform a full logical backup on the master
4. Copy to the slave and perform a full recovery
5. Execute change master on the slave to set the master server information
Execute start slave on the slave to enable replication and read data from the database to the main database
- Both master and slave databases establish replication accounts
grant replication slave,replication client on *.* to repuser@'172.16.1.%' identified by '123456';
grant replication slave on *.* to rep@'10.0.0.%' identified by '123';
mysql8.0 master slave replication user
default-authentication-plugin=mysql_native_password
create user 'aaa'@'10.0.0.%' identified with mysql_native_password by '123';
- Configure master library
In the main database, you need to specify a unique server id and enable binary logging
[mysqld] server-id=1 log-bin=mysql-bin log-bin_index binlog_format=row binlog_cache_size=2M max_binlog_size=1G expire_logs_days=7 binlog-ignore-db = mysql replicate-ignore-db = mysql skip-name-resolve gtid_mode=off
[mysqld] server-id=1 log-bin=/application/mysql/mysqlbinlog/mysql-bin basedir=/application/mysql datadir=/application/mysql/data binlog-ignore-db=mysql
[mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data user=mysql socket=/usr/local/mysql/mysql.sock log_error=/var/log/mysql.log secure-file-priv=/tmp server-id=1 log_bin=/data/binlog/mysql-bin binlog_format=row sync_binlog=1 slow_query_log=1 slow_query_log_file=/data/binlog/slow.log long_query_time=0.01 log_queries_not_using_indexes
Restart to make the binary log effective. Note that if you do not explicitly set the server ID or set it to its default value of 0
The master will reject any connection from the library
It is recommended to enable
innodb_flush_log_at_trx_commit=1 sync_binlog=1 innodb_support_xa=1
- Master database creates master-slave copy account
grant replication slave on *.* to 'rep'@'172.16.1.%' identified by '123456';
- Main warehouse lock table
flush table with read lock;
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 405 | | | | +------------------+----------+--------------+------------------+-------------------+
- Export all data
mysqldump -A -B --master-data=2 --single-transaction > all.sql
mysqldump -uroot -p123456 -A -B --single-transaction --master-data=2 > /tmp/full.sql
- Unlock
unlock table;
- Configure slave Library
You need to specify a unique server id in the slave library
[mysqld] server-id=2 log-bin=mysql-bin relay-log=/directory/mysql-relay-bin relay-log-info-file=/directory/relay-log.info log-slave-updates=1 read-only=1 sync_master_info=1 sync_relay_log=1 sync_relay_log_info=1 master_info_repository=table relay-log-info-repository=table
[mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data user=mysql socket=/usr/local/mysql/mysql.sock log_error=/var/log/mysql.log secure-file-priv=/tmp server-id=2 log_bin=/data/binlog/mysql-bin binlog_format=row sync_binlog=1 slow_query_log=1 slow_query_log_file=/data/binlog/slow.log long_query_time=0.01 log_queries_not_using_indexes relay-log=/data/relaylog/mysql-relay-bin relay-log-info-file=/data/relaylog/relay-log.info
relay_log
relay_log_index
relay_log_info_file
master_info_repository
[mysqld] server-id=2 log-bin=/application/mysql/mysqlbinlog/mysql-bin log-slave-updates=1 basedir=/application/mysql datadir=/application/mysql/data relay-log=/application/mysql/mysqlrelaylog/mysql-relay-bin
[mysql] user=root password=oldboy123 [mysqladmin] user=root password=oldboy123 [mysqldump] user=root password=oldboy123
[mysqld] server-id=3 basedir=/application/mysql datadir=/application/mysql/data socket=/application/mysql/tmp/mysql.sock character-set-server=utf8 innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend log-bin=/application/mysql/mysql-bin binlog_format = 'ROW' binlog-ignore-db = mysql replicate-ignore-db = mysql skip-name-resolve slow-query-log=1 long_query_time = 2 log_queries_not_using_indexes slow_query_log_file= /application/mysql/slow.log min_examined_row_limit = 1000
- Configure master-slave synchronization in slave Library
change master to master_host='172.17.0.3', master_port=3306, master_user='rep', master_password='123456', master_log_file='mysql-bin.000006', master_log_pos=120; master_connect_retry=10;
Start master-slave copy all threads
start slave;
Start io thread
start slave io_thread;
Start SQL thread
start slave sql_thread;
- Lock table
mysql> flush table with read lock; Query OK, 0 rows affected (0.00 sec)
- Unlock
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
- Basic data source of replication
The above method is applicable to the case where the server has just been built and there is no application.
How to build a slave Library in a running master library?
1. Copy data from master library
2. Clone data from another library
3. The latest full backup starts from the library.
We need to identify three factors:
1. Data snapshot of the master database at a certain point in time
2. The binary log file of the main library and the offset in the binary file when the snapshot is taken
3. Binary file from snapshot to present
- Set up replication from primary server
Method of obtaining data:
1. Use cold backup, or flush tables with read lock; The disadvantage of copying data to the slave database is that the service needs to be stopped
2. Use hot backup. mysqldump
3. Use percona xtrabackup (innobackupex)
If the backup is obtained from the main library, you can use xtrabackup_binlog_pos_innodb gets the starting position
If the backup is obtained from the library, you can use xtrabackup_slave_info get start position
Note that cold backup and hot backup need to refer to the following from the Library:
show slave status; Get the execution location of the binary file of the current execution master library of the slave library
Disadvantages: if the slave database is not synchronized with the master database, the data obtained is dirty data
Tip: if you need to reconfigure the slave server that has been configured for replication. reset slave command initialization is required
- Copy status information file
During the replication process, the slave server will create multiple information files to record the replication status information.
1, master info log master.info
master-info-repository = table
Record to MySQL slave_ master_ info
sync-master-info = 1
2, relay log info log relay-log.info
relay-log-info-repository = table
Record to MySQL slave_ relay_ log_ info
sync_relay_log_info=1
Size by max_relay_log_size. If the value is 0, the size is determined by max_binlog_size decision
3, relay log mysqld-relay-bin.000001
relay-log=
sync_relay_log=1
The format of relay log is the same as that of binary log, which can be read with mysqlbinlog
4, relay log index
relay-log-index=
mysqld-relay-bin.index tracks the currently used relay log
- Start but not start slave
./mysql start --skip-slave-start
- Replicated applications
- Use replication as backup server
For a small amount of data, you can use mysqldump
For large amounts of data, you can use cold backup or Xtrabackup
-
Copy different databases to different slave
-
Common faults and solutions of replication
Source of error information: show slave status\G;
Phenomenon:
Slave_IO_Running:No Slave_SQL_Running:No Last_IO_Errno: Last_IO_Error: Last_SQL_Errno: Last_SQL_Error:
- Same server ID
When the master and slave are the same:
The slave io thread cannot be synchronized. show slave status
From and from the same:
The io threads of the slave server always switch between these. The two io threads of the slave library compete for the connection of the master library in turn
The master doesn't know which slave to send the binlog to. The slave doesn't seem to send the binlog to it
- Primary key conflict Error_code:1062
Check whether the records corresponding to the master are consistent with the records of the slave. If they are consistent, skip the error. If they are inconsistent, delete the records corresponding to the slave
Note: if the current high availability architecture is master master, the following operations must be performed from the library
set sql_log_bin=0 prevents statements executed from the library from being synchronized to the main library
agreement:
Mysql> stop slave; Mysql> set global sql_slave_skip_counter=1; Mysql> start slave;
atypism:
Mysql> stop slave; Mysql> set sql_log_bin=0; Mysql> delete from trends_uint where itemid=xx and clock=xxx; Mysql> start slave; Mysql> set sql_log_bin=1;
- Record does not exist Error_code:1032
delete: you can skip the error directly
update: find the corresponding record in the master and insert it into the slave according to the binlog location information
Mysql> set sql_log_bin=0; Mysql>insert into items values(...); Mysql> start slave; Mysql> set sql_log_bin=1;
Extension: if required by the business, the confirmation error can be skipped. Not recommended
[mysqld] slave-skip-errors=1062,1053,1146 slave-skip-errors=all
It is recommended to set read on the slave_ Only = 1 data cannot be modified unless you have super permission
- Table does not exist Error_code:1146
This is a serious error. It is recommended to rebuild the slave
- Replication delay
Seconds_Behind_Master:xxx
0 indicates that the master-slave replication is good, and it can be considered that there is no delay
A positive value indicates that the master-slave database has been delayed. A larger number indicates that the slave database lags behind the master database more
Cause: mysql compares SQL_ timestamp and IO of event executed by thread_ Copy the thread into the relay log
The difference obtained by comparing the timestamp of event is in seconds
be careful:
If the I/O load of the main library is heavy or the network is blocked io_thread cannot copy binlog in time (it is also copying without interruption),
And sql_thread can keep up with IO all the time_ The script of thread, then seconds_ Behind_ The value of master is 0, that is, we think there is no delay, but this situation does not correctly reflect the replication situation
You can use the Position of show master status\G on the master and the Position of show slave status\G on the slave
Read_Master_Log_Pos and Exec_Master_Log_Pos compares whether the replication can keep up with the master state
If the main reservoir pressure is normal, the network is normal. Causes of delay:
1. The load of slave silo is high and the system pressure is high
2. The SQL process is single threaded and the execution speed is slow. MsSQL5.6 start improvement
- Master Slave field definitions are different
mysql> stop slave; mysql> set global slave_type_conversions=ALL_NON_LOSSY; mysql> start slave;
The default is, that is, inconsistent master-slave field types are not supported,
The other three types are:
all_lossy supports lossy conversion, such as int - > tiny int
all_non_lossy supports lossless conversion, such as char (20) - > varchar (25)
all_lossy,all_non_lossy supports all transformations
- Field inconsistency error
1. Modify the student table structure from char20 to char40
mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table student modify column name char(40); Query OK, 6 rows affected (0.04 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec)
2.master insert record
mysql> insert into student(name,age) values('xxxxxxxxxxxxxxxxxxxx',36);
Query OK, 1 row affected (0.00 sec)
3.Slave error message
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.51
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1603
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 809
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1677
Last_Error: Column 1 of table 'oldboy.student' cannot be converted from type 'char(20)' to type 'char(40)'
Skip_Counter: 0
Exec_Master_Log_Pos: 1390
Relay_Log_Space: 1172
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1677
Last_SQL_Error: Column 1 of table 'oldboy.student' cannot be converted from type 'char(20)' to type 'char(40)'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
resolvent
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> set global slave_type_conversions=ALL_NON_LOSSY;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.51
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1603
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1603
Relay_Log_Space: 1318
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
mysql> select * from student;
+----+----------------------+-----+------+
| id | name | age | dept |
+----+----------------------+-----+------+
| 2 | oldboy | 0 | NULL |
| 4 | oldboy2 | 0 | NULL |
| 6 | andy | 36 | NULL |
| 8 | andy | 36 | NULL |
| 10 | andy2 | 36 | NULL |
| 12 | andy2 | 36 | NULL |
| 13 | xxxxxxxxxxxxxxxxxxxx | 36 | NULL |
+----+----------------------+-----+------+
7 rows in set (0.00 sec)
- slave startup error
mysql> reset slave; mysql> change master to ... mysql> start slave;
- sql thread NO
stop slave; #<== Temporary stop synchronization switch.
set globalsql_ slave_ skip_ counter = 1; #<== Move the synchronization pointer down one. If it is not synchronized for many times, the operation can be repeated.
start slave;
configuration file
slave-skip-errors = 1032,1062,1007
set global sql_slave_skip_counter=N；
This statement skips the next event of master N. This is useful for recovering from a replication stop caused by a statement
MySQL common error code and code description
http://oldboy.blog.51cto.com/2561410/1728380
- Master slave replication delay
- Problem 1: there are too many slave libraries in a master database, resulting in replication delay
It is suggested that the number of slave libraries should be 3 ~ 5. Too many slave nodes to be replicated will lead to replication delay
- Problem 2: the slave library hardware is worse than the master library, resulting in replication delay
Check the system configuration of master and slave. The replication delay may be caused by the problem of machine configuration, including disk io, CPU, memory and other factors. It usually occurs in the scenario of high concurrency and large amount of data writing
- Problem 3: too many slow SQL statements
If the execution time of an SQL statement is 20 seconds, it will be at least 20 seconds from the completion of the execution of the database to the data available from the database, which will delay 20 seconds
Question 4: Design of master-slave replication
For example, master-slave replication is a single thread. If the write concurrency of the master library is too large to be transmitted to the slave library, it will lead to delay
Higher versions of MySQL can support multi-threaded replication,
-
Read only from library
-
Monitoring of replication
Main monitoring:
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Seconds_Behind_Master:0
- binlog_row_image
When a table contains blob text fields and updates, even if these fields are not updated, they will be logged, resulting in a sharp increase in binlog. This parameter is added in 5.6. We can configure it to reduce the amount of binlog
Optional values are:
full: record all line information, which is no different from the previous one
nolob: records all fields except BLOB and TEXT
minimal: binlog only records the column to be modified
be careful:
When binlog_ When format = statement, it does not work;
=row perfect support;
=mixed is determined according to the actual situation;
- mysql master-slave architecture in-depth
php encryption for ordinary people
http://justcoding.iteye.com/blog/2120329?utm_source=tuicool
- Semi synchronous replication
mysql5. Before 5, replication was actually asynchronous rather than synchronous, which means that there is a certain delay in the data between the master and slave. Asynchronous replication makes the master in a state of optimal performance: after writing binlog, it can be submitted without waiting for the slave operation to complete
Disadvantages: when you use a slave as a backup, if the master hangs up, there is a possibility that some committed transactions may not be successfully transferred to the slave, which means data loss!
The master will not submit until the binlog is successfully transmitted and written to the relay log of at least one slave. Otherwise, it will wait until timeout (10s by default). When timeout occurs, the master will automatically switch from semi synchronous to asynchronous until at least one slave confirms that it has received the replication event, and then the master will switch back to semi synchronous mode. Combined with this new function, we can ensure the absolute safety of synchronous data on the premise of allowing a certain amount of transaction swallowing to be lost, Because when you set the timeout to a large enough value, any submitted data will arrive in the slave safely
mysql5. Version 5 supports semi synchronous replication, but it is not native. It is supported through plugin, and the plug-in is not installed by default
This plug-in will be generated by default whether it is published in binary or compiled by your own source code. One is for master and the other is for slave. You need to install these two plugins before using them
mysql> show plugins; #View all supported plugin s
- Loading plug-ins
master
install plugin rpl_semi_sync_master soname 'semisync_master.so';
slave
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
- View plug-ins
show plugins;
- start-up
main
SET GLOBAL rpl_semi_sync_master_enabled = 1; SET GLOBAL rpl_semi_sync_master_timeout = N; #Unit: milliseconds The default value is 10000 (10 seconds)
configuration file
[mysqld] rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000 # 1 second
from
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
- Restart the IO thread on the slave library
stop slave io_thread; start slave io_thread;
- Check whether it is running
Master:
show status like 'Rpl_semi_sync_master_status';
From:
show status like 'Rpl_semi_sync_slave_status';
- test
View delay time
show variables like '%rpl_semi_sync%';
- mysql 5.7 semi synchronous replication
master: mysql> show global variables like '%rpl_semi%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 1000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_count | 1 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | +-------------------------------------------+------------+ 6 rows in set (0.00 sec) slave: show global variables like '%rpl_semi%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 1000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_count | 1 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +-------------------------------------------+------------+
- GTID
- Advantages of GTID:
After master-slave switching, in the traditional way, you need to find binlog and POS points, and then change master to point to.
In mysql5 In 6, you no longer need to know binlog and POS points. You only need to know the ip, port, account and password of the master,
Because synchronous replication is automatic, mysql automatically finds synchronization through the internal mechanism GTID
- GTID introduction
Global Transaction Identifiers
One transaction corresponds to one ID
A GTID transaction is executed only once on a server
GTID 5.6.2 support, 5.6.10 improvement
binlog_format=row must be in row mode
- Restrictions on GTID
The create table... select statement is not supported, and the master directly reports an error
Non transaction engines are not supported
In a replication group, gtids are required to be turned on or off uniformly
Restart is required to start GTID
create temporary table drop temporary table is not supported
SQL is not supported_ slave_ skip_ counter
- How to skip statements
mysql> show slave status\G; mysql>stop slave; mysql>set gtid_next 'xxxx:N'; (Here xxxx by show slave status of executed_Gtid_Set,and N reference resources executed_Gtid_Set Li itself uuid of N Value, fill in its next value) mysql>begin;commit; mysql>set gtid_next='AUTOMATIC'; mysql>start slave; Retrieved_Gtid_Set: f2e00f82-3ed1-11e8-b729-000c29c2da47:1-2 Executed_Gtid_Set: f2e00f82-3ed1-11e8-b729-000c29c2da47:1, f6f43626-3ed1-11e8-b729-000c29ed3675:1 stop slave; set gtid_next='f2e00f82-3ed1-11e8-b729-000c29c2da47:2'; begin;commit; set gtid_next='AUTOMATIC'; start slave; there xxxxx:N That's yours slave sql thread Error reporting GTID，Or what you want to skip GTID
change master to And bring it master_auto_position=1 Enable based GTID Replication of
- Delayed replication
CHANGE MASTER TO MASTER_DELAY = 60;