mysql8.0带来的新特性,结合MySQLshell,不需要第三方中间件,自动构建高可用集群。
mysql8.0作为一款新产品,其内置的mysq-innodb-cluster(MIC)高可用集群的技术确实惊艳,结合MySQLshell能够实施集群的快速部署,MySQL-route能够实现灾备快速切换,内置读写分离技术,负载均衡技术。结合但实际效果如何,还需验证。
一,集群部署
1.1 安装环境;
操作系统:Linux,版本:CentOS-7-x86
介质准备:无
环境清理
释放yum进程
[root@bug ~]# ps -ef|grep yum root 22481 1694 5 17 : 23 ? 00 : 00 : 03 /usr/bin/python /usr/share/PackageKit/helpers/yum/yumBackend.py get -updates none root 22591 22507 1 17 : 24 pts/ 00 : 00 : 00 grep --color=auto yum [root@bug ~]# kill - 9 22481
查看是否有多余系统,有则卸载
1 2 | [root@bug ~]# rpm -qa|grep mairadb [root@bug ~]# rpm -qa|grep mysql |
关闭防火墙
1 2 3 4 | [root@bug ~]# systemctl status firewalld.service ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled) Active: inactive (dead) |
关闭selinux,重启系统后生效
[root@bug selinux]# vi /etc/selinux/config SELINUX= disabled
[root@bug selinux]# reboot
1.2 安装MySQL8.0
1 2 3 | [root@bug ~]# yum install -y wget https: //repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm [root@bug ~]# yum list|grep mysql [root@bug ~]# yum install -y mysql-community-client.x86_64 mysql-router.x86_64 mysql-shell.x86_64 |
采用YUM源安装方式,总下载量约400M,
1.3自动集群部署
部署节点1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [root@bug ~]# mysqlsh
MySQL JS > dba.deploySandboxInstance(3310); A new MySQL sandbox instance will be created on this host in /root/mysql-sandboxes/3310
Warning: Sandbox instances are only suitable for deploying and running on your local machine for testing purposes and are not accessible from external networks.
Please enter a MySQL root password for the new instance: ****** Deploying new MySQL instance...
Instance localhost:3310 successfully deployed and started. Use shell.connect( 'root@localhost:3310' ); to connect to the instance. |
第一个节点部署完毕,端口设置为3310,登陆账号为root@localhost,密码 ******
使用本地认证的方式, 登陆数据库实例,进行验证。
1 2 3 | [root@bug ~]# mysql -uroot -porange -S /root/mysql-sandboxes/3310/sandboxdata/mysqld.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. |
同样的方法,部署节点2,节点3。
1 2 3 | MySQL JS > dba.deploySandboxInstance(3320);
MySQL JS > dba.deploySandboxInstance(3330); |
1.4创建集群
此实验采用简单的创建本地集群。
1 2 3 4 5 6 7 8 9 10 11 12 | MySQL JS > \connect root@localhost:3310 Creating a session to 'root@localhost:3310'
***************************************************** MySQL localhost:3310 ssl JS > var cluster=dba.createCluster( 'test' ) A new InnoDB cluster will be created on instance 'root@localhost:3310' .
*********************************************************
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances. At least 3 instances are needed for the cluster to be able to withstand up to one server failure. |
集群系统已经创建成功,最后一行:At least 3 instances are needed for the cluster to be able to withstand up to one server failure.提示需要至少三个实例,才能保证灾备,所以接下来,将节点2,3添加进集群。
1 2 3 4 5 6 7 8 | MySQL localhost:3310 ssl JS > cluster.addInstance( 'root@localhost:3320' )
The instance 'root@localhost:3320' was successfully added to the cluster.
MySQL localhost:3310 ssl JS > cluster.addInstance( 'root@localhost:3330' )
The instance 'root@localhost:3330' was successfully added to the cluster. |
查看集群状态
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | MySQL localhost:3310 ssl JS > dba.getCluster().status() { "clusterName" : "test" , "defaultReplicaSet" : { "name" : "default" , "primary" : "localhost:3310" , "ssl" : "REQUIRED" , "status" : "OK" , "statusText" : "Cluster is ONLINE and can tolerate up to ONE failure." , "topology" : { "localhost:3310" : { "address" : "localhost:3310" , "mode" : "R/W" , "readReplicas" : {}, "role" : "HA" , "status" : "ONLINE" }, "localhost:3320" : { "address" : "localhost:3320" , "mode" : "R/O" , "readReplicas" : {}, "role" : "HA" , "status" : "ONLINE" }, "localhost:3330" : { "address" : "localhost:3330" , "mode" : "R/O" , "readReplicas" : {}, "role" : "HA" , "status" : "ONLINE" } } }, "groupInformationSourceMember" : "mysql://root@localhost:3310" } |
一个简单的包含三个节点的集群已经创建完成。可以完成数据同步,读写分离等功能,比如此刻3310端口的状态是"R/W",同时read与write,3320与3330的状态是"R/O",只读模式。
1.5配置中间件
此时的集群的高可用性还不完整,需要MySQL-router来完成集群与外部的对接,实现自动切换,故障转移等功能。
MySQL-router的作用类似keepalived 类的中间件。当主机发生故障后,自动将应用切换到其他实例。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [root@bug ~]# mysqlrouter --bootstrap root@localhost:3310 --user=mysqlrouter Please enter MySQL password for root: *****
Bootstrapping system MySQL Router instance... Checking for old Router accounts Creating account mysql_router2_j05xzi45m81x@ '%' MySQL Router has now been configured for the InnoDB cluster 'test' .
The following connection information can be used to connect to the cluster.
Classic MySQL protocol connections to cluster 'test' : - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 X protocol connections to cluster 'test' : - Read/Write Connections: localhost:64460 - Read/Only Connections: localhost:64470
Existing configurations backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak' [root@bug ~]# mysqlrouter& [1] 25602 [root@bug ~]# ps -ef|grep router mysqlro+ 25602 22507 8 19:35 pts/0 00:00:01 mysqlrouter root 25619 22507 0 19:36 pts/0 00:00:00 grep --color=auto router |
验证MySQL-router安装效果
在MySQL-router默认配置下,
主机端口:6446
从库端口:6447
1 2 3 4 5 | [root@bug ~]# mysql -uroot -h 127.0.0.1 -P 6446 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. ********************************************************8 mysql> |
证明MySQL-router配置完成可用。
1.6验证集群效果
1,通过router同时登陆三个节点,查看端口号。
2,在节点1构造数据,在节点2,3差看状态,验证数据同步性。
3,节点1(主机)离线,查看集群状态与节点2,节点3状态,验证灾备能力。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31562969/viewspace-2284791/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31562969/viewspace-2284791/