MySQL master-slave replication--mysql-5.6 based on Gtid and multi-threaded replication

First, Mysql 5.6 new features

....

Improvements to replication features

⒈ supports multi-threaded replication, (slave-parallel-workers=0 0: Disables multithreading;) it actually turns on the corresponding independent thread for each database. That is, each library has a separate (SQL thread), if the online business, only one database or most of the pressure on the individual database, multi-threaded concurrent replication feature is meaningless.

⒉ support to enable the Gtid, it should be a good thing for operators, in the configuration of master-slave replication, the traditional way, you need to find Binlog and POS points, and then change the master to point, and not very experienced operations, often will be wrong, causing the master-slave synchronous replication error , in mysql5.6, no longer know Binlog and Pos point, need to know the master IP, port, account password can, because synchronous replication is automatic, MySQL through the internal mechanism Gtid automatically find point synchronization.

Second, Gtid detailed

One of the new features of MySQL 5.6 is the addition of a global transaction ID (GTID) to enhance the database's primary and standby consistency, fault recovery, and fault tolerance.

⒈ what is Gtid?

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

The Gtid is all called the global Transaction Identifier, which can be translated as a globally transaction identifier (or global transaction), and Gtid is created when the transaction commits on the original master. The Gtid needs to remain unique in the global primary-standby topology.

MySQL 5.6, each GTID represents a database transaction, GTID consists of two parts:

GTID = source_id:transaction_id

The source_id is used to indicate the source server, denoted by Server_uuid, which is generated at the first boot and written to the configuration file data/auto.cnf

TRANSACTION_ID is determined based on the transaction committed on the source server. TRANSACTION_ID is a self-increment count starting from 1, representing the nth transaction executed on this main library. MySQL will guarantee a 1:1 mapping between transactions and GTID.

For example, here is a Gtid

3e11fa47-71ca-11e1-9e33-c80aa9429562:50

Represents the 50th database transaction performed on a MySQL instance that is uniquely labeled as "3e11fa47-71ca-11e1-9e33-c80aa9429562". It is easy to understand that MySQL can guarantee the global uniqueness of GTID by ensuring that each database is server_uuid globally unique, and that each database generates transaction_id itself unique.

The life cycle of a Gtid includes:

1. Transactions are executed and committed on the main library

The transaction is assigned a Gtid (by the main library's UUID and the smallest transaction sequence number not used on that server), and the Gtid is written to Binlog.

2. The standby library reads the Gtid in the Relaylog and sets the value of the session level Gtid_next to tell the repository that the next transaction must use this value

3. The repository checks to see if the Gtid has been used and recorded in his own binlog. Slave need to guarantee that the previous transaction did not use this gtid, but also to guarantee that the Gtid is read at this time, but the uncommitted transaction is not the same as the use of this gtid.

4. Because Gtid_next is not empty, slave does not generate a new gtid, but uses the Gtid obtained from the main library. This guarantees that the same transaction in one replication topology Gtid unchanged.

Due to the uniqueness of the Gtid in the global, through the Gtid, we can automatically switch to some of the complex replication topology to facilitate the new main library and new repositories, for example, by pointing to a specific gtid to determine the new repository replication coordinates.

Of course, there are some limitations to using Gtid:

1. Updates in transactions contain non-transactional storage engines, which can cause multiple gtid to be assigned to the same transaction.

2. The Create TABLE...SELECT statement is not supported because the statement is split into CREATE table and insert two transactions, and this two transaction is assigned the same gtid, which causes the insert to be ignored by the repository.

3. Create/drop temporary table operation not supported

As you can see, support for Gtid replication has some limitations on some statements, and MySQL also provides an option disable-gtid-unsafe-statements to prohibit the execution of these statements.

⒉ what is Server_uuid?

MySQL 5.6 Replaces most features of the original 32-bit server_id with a 128-bit server_uuid. The reason is simple, server_id relies on my.cnf manual configuration, there is a possibility of conflict--and the automatic generation of 128-bit UUID algorithm can guarantee that all MySQL uuid will not conflict.

At the first boot, MySQL calls Generate_server_uuid () to automatically generate a server_uuid and save it to the Auto.cnf file-the only purpose of this file currently exists is to save the server_uuid.

One benefit of the globally unique server_uuid is that it resolves the abort of the MySQL primary and standby replication caused by the server_id configuration conflict (BUG #33815?)

When MySQL 5.6,slave applies for binlog to Master, it first sends its own server_uuid,master server_uuid instead of server_id (the way it was before MySQL 5.6) as Kill_zombie_dump_threads parameters, terminating the conflict or zombie Binlog_dump thread.

[email protected] ~]# cat/mydata/data/auto.cnf
[Auto]
server-uuid=cc8d2c69-0ad2-11e4-8ec5-000c296f579d

Mysql> SHOW GLOBAL VARIABLES like ' server_uuid ';

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

| variable_name | Value |

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

| Server_uuid | cc8d2c69-0ad2-11e4-8ec5-000c296f579d |

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

1 row in Set (0.00 sec)

three, multi-threaded replication based on the library

Prior to MySQL version 5.6, synchronous replication is single-threaded, queued, and can only be executed one at a time, In 5.6, multiple libraries can be multi-threaded replication, such as the database, the user table, commodity table, price list, order table, then each business table in a separate library, then you can do multi-threaded replication, but a library table, multi-threaded replication is invalid.

Note that only one thread per database can be used, and multi-threaded replication is meaningful when copying involves multiple databases.

iv. Mysql 5.6 Replication Management Tools

Official: http://dev.mysql.com/downloads/tools/utilities/#downloads
Mysqlreplicate Quick Start Replication
Mysqlrplcheck Fast Check replication environment
Mysqlrplshow Show Replication topology
Mysqlfailover failover
Mysqlrpladmim Management Tools

Five, based on Gtid and multi-threaded replication experiment

To use the replication feature in MySQL 5.6, the following options should be defined in the service configuration segment [mysqld] for Less:

⑴binlog-format: Binary log format, there are several types of row, statement and mixed;

Note that when you set the isolation level to read-commited, you must set the binary log format to row, and now MySQL officially believes that statement this is no longer suitable for continued use, but the mixed type may result in inconsistent master-slave data under the default transaction isolation level;

⑵log-slave-updates, Gtid-mode, Enforce-gtid-consistency, Report-port and report-host: used to initiate gtid and meet other requirements of the subsidiary;

⑶master-info-repository and Relay-log-info-repository: Enable these two items, which can be used to ensure the security of the binary and slave servers in the crash;

⑷sync-master-info: Enable it to ensure that no information is lost;

⑸slave-paralles-workers: Set the number of SQL threads from the server; 0 to turn off the multithreading replication function;

⑹binlog-checksum, Master-verify-checksum, and slave-sql-verify-checksum: Enable all verification functions related to replication;

⑺binlog-rows-query-log-events: Enables the use of information related to logging events in binary logging to reduce the complexity of troubleshooting;

⑻log-bin: Enable the binary log, which is the basic premise to ensure the replication function;

⑼server-id: The ID number of all servers in the same replication topology must be unique;

Report-host:

The host name or IP address of the slave to is reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.

Report-port:

The TCP/IP port number for connecting to the slave is reported to the master during slave registration.

Master-info-repository:

The setting of this variable determines whether the slave logs master status and connection information to a FILE (master. info), or to a TABLE (Mysql.slave_master_info)

Relay-log-info-repository:

This option causes the server to log it relay log info to a file or a table.

Log_slave_updates:

Whether updates received by a slave server from a master server should is logged to the slave ' s own binary log. Binary logging must is enabled on the slave for this variable to has any effect.

Simple master-slave mode configuration steps

㈠, simple master-slave mode configuration steps
1, configure the master-slave node of the service configuration file
1.1. Configure the Master node:
[Mysqld]
Binlog-format=row
Log-bin=master-bin
Log-slave-updates=true
Gtid-mode=on
Enforce-gtid-consistency=true
Master-info-repository=table
Relay-log-info-repository=table
Sync-master-info=1
slave-parallel-workers=2
Binlog-checksum=crc32
Master-verify-checksum=1
Slave-sql-verify-checksum=1
Binlog-rows-query-log_events=1
Server-id=1
report-port=3306
port=3306
Datadir=/mydata/data
Socket=/tmp/mysql.sock
report-host=192.168.1.170
1.2. Configure the Slave node:
[Mysqld]
Binlog-format=row
Log-slave-updates=true
Gtid-mode=on
Enforce-gtid-consistency=true
Master-info-repository=table
Relay-log-info-repository=table
Sync-master-info=1
slave-parallel-workers=2
Binlog-checksum=crc32
Master-verify-checksum=1
Slave-sql-verify-checksum=1
Binlog-rows-query-log_events=1
server-id=11
report-port=3306
port=3306
Log-bin=mysql-bin.log
Datadir=/mydata/data
Socket=/tmp/mysql.sock
report-host=192.168.1.180

Restart the master-slave mysqld service and view Gtid related information

[email protected] ~]# service mysqld Restart
Shutting down MySQL. [OK]
Starting MySQL. [OK]

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

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

| variable_name | Value |

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

| enforce_gtid_consistency | On |

| gtid_executed | |

| Gtid_mode | On |

| gtid_owned | |

| gtid_purged | |

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

5 rows in Set (0.03 sec)

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/E/U261/LANG/ZH-CN /images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>    
2. Create a replication user    
mysql> GRANT REPLICATION SLAVE on * * to [email protected"   identified by ' Replpass ';    
Description: 192.168.1.180 is from the node server, if you want to authorize more nodes at once, you can modify it as needed,    
3, provide the initial dataset     for the standby node;
Lock the primary table, back up the data on the primary node, restore it to the slave node, or, if Gtid is not enabled, use the Show Master Status command on master to view the binary log file name and the event location when you back up, so that you can use it later when you start the slave node.    
4, start the replication thread from the node    
If the Gtid feature is enabled, use the following command:    
mysql> change MASTER to Master_ Host= ' 192.168.1.170 ', master_user= ' repluser ', master_password= ' replpass ', master_auto_position=1;    
mysql> start slave;

Gtid is not enabled, you need to use the following command:
slave> change MASTER to master_host= ' 192.168.1.170 ',
Master_user= ' Repluser ',
Master_password= ' Replpass ',
Master_log_file= ' master-bin.000003 ',
master_log_pos=1174;

5. Verification

Mysql> show Slave hosts;

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

| server_id | Host | Port | master_id | Slave_uuid |

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

| 11 | 192.168.1.180 | 3306 | 1 | f669daac-0ad3-11e4-8ecd-000c299c0b0f |

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

1 row in Set (0.00 sec)

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

Note: Each statement, automatic commit, will result in a large number of logs, can turn off the automatic commit function, only input commit, the transaction is submitted.

Mysql>set autocommit=1

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

㈡, semi-synchronous replication
1. Install the relevant plugins on the master and slave nodes respectively
master> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';
slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';
2. Enable semi-synchronous replication
In the configuration file on master, add
Rpl_semi_sync_master_enabled=on

Add a configuration file in at least one slave node
Rpl_semi_sync_slave_enabled=on
Then restart the MySQL service to take effect.
Alternatively, you can dynamically start its related features on the MySQL service:
master> SET GLOBAL rpl_semi_sync_master_enabled = on;
slave> SET GLOBAL rpl_semi_sync_slave_enabled = on;
slave> STOP slave io_thread; START SLAVE Io_thread;
3. Confirm that the half-sync function is enabled
master> CREATE DATABASE magedudb;
master> SHOW STATUS like ' rpl_semi_sync_master_yes_tx ';
Slave> SHOW DATABASES;

attachment One: How to ignore replication errors

When there is an error in the repository replication, the traditional way to skip the error is to set Sql_slave_skip_counter and then start slave.

However, if Gtid is turned on, the failure will be set:

mysql> Set Global sql_slave_skip_counter = 1;

ERROR 1858 (HY000): Sql_slave_skip_counter can Not was set when the server is running with @ @GLOBAL. Gtid_mode = on. Instead, for each transaction so want to skip, generate a empty transaction with the same GTID as the transaction

The error message tells us that the wrong transaction can be skipped by generating an empty transaction.

We manually generated a copy of the repository error:

Last_sql_error:error ' Unknown table ' test.t1 ' on query. Default database: ' Test '. Query: ' DROP TABLE ' t1 '/* generated by server */'

In view Binlog, the DDL corresponds to a gtid of 7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1131

Execute on the standby:

Mysql> STOP SLAVE;

Query OK, 0 rows Affected (0.00 sec)

mysql> SET SESSION gtid_next = ' 7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1131′;

Query OK, 0 rows Affected (0.00 sec)

Mysql> BEGIN; COMMIT;

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

mysql> SET SESSION gtid_next = AUTOMATIC;

Query OK, 0 rows Affected (0.00 sec)

Mysql> START SLAVE;

If you look at show slave status again, you will see that the error transaction has been skipped. The principle of this method is simple, the gtid of the empty transaction is added to the gtid_executed, which is equivalent to telling the repository that the Gtid corresponding transaction has been executed.

The introduction of Gtid, organized from the network

Linux/mysql_5_6_jiyugtidjiduoxianchengdefuzhixiangjie_571537_1377508923.html

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

http://mysqllover.com/?p=87

This article is from the "Small Wind" blog, please be sure to keep this source http://renfeng.blog.51cto.com/7280443/1437678