New features of MySQL 5.6: GTID replication

New features of MySQL 5.6:

MySQL 5.6 contains a new feature for replication, enabling DEVOPS teams to reliably scale-out their MySQL infrastructure across commodity hardware,

Rel= "nofollow" >global Transaction Identifiers (gtids) function,

In order to resolve the following issues:

-Seamless failback and master-to-slave switching

-Can point the slave to the new master

-Reduced manual intervention and reduced service downtime

What is Gtid

GTID is divided into two parts, part of which is the uuid,uuid of the service stored in the MySQL data directory auto.cnf file, which is a very important

File, cannot be deleted, this part will not change. The other part is the transaction ID, and as the transaction increases, the value is incremented once, as

+---------------+----------+--------------+------------------+--------------------------------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+---------------+----------+--------------+------------------+--------------------------------------------+

|    binlog.000029 |              23556 |                  | | 724afcc2-29d6-11e4-9902-000c290c0121:1-362 |

+---------------+----------+--------------+------------------+--------------------------------------------+

gtid:724afcc2-29d6-11e4-9902-000c290c0121:1-362

uuid:724afcc2-29d6-11e4-9902-000c290c0121

transactionid:1-362

The Gtid is unchanged throughout the replication architecture, even if multiple serial owners do not change from there.

Example: ServerA--->serverb---->serverc

Gtid is the same in ServerA, Serverb,serverc.

Gtid the principle of replication.

The biggest difference between gtid replication and normal copy mode is that you do not need to specify a binary file name and location.

When the host is hung up and we lift a standby machine from a large number of standby machines, the rest of the standby machine only needs to be executed.

Change Master to

Master_host= ' 192.168.12.23 ',

Master_user= ' Repluser ',

Master_password= ' 123456 ',

Master_auto_position=1;

Can be quickly synchronized (provided that the new host has user name Repluser), the traditional copy mode needs to find the copied binary file name

and location, this extremely time-consuming, cumbersome.

So how does Gtid replication automatically synchronize and automatically correspond to the location?

Example: ServerC <-----ServerA----> ServerB

Host ServerA

Standby machine: Serverb,serverc

When the host ServerA hung up, ServerB executed all the transactions from the ServerA.

ServerC delay a bit. This time the ServerB needs to be promoted to the host, and Server C continues to be the standby machine.

After ServerC link ServerC, first find the latest gtid from ServerA in your binary file,

Then send this gtid to ServerB, ServerB get this gtid, start from this gtid the next Gtid

Start sending transactions to ServerC. This pattern of self-seeking replication locations reduces the likelihood of transaction loss and the time to recover from failure.

Gtid Copy Build:

Main Library configuration:

Binlog_format = Mixed

Connect_timeout = 1000

Expire_logs_days = 10

Innodb_additional_mem_pool_size = 16M

Innodb_buffer_pool_size =100m

Key_buffer_size = 50M

Log_output = table

Master_info_repository = table

Master_verify_checksum = On

max_connect_errors = 1000

Max_connections = 800

Relay_log_info_repository = table

Skip_name_resolve = 1

Slave_parallel_workers = 2

#以下几项是开启gtid的必须项, or MySQL will not start

Enforce_gtid_consistency = True

Gtid_mode = On

Log_slave_updates = True

Log_bin = Mysqllog

From the Library configuration:

Binlog_format = Mixed

Connect_timeout = 1000

Expire_logs_days = 10

Innodb_additional_mem_pool_size = 32M

Innodb_buffer_pool_size = 300M

Key_buffer_size = 100M

Log_output = table

Master_info_repository = table

Master_verify_checksum = On

max_connect_errors = 1000

Max_connections = 800

Relay_log_info_repository = table

Skip_name_resolve = 1

Slave_parallel_workers = 2

Replicate_wild_do_table = db_vip%.%

#以下几项是开启gtid的必须项, or MySQL will not start

Enforce_gtid_consistency = True

Gtid_mode = On

Log_slave_updates = True

Log_bin = Mysqllog

# Create replication user on main library

Grant replication Slave, replication client on * * to [e-mail protected] ' 192.168.%.% ' identified by ' [email protected] '

# Execute from the library

Change Master to

Master_host= ' 192.168.1.60 ',

Master_user= ' Repl ',

master_password= ' [email protected] ',

Master_auto_position=1;

# Turn on replication

Start slave;

The show slave status parameter explains:

Retrieved_gtid_set:724afcc2-29d6-11e4-9902-000c290c0121:1-13

EXECUTED_GTID_SET:724AFCC2-29D6-11E4-9902-000C290C0121:1-13,

934024ce-29db-11e4-9923-000c296cf315:1-8

Retrieved_gtid_set: The latest Gtid copied from the main library

Executed_gtid_set: The latest Gtid from the library, there are two Gtid, one main library generated, one from the library's own

Mysql> show global variables like '%gtid% ';

+--------------------------+----------------------------------------------------------------------------------- --+

| variable_name | Value |

+--------------------------+----------------------------------------------------------------------------------- --+

| enforce_gtid_consistency | On |

| gtid_executed | 724AFCC2-29D6-11E4-9902-000C290C0121:1-13,

934024ce-29db-11e4-9923-000c296cf315:1-8 |

| Gtid_mode | On |

|                                                                                     | |

|                                                                                     gtid_purged | |

+--------------------------+----------------------------------------------------------------------------------- --+

Explanation of the relevant state variables of the Gtid:

Gtid_executed:gtid_executed represents a transaction that has already been performed on the instance, and the reset MASTER will empty the variable;

We can also influence gtid_executed by setting Gtid_next to execute an empty transaction.

Gtid_next: Is the session level variable that represents the next GTID to be used

The global object gtid_state corresponding to Gtid_purged, gtid_owned, gtid_executed is also maintained in memory.

Gtid_state maintains three sets, of which logged_gtids corresponds to gtid_executed, lost_gtids corresponds to Gtid_purged,owned_gtids gtid_owned

Gtid_mode:on means start Gtid mode

Gtid_owned: The Gtid being executed

Gtid_purged: Gtid deleted in the intermediate log

Gtid in replication mode, the master never synchronizes the fault:

The operation must be performed from the library.

1.stop slave;

2.reset master; # Empty the contents of Master.info, Relay-log.info

3.set global gtid_purged= ' 724afcc2-29d6-11e4-9902-000c290c0121:1-15 '; #这个gtid is performing an error from the library Gtid

4.start slave;

Master never synchronizes in Gtid mode

Http://wenku.baidu.com/link?url= 0hids4uu9pxh2erw-ox7yv9fxdf0y7x4docs7k2zoufo4jjdr-lvuosd-mm0btd5chtfplw9jqjlvfls7dl70cmgzsdftsczuxh7jf1vbyw

Failover of Gtids

Http://www.topthink.com/topic/3674.html

Http://www.tuicool.com/articles/NjqQju

Program/310852.html

Http://blog.sina.com.cn/s/blog_53b13d95010180nf.html

This article is from "SQL Server MySQL" blog, declined reprint!

New features of MySQL 5.6: GTID replication