MariaDB多线程复制SQL thread和GTID详解

一、简单主从模式配置步骤
环境如下：
db:mariadb-10.0.10-linux-x86_64.tar.gz
os:centos6.7
1、配置主从节点的服务配置文件

1.1、配置master节点：

[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)
创建数据库和表

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)]>
查看
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、配置slave节点：
[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、创建复制用户

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、为备节点提供初始数据集

锁定主表，备份主节点上的数据，将其还原至从节点；如果没有启用GTID，在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置，以便后面启动slave节点时使用。

由于在master上有数据，则手动备份后倒入到从即可！

[root@mysql-slave mysql]# mysqldump --all-databases --lock-all-tables --flush-logs --master-data=2 > /tmp/all.sql
复制到从数据库

[root@mysql-slave mysql]# scp /tmp/all.sql [email protected]:/tmp/
[email protected]'s password:
all.sql      100%  511KB 510.6KB/s   00:00   
[root@mysql-slave mysql]#
复制完成后导入即可

[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、启动从节点的复制线程

1，打开备份文件查看复制位置
[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)

启动复制进程：
MariaDB [(none)]> start slave;

2，如果启用了GTID功能，则使用如下命令：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;
如：

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 statusG
*************************** 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)]>

验证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)]>
验证多线程：
在主库导入数据可在从使用以下进行查看验证
[root@slave mysql]# watch -n .5 "mysql -e 'show slave statusG'"
[root@slave mysql]# watch -n .5 "mysql -e 'show processlistG'"

双线程已经打开！
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和mariadb不同如下：
MariaDB GTID:

如上中应用MariaDB-10，需要做的修改：
1、不支持的参数：

gtid-mode=on
enforce-gtid-consistency=true
2、修改的参数：
slave-parallel-workers参数修改为slave-parallel-threads

3、连接至主服务使用的命令：
一个新的参数：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;