MySQL 5.6 GTID Replication "Turn"

I. MySQL 5.6 Introduces the concept of Gtid, so what is the Gtid sacred? In fact, it is not complicated, it is a global transaction identifier. With Gtid, each transaction commit generates 1 unique identifiers in the Binlog, which consists of a UUID and a transaction ID. The first-Commit transaction ID is 1, the second is 2, and the third is 3, as this example pushes. The UUID is the identity ID of the server, and when you start MySQL for the first time, a server_uuid is automatically generated and is written to the auto.cnf file in the data directory by default. We generally do not need to change, the official also does not recommend modification. Refer to MARIADB official documentation for more details

https://mariadb.com/kb/en/mariadb/mariadb-documentation/replication-cluster-multi-master/replication/parallel-replication/

So what are the benefits of replication based on Gtid? The main benefits are the following 2 points:

(1) In the traditional replication, when the failure, need to master-slave switch, need to find Binlog and Pos point, and then change the master to point to the new master, relatively troublesome, but also prone to error. In MySQL 5.6, no need to find Binlog and Pos point, we just need to know the master's IP, port, and account password on the line, because replication is automatic, MySQL through the internal mechanism Gtid automatically find point synchronization.

(2) Multi-threaded replication (library-based). In the previous version of MySQL 5.6, slave replication was single-threaded. An event read application of an event. The master is written concurrently, so the delay is unavoidable. The only effective way is to put multiple libraries on multiple slave, which is a bit of a waste of the server. In MySQL 5.6, we can put multiple tables in multiple libraries, so you can use multi-threaded replication, when there are only 1 libraries, multi-threaded replication is useless.

Gtid related features are turned off by default (not officially mature enough), as follows:

mysql [localhost] {msandbox} ((none)) > Show variables like '%gtid% '; +--------------------------+-----------+| Variable_name            | Value     |+--------------------------+-----------+| enforce_gtid_consistency | OFF       | | gtid_executed            |           | | gtid_mode                | OFF       | | gtid_next                | AUTOMATIC | | gtid_owned               |           | | gtid_purged              |           

Binlog there will not be gtid related records, and ordinary copy is the same, as follows:

[[email protected] data]# mysqlbinlog--no-defaults-v--base64-output=decode-rows mysql_sandbox5619-bin.000006 /*!50530 SET @ @SESSION. pseudo_slave_mode=1*/;/*!40019 SET @ @session. max_insert_delayed_threads=0*/;/*!50003 Set @[email protected]@ completion_type,completion_type=0*/;D elimiter/*!*/;# at 4#140706 20:53:36 server ID 1 end_log_pos start:binlog v 4, Server v 5.6.19-log created 140706 20:53:36 at startup# warning:this Binlog are either in use or were not closed Properl y.rollback/*!*/;# at 120#140706 21:32:15 Server ID 1 end_log_pos 207 Query thread_id=1 exec_time=0 Error_code =0set timestamp=1404653535/*!*/; SET @ @session. pseudo_thread_id=1/*!*/; SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/; SET @ @session. sql_mode=1073741824/*!*/; SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;/*!\c UTF8 *//*!*/; SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=8/*!*/; SET @ @session. lc_time_names=0/*!*/; SET @ @session. collation_database=default/*!*/;d ROP database yayunz/*!*/;D elimiter; # End of log filerollback/* added by M Ysqlbinlog */;/*!50003 Set [email protected]_completion_type*/;/*!50530 set @ @SESSION. pseudo_slave_mode=0*/;  [[email protected] data]#

When the Gtid feature is turned on, the Binlog record format is like this (specific opening is described later)

Note: (When Gtid is enabled, the Binlog format varies greatly, and if you continue to view with the lower version of the Mysqlbinlog command, you will get the following error).

[Email protected] data]# mysqlbinlog--no-defaults-v--base64-output=decode-rows mysql-bin.000004/*!50530 SET @@ SESSION. pseudo_slave_mode=1*/;/*!40019 SET @ @session. max_insert_delayed_threads=0*/;/*!50003 Set @[email protected]@ completion_type,completion_type=0*/;D elimiter/*!*/;# at 4#140706 22:08:25 server ID 1  end_log_pos 120   

The following is a mysqlbinlog command that uses MySQL 5.6 to view the records that have the Gtid log turned on.

[[email protected] data]#/data/sandbox_mysql/5.6.19/bin/mysqlbinlog--no-defaults-v--base64-output= Decode-rows mysql-bin.000002 | Tail-n 30DELIMITER/*!*/;# at 4#140706 21:58:35 server ID 1 end_log_pos CRC32 0xcb49c4fe, v 4, server V 5.6.19-log created 140706 21:58:35# at 120#140706 21:58:35 Server ID 1 end_log_pos 191 CRC32 0x0e40da64 previous-gtids # 9b0147c3-eed0-11e3-9821-000c29e3621e:1# at 191#140706 22:00:37 Server ID 1 end_log_pos 239 CRC32 0x52fc16ed GTID [comm It=yes]set @ @SESSION. gtid_next= ' 9b0147c3-eed0-11e3-9821-000c29e3621e:2 '/*!*/;# @ 239#140706 22:00:37 server ID 1 end_log_pos CRC32 0xf65 2f593 Query thread_id=3 exec_time=0 error_code=0set timestamp=1404655237/*!*/; SET @ @session. pseudo_thread_id=3/*!*/; SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/; SET @ @session. sql_mode=1073741824/*!*/; SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;/*!\c UTF8 *//*!*/; SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=8/*!*/; SET @ @session. lc_time_names=0/*!*/; SET @ @session. collation_database=default/*!*/;create database dyy/*!*/; SET @ @SESSION. gtid_next= ' AUTOMATIC '/* Added by Mysqlbinlog *//*!*/;# at 330#140706 22:00:42 server ID 1 end_log_pos 377 CRC32 0x68ff 3FC0 Rotate to mysql-bin.000003 pos:4delimiter; # End of log filerollback/* Added by Mysqlbinlog */;/*!50003 SET [Emai l protected]_completion_type*/;/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;  [[email protected] data]#

We can clearly see the transaction ID from the above output (this is the second transaction, as mentioned above):

SET @ @SESSION. gtid_next= ' 9b0147c3-eed0-11e3-9821-000c29e3621e:2 '/*!*/;

The simple workflow of Gtid is as follows:

(1) Generate a transaction on master and commit, and write to Binlog

(2) The Binlog on Master is sent to Slave,slave received and written relay Log,slave read to this gtid, and set the value of Gtid_next, for example:

SET @ @SESSION. Gtid_next= ' 9b0147c3-eed0-11e3-9821-000c29e3621e:2

Then tell slave that the next transaction must use Gtid and write to its own binlog.
(3) Slave checks and confirms that the Gtid is not being used, and if it is not used, start executing the transaction and writing it to its own binlog.

(4) Since the value of the Gtid_next is not empty, slave does not attempt to generate a new gtid, but instead acquires Gtid through a master-slave copy.

Two. The configuration of Gtid-based replication is as follows (minimum configuration options, like Master and slave, Server-id need to be different, the following parameters are only for Gtid, as for replication filtering options and normal replication, Because Gtid mode replication can also be converted to normal mode of replication)

Binlog_format = Rowgtid-mode = Onenforce-gtid-consistency = Onlog-bin=mysql-binlog-slave-updates

Less relevant options, start the error, which is the error log, as follows:

2014-07-06 22:47:44 15228 [ERROR]--gtid-mode=on or upgrade_step_1 or upgrade_step_2 requires--log-bin and--log-slave-up Dates

where Binlog_format = row is not required, but it is recommended to use the row format, specifically refer to the reasons I mentioned earlier in the article.

After the above parameters are configured, we will complete a gtid-based replication (with a few differences from normal replication)

(1) authorizing an account on master;

(2) Back up the library to be synchronized on master (the backup parameters here are self-checking documents, of course, there are several parameters may not be used)

(3) Import the backed up data on slave:

(4) Change Master to Operation

mysql [localhost] {root} (DYY) > Change MASTER to master_host= ' 192.168.0.100 ', master_port=5619,master_user= ' repl ', Master_password= ' 123456 ', master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.03 sec) MySQL [localhost] {root} (DYY) > show warnings;+-------+------+-------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------+| Level | Code |                                                                                                                                                                                                                                             Message |+-------+------+------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------+| Note | 1759 |                                                                                                                                                                             Sending passwords in plain text without SSL/TLS is extremely insecure. || Note | 1760 | Storing MySQL user name or password information in the master Info repository are not secure and are therefore not recommend Ed. Consider using the USER and PASSWORD connection options for START SLAVE; See the ' START SLAVE Syntax ' on the MySQL Manual for more information. |+-------+------+---------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------+2 rows in Set (0.00 sec) MySQL [localhost] {root} (DYY) >  

Can see success, but there are two warnings, the original in MySQL 5.6 is more and more stringent, said the use of this method is not recommended.

mysql [localhost] {root} (DYY) > Start slave; Query OK, 0 rows affected (0.01 sec) MySQL [localhost] {root} (DYY) > show slave status\g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              t:192.168.0.100 Master_user:repl master_port:5619 connect_retry:60 master_log_file:mysql-bin.000001 read_master_log_pos:151 Relay_log_file:mysql_sandbox 5619-relay-bin.000002 relay_log_pos:361 relay_master_log_file:mysql-bin.000001 Slave_I O_running:yes Slave_sql_running:yes Replicate_do_db:replicate_ignore_db:r                    Eplicate_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:151 relay_log_space:577 until_condition:none Until_log_file: until_log_pos:0 Master_ssl_allowed:no Master_ssl_ca_file:master_ssl_ca_pa Th:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Ma Ster:0master_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 MASTER_UUID:9B0147C3-EED0-11E3-9821-000C29E3621E master_info_file:/root/sandboxes/msb_5_6_19/data/master. Info sql_delay:0 sql_remaining_delay:null Slave_sql_running_state:slave have read all r Elay log; Waiting for the slave I/O thread to update it master_retry_count:86400 Master_bind:last _io_error_timeStamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_G Tid_set:executed_gtid_set:9b0147c3-eed0-11e3-9821-000c29e3621e:1-13,d3c1aada-fad2-11e3-a66f-000c29ce3f18:1-8  5 Auto_position:11 row in Set (0.00 sec) MySQL [localhost] {root} (DYY) >

If you use Gtid mode for replication and change master to also uses normal mode, an error will be added.

Three. Limitations of the Gtid (or the place that is not well-established)

(1) Currently Gtid does not support MyISAM table (Gtid is transaction-based)

(2) to create TABLE ..... The SELECT statement is not supported.

mysql [localhost] {root} (DYY) > CREATE TABLE dyy select * from T1;  

(3) You must turn on the enforce-gtid-consistency parameter, otherwise start MySQL error, as follows:

2014-07-06 23:44:27 17563 [ERROR]--gtid-mode=on or upgrade_step_1 requires--enforce-gtid-consistency2014-07-06 23:44:27 17563 [ERROR] Aborting

Note: The 5.6.9 version does not support the creation of temporary tables, I am testing the 5.6.19 version and can already create temporary tables.

Four. Multi-threaded replication (library-based)

Multi-threaded replication is controlled by the parameter slave-paralles-workers, setting the number of SQL threads from the server, 0 for turning off multi-threaded replication, the default is 0, and the maximum can be set to 1024 threads. In the configuration file [mysqld] section of the library, add the configuration:

Slave_parallel_workers = 6

After restarting MySQL, use show processlist to see the following:

mysql [localhost] {msandbox} ((none)) > Show processlist;+----+-------------+-----------+------+---------+------+ -----------------------------------------------------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-------------+-----------+------+---------+------+------------------------------------------------  -----------------------------+------------------+| 1 |           System user | | NULL |  Connect | 173 | Slave have read all relay log; Waiting for the slave I/O thread to update it |  NULL | | 2 |           System user | | NULL |  Connect | 173 | Waiting for Master to send event |  NULL | | 3 |           System user | | NULL |  Connect | 173 | Waiting for a event from Coordinator |  NULL | | 4 |  System user |         | NULL |  Connect | 173 | Waiting for a event from Coordinator |  NULL | | 5 |           System user | | NULL |  Connect | 173 | Waiting for a event from Coordinator |  NULL | | 6 |           System user | | NULL |  Connect | 173 | Waiting for a event from Coordinator |  NULL | | 7 |           System user | | NULL |  Connect | 173 | Waiting for a event from Coordinator |  NULL | | 8 |           System user | | NULL |  Connect | 173 | Waiting for a event from Coordinator | NULL | | 10 | Msandbox | localhost | NULL |    Query | 0 | init | Show Processlist |+----+-------------+-----------+------+---------+------+-------------------------------------- ---------------------------------------+------------------+9 rows in Set (0.00 sec) MySQL [localhost] {msandbox} ((none)) >  

Also used to ensure that the relog information and master information is not lost, you need to set the relevant parameters to ensure that you can refer to my previous article.

Summarize:

As much as possible, we split the tables in a library into multiple libraries according to the business logic, so that when writing on master, slave can replicate multithreaded based on the number of threads we set, reducing the problem of traditional replication-latency. For example 2 libraries we can open 2 threads, 3 libraries can open 3 threads, and so on. There is also the current gtid seems not too mature, the official version is not open by default, so a lot of testing is required.

Resources

Http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html

Http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

Turn from

MySQL 5.6 GTID Replication-yayun-Blog Park
Http://www.cnblogs.com/gomysql/p/3816031.html

MySQL 5.6 GTID Replication "Turn"