mysql 主从复制 并行_MariaDB/MySQL数据库(GTID)主从复制并行复制配置

实验目的：

体验在数据库中配置多个slave线程进行同放操作有助于提高复制效率，减少主从数据库的数据差异。

操作演示：

在my.conf文件中加入slave_parallel_threads=N，N为并行数量。

[root@dbserver2 bin]# cat /etc/my.cnf

#

# This group is read both both by the client and the server

# use it for options that affect everything

#

[client-server]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

[mysqld]

log-bin=logbin

server_id=2

expire_logs_days=7

slave_parallel_threads=2

MariaDB [(none)]> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.221.135

Master_User: slave_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: logbin.000034

Read_Master_Log_Pos: 323

Relay_Log_File: dbserver2-relay-bin.000002

Relay_Log_Pos: 607

Relay_Master_Log_File: logbin.000034

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: 323

Relay_Log_Space: 908

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: 1

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: Slave_Pos

Gtid_IO_Pos: 0-1-12

1 row in set (0.00 sec)

可以看到已经有2个并行的进程在等待了：

MariaDB [(none)]> show processlist;

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+

| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             | Progress |

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+

|  3 | system user |           | NULL | Connect |  148 | Waiting for work from SQL thread                                            | NULL             |    0.000 |

|  4 | system user |           | NULL | Connect |  148 | Waiting for work from SQL thread                                            | NULL             |    0.000 |

|  5 | system user |           | NULL | Connect |  148 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |

|  6 | system user |           | NULL | Connect |  148 | 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)

提升到5的并发，看一下是否有5个线程在等待：

[root@dbserver2 bin]#cat /etc/my.cnf

#

# This group is read both both by the client and the server

# use it for options that affect everything

#

[client-server]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

[mysqld]

log-bin=logbin

server_id=2

expire_logs_days=7

slave_parallel_threads=5

验证是否分配了5个并发：

MariaDB [(none)]>start slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> show processlist;

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+

| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             | Progress |

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+

|  3 | system user |           | NULL | Connect |   14 | Waiting for work from SQL thread                                            | NULL             |    0.000 |

|  4 | system user |           | NULL | Connect |   14 | Waiting for work from SQL thread                                            | NULL             |    0.000 |

|  5 | system user |           | NULL | Connect |   14 | Waiting for work from SQL thread                                            | NULL             |    0.000 |

|  6 | system user |           | NULL | Connect |   14 | Waiting for work from SQL thread                                            | NULL             |    0.000 |

|  7 | system user |           | NULL | Connect |   14 | Waiting for work from SQL thread                                            | NULL             |    0.000 |

|  8 | system user |           | NULL | Connect |   14 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |

|  9 | system user |           | NULL | Connect |   14 | Waiting for master to send event                                            | NULL             |    0.000 |

| 11 | root        | localhost | NULL | Query   |    0 | init                                                                        | show processlist |    0.000 |

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+

8 rows in set (0.00 sec)

MariaDB [(none)]>show processlist\G;

*************************** 1. row ***************************

Id: 3

User: system user

Host:

db: NULL

Command: Connect

Time: 36

State: Waiting for work from SQL thread

Info: NULL

Progress: 0.000

*************************** 2. row ***************************

Id: 4

User: system user

Host:

db: NULL

Command: Connect

Time: 36

State: Waiting for work from SQL thread

Info: NULL

Progress: 0.000

*************************** 3. row ***************************

Id: 5

User: system user

Host:

db: NULL

Command: Connect

Time: 36

State: Waiting for work from SQL thread

Info: NULL

Progress: 0.000

*************************** 4. row ***************************

Id: 6

User: system user

Host:

db: NULL

Command: Connect

Time: 36

State: Waiting for work from SQL thread

Info: NULL

Progress: 0.000

*************************** 5. row ***************************

Id: 7

User: system user

Host:

db: NULL

Command: Connect

Time: 36

State: Waiting for work from SQL thread

Info: NULL

Progress: 0.000

*************************** 6. row ***************************

Id: 8

User: system user

Host:

db: NULL

Command: Connect

Time: 36

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

Progress: 0.000

*************************** 7. row ***************************

Id: 9

User: system user

Host:

db: NULL

Command: Connect

Time: 36

State: Waiting for master to send event

Info: NULL

Progress: 0.000

*************************** 8. row ***************************

Id: 11

User: root

Host: localhost

db: NULL

Command: Query

Time: 0

State: init

Info: show processlist

Progress: 0.000

8 rows in set (0.00 sec)

OK,已经有5个线程在等待重放主库的binlog！~~~