mysql 主从并行_mysql 主从复制优化、并行复制

实验环境：

server2 为 server3 的 master

server3 为 server1 的 master

即server1、2、3的身份如下：

server2：master

server3：master / slave

server2：slave

由于先前做过其他数据库实验，环境是server2为master, server1和server3为slave

实验操作：

server1、2、3配置文件修改如下：

server2

server3

server1

server3

mysql> grant replication slave on *.* to 'tutu'@'172.25.54.%' identified by '5820hhXM!@#';

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

server1

mysql> change master to master_host='172.25.54.3',master_user='tutu',master_password='5820hhXM!@#',master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.17 sec)

若出现Slave_SQL_Running: No

(原因：数据不同步，

在 server3 上 reset msater;

server1 上 shop slave; –> reset slave; –>start slave;)

server3

server1

测试

server2

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.00 sec)

mysql> create database test;

Query OK, 1 row affected (0.13 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

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

5 rows in set (0.00 sec)

server3 和 server1

server2

mysql> use test;

Database changed

mysql> create table userlist (

-> username varchar(15) not null,

-> password varchar(25) not null);

Query OK, 0 rows affected (0.41 sec)

mysql> desc userlist;

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

| Field | Type | Null | Key | Default | Extra |

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

| username | varchar(15) | NO | | NULL | |

| password | varchar(25) | NO | | NULL | |

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

2 rows in set (0.01 sec)

mysql> insert into userlist values ('user1','147');

Query OK, 1 row affected (0.09 sec)

mysql> insert into userlist values ('user2','258');

Query OK, 1 row affected (0.16 sec)

mysql> insert into userlist values ('user3','369');

Query OK, 1 row affected (0.08 sec)

mysql> select * from userlist;

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

| username | password |

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

| user1 | 147 |

| user2 | 258 |

| user3 | 369 |

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

3 rows in set (0.00 sec)

mysql> Bye

[root@server2 ~]#

server3 和 server1

注：bin log索引文件: /var/lib/mysql/mysql-bin.index

查看二进制日志：mysqlbinlog -vv –base64-output=DECODE-ROWS mysql-bin.000006 //-vv详细显示，–base64查看加密的数据

并行复制

server3——单线程–>多线程工作

mysql> show processlist;

[root@server3 ~]# vim /etc/my.cnf

[root@server3 ~]# cat /etc/my.cnf | tail -n 11

server-id=3

log-bin=mysql-bin

log-slave-updates

gtid_mode=ON

enforce-gtid-consistency=true

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

[root@server3 ~]# /etc/init.d/mysqld restart

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

[root@server3 ~]# mysql -p5820hhXM\!\@#

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 23

Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show processlist;

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

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

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

| 1 | system user | | NULL | Connect | 58 | Slave has read all relay log; waiting for more updates | NULL |

| 2 | system user | | NULL | Connect | 60 | Waiting for master to send event | NULL |

| 3 | system user | | NULL | Connect | 83590 | Waiting for an event from Coordinator | NULL |

| 4 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 6 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 7 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 8 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 9 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 10 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 11 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 12 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 13 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 16 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 17 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 18 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 19 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 20 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 21 | system user | | NULL | Connect | 60 | Waiting for an event from Coordinator | NULL |

| 22 | tutu | server1:48836 | NULL | Binlog Dump GTID | 5 | Master has sent all binlog to slave; waiting for more updates | NULL |

| 23 | root | localhost | NULL | Query | 0 | starting | show processlist |

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

20 rows in set (0.00 sec)