MariaDB多线程复制SQL thread和GTID

2023年 7月 15日 111.9k 0

一、简单主从模式配置步骤环境如下: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;

请输入图片描述

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论