Mariadb-10gtid replication and multi-source replication

---The outline of this article

First, what is Gtid

Second, the application scenario

Three, multi-threaded replication instructions

Iv. Process of realization

Five, multi-source replication principle

Vi. the process of realization

----------------------------------

First, what is GITD

The Gtid (Global Transaction IDs), introduced from MySQL 5.6, makes the configuration, monitoring, and management of its replication capabilities easier to implement and more robust. Official documents in this document, we can know that the official definition of the global transaction ID is: GTID = source_id:transaction_id

Gtid is a unique, unique representation of the server's UUID, which is a globally unique identifier, consisting of 128-bit random characters, and mysql-5.6 relies on server-id and UUID to identify each host in the replication schema. Because a random string of 128 bits is not duplicated globally, the Server-id and UUID and the sequence number of each MySQL thing constitute the only gtid, and since the introduction of mysql-5.6 every binary log has a Gtid tag in each object's header. So Gtid makes it very easy to track and compare replication things and to recover quickly from crashes. In particular, the INNODB engine must be implemented with the help of gtid in order to achieve high-availability functionality.

Second, the application scenario

Gtid is especially important when using mariadb for high availability, for example, in a high-availability environment, in a master multi-slave mode, when the main library goes down (write latency), the cluster resource manager can select one of the many from the library in the node list to promote the primary library without affecting the business itself. The other from the library will be a new upgrade to the main library in the current cluster, and later will replicate the synchronization from this library, although this completes the "instantaneous" business switchover, but may be in the main library before the outage, a number of replication from the library synchronization lag behind the main library, so when the main library downtime, The cluster Explorer just switches to a potentially inconsistent submission from the library (B), and when an app reads a transaction that has been committed in the main library of the outage before it has been read, it finds that the current main library is not present, so there is a transaction inconsistency, and Gtid can solve the problem well. First, when a library is promoted to a main library, then the slave libraries that point to the original main library will all point to the newly promoted main library for subsequent replication, and then the newly promoted main library B consolidates all the transactions that have been done from the library to fill its own missing parts, and b knows from there that the missing is the transaction. There are so many things that this transaction is that one, which is identified by Gtid, because Gtid identifies the identity of the source server and the number of transactions (SOURCE_ID:TRANSACTION_ID). Each change is an event that is associated with the Gtid and is recorded in the Binlog log for additional synchronization from the library, so that a globally unique identity can be achieved.

Note:

Gtid is not changed in multi-level replication.

In Gtid, if there are multiple databases in the primary server, the multi-threaded replication is replicated by I/O to the trunk log from the library and is applied locally by more than one SQL thread.

Three, multi-threaded replication instructions

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:

Only one thread per database can be used, and it makes sense to replicate multiple databases when multi-threaded replication is involved;

Transactional replication of the same library must be replicated sequentially.

In the replication model, the same thread (I/O thread) can serve multiple databases.

Iv. Process of realization

Note:

Only Gtid replication is implemented here.

1. Resource allocation

Service type	Version type	IP Address
OS	Centos6.5x86_64	No
MARIADB Master	10.0.10-mariadb-log Source Distribution	192.168.1.122/24
MARIADB Slave	10.0.10-mariadb-log Source Distribution	192.168.1.210/24

2. MARIADB Master Configuration Checklist

⑴, edit configuration file/etc/my.cnf

[Client]port        = 3306socket      =/tmp/maria.sock[mysqld]port        = 3306socket =      /tmp/ Maria.sockskip-external-lockingkey_buffer_size = 256mmax_allowed_packet = 1mtable_open_cache = 256sort_buffer_size = 1mread_buffer_size = 1mread_rnd_buffer_size = 4mmyisam_sort_buffer_size = 64mthread_cache_size = 8query_cache_size= 16mthread_concurrency = 4datadir=/mydata/datalog-bin=mysql-binbinlog_format=rowserver-id=10log-slave-update = truemaster-info-repository=tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-threads= 2master-verify-checksum=crc32slave-sql-verify-checksum=1binlog-rows-query-log-events=1report-port= 3306report-host=master.mysql.com[mysqldump]quickmax_allowed_packet = 16m[mysql]no-auto-rehash[myisamchk]key_ Buffer_size = 128msort_buffer_size = 128mread_buffer = 2mwrite_buffer = 2m[mysqlhotcopy]interactive-timeout

Annotations:

Binlog-format

Binary log format, there are several types of row, statement and mixed;

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 that the mixed type under the default transaction isolation level may result in inconsistent master-slave data;

Log-slave-updates, Report-port and Report-host

Used to initiate gtid and meet other ancillary requirements;

Master-info-repository and Relay-log-info-repository

Enable these two items, can be used to implement in the crash to ensure that the binary and security from the server function;

Sync-master-info

Enable it to ensure that no information is lost;

Slave-paralles-threads

Set the number of SQL threads from the server, and 0 to turn off the multithreaded replication feature;

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

Log_slave_updates

Record updates from the server

⑵, authorized replication nodes and users

MariaDB [(None)]> GRANT REPLICATION SLAVE on*.* to [e-mail protected] identified by ' Replpass '; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> flush privileges    ; Query OK, 0 rows Affected (0.00 sec)

⑶, backup master node data, synchronous to slave (this operation is not necessary, if there is a large amount of data on master to synchronize to the new slave, Slave is new on-line this operation makes sense)

# mysqldump-uroot-hmaster.mysql.com--all-databases--lock-all-tables--flush-logs--master-data=2 >/tmp/all.sql# Scp/tmp/all.sql 192.168.1.210:/tmp

⑷, check the master Gtid logo

MariaDB [(None)]> show variables like "%gtid%"    , +------------------------+----------+| Variable_name          | Value    |+------------------------+----------+| gtid_binlog_pos        | 0-1-2130 | | gtid_binlog_state      | 0-1-2130 | | Gtid_current_pos |       0-1-2130 | | gtid_domain_id         | 0        | | gtid_ignore_duplicates | OFF      | | gtid_seq_no            | 0        | | gtid_slave_pos         |          | | gtid_strict_mode       | OFF      | | Last_gtid              | 0-1-2130 |+------------------------+----------+9 rows in Set (0.00 sec)

3. Mariadb Slave Configuration List

⑴, edit configuration file/etc/my.cnf

[Client]port        = 3306socket      =/tmp/maria.sock[mysqld]port        = 3306socket =      /tmp/ Maria.sockskip-external-lockingkey_buffer_size = 256mmax_allowed_packet = 1mtable_open_cache = 256sort_buffer_size = 1mread_buffer_size = 1mread_rnd_buffer_size = 4mmyisam_sort_buffer_size = 64mthread_cache_size = 8query_cache_size= 16mthread_concurrency = 4datadir=/mydata/datalog-bin=mysql-binrelay-log=relay-log-binserver-id=20binlog-format= rowlog-slave-updates=truemaster-info-repository=tablerelay-log-info-repository=tablesync-master-info= 1slave-parallel-threads=2binlog-checksum=crc32master-verify-checksum=1slave-sql-verify-checksum= 1binlog-rows-query-log_events=1report-port=3306report-host=slave.mysql.com[mysqldump]quickmax_allowed_packet = 16m[mysql]no-auto-rehash[myisamchk]key_buffer_size = 128msort_buffer_size = 128mread_buffer = 2Mwrite_buffer = 2M[ Mysqlhotcopy]interactive-timeout

Note:

In addition to Server-id, the others are the same as the main library.

⑵, apply full backup

# Mysql/all.sql

⑶, Connection Master

MariaDB [(none)]> change master to master_host= ' 192.168.1.122 ', master_user= ' repluser ', master_password= ' Replpass ' , Master_use_gtid=current_pos;

⑷, look at the effect

⑸, test it.

Create a database in master and set up a test table

MariaDB [(None)]> use yydbdatabase changedmariadb [yydb]> CREATE TABLE T1 (ID int. (4) Not null,name char (+) NOT NULL ); Query OK, 0 rows affected (0.03 sec)

Check the slave log to see if the Gtid logo is included

# at 793#140419 23:40:27 server ID 1  end_log_pos 726 CRC32 0x7d8f09f0 GTID  0-1-2132/*!100001 SET @ @session. gtid_se q_no=2132*//*!*/;# at 835#140419 23:40:27 server ID 1  end_log_pos 852 CRC32 0x4b60d93e  Query   thread_id=4 Exec_time=0 error_code=0use ' Yydb '/*!*/; SET timestamp=1397922027/*!*/;create table t1 (id int (4) not null,name char (+) not null)/*!*/;

Note:

If Master does not match the Gtid identity of Slave, Gtid-based replication cannot be implemented, as long as the Gtid modifications in the slave library are consistent with the master.

The statement that views the Gtid identity is

MariaDB [yydb]> show global variables like ' gtid_current_pos ';

The statement that modifies Gtid is

MariaDB [(None)]> SET GLOBAL gtid_slave_pos = ' Master_gtid ';

================================= based on Gtid copy to this end ====================================

Five, multi-source replication principle

First, we need to be clear that multi-master is not the same as multi-source replication. Multi-master replication is typically circular replication, where you can replicate data to other hosts on any host.

The multi-source are different. A replication limit has been fixed in the MySQL5.7 version, which is limited to one master station only. This is a limiting factor in our design replication environment, and some geeks have made it work properly. But now there is an official solution. So. Simply put, multi-source means that a slave can have more than one master station. Now, like the same replication environment is possible:

This will help us create some duplicate hierarchies that were not possible in the past. For instance, you can put a slave in your office. Replicate data from all the main stations in the office to spread around the world.

And on the micro, each master opened a binlog dump thread to notify the slave side, by slave to open multiple IO threads to the master side of the copy of the binary log and recorded in the local relay log, by the local SQL The thread is read out and applied locally. This enables replication functionality.

Vi.. Configuration process

1. Resource allocation

Service type	Version type	IP Address
OS	Centos6.5x86_64	No
Master 1	10.0.10-mariadb-log Source Distribution	192.168.1.109
Master 2	10.0.10-mariadb-log Source Distribution	192.168.1.111
Slave	10.0.10-mariadb-log Source Distribution	192.168.1.110

2, open the binary log on each master, authorize the replication user

⑴, Master 1

Editing a configuration file

[Mysqld]port        = 3306socket =      /tmp/mysql.sockskip-external-lockingkey_buffer_size = 256mmax_allowed_packet = 1mtable_open_cache = 256sort_buffer_size = 1mread_buffer_size = 1mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64mthread_cache_size = 8query_cache_size= 16mthread_concurrency = 4datadir=/mariadb/dataserver-id= 10log-bin= Mysql-binbinlog-format=row

Note:

Binlog-format log format can be used by default

Only the [mysqld] area needs to be modified

Authorizing replication users

MariaDB [(None)]> Grant replication Slave,replication Client on * * to ' repluser ' @ ' 192.168.1.110 ' identified by ' Replpa SS '; Query OK, 0 rows Affected (0.00 sec)

⑵, Master 2

Editing a configuration file

#vim/etc/my.cnf[mysqld]port        = 3306socket      =/tmp/maria.sockskip-external-lockingkey_buffer_size = 256mmax_ Allowed_packet = 1mtable_open_cache = 256sort_buffer_size = 1mread_buffer_size = 1mread_rnd_buffer_size = 4Mmyisam_sort_ Buffer_size = 64mthread_cache_size = 8query_cache_size= 16mthread_concurrency = 4datadir=/mydata/datalog-bin= Mysql-binserver-id= 20binlog-format=row

Note:

Binlog-format log format can be used by default

Only the [mysqld] area needs to be modified

Authorizing replication users

MariaDB [(None)]> grantreplication slave,replication client on*.* to ' repluser ' @ ' 192.168.1.110 ' identified by ' Replpass '; Query OK, 0 rowsaffected (0.00 sec)

⑶, Slave

Connect two Master

Master 1

MariaDB [(none)]> change master ' Master1 ' to master_host= ' 192.168.1.109 ', master_user= ' repluser ', master_password= ' Replpass ', master_log_file= ' mysql-bin.000006 ', master_log_pos=326;

View the status of slave

MariaDB [(none)]> show slave ' Master1 ' status\g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos t:192.168.1.109 Master_user:repluser master_port:3306 Connect_retry:6 0 master_log_file:mysql-bin.000007 read_master_log_pos:326 Relay_log_file:master-re lay-bin-master1.000009 relay_log_pos:535 relay_master_log_file:mysql-bin.000007 Slave_ Io_running:yes Slave_sql_running:yes Replicate_Do_DB:Replicate_Ignore_DB:Re                   Plicate_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:326    relay_log_space:1128 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:0master_ssl_verify_server_cert:no last_io_errno:0 Last_io_erro r:last_sql_errno:0 Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_I D:10 Master_SSL_Crl:Master_SSL_Crlpath:Using_Gtid:No Gtid_i O_pos:1 row in Set (0.00 sec)

Master 2

MariaDB [(none)]> change master ' Master2 ' to master_host= ' 192.168.1.111 ', master_user= ' repluser ', master_password= ' Replpass ', master_log_file= ' mysql-bin.000010 ', master_log_pos=342;

View the status of slave

MariaDB [(none)]> show slave ' Master2 ' status\g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos t:192.168.1.111 Master_user:repluser master_port:3306 Connect_retry:6 0 master_log_file:mysql-bin.000010 read_master_log_pos:342 Relay_log_file:master-re lay-bin-master2.000002 relay_log_pos:535 relay_master_log_file:mysql-bin.000010 Slave_ Io_running:yes Slave_sql_running:yes Replicate_Do_DB:Replicate_Ignore_DB:Re                   Plicate_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:342     relay_log_space:841 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: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 : Master_SSL_Crl:Master_SSL_Crlpath:Using_Gtid:No Gtid_io _pos:1 row in Set (0.00 sec)

⑷, test it.

Create a database in Master1, and create a database in Master2

Master 1

MariaDB [(None)]> show variables like ' server_id '; +---------------+-------+| variable_name | Value |+---------------+-------+| server_id     |    |+---------------+-------+1 row in Set (0.00 sec) MariaDB [(None)]> CREATE Database m1db; Query OK, 1 row affected (0.01 sec) MariaDB [(None)]> show databases;+--------------------+| Database           |+--------------------+| information_schema | | jjdb               | | m1db               | | mysql              | | performance_schema | | Test               | | yydb               |+--------------------+7 rows in Set (0.00 sec)

Master 2

MariaDB [(None)]> show variables like ' server_id '; +---------------+-------+| variable_name | Value |+---------------+-------+| server_id     |    |+---------------+-------+1 row in Set (0.00 sec) MariaDB [(None)]> CREATE Database m2db; Query OK, 1 row Affected (0.00 sec) MariaDB [(None)]> show databases;+--------------------+| Database           |+--------------------+| information_schema | | m2db               | | mysql              | | performance_schema | | Test               | | Xxdb               |+--------------------+6 rows in Set (0.00 sec) MariaDB [(None)]>

The result is displayed on the slave side

MariaDB [(None)]> show databases;+--------------------+| Database           |+--------------------+| information_schema | | m1db               | | m2db               | | mysql              | | performance_schema || Test               | | xxdb               |+--------------------+7 rows in Set (0.00 sec)

Note:

In this experiment, multiple source replication cannot have a library of the same name in each master, or replication will fail.

=========================================== Finish =====================================

This article is from the "gentle" blog, make sure to keep this source http://essun.blog.51cto.com/721033/1398848