mysql 集群 replication,mysql group replication集群研究之三

前言

上一篇中我们从架构图中已经看到了Mysql-router.那么Mysql-router是什么呢?

MySQL Router就是一个轻量级的中间件,由Oracle的Mysql维护团队开发,用来

取代Mysql-Proxy.

Mysql-router用来实现后端MGR集群的高可用扩展.对应用程序来说链接Mysql-router

就和直连mysql一样,在MGR单主集群模式下,如果后端集群的主节点进行了切换,

mysql-router会自动更新元数据,使链接的请求正常处理.并且和MySQL Fabric不同的

是mysql-router不需要用独有的Java或python驱动.

安装MYSQL-ROUTER去mysql官方下载最新的包

1yum -y install https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-2.1.3-1.el7.x86_64.rpm

如果前面章节中你已经安装了mysql官方yum源

1yum -y install mysql-router

BootStrap MYSQL-ROUTER为了获得更好的性能,在实际的Product环境中Mysql-router通常与应用程序同台安装.

这样的好处是应用程序可以使用本地unix套接字连接Mysql-router,减少网络延迟.

下面是一个初始化例子:

1[root@app3 ~]# mysqlrouter --bootstrap root@db1 --user=mysqlrouter --conf-use-sockets -d /opt/myrouter

2Please enter MySQL password for root:

3

4Bootstrapping MySQL Router instance at /opt/myrouter...

5MySQL Router has now been configured for the InnoDB cluster 'devCluster'.

6

7The following connection information can be used to connect to the cluster.

8

9Classic MySQL protocol connections to cluster 'devCluster':

10- Read/Write Connections: localhost:6446

11- Read/Write Connections: /opt/myrouter/mysql.sock

12- Read/Only Connections: localhost:6447

13- Read/Only Connections: /opt/myrouter/mysqlro.sock

14

15X protocol connections to cluster 'devCluster':

16- Read/Write Connections: localhost:64460

17- Read/Write Connections: /opt/myrouter/mysqlx.sock

18- Read/Only Connections: localhost:64470

19- Read/Only Connections: /opt/myrouter/mysqlxro.sock

注解:

root@db1 这个是上节中我们配置集群通信用的用户.

--user=mysqlrouter 启动守护进程的用户是mysqlrouter,不建议用root.

--conf-use-sockets 使用本地sockets.

-d /opt/myrouter 指定初始化的目录.

启动mysql-router1[root@app3 ~]# cd /opt/myrouter/

2[root@app3 myrouter]# ls

3data log mysqlrouter.conf mysqlrouter.key run start.sh stop.sh

4[root@app3 myrouter]# sh start.sh

5[root@app3 myrouter]# ss -ntlp |grep 644

6LISTEN 0 128 *:64460 *:* users:(("main",pid=3062,fd=7))

7LISTEN 0 128 *:6446 *:* users:(("main",pid=3062,fd=5))

8LISTEN 0 128 *:6447 *:* users:(("main",pid=3062,fd=4))

9LISTEN 0 128 *:64470 *:* users:(("main",pid=3062,fd=6))

我们可以看到有两个端口分别是:6446和6447.

其中6446端口是高可用集群端口,通过此端口链接将定向到MGR集群的主节点.

6447端口是Round Robin模式的ReadOnly端口.

链接Mysql-router

我们通过链接Mysql-router的6446端口,创建个test数据库.

1[root@app3 myrouter]# mysql -u jeremy -P 6446 -p -h 127.0.0.1

2Enter password:

3mysql> CREATE DATABASE test;

4Query OK, 1 row affected (0.00 sec)

5

6mysql> use test

7Database changed

8

9mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);

10Query OK, 0 rows affected (0.03 sec)

11

12mysql> INSERT INTO t1 VALUES (1, 'Luis');

13Query OK, 1 row affected (0.01 sec)

创建成功后,我们手动连接到db1的mysql集群节点查看:

1## db1的mysql

2[root@db1 ~]# mysql -uroot

3mysql> show databases;

4+-------------------------------+

5| Database |

6+-------------------------------+

7| information_schema |

8| mysql |

9| mysql_innodb_cluster_metadata |

10| performance_schema |

11| sys |

12| test |

13+-------------------------------+

146 rows in set (0.00 sec)

15

16mysql> select * from test.t1;

17+----+------+

18| c1 | c2 |

19+----+------+

20| 1 | Luis |

21+----+------+

221 row in set (0.00 sec)

23

24## db3的mysql

25mysql> show databases;

26+-------------------------------+

27| Database |

28+-------------------------------+

29| information_schema |

30| mysql |

31| mysql_innodb_cluster_metadata |

32| performance_schema |

33| sys |

34| test |

35+-------------------------------+

366 rows in set (0.00 sec)

37

38mysql> select * from test.t1;

39+----+------+

40| c1 | c2 |

41+----+------+

42| 1 | Luis |

43+----+------+

441 row in set (0.00 sec)

45## 其他的节点就不测了,大家自己测下.

简单的测试来看,数据同步没问题.在实际的Product环境中,我们推荐的方式是,

先将mysql数据进行同步到每个要加入集群的mysql实例,在创建集群.

测试高可用

上面我们已经简单的看到创建了一个简单数据库test,并创建了t1表插入了简单数据.

我们先来看看现在主节点是哪个:

1[root@db3 myrouter]# mysqlsh --uri root@localhost -P 6446

2Creating a Session to 'root@localhost:6446'

3Enter password:

4Classic Session successfully established. No default schema selected.

5

6mysql-js> \py

7Switching to Python mode...

8mysql-py> cluster=dba.get_cluster()

9mysql-py> cluster.status()

10{

11"clusterName": "devCluster",

12"defaultReplicaSet": {

13"name": "default",

14"primary": "172.16.30.115:3306",

15"status": "OK_NO_TOLERANCE",

16"statusText": "Cluster is NOT tolerant to any failures.",

17"topology": {

18"172.16.30.115:3306": {

19"address": "172.16.30.115:3306",

20"mode": "R/W",

21"readReplicas": {},

22"role": "HA",

23"status": "ONLINE"

24},

25.......

很明显的看到R/W节点是172.16.30.115,也就是db1.

我们先通过mysql客户端登录6446端口,进行查询:

1shell > mysql -uroot -P 6446

2mysql> show databases;

3+-------------------------------+

4| Database |

5+-------------------------------+

6| information_schema |

7| crontab |

8| mysql |

9| mysql_innodb_cluster_metadata |

10| performance_schema |

11| sys |

12| test |

13+-------------------------------+

147 rows in set (0.02 sec)

15

16mysql> select * from test.tl;

17+----+----------+

18| id | name |

19+----+----------+

20| 7 | jeremy |

21| 14 | wangyang |

22+----+----------+

232 rows in set (0.19 sec)

这时我们手动down掉db1.example.com机器的mysql实例:

1mysql-py> cluster.status()

2Traceback (most recent call last):

3File "", line 1, in

4SystemError: MySQL Error (2006): MySQL server has gone away

5

6mysql-py> shell.connect('root@localhost:6446')

7Please provide the password for 'root@localhost:6446':

8Classic Session successfully established. No default schema selected.

9mysql-py> cluster.status()

10{

11"clusterName": "devCluster",

12"defaultReplicaSet": {

13"name": "default",

14"primary": "db2.example.com:3306",

15"status": "OK_NO_TOLERANCE",

16"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",

17"topology": {

18"172.16.30.115:3306": {

19"address": "172.16.30.115:3306",

20"mode": "R/O",

21"readReplicas": {},

22"role": "HA",

23"status": "(MISSING)"

24},

25"db2.example.com:3306": {

26"address": "db2.example.com:3306",

27"mode": "R/W",

28"readReplicas": {},

29"role": "HA",

30"status": "ONLINE"

31}

32}

33}

34}

35.....

这时间我们可以看到先前的mysql回话显示gone away.重连后再次查看

发现R/W节点已切换到db2.example.com.

我们通过mysql客户端连接,进行查询请求:

1[root@app3 myrouter]# mysql -uroot -P 6446

2mysql> select * from test.tl;

3+----+----------+

4| id | name |

5+----+----------+

6| 7 | jeremy |

7| 14 | wangyang |

8+----+----------+

92 rows in set (0.05 sec)

可以看到依然是没有问题的.如果请求在主节点切换时正好到达,会抛出一个ERROR: 2013 (HY000): Lost connection to MySQL server during query异常.

本文到这里先结束了,下一篇介绍MGR常见的问题和使用的一些问题.