Mysql InnoDB Cluster部署装配_HTML

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');