MariaDB多线程复制SQL thread和GTID-每日运维

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

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 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]#

复制完成后导入即可

[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-39mysql> 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;

请输入图片描述

MariaDB多线程复制SQL thread和GTID

相关文章

发布评论取消回复

大树

使用BBED修复损坏的SYSTEM文件头

Apache的URL缩短功能如何实现

国产编程语言 MoonBit 登顶海外知名媒体 The New Stack 热门文章榜

MariaDB多线程复制SQL thread和GTID

相关文章

发布评论 取消回复

发布评论取消回复