Mysql InnoDB Cluster部署装配
Mysql集群安装
参考官网:https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-userguide.html
环境简介:
四台虚拟机,分比为mysql1、mysql2、mysql3、mysql4(1、2、3安装集群,4安装mysqlrouter.)
(先看完再操作)
一、安装三个软件
1.1 mysql安装
官网资料: https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html
安装:
$> yum install mysql-community-server mysql-community-client
$> service mysqld start
Starting mysqld:[ OK ]
$> sudo service mysqld status
mysqld (pid 3066) is running.
初始密码:
$> grep 'temporary password' /var/log/mysqld.log
$> mysql -u root -p
Mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Dascom12__';
1.2 mysql-shell安装
$> yum install mysql-shell
(以上两步在 mysql1、mysql2、mysql3都安装)
1.3 mysql-router安装
$> yum install mysql-router
(mysqlrouter在架构里是跟application在一个机器上,所以一个系统里可能有多个mysqlrouter.)
(只在 mysql4 上安装)
二、配置
2.1 关闭防火墙和selinux、host
$> systemctl stop firewalld
$> vim /etc/selinux/config
SELINUX=disabled
$> vim /etc/hosts 增加主机名配置
192.168.*.* mysql1
192.168.*.* mysql2
192.168.*.* mysql3
2.2 创建用户与检查 (3个服务器都执行)
a. mysqlsh> dba.configureLocalInstance('root@localhost:3306')
输入 2 (创建新的集群用户)
b. $> service mysqld restart (重启mysql)(关键重要)
c. $> mysqlsh --uri "ic_user@mysql1:3306"
mysqlsh> dba.checkInstanceConfiguration()
(输出 status : OK 就好了)
2.3 创建集群
(创建集群和添加实例都只在master上操作,初次安装选择 mysql1上操作,mysql1 就是master。但 mysql1重启master会飘走)
master服务器执行
$> mysqlsh --uri "ic_user@mysql1:3306"
mysqlsh*** > var cluster = dba.createCluster('ClusterName')
成功:
Adding Seed Instance...
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.
失败:
提示Error就是失败了.
$> mysql -uroot -p
mysql> reset master
mysql> exit
再次尝试createCluster
2.4 添加实例
把mysql2、mysql3添加进来
注意:
1、获取cluster
var cluster是定义临时变量,\exit退出后失效.
$> mysqlsh --uri "ic_user@mysql1:3306"
mysqlsh***> var cluster = dba.getCluster('ClusterName')
2、添加之前先检查一下实例
$> mysqlsh
mysqlsh > dba.checkInstanceConfiguration('ic_user@mysql2:3306')
Please provide the password for 'ic_user@mysql2:3306': **********
Dba.checkInstanceConfiguration: Can't connect to MySQL server on 'mysql2' (113) (MySQL Error 2003)
如果出现这个错,就是从master(mysql1)到mysql2不通.检查防火墙或者主机名的配置.
直到在mysql1上 $> mysql -u mysql2 -u ic_user -p 能够登录为止. mysql3同理.
mysqlsh***> cluster.addInstance('ic_user@mysql2:3306')
mysqlsh***> cluster.addInstance('ic_user@mysql3:3306')
成功后
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 95f024a0-e2af-11ea-8bb1-08002773b035 | mysql3 | 3306 | ONLINE |
| group_replication_applier | 9c40caa1-e2af-11ea-877c-080027054126 | mysql2 | 3306 | ONLINE |
| group_replication_applier | caa78af2-e2ae-11ea-b8ca-080027ab81dd | mysql1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
(附: cluster.rejoinInstance()添加掉线的实例.)
2.5 配置mysqlrouter
安装后: (关闭firewalld)(在application服务器上)
$> mysqlrouter --bootstrap ic_user@192.168.21.97:3306 --user=mysqlrouter (注意这个user是mysqlrouter)
输出以下内容就是成功.
# Bootstrapping system MySQL Router instance...
- Creating account(s) (only those that are needed, if any)
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /etc/mysqlrouter/mysqlrouter.conf
Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'
# MySQL Router configured for the InnoDB Cluster 'cluster1'
After this MySQL Router has been started with the generated configuration
$ /etc/init.d/mysqlrouter restart
or
$ systemctl start mysqlrouter
or
$ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
the cluster 'cluster1' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
## MySQL X protocol
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470
启动mysqlrouter
$> systemctl start mysqlrouter
$> systemctl status mysqlrouter (查看运行状态)
登录
$> mysql -uic_user -pDascom12__ -P6446 --protocol=TCP
mysql> SELECT coalesce(@@report_host, @@hostname);
+-------------------------------------+
| coalesce(@@report_host, @@hostname) |
+-------------------------------------+
| mysql1 |
+-------------------------------------+
1 row in set (0.00 sec)
显示的是master的主机名.
附:
---查primary是哪台机器
select member_host from performance_schema.replication_group_members where MEMBER_ID in (select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member');
---查看cluster有哪些成员
select * from performance_schema.replication_group_members;
---查看mysql所在主机名
SELECT coalesce(@@report_host, @@hostname);
本文地址:https://blog.csdn.net/w0z1y/article/details/108202025
上一篇: 在Ansible中缓存事实
下一篇: Flink筛选重要配置参数