MySQL5.7 configuration Gtid Master-Slave---build gtid master-Slave

Preparation instructions:

master:192.168.10.100

Slave:192.168.10.101

First, configure the Gtid parameter

Configuration files are/etc/my.cnf

Master parameter configuration:

Gtid-mode = Onenforce-gtid-consistency = onserver-id =+ = Rowlog -bin =/data/mysql/data/mysql-bin

Slave on the parameter configuration:

Gtid-mode = Onenforce-gtid-consistency = onserver-id =$ = Rowlog -bin =/data/mysql/mysql3306/logs/mysql-= onskip1

Second, configure synchronization account:

' Repl '@'192.168.10.%'123456'; MySQL> Flush privileges;

192.168.10.% is a synchronous host IP, the recommended end of the use of%, so that after the intranet in the addition of slave can be directly synchronized.

Third, back up the main library data

-uroot -p --master-data=2 --single-transaction -R --triggers --events -A > master.sql 

New library can be removed from this operation, directly configure synchronization to

Backup parameter Description:

-H,--host=name the host to which the target database is exported, localhost by default-U,--user=name link the database user name of the target database-P,--password[=name] link the database password for the target database-P,--port=# Link The port of the target database--add-drop-database is using--databases or--all-the DROP Database command is added to each CREATE DATABASE command before the databases parameter--add-drop-table adds the drop table command before each CREATE TABLE command--default-character-set=name Specifies the default character set, which is UTF8 by default--Replace uses the command to insert data instead of using the Insert command--set-CharSet the Set names Default_character_set command is written to the export backup file, which is turned on by default--dump-slave[=#] means that the backup is exported from the library from the copied slave and contains the change master pass statement. Value parameter if not written or =-1, the change master to statement is written to the dump file, and set to 2 indicates that it is also written to the dump file, except that it is commented out--master-data[=#] means that the backup is exported from the replicated main library. The value parameter is the same as--dump-slave. Use this parameter to automatically open the lock-all-table parameter unless you use--single-Transaction Parameters-T,--tab=name indicates that the backup file is generated as a text file and specifies the file path, each table generates two files, one is. sql file save table structure, one is. txt file save table data information-A,--all-databases export all tables in all databases-B,--databases exporting one or more databases specified--ignore-table=name means that an export of a specified table is ignored during export, and if you want to omit more than one table, this parameter uses multiple-D,--no-data represents export-only table structure-R,--routines represents the export of stored procedures and functions also to guide the--triggers on behalf of the export.-W,--where=name represents the export of eligible data-X,--lock-all-tables represents a read-only lock on each table for each database during the export process--no-autocommit represents the data export for each table with set autocommit=0 and commit two statement packagesThe--single-transaction representative sets the transaction isolation level to repeatable read and opens a new transaction when the export starts execution start transaction, and does not block any read and write operations during dump execution

Iv. configuring synchronization from the Library

1. Import the data from the master backup

2. Configure synchronization

mysql> Change MASTER to Master_host='192.168.10.100', master_user= ' Repl ' , Master_password='123456', master_port=3306, Master_auto_position=1;

3. Turn on sync and view sync status

Mysql>Start Slave;mysql>Show Slave Status\g***************************1. Row ***************************slave_io_state:waiting forMaster to send event Master_host:192.168.10.100master_user:repl Master_port:3306Connect_retry: -Master_log_file:mysql-bin.000001Read_master_log_pos:918relay_log_file:client-relay-bin.000004Relay_log_pos:454Relay_master_log_file:mysql-bin.000001Slave_io_running:yes Slave_sql_running:yes replicate_do_db:replicate _ignore_db:replicate_do_table:replicate_ignore_table:replicate_wild_do_table:replicate_wild_ Ignore_table:last_errno:0Last_error:skip_counter:0Exec_master_log_pos:918Relay_log_space:962until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SS L_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0master_ssl_verify_server_cert:no Last_io_errno:0Last_io_error:last_sql_errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:1003306master_uuid:b3f31135-4851-11e8-b758-000c29148b03 master_info_file:/data/mysql/data/master.InfoSql_delay:0Sql_remaining_delay:null Slave_sql_running_state:slave have read all relay log; waiting for  MoreUpdates Master_retry_count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL _crl:master_ssl_crlpath:retrieved_gtid_set:b3f31135-4851-11E8-B758-000C29148B03:1-4EXECUTED_GTID_SET:25D36CBF-485A-11E8-A621-000C292C6F36:1-3, b3f31135-4851-11E8-B758-000C29148B03:1-4auto_position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1RowinchSet (0.00Sec

Parameter description:

retrieved_gtid_set:b3f31135-4851-11e8-b758-000c29148b03:1-4 #表示收到的事务数 /c2>EXECUTED_GTID_SET:25D36CBF-485a-11e8-a621-000c292c6f36:1-3, b3f31135- 4851-11e8-b758-000c29148b03:1-4 #表示执行完的事务数

When a master-slave failure occurs, you can observe the specific card in which transaction.

Five, Gtid parameter introduction

The Gtid has the following parameter configurations:

' %gtid% ' ; +----------------------------------+-----------+| Variable_name                    | Value     |+----------------------------------+-----------+| binlog_gtid_simple_recovery      | On        | | enforce_gtid_consistency         | On              | | | Gtid_mode                        | On        | | Gtid_next                        | AUTOMATIC | | gtid_owned | |           | gtid_purged                      |           | | | session_track_gtids              | OFF       |+----------------------------------+-----------+8 in Set (0.01 sec)

Related parameter Description:

Gtid_mode:-on: Generate Gtid,slave only accept transactions with GTID-on_permissive: Generate Gtid,slave Accept transactions with GTID without GTID transactions-OFF: Does not produce gtid,slave only accept transactions without the reference GTID-off_permissive: Do not produce Gtid,slave accept transactions with GTID without GTID transactions enforce-gtid-Consistency-On: When the discovery statement/error message returned when transaction does not support GTID-WARN: When discovery does not support statements/transaction, returns a warning, and logs a warning message-OFF: Do not check if there are statements that are not supported by GTID/Transaction
Gtid_executed_compression_period #这个参数是控制表压缩率
Gtid_next:automatic #gtid获取下一个事务的方式, Automatic is automatically acquired, you can specify the ID of a transaction when there is a slave failure to skip a transaction
gtid_owned: #表示正在执行的事务的gtid以及对应的线程ID
Gtid_purged: #已经被删除的binlog的事务, which is a subset of the gtid_executed, starting with MySQL5.6.9, the variable cannot be set
Session_track_gtids:

MySQL5.7 configuration Gtid Master-Slave---build gtid master-Slave