MARIADB multithreaded replication SQL thread and Gtid detailed

First, simple master-slave mode configuration steps
The environment is as follows:
Db:mariadb-10.0.10-linux-x86_64.tar.gz
os:centos6.7
1. Configure the service profile of the master and slave node

1.1. Configure 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-threads=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=master.linuxea

[Root@mysql-slave mysql]# hostname Master.linuxea
[Root@mysql-slave mysql]# cat/etc/hosts
192.168.0.100 Master.linuxea
192.168.0.101 Slave.linuxea
MARIADB [(None)]> show global variables like '%gtid% ';
+------------------------+-------+
| variable_name | Value |
+------------------------+-------+
| Gtid_binlog_pos | |
| Gtid_binlog_state | |
| Gtid_current_pos | |
| gtid_domain_id | 0 |
| Gtid_ignore_duplicates | Off |
| Gtid_slave_pos | |
| Gtid_strict_mode | Off |
+------------------------+-------+
7 rows in Set (0.02 sec)
Creating databases and Tables

MARIADB [(None)]> CREATE Database Linuxea;
Query OK, 1 row affected (0.01 sec)

MARIADB [(None)]> CREATE TABLE Linuxea.t5 (Name CHAR (30));
Query OK, 0 rows affected (0.03 sec)

MARIADB [(None)]>
View
MARIADB [(None)]> show Binlog events in ' master-bin.000001 ';
+-------------------+-----+-------------------+-----------+-------------+-------------------------------------- ----------+
| Log_name | Pos | Event_type | server_id | End_log_pos | Info |
+-------------------+-----+-------------------+-----------+-------------+-------------------------------------- ----------+
| master-bin.000001 | 4 | Format_desc | 1 | 248 | Server ver:10.0.10-mariadb-log, Binlog ver:4 |
| master-bin.000001 | 248 | Gtid_list | 1 | 277 | [] |
| master-bin.000001 | 277 | Binlog_checkpoint | 1 | 321 | master-bin.000001 |
| master-bin.000001 | 321 | Gtid | 1 | 363 | Gtid 0-1-1 |
| master-bin.000001 | 363 | Query | 1 | 456 | Create Database Linuxea |
| master-bin.000001 | 456 | Gtid | 1 | 498 | Gtid 0-1-2 |
| master-bin.000001 | 498 | Query | 1 | 599 | CREATE TABLE Linuxea.t5 (Name CHAR (30)) |
+-------------------+-----+-------------------+-----------+-------------+-------------------------------------- ----------+
7 rows in Set (0.01 sec)

MARIADB [(None)]>

1.2, configure the slave node:
[Root@slave mysql]# hostname Slave.linuxea

[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-threads=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=slave.linuxea
MARIADB [(None)]>show global variables like '%gtid% ';
+------------------------+------------+
| variable_name | Value |
+------------------------+------------+
| Gtid_binlog_pos | 0-200-2129 |
| Gtid_binlog_state | 0-200-2129 |
| Gtid_current_pos | 0-200-2129 |
| gtid_domain_id | 0 |
| Gtid_ignore_duplicates | Off |
| Gtid_slave_pos | |
| Gtid_strict_mode | Off |
+------------------------+------------+
7 Rows in Set (0.00 sec)

MARIADB [(None)]>
2. Create a replication user

MARIADB [(None)]> GRANT REPLICATION SLAVE on *.* to ' user ' @ ' 192.168.0.% ' identified by ' pass ';
Query OK, 0 rows Affected (0.00 sec)

MARIADB [(None)]> flush privileges;
Query OK, 0 rows Affected (0.00 sec)

MARIADB [(None)]>
3. Provide initial data set for standby node

Lock the primary table, back up the data on the master node, and restore it to the from node, and if you do not enable Gtid, you need to use the show Master Status command on master to view the binary log file name and event location at the time of the backup so that you can start the slave node later.

Since there is data on master, the manual backup is poured into the from!

[Root@mysql-slave mysql]# mysqldump--all-databases--lock-all-tables--flush-logs--master-data=2 >/tmp/all.sql
Copy to from database

[Root@mysql-slave mysql]# Scp/tmp/all.sql root@192.168.0.101:/tmp/
Root@192.168.0.101 ' s Password:
All.sql 100% 511KB 510.6kb/s 00:00
[Root@mysql-slave mysql]#
Import can be completed after replication

[Root@slave mysql]# MySQL </tmp/all.sql
[Root@slave mysql]# mysql-e "show databases;"
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Linuxea |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
[Root@slave mysql]#
4, start the replication thread from the node

1, open the backup file to view the replication location
[Root@slave mysql]# Vim/tmp/all.sql

--Change MASTER to master_log_file= ' master-bin.000003 ', master_log_pos=395;
MARIADB [(None)]> change MASTER to master_host= ' 192.168.0.100 ', master_user= ' USER ', master_password= ' pass ', master_ Log_file= ' master-bin.000003 ', master_log_pos=395;
Query OK, 0 rows affected (0.05 sec)

To start the replication process:
MARIADB [(None)]> start slave;

2, if the Gtid feature is enabled, use the following command: gtid_io_pos:0-300-39
mysql> change MASTER to master_host= ' 192.168.0.100 ', master_user= ' USER ', master_password= ' pass ', master_use_gtid= Current_pos;
Such as:

MARIADB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)

MARIADB [(None)]>change MASTER to master_host= ' 192.168.0.100 ', master_user= ' USER ', master_password= ' pass ', master_ Use_gtid=current_pos;
Query OK, 0 rows affected (0.07 sec)

MARIADB [(None)]> start slave;
Query OK, 0 rows affected (0.02 sec)

MARIADB [(none)]> show Slave status\g
1. Row *************************** slave_io_state:waiting for master to send event
master_host:192.168.0.100
Master_user:user
master_port:3306
Connect_retry:60
master_log_file:master-bin.000003
read_master_log_pos:8879
relay_log_file:slave-relay-bin.000002
relay_log_pos:730
relay_master_log_file:master-bin.000003
Slave_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:0
Last_error:
skip_counter:0
exec_master_log_pos:8879
relay_log_space:1031
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:0
Master_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:300
MASTER_SSL_CRL:
Master_ssl_crlpath:
Using_gtid:current_pos
Gtid_io_pos:0-300-39
1 row in Set (0.00 sec)

MARIADB [(None)]>

Verify Gtid:

MARIADB [(None)]> show global variables like '%gtid% ';
+------------------------+---------------------+
| variable_name | Value |
+------------------------+---------------------+
| Gtid_binlog_pos | 0-300-39 |
| Gtid_binlog_state | 0-200-2257,0-300-39 |
| Gtid_current_pos | 0-300-39 |
| gtid_domain_id | 0 |
| Gtid_ignore_duplicates | Off |
| Gtid_slave_pos | 0-300-39 |
| Gtid_strict_mode | Off |
+------------------------+---------------------+
7 Rows in Set (0.00 sec)

MARIADB [(None)]>
Verify Multithreading:
Importing data in the main library can be viewed as validation from the use of the following
[Root@slave mysql]# watch-n. 5 "mysql-e ' show slave status\g '"
[Root@slave mysql]# watch-n. 5 "Mysql-e ' Show Processlist\g '"

Dual thread already open!
MARIADB [(None)]> show global variables like '%parallel% ';
+-------------------------------+--------+
| variable_name | Value |
+-------------------------------+--------+
| Slave_domain_parallel_threads | 0 |
| slave_parallel_max_queued | 131072 |
| Slave_parallel_threads | 2 |
+-------------------------------+--------+
3 Rows in Set (0.00 sec)

MARIADB [(none)]>show processlist;
+----+-------------+-----------+------+---------+------+------------------------------------------------------- ----------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+---------+------+------------------------------------------------------- ----------------------+------------------+----------+
| 3 | System user | | NULL | Connect | 22 | Waiting for work from SQL thread | NULL | 0.000 |
| 4 | System user | | NULL | Connect | 22 | Waiting for work from SQL thread | NULL | 0.000 |
| 5 | System user | | NULL | Connect | 22 | Slave has read all relay log; Waiting for the slave I/O thread to update it | NULL | 0.000 |
| 6 | System user | | NULL | Connect | 22 | Waiting for Master to send event | NULL | 0.000 |
| 8 | Root | localhost | NULL | Query | 0 | init | Show Processlist | 0.000 |
+----+-------------+-----------+------+---------+------+------------------------------------------------------- ----------------------+------------------+----------+
5 rows in Set (0.00 sec)

MARIADB [(None)]>

MySQL Gtid and mariadb differ as follows:
MARIADB Gtid:

If you apply MariaDB-10 in the above, you need to make the changes:
1, unsupported parameters:

Gtid-mode=on
Enforce-gtid-consistency=true
2, the modified parameters:
Slave-parallel-workers parameter modified to Slave-parallel-threads

3, connect to the main service to use the command:
A new parameter: Master_user_gtid={current_pos|slave_pos|no}

Change MASTER to Master_host= "127.0.0.1", master_port=3310, master_user= "root", master_use_gtid=current_pos;