mysql1-CSDN博客

本文链接：https://blog.csdn.net/weixin_45308597/article/details/115455520

NSD RDBMS1 DAY01
案例1：构建MySQL服务器
案例2：数据库基本管理
案例3：字符类型
案例4：数值类型
案例5：日期时间类型
案例6：枚举类型
1 案例1：构建MySQL服务器
1.1 问题
要求如下：

在IP地址192.168.4.50主机上部署mysql服务
设置数据库管理员root本机登录密码为tarena
1.2 方案
克隆新的虚拟机：

eth0网卡:192.168.4.50

主机名称:host50

下载软件mysql-5.7.17.tar

关闭防火墙（如果有的话）

关闭SELinux（如果有的话）

1.3 步骤
实现此案例需要按照如下步骤进行。

步骤一：准备工作

1）如果之前有mariadb，则需要先卸载，并删除对应的配置与数据：

[root@localhost ~]# systemctl stop mariadb
2）删除/etc/my.cnf配置文件

此配置文件由RHEL自带的mariadb-libs库提供：

[root@localhost ~]# rm -rf /etc/my.cnf

3）删除数据

[root@localhost ~]# rm -rf /var/lib/mysql/*
4）卸载软件包（没有会显示未安装软件包）

[root@localhost ~]# rpm -e --nodeps mariadb-server mariadb
警告：/var/log/mariadb/mariadb.log 已另存为/var/log/mariadb/mariadb.log.rpmsave
步骤二：安装mysql软件包

1）解压mysql-5.7.17.tar 软件包

[root@host50 ~]# tar -xvf mysql-5.7.17.tar //解压mysql整合包
./mysql-community-client-5.7.17-1.el7.x86_64.rpm
./mysql-community-common-5.7.17-1.el7.x86_64.rpm
./mysql-community-devel-5.7.17-1.el7.x86_64.rpm
./mysql-community-embedded-5.7.17-1.el7.x86_64.rpm
./mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm
./mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm
./mysql-community-libs-5.7.17-1.el7.x86_64.rpm
./mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
./mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
./mysql-community-server-5.7.17-1.el7.x86_64.rpm
./mysql-community-test-5.7.17-1.el7.x86_64.rpm
2）安装MySQL软件包

[root@host50 ~]# yum -y install mysql-community-*.rpm //yum安装自动解决依赖
./mysql-community-client-5.7.17-1.el7.x86_64.rpm
./mysql-community-common-5.7.17-1.el7.x86_64.rpm
./mysql-community-devel-5.7.17-1.el7.x86_64.rpm
./mysql-community-embedded-5.7.17-1.el7.x86_64.rpm
./mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm
./mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm
./mysql-community-libs-5.7.17-1.el7.x86_64.rpm
./mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
./mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
./mysql-community-server-5.7.17-1.el7.x86_64.rpm
./mysql-community-test-5.7.17-1.el7.x86_64.rpm
3）启动MySQL数据库服务并设置开机自启

提示：第一次启动，需要初始化数据，会比较慢

[root@host50 ~]# systemctl start mysqld //启动mysql服务
[root@host50 ~]# systemctl enable mysqld //设置开机自启
[root@host50 ~]# systemctl status mysqld //查看mysql服务状态
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 二 2018-08-28 10:03:24 CST; 8min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 4284 (mysqld)
CGroup: /system.slice/mysqld.service
└─4284 /usr/sbin/mysqld --daemonize --pid-file=/var/r…
8月 28 10:02:56 localhost.localdomain systemd[1]: Starting MySQ…
8月 28 10:03:24 localhost.localdomain systemd[1]: Started MySQL…
Hint: Some lines were ellipsized, use -l to show in full.
步骤三：连接MySQL服务器，修改密码

查看初始密码

[root@host50 ~]#grep –i ‘password’ /var/log/mysqld.log
2017-04-01T18:10:42.948679Z 1 [Note] A temporary password is generated for root@localhost: mtoa>Av<p6Yk //随机生成的管理密码为mtoa>Av<p6Yk
2）使用初始密码连接mysql服务

[root@host50 ~]# mysql -u root -p’mtoa>Av<p6Yk’ //初始密码登录，
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 11
Server version: 5.7.17
Copyright © 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> //登录成功后，进入SQL操作环境
3）重置数据库管理员roo本机登录密码

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement //提示必须修改密码
mysql> alter user root@”localhost” identified by “123qqq…A”; //修改登陆密码
Query OK, 0 rows affected (0.00 sec)
mysql> exit //断开连接
[root@host50 ~]#
4）修改密码策略

[root@host50 ~]# mysql -uroot –p123qqq…A
mysql>
mysql>set global validate_password_policy=0; //只验证长度
Query OK, 0 rows affected (0.00 sec)
mysql>set global validate_password_length=6； //修改密码长度,默认值是8个字符
Query OK, 0 rows affected (0.00 sec)
mysql> alter user root@”localhost” identified by “tarena”; //修改登陆密码
Query OK, 0 rows affected (0.00 sec)
mysql>exit
5）使用修改后的密码登录

[root@host50 ~]# mysql -uroot -ptarena //登录
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright © 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.
mysql> show databases; //查看数据库
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)
mysql>
2 案例2：数据库基本管理
2.1 问题
本案例练习对库、表、记录的基本管理，具体操作如下：

使用mysql命令连接数据库
练习库管理命令（查看、删除、创建库、切换）
练习表管理命令（查看、删除、创建表）
练习记录管理命令（插入、查看、修改、删除）
表－1 测试用表数据

2.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：使用mysql命令连接数据库

连接MySQL服务器时，最基本的用法是通过 -u 选项指定用户名、-p指定密码。密码可以写在命令行（如果不写，则出现交互，要求用户输入），当然基于安全考虑一般不推荐这么做：

[root@dbsvr1 ~]# mysql -uroot -p123456 //紧挨着选项，不要空格
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 16
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright © 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> exit //退出已登录的mysql> 环境
Bye
默认情况下，msyql命令会连接本机的MySQL服务。但在需要的时候，可以通过 -h 选项指定远程主机；

[root@dbsvr1 ~]# mysql -h 127.0.0.1 –u root –p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright © 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> exit //退出已登录的mysql环境
Bye
步骤二：练习查看/删除/创建库的相关操作

以root用户登入“mysql> ”环境后，可以执行各种MySQL指令、SQL指令。基本的用法事项如下：

操作指令不区分大小写（库名/表名、密码、变量值等除外）。
每条SQL指令以 ; 结束或分隔。
不支持 Tab 键自动补齐。
\c 可废弃当前编写错的操作指令。
1）查看现有的库

mysql> use sys; //切换到sys库
Database changed
mysql> select database(); //确认当前所在的库
±-----------+
| DATABASE() |
±-----------+
| sys |
±-----------+
1 row in set (0.00 sec)
切换到mysql库：

mysql> use mysql; //切换到mysql库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select database(); //确认当前所在的库
±-----------+
| DATABASE() |
±-----------+
| mysql |
±-----------+
1 row in set (0.00 sec)
5 rows in set (0.00 sec)
3）新建名为newdb的库，确认结果：

1）查看指定的库里有哪些表

查看mysql库里有哪些表：

当前库为mysql，查看columns_priv表的结构，以列表形式展现：

mysql> desc columns_priv\G //查看表结构，以列表形式展现，末尾不用分号
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: Db
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: User
Type: char(32)
Null: NO
Key: PRI
Default:
Extra:
*************************** 4. row ***************************
Field: Table_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 5. row ***************************
Field: Column_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 6. row ***************************
Field: Timestamp
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 7. row ***************************
Field: Column_priv
Type: set(‘Select’,‘Insert’,‘Update’,‘References’)
Null: NO
Key:
Default:
Extra:
7 rows in set (0.01 sec)
查看columns_priv表的结构，以表格形式展现：

mysql> desc columns_priv; //查看表结构，以表格形式展现末尾需要有分号
±------------±---------------------------------------------±-----±----±------------------±----------------------------+
| Field | Type | Null | Key | Default | Extra |
±------------±---------------------------------------------±-----±----±------------------±----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set(‘Select’,‘Insert’,‘Update’,‘References’) | NO | | | |
±------------±---------------------------------------------±-----±----±------------------±----------------------------+
7 rows in set (0.00 sec)
上述操作中，当引用非当前库中的表时，可以用“库名.表名”的形式。比如，切换为mysql库再执行“desc columns_priv;”，与以下操作的效果是相同的：

mysql> desc mysql.columns_priv;
±------------±---------------------------------------------±-----±----±------------------±----------------------------+
| Field | Type | Null | Key | Default | Extra |
±------------±---------------------------------------------±-----±----±------------------±----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set(‘Select’,‘Insert’,‘Update’,‘References’) | NO | | | |
±------------±---------------------------------------------±-----±----±------------------±----------------------------+
7 rows in set (0.00 sec)
3）在test库中创建一个名为pwlist的表

包括name、password两列，其中name列作为主键。两个字段值均不允许为空，其中密码列赋予默认空值，相关操作如下所述。

切换到mydb库：

mysql> use mydb;
Database changed
新建pwlist表：

mysql> create table pwlist(
-> name char(16) not null,
-> password char(48)default ‘’,
-> primary key(name)
-> );
Query OK, 0 rows affected (0.38 sec)
确认新创建的表：

mysql> show tables;
±---------------+
| Tables_in_mydb |
±---------------+
| pwlist | //新建的pwlist表
±---------------+
1 rows in set (0.01 sec)
查看pwlist表的字段结构：

删除当前库中的pwlist表：

mysql> drop table pwlist;
Query OK, 0 rows affected (0.01 sec)
确认删除结果：

mysql> show tables;
Empty set (0.00 sec)
5）在mydb库中创建一个学员表

表格结构及数据内容如表-1所示。

在MySQL表内存储中文数据时，需要更改字符集（默认为latin1不支持中文），以便MySQL支持存储中文数据记录；比如，可以在创建库或表的时候，手动添加“DEFAULT CHARSET=utf8”来更改字符集。

根据上述表格结构，创建支持中文的student表：

mysql> CREATE TABLE mydb.student(
-> 学号 char(9) NOT NULL,
-> 姓名 varchar(4) NOT NULL,
-> 性别 enum(‘男’,‘女’) NOT NULL,
-> 手机号 char(11) DEFAULT ‘’,
-> 通信地址 varchar(64),
-> PRIMARY KEY(学号)
-> ) DEFAULT CHARSET=utf8; //手工指定字符集，采用utf8
Query OK, 0 rows affected (0.31sec)
查看student表的字段结构：

mysql> DESC mydb.student;
±-------------±------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------------±------------------±-----±----±--------±------+
| 学号 | char(9) | NO | PRI | NULL | |
| 姓名 | varchar(4) | NO | | NULL | |
| 性别 | enum(‘男’,‘女’) | NO | | NULL | |
| 手机号 | char(11) | YES | | | |
| 通信地址 | varchar(64) | YES | | NULL | |
±-------------±------------------±-----±----±--------±------+
5 rows in set (0.00 sec)
查看student表的实际创建指令：

mysql> SHOW CREATE TABLE mydb.student;
±--------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Table |Create Table |
±--------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE student (
学号 char(9) NOT NULL,
姓名 varchar(4) NOT NULL,
性别 enum(‘男’,‘女’) NOT NULL,
手机号 char(11) DEFAULT ‘’,
通信地址 varchar(64) DEFAULT NULL,
PRIMARY KEY (学号)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
注意：若要修改MySQL服务的默认字符集，可以更改服务器的my.cnf配置文件，添加character_set_server=utf8 配置，然后重启数据库服务。

3.5 步骤
实现此案例需要按照如下步骤进行。

步骤一：创建a3表

1）新建db1库，并切换到db1库

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)
mysql> USE db1;
Database changed
2）新建t3表

mysql> CREATE TABLE db1.t3 (
-> name char(5) ,
-> mail varchar(10),
-> homedir varchar(50)
-> );
Query OK, 0 rows affected (0.61sec)
3) 查看a3表结构

4.5 步骤
实现此案例需要按照如下步骤进行。

步骤一：创建t2表

1）切换到db1库

mysql> USE db1;
Database changed
2）新建t2表

mysql> create table db1.t2(
-> stu_num int,
-> name char(5),
-> age tinyint,
-> pay float,
-> money float(5,2)
-> );
Query OK, 0 rows affected (0.03 sec)
3) 查看t2表结构

5.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：练习时间函数的使用

1）使用now()查看当前的日期和时间

mysql> SELECT now();
±--------------------+
| now() |
±--------------------+
| 2019-07-03 05:00:15 |
±--------------------+
1 row in set (0.00 sec)
mysql>
2）使用curdate()获得当前的日期

mysql> SELECT curdate();
±-----------+
| curdate() |
±-----------+
| 2019-07-03 |
1 row in set (0.00 sec)
mysql>
3）使用curtime()获得当前的时间

mysql> SELECT curtime();
±----------+
| curtime() |
±----------+
| 04:04:55 |
±----------+
1 row in set (0.00 sec)
4）分别获取当前日期时间中的年份、月份、日

mysql> SELECT year(now()) , month(now()) , day(now());
±------------±-------------±-----------+
| year(now()) | month(now()) | day(now()) |
±------------±-------------±-----------+
| 2019 | 7 | 3 |
±------------±-------------±-----------+
1 row in set (0.00 sec)
mysql>
6）获取系统日期

mysql> select date(now());
±------------+
| date(now()) |
±------------+
| 2019-07-03 |
±------------+
1 row in set (0.00 sec)1 row in set (0.00 sec)
Mysql>
步骤二：创建t4表

1）建表

mysql> create table db1.t4(
-> name char(10),
-> your_start year,
-> up_time time,
-> birthday date,
-> party datetime
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
2) 查看表结构

mysql>
mysql> insert into db1.t4 values(“bob”,1990,083000,20191120,2019082820000);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into db1.t4 values(“tom”,1991,090000,20191120,now());
Query OK, 1 row affected (0.02 sec)
mysql>
4）查看表记录

mysql>

mysql> select * from db1.t4;

±-----±-----------±---------±-----------±--------------------+

±-----±-----------±---------±-----------±--------------------+

| bob | 1990 | 08:30:00 | 2019-11-20 | 0000-00-00 00:00:00 |

| tom | 1991 | 09:00:00 | 2019-11-20 | 2019-07-03 05:12:41 |

±-----±-----------±---------±-----------±--------------------+

2 rows in set (0.00 sec)

mysql>

6 案例6：枚举类型
6.1 问题
按照图-4所示建表

图－4

6.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：创建t5表

1）建表

mysql>
mysql> create table db1.t5 (
-> name char(5),
-> likes set(“eat”,“game”,“film”,“music”),
-> sex enum(“boy”,“girl”,“no”)
-> );
Query OK, 0 rows affected (0.04 sec)
Mysql>
2）查看表结构

mysql>
mysql> insert into db1.t5 values (“bob”,“eat,film,game”,“boy”);
Query OK, 1 row affected (0.03 sec)
mysql>
4）查看表记录

mysql> select * from db1.t5;
±-----±--------------±-----+
| name | likes | sex |
±-----±--------------±-----+
| bob | eat,game,film | boy |
±-----±--------------±-----+
1 rows in set (0.00 sec)
mysql>

Top
NSD RDBM2 DAY04
案例1：准备MHA集群环境
案例2：部署MHA集群
案例3：测试配置
1 案例1：准备MHA集群环境
1.1 问题
配置SSH免密登录
安装依赖包
配置MySQL一主多从结构
1.2 方案
准备5台虚拟机，角色规划如图-1所示。

图－1

IP规划，如图-2所示：

图-2

1.3 步骤
实现此案例需要按照如下步骤进行。

步骤一：配置ssh免密登录

1）配置数据库服务器192.168.4.51

[root@host51 ~]# ssh-keygen //创建秘钥对

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/id_rsa): //回车

Enter passphrase (empty for no passphrase): //回车

Enter same passphrase again:

Your identification has been saved in /root/.ssh/id_rsa.

Your public key has been saved in /root/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:qb7EZByHad3Jadr+zkiEbo7ZKGmCNlctgp+Wfp3Yad0 root@pxcnode71

The key’s randomart image is:

±–[RSA 2048]----+

| |

| + o o |

| = o * |

| o o * |

| . = S o |

| . . * + o |

| … =.O * + |

|.o.*+= & o E |

|. =+…B.o …+ |

±—[SHA256]-----+

[root@host51 ~]#

[root@host51 ~]# ssh-copy-id root@192.168.4.52 //传递公钥给host52主机

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: “/root/.ssh/id_rsa.pub”

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed – if you are prompted now it is to install the new keys

root@192.168.4.71’s password: //输入host52主机系统管理员root用户密码

Number of key(s) added: 1

Now try logging into the machine, with: “ssh ‘root@192.168.4.52’”

and check to make sure that only the key(s) you wanted were added.

[root@host51 ~]#

[root@host51 ~]# ssh-copy-id root@192.168.4.53 //传递公钥给host53主机

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: “/root/.ssh/id_rsa.pub”

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed – if you are prompted now it is to install the new keys

root@192.168.4.71’s password: //输入host53主机系统管理员root用户密码

Number of key(s) added: 1

Now try logging into the machine, with: “ssh ‘root@192.168.4.53’”

and check to make sure that only the key(s) you wanted were added.

[root@host51 ~]#

[root@host51 ~]# ssh root@192.168.4.52 //可以无密码连接52主机

Last login: Fri Jun 21 13:21:39 2019 from 192.168.4.254

.-"""-.

/ .===. \

/ 6 6 /

( ___/ )

ooo_/__________

/ \

| I am Virtual Host ! ! ! |

_______________ooo/

| | |

|_ | _|

| | |

|||

/-‘Y’-\

(__/ __)

[root@host52 ~]#

[root@host52 ~]# exit //断开连接

登出

Connection to 192.168.4.52 closed.

[root@host51 ~]#

[root@host51 ~]# ssh root@192.168.4.53 //可以无密码连接52主机

Last login: Fri Jun 21 09:01:15 2019 from 192.168.4.254

.-"""-.

/ .===. \

/ 6 6 /

( ___/ )

ooo_/__________

/ \

| I am Virtual Host ! ! ! |

_______________ooo/

| | |

|_ | _|

| | |

|||

/-‘Y’-\

(__/ __)

[root@host53 ~]# exit//断开连接

登出

Connection to 192.168.4.53 closed.

[root@host51 ~]#

2）配置数据库服务器192.168.4.52

[root@host52 ~]# ssh-keygen //创建秘钥对

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/id_rsa): //回车

Enter passphrase (empty for no passphrase): //回车

Enter same passphrase again:

Your identification has been saved in /root/.ssh/id_rsa.

Your public key has been saved in /root/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:qb7EZByHad3Jadr+zkiEbo7ZKGmCNlctgp+Wfp3Yad0 root@pxcnode71

The key’s randomart image is:

±–[RSA 2048]----+

| |

| + o o |

| = o * |

| o o * |

| . = S o |

| . . * + o |

| … =.O * + |

|.o.*+= & o E |

|. =+…B.o …+ |

±—[SHA256]-----+

[root@host52 ~]#

[root@host52 ~]# ssh-copy-id root@192.168.4.51 //传递公钥给host51主机

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: “/root/.ssh/id_rsa.pub”

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed – if you are prompted now it is to install the new keys

root@192.168.4.51’s password: //输入host51主机系统管理员root用户密码

Number of key(s) added: 1

Now try logging into the machine, with: “ssh ‘root@192.168.4.51’”

and check to make sure that only the key(s) you wanted were added.

[root@host52 ~]#

[root@host52 ~]# ssh-copy-id root@192.168.4.53 //传递公钥给host53主机

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: “/root/.ssh/id_rsa.pub”

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed – if you are prompted now it is to install the new keys

root@192.168.4.53’s password: //输入host53主机系统管理员root用户密码

Number of key(s) added: 1

Now try logging into the machine, with: “ssh ‘root@192.168.4.53’”

and check to make sure that only the key(s) you wanted were added.

[root@host52 ~]#

[root@host52 ~]# ssh root@192.168.4.51 //可以无密码连接51主机

Last login: Fri Jun 21 13:21:39 2019 from 192.168.4.254

.-"""-.

/ .===. \

/ 6 6 /

( ___/ )

ooo_/__________

/ \

| I am Virtual Host ! ! ! |

_______________ooo/

| | |

|_ | _|

| | |

|||

/-‘Y’-\

(__/ __)

[root@host51 ~]#

[root@host51 ~]# exit //断开连接

登出

Connection to 192.168.4.52 closed.

[root@host52 ~]#

[root@host52 ~]# ssh root@192.168.4.53 //可以无密码连接53主机

Last login: Fri Jun 21 09:01:15 2019 from 192.168.4.254

.-"""-.

/ .===. \

/ 6 6 /

( ___/ )

ooo_/__________

/ \

| I am Virtual Host ! ! ! |

_______________ooo/

| | |

|_ | _|

| | |

|||

/-‘Y’-\

(__/ __)

[root@host53 ~]# exit//断开连接

登出

Connection to 192.168.4.53 closed.

[root@host52 ~]#

3）配置数据库服务器192.168.4.53

[root@host53 ~]# ssh-keygen //创建秘钥对

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/id_rsa): //回车

Enter passphrase (empty for no passphrase): //回车

Enter same passphrase again:

Your identification has been saved in /root/.ssh/id_rsa.

Your public key has been saved in /root/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:qb7EZByHad3Jadr+zkiEbo7ZKGmCNlctgp+Wfp3Yad0 root@pxcnode71

The key’s randomart image is:

±–[RSA 2048]----+

| |

| + o o |

| = o * |

| o o * |

| . = S o |

| . . * + o |

| … =.O * + |

|.o.*+= & o E |

|. =+…B.o …+ |

±—[SHA256]-----+

[root@host53 ~]#

[root@host53 ~]# ssh-copy-id root@192.168.4.51 //传递公钥给host51主机

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: “/root/.ssh/id_rsa.pub”

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed – if you are prompted now it is to install the new keys

root@192.168.4.51’s password: //输入host51主机系统管理员root用户密码

Number of key(s) added: 1

Now try logging into the machine, with: “ssh ‘root@192.168.4.51’”

and check to make sure that only the key(s) you wanted were added.

[root@host53 ~]#

[root@host53 ~]# ssh-copy-id root@192.168.4.52 //传递公钥给host52主机

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: “/root/.ssh/id_rsa.pub”

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed – if you are prompted now it is to install the new keys

root@192.168.4.52’s password: //输入host52主机系统管理员root用户密码

Number of key(s) added: 1

Now try logging into the machine, with: “ssh ‘root@192.168.4.52’”

and check to make sure that only the key(s) you wanted were added.

[root@host53 ~]#

[root@host53 ~]# ssh root@192.168.4.51 //可以无密码连接51主机

Last login: Fri Jun 21 13:21:39 2019 from 192.168.4.254

.-"""-.

/ .===. \

/ 6 6 /

( ___/ )

ooo_/__________

/ \

| I am Virtual Host ! ! ! |

_______________ooo/

| | |

|_ | _|

| | |

|||

/-‘Y’-\

(__/ __)

[root@host51 ~]#

[root@host51 ~]# exit //断开连接

登出

Connection to 192.168.4.51 closed.

[root@host53 ~]#

[root@host53 ~]# ssh root@192.168.4.52 //可以无密码连接52主机

Last login: Fri Jun 21 09:01:15 2019 from 192.168.4.254

.-"""-.

/ .===. \

/ 6 6 /

( ___/ )

ooo_/__________

/ \

| I am Virtual Host ! ! ! |

_______________ooo/

| | |

|_ | _|

| | |

|||

/-‘Y’-\

(__/ __)

[root@host52 ~]# exit//断开连接

登出

Connection to 192.168.4.52 closed.

[root@host53 ~]#

步骤二：安装依赖包

1）配置数据库服务器192.168.4.51

[root@host51 ~]# yum -y install perl-* //安装系统自带的perl软件包

[root@host51 ~]# cd mha-soft-student

[root@host51 ~]# yum –y install perl-* //安装共享的perl软件包

2）配置数据库服务器192.168.4.52

[root@host52 ~]# yum -y install perl-* //安装系统自带的perl软件包
[root@host52 ~]# cd mha-soft-student
[root@host52 ~]# yum –y install perl-* //安装共享的perl软件包[root@localhost
3）配置数据库服务器192.168.4.53

[root@host53 ~]# yum -y install perl-* //安装系统自带的perl软件包

[root@host53 ~]# cd mha-soft-student

[root@host53 ~]# yum –y install perl-* //安装共享的perl软件包

4）配置管理服务器192.168.4.57

[root@mgm57 ~]# yum -y install perl-* //安装系统自带的perl软件包

[root@mgm57 ~]# cd mha-soft-student

[root@mgm57 ~]# yum –y install perl-* //安装共享的perl软件包

步骤三：配置MySQL一主多从结构

1）配置主服务器192.168.4.51

[root@host51 ~]# vim /etc/my.cnf
[mysqld]
log-bin=master51 //日志名
server_id=51 //指定server_id
:wq
[root@host51 ~]# systemctl restart mysqld
[root@host51 ~]# mysql -uroot -p123qqq…A
mysql> grant replication slave on . to repluser@"%" identified by “123qqq…A"; //添加从服务器同步数据连接用户
mysql> show master status; //查看日志信息
mysql: [Warning] Using a password on the command line interface can be insecure.
±----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±----------------±---------±-------------±-----------------±------------------+
| master51.000001 | 441 | | | |
±----------------±---------±-------------±-----------------±------------------+
[root@host51 ~]#
2）配置从服务器192.168.4.52

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

[mysqld]

server_id=52 //指定server_id

:wq

[root@host52 ~]# systemctl restart mysqld //重启数据库服务

[root@host52 ~]# mysql -uroot –p123qqq…A //数据库管理员登录

mysql> change master to //指定主服务器信息

master_host=“192.168.4.51”, //IP地址

master_user=“repluser”, //授权用户

master_password=“123qqq…A”, //授权用户密码

master_log_file=“master51.000001”, //binlog日志

master_log_pos=441; //偏移量

mysql> start slave; //启动slave进程

mysql> exit ; //断开连接

[root@host52 ~]# mysql -uroot –p123qqq…A –e “show slave status\G” | grep 192.168.4.51

Master_Host: 192.168.4.51 //主服务器Ip地址

[root@host52 ~]# mysql -uroot –p123qqq…A –e “show slave status\G” | grep –i yes

Slave_IO_Running: Yes //I0线程正常

Slave_SQL_Running: Yes //SQL线程正常

3）配置从服务器192.168.4.53

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

[mysqld]

server_id=53 //指定server_id

:wq

[root@host53 ~]# systemctl restart mysqld //重启数据库服务

[root@host53 ~]# mysql -uroot –p123qqq…A //数据库管理员登录

mysql> change master to //指定主服务器信息

master_host=“192.168.4.51”, //IP地址

master_user=“repluser”, //授权用户

master_password=“123qqq…A”, //授权用户密码

master_log_file=“master51.000001”, //binlog日志

master_log_pos=441; //偏移量

mysql> start slave; //启动slave进程

mysql> exit ; //断开连接

[root@host53 ~]# mysql -uroot –p123qqq…A –e “show slave status\G” | grep 192.168.4.51

Master_Host: 192.168.4.51 //主服务器Ip地址

[root@host53 ~]# mysql -uroot –p123qqq…A –e “show slave status\G” | grep –i yes

Slave_IO_Running: Yes //I0线程正常

Slave_SQL_Running: Yes //SQL线程正常

2 案例2：部署MHA集群
2.1 问题
配置管理节点
配置数据节点
2.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：配置管理节点

1）安装软件

[root@mgm57 ~]# cd mha-soft-student/
[root@mgm57 mha-soft-student]#
[root@mgm57 mha-soft-student]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm//安装mha-node软件包
准备中… ################################# [100%]
正在升级/安装…
1:mha4mysql-node-0.56-0.el6 ################################# [100%]
[root@mgm57 mha-soft-student]#
[root@mgm57 mha-soft-student]# rpm -qa | grep mha //查看是否安装成功
mha4mysql-node-0.56-0.el6.noarch
[root@mgm57 mha-soft-student]#
[root@mgm57 mha-soft-student]# tar -zxvf mha4mysql-manager-0.56.tar.gz //解压mha-manager软件包
mha4mysql-manager-0.56/
mha4mysql-manager-0.56/debian/
mha4mysql-manager-0.56/debian/control
mha4mysql-manager-0.56/debian/copyright
……
……
[root@mgm57 mha-soft-student]# ls
app1.cnf mha4mysql-manager-0.56
mha4mysql-node-0.56-0.el6.noarch.rpm
master_ip_failover mha4mysql-manager-0.56.tar.gz
[root@mgm57 mha-soft-student]# cd mha4mysql-manager-0.56 //进入源码目录
[root@mgm57 mha4mysql-manager-0.56]# ls //查看文件列表
AUTHORS COPYING inc Makefile.PL META.yml rpm t
bin debian lib MANIFEST README samples tests
[root@mgm57 mha4mysql-manager-0.56]#
[root@mgm57 mha4mysql-manager-0.56]# perl Makefile.PL //配置
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies…
[Core Features]

DBI …loaded. (1.627)
DBD::mysql …loaded. (4.023)
Time::HiRes …loaded. (1.9725)
Config::Tiny …loaded. (2.14)
Log::Dispatch …loaded. (2.41)
Parallel::ForkManager …loaded. (1.18)
MHA::NodeConst …loaded. (0.56)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete…
Looks good
Writing Makefile for mha4mysql::manager
Writing MYMETA.yml and MYMETA.json
[root@mgm57 mha4mysql-manager-0.56]# make //编译
[root@mgm57 mha4mysql-manager-0.56]# make install //安装
[root@mgm57 mha4mysql-manager-0.56]# ls /root/perl5/bin //查看安装的命令
masterha_check_repl masterha_conf_host masterha_master_switch
masterha_check_ssh masterha_manager masterha_secondary_check
masterha_check_status masterha_master_monitor masterha_stop
2）编辑主配置文件

[root@mgm57 ~ ]# mkdir /etc/mha //创建工作目录
[root@mgm57 ~ ]# cp mha4mysql-manager-0.56/sample/conf/app1.cnf /etc/mha/ //拷贝模板文件
[root@mgm57 ~ ]# vim /etc/mha/app1.cnf //编辑主配置文件
[server default] //管理服务默认配置
manager_workdir=/etc/mha //工作目录
manager_log=/etc/mha/manager.log //日志文件
master_ip_failover_script=/etc/mha/master_ip_failover //故障切换脚本
ssh_user=root //访问ssh服务用户
ssh_port=22 //ssh服务端口
repl_user=repluser //主服务器数据同步授权用户
repl_password=123qqq…A //密码
user=root //监控用户
password=123qqq…A //密码
[server1] //指定第1台数据库服务器
hostname=192.168.4.51 //服务器ip地址
port=3306 //服务端口
candidate_master=1 //竞选主服务器
[server2] //指定第2台数据库服务器
hostname=192.168.4.52
port=3306
candidate_master=1

[server3] //指定第3台数据库服务器
hostname=192.168.4.53
port=3306
candidate_master=1
:wq
3）创建故障切换脚本

[root@mgm57 ~]# cp mha-soft-student/master_ip_failover /etc/mha/
[root@mgm57 ~]# vim +35 /etc/mha/master_ip_failover
my $vip = ‘192.168.4.100/24’; # Virtual IP //定义VIP地址
my $k e y = " 1 "; / / 定义变量$ key
my $ssh_start_vip = "/sbin/ifconfig eth0:$ key $vip"; //部署vip地址命令
my $ssh_stop_vip = "/sbin/ifconfig eth0:$ key down"; //释放vip地址命令
:wq
[root@mgm57 ~]# chmod +x /etc/mha/master_ip_failover //给脚本加执行权限
4）在当前主服务器部署vip地址

[root@host51 ~]# ifconfig eth0:1 //部署之前查看
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
ether 52:54:00:d8:10:d7 txqueuelen 1000 (Ethernet)
[root@host51 ~]# ifconfig eth0:1 192.168.4.100 //部署vip地址
[root@host51 ~]# ifconfig eth0:1 //部署后查看
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.4.100 netmask 255.255.255.0 broadcast 192.168.4.255
ether 52:54:00:d8:10:d7 txqueuelen 1000 (Ethernet)
步骤二：配置数据节点

1）在所有数据库服务器上，安装mha-node软件包

]# cd /root/mha-soft-student/
]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
准备中… ################################# [100%]
正在升级/安装…
1:mha4mysql-node-0.56-0.el6 ################################# [100%]
2）在所有数据服务器上添加监控用户

可以只在host51主机执行授权命令，host52和host53 会自动同步授权

]# mysql –uroot –p密码
mysql> grant all on . to root@"%" identified by “123qqq…A”;
mysql> exit;
3）在2台从服务器上添加，数据同步连接用户

在从服务器host52添加用户

[root@host52]# mysql –uroot –p密码
mysql> grant replication slave on . to repluser@"%" identified by “123qqq…A”;
mysql> exit;
在从服务器host53添加用户

[root@host53]# mysql –uroot –p密码
mysql> grant replication slave on . to repluser@"%" identified by “123qqq…A”;
mysql> exit;
4）修改数据库服务运行参数

修改主服务器host51

[root@host51 ~]# vim /etc/my.cnf
[mysqld]
plugin-load=“rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so” //加载模块
rpl_semi_sync_master_enabled=1 //启用master模块
rpl_semi_sync_slave_enabled=1 //启用slave模块
relay_log_purge=0 //禁止自动删除中继日志文件
:wq
[root@host51 ~]# systemctl restart mysqld //重启服务
修改从服务器host52

[root@host52 ~]# vim /etc/my.cnf
[mysqld]
log-bin=master52
plugin-load=“rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so” //加载模块
rpl_semi_sync_master_enabled=1 //启用master模块
rpl_semi_sync_slave_enabled=1 //启用slave模块
relay_log_purge=0 //禁止自动删除中继日志文件
:wq
[root@host52 ~]# systemctl restart mysqld //重启服务
修改从服务器host53

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

[mysqld]

log-bin=master53

plugin-load=“rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so” //加载模块

rpl_semi_sync_master_enabled=1 //启用master模块

rpl_semi_sync_slave_enabled=1 //启用slave模块

relay_log_purge=0 //禁止自动删除中继日志文件

:wq

[root@host53 ~]# systemctl restart mysqld //重启服务

3 案例3：测试配置
3.1 问题
测试集群环境
访问集群
测试高可用
修复故障服务器
3.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：测试集群环境

1）在管理主机，测试ssh配置

[root@mgm57 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf //执行测试命令
Thu Jun 20 15:33:48 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 15:33:48 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf…
Thu Jun 20 15:33:48 2019 - [info] Reading server configuration from /etc/mha/app1.cnf…
Thu Jun 20 15:33:48 2019 - [info] Starting SSH connection tests…
Thu Jun 20 15:33:49 2019 - [debug]
Thu Jun 20 15:33:48 2019 - [debug] Connecting via SSH from root@192.168.4.51(192.168.4.51:22) to root@192.168.4.52(192.168.4.52:22)…
Thu Jun 20 15:33:49 2019 - [debug] ok.
Thu Jun 20 15:33:49 2019 - [debug] Connecting via SSH from root@192.168.4.51(192.168.4.51:22) to root@192.168.4.53(192.168.4.53:22)…
Thu Jun 20 15:33:49 2019 - [debug] ok.
Thu Jun 20 15:33:50 2019 - [debug]
Thu Jun 20 15:33:49 2019 - [debug] Connecting via SSH from root@192.168.4.52(192.168.4.52:22) to root@192.168.4.51(192.168.4.51:22)…
Thu Jun 20 15:33:49 2019 - [debug] ok.
Thu Jun 20 15:33:49 2019 - [debug] Connecting via SSH from root@192.168.4.52(192.168.4.52:22) to root@192.168.4.53(192.168.4.53:22)…
Thu Jun 20 15:33:49 2019 - [debug] ok.
Thu Jun 20 15:33:50 2019 - [debug] ok.
Thu Jun 20 15:33:50 2019 - [debug] Connecting via SSH from root@192.168.4.53(192.168.4.53:22) to root@192.168.4.52(192.168.4.52:22)…
Thu Jun 20 15:33:50 2019 - [debug] ok.
Thu Jun 20 15:33:51 2019 - [info] All SSH connection tests passed successfully.//测试成功提示
2）在管理主机，测试主从同步

[root@host57 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf //执行测试命令
Thu Jun 20 15:37:46 2019 - [info] Reading server configuration from /etc/mha/app1.cnf…
Thu Jun 20 15:37:46 2019 - [info] MHA::MasterMonitor version 0.56.
Thu Jun 20 15:37:47 2019 - [info] GTID failover mode = 0
Thu Jun 20 15:37:47 2019 - [info] Dead Servers: //没有停止的mysql服务器
Thu Jun 20 15:37:47 2019 - [info] Alive Servers://运行mysql服务主机列表
Thu Jun 20 15:37:47 2019 - [info] 192.168.4.51(192.168.4.51:3306)
Thu Jun 20 15:37:47 2019 - [info] 192.168.4.52(192.168.4.52:3306)
Thu Jun 20 15:37:47 2019 - [info] 192.168.4.53(192.168.4.53:3306)
Thu Jun 20 15:37:47 2019 - [info] Alive Slaves:
Thu Jun 20 15:37:47 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Jun 20 15:37:47 2019 - [info] 192.168.4.53(192.168.4.53:3306) Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jun 20 15:37:47 2019 - [info] Replicating from 192.168.4.51(192.168.4.51:3306)
Thu Jun 20 15:37:47 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Jun 20 15:37:47 2019 - [info] Current Alive Master: 192.168.4.51(192.168.4.51:3306)
Thu Jun 20 15:37:47 2019 - [info] Checking slave configurations…
Thu Jun 20 15:37:47 2019 - [info] read_only=1 is not set on slave 192.168.4.52(192.168.4.52:3306).
Thu Jun 20 15:37:47 2019 - [info] read_only=1 is not set on slave 192.168.4.53(192.168.4.53:3306).
Thu Jun 20 15:37:47 2019 - [info] Checking replication filtering settings…
Thu Jun 20 15:37:47 2019 - [info] binlog_do_db= , binlog_ignore_db=
Thu Jun 20 15:37:47 2019 - [info] Replication filtering check ok.
Thu Jun 20 15:37:47 2019 - [info] GTID (with auto-pos) is not supported
Thu Jun 20 15:37:47 2019 - [info] Starting SSH connection tests…
Thu Jun 20 15:37:49 2019 - [info] All SSH connection tests passed successfully.
Thu Jun 20 15:37:49 2019 - [info] Checking MHA Node version…
Thu Jun 20 15:37:50 2019 - [info] Version check ok.
Thu Jun 20 15:37:50 2019 - [info] Checking SSH publickey authentication settings on the current master…
Thu Jun 20 15:37:50 2019 - [info] HealthCheck: SSH to 192.168.4.51 is reachable.
Thu Jun 20 15:37:50 2019 - [info] Master MHA Node version is 0.56.
Thu Jun 20 15:37:50 2019 - [info] Checking recovery script configurations on 192.168.4.51(192.168.4.51:3306)…
Thu Jun 20 15:37:50 2019 - [info] Connecting to root@192.168.4.51(192.168.4.51:22)…
Creating /var/tmp if not exists… ok.
Checking output directory is accessible or not…
ok.
Binlog found at /var/lib/mysql, up to master51.000002
Thu Jun 20 15:37:50 2019 - [info] Binlog setting check done.
Thu Jun 20 15:37:50 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers…
Thu Jun 20 15:37:50 2019 - [info] Connecting to root@192.168.4.52(192.168.4.52:22)…
Checking slave recovery environment settings…
Opening /var/lib/mysql/relay-log.info … ok.
Relay log found at /var/lib/mysql, up to host52-relay-bin.000006
Temporary relay log file is /var/lib/mysql/host52-relay-bin.000006
Testing mysql connection and privileges…mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output… done.
Cleaning up test file(s)… done.
Thu Jun 20 15:37:51 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user=‘root’ --slave_host=192.168.4.53 --slave_ip=192.168.4.53 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.17-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Thu Jun 20 15:37:51 2019 - [info] Connecting to root@192.168.4.53(192.168.4.53:22)…
Checking slave recovery environment settings…
Opening /var/lib/mysql/relay-log.info … ok.
Relay log found at /var/lib/mysql, up to host53-relay-bin.000006
Temporary relay log file is /var/lib/mysql/host53-relay-bin.000006
Testing mysql connection and privileges…mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output… done.
Cleaning up test file(s)… done.
Thu Jun 20 15:37:52 2019 - [info] Slaves settings check done.
Thu Jun 20 15:37:52 2019 - [info]
192.168.4.51(192.168.4.51:3306) (current master)
±-192.168.4.52(192.168.4.52:3306)
±-192.168.4.53(192.168.4.53:3306)
Thu Jun 20 15:37:52 2019 - [info] Checking replication health on 192.168.4.52…
Thu Jun 20 15:37:52 2019 - [info] ok.
Thu Jun 20 15:37:52 2019 - [info] Checking replication health on 192.168.4.53…
Thu Jun 20 15:37:52 2019 - [info] ok.
Thu Jun 20 15:37:52 2019 - [info] Checking master_ip_failover_script status:
Thu Jun 20 15:37:52 2019 - [info] /etc/mha/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.4.51 --orig_master_ip=192.168.4.51 --orig_master_port=3306
Thu Jun 20 15:37:52 2019 - [info] OK.
Thu Jun 20 15:37:52 2019 - [warning] shutdown_script is not defined.
Thu Jun 20 15:37:52 2019 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.//测试成功提示信息
3）启动管理服务

[root@mgm57 ~]# masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf
–ignore_last_failover //执行启动命令
Thu Jun 20 17:05:58 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 17:05:58 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf…
Thu Jun 20 17:05:58 2019 - [info] Reading server configuration from /etc/mha/app1.cnf…
4）查看服务状态

[root@mgm57 ~]# masterha_check_status --conf=/etc/mha/app1.cnf//执行命令
app1 (pid:15806) is running(0:PING_OK), master:192.168.4.51 //服务运行，监视主服务器192.168.4.51
[root@mgm57 ~]# ls /etc/mha/ //查看工作目录文件列表
app1.cnf app1.master_status.health manager.log master_ip_failover
步骤二：访问集群

1）在主服务器51 添加访问数据的连接用户

]# mysql -uroot -p123qqq…A
mysql> create database db9;
Query OK, 1 row affected (0.05 sec)
mysql> create table db9.a (id int);
Query OK, 0 rows affected (0.63 sec)
mysql> grant select,insert on db9.* to yaya55@"%" identified by “123qqq…A”;
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql>exit
2）客户端50 连接vip地址访问集群

host50~]# mysql -h192.168.4.100 -uyaya55 -p123qqq…A
mysql> select * from db9.a;
mysql> insert into db9.a values(100);
mysql> select * from db9.a;
±-----+
| id |
±-----+
| 100 |
±-----+
1 row in set (0.00 sec)
mysql>exit
3）在从服务器host52 查看数据

[root@host52 ~]# mysql -uroot -p123qqq…A -e “select * from db9.a”
mysql: [Warning] Using a password on the command line interface can be insecure.
±-----+
| id |
±-----+
| 100 |
±-----+
4）在从服务器host53 查看数据

[root@host53 ~]# mysql -uroot -p123qqq…A -e “select * from db9.a”
mysql: [Warning] Using a password on the command line interface can be insecure.
±-----+
| id |
±-----+
| 100 |
±-----+
步骤三：测试高可用

1）停止主服务器51的mysql服务

host51~]# systemctl stop mysqld
2）查看管理服务，输出的监控信息

[root@mgm57~]#masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf \

–ignore_last_failover
Thu Jun 20 17:05:58 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 17:05:58 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf…
Thu Jun 20 17:05:58 2019 - [info] Reading server configuration from /etc/mha/app1.cnf…
Creating /var/tmp if not exists… ok.
Checking output directory is accessible or not…
ok.
Binlog found at /var/lib/mysql, up to master51.000002
Thu Jun 20 17:35:59 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 17:35:59 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf…
Thu Jun 20 17:35:59 2019 - [info] Reading server configuration from /etc/mha/app1.cnf…
[root@host57 ~]#
[root@mgm57 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING). //监控到主服务器宕机管理服务自动停止
[root@mgm57 ~]#
3）客户端依然连接vip地址，可以访问到数据

client50]# ping -c 2 192.168.4.100 //能够ping通vip地址
PING 192.168.4.100 (192.168.4.100) 56(84) bytes of data.
64 bytes from 192.168.4.100: icmp_seq=1 ttl=255 time=0.222 ms
64 bytes from 192.168.4.100: icmp_seq=2 ttl=255 time=0.121 ms
— 192.168.4.71 ping statistics —
2 packets transmitted, 2 received, 0% packet loss, time 999ms
rtt min/avg/max/mdev = 0.121/0.171/0.222/0.052 ms
client50]# mysql -h192.168.4.100 -uyaya55 -p123qqq…A //连接vip地址
mysql> insert into db9.a values(200); //插入记录
mysql> select * from db9.a;//查询记录
±-----+
| id |
±-----+
| 100 |
| 200 |
±-----+
4）查看vip地址

在host52主机查看到vip地址，说明host52 主机被选举为主服务器

[root@host52 ~]# ifconfig eth0:1
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.4.100 netmask 255.255.255.0 broadcast 192.168.4.255
ether 52:54:00:f5:c4:6a txqueuelen 1000 (Ethernet)
在host53主机未查看到vip地址，说明host53主机是当前host52的从服务器

[root@host53 ~]# ifconfig eth0:1 //未查到vip地址
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
ether 52:54:00:28:22:2e txqueuelen 1000 (Ethernet)
[root@host53 ~]# mysql -uroot -p123qqq…A -e “show slave status\G” | grep -i 192
mysql: [Warning] Using a password on the command line interface can be insecure.
Master_Host: 192.168.4.52 //主服务器Ip地址
[root@host53 ~]#
[root@host53 ~]# mysql -uroot -p123qqq…A -e “show slave status\G” | grep -i yes
mysql: [Warning] Using a password on the command line interface can be insecure.
Slave_IO_Running: Yes //IO线程正常
Slave_SQL_Running: Yes //SQL线程正常
[root@host53 ~]# mysql -uroot -p123qqq…A -e “select * from db9.a” //自动同步数据
mysql: [Warning] Using a password on the command line interface can be insecure.
±-----+
| id |
±-----+
| 100 |
| 200 |
±-----+
步骤四：修复故障服务器

host51~]# systemctl start mysqld
[root@host52 ~]# mysqldump -uroot -p123qqq…A --master-data db9 > db9.sql //在主服务器host52 做完全备份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host52 ~]#
[root@host52 ~]# scp db9.sql root@192.168.4.51:/root/ //拷贝备份文件给host51主机
db9.sql 100% 1918 3.1MB/s 00:00
[root@host52 ~]#
host51 ~]# mysql -uroot -p123qqq…A db9 < /root/db9.sql//host51 主机使用备份文件恢复数据
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host51 ~]# grep master52 /root/db9.sql //查看日志名及偏移量
CHANGE MASTER TO MASTER_LOG_FILE=‘master52.000001’, MASTER_LOG_POS=895;
[root@host51 ~]# mysql -uroot -p123qqq…A
mysql>change master to master_host=“192.168.4.52”,master_user=“repluser”,master_password=“123qqq…A”,master_log_file=“master52.000001”,master_log_pos=895;
Query OK, 0 rows affected, 2 warnings (0.14 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
Mysql> exit ;
[root@host51 ~]# mysql -uroot -p123qqq…A -e “show slave status\G” |grep 192.168.4.52
mysql: [Warning] Using a password on the command line interface can be insecure.
Master_Host: 192.168.4.52 //主服务器ip地址
[root@host51 ~]#
[root@host51 ~]# mysql -uroot -p123qqq…A -e “show slave status\G” |grep -i yes
mysql: [Warning] Using a password on the command line interface can be insecure.
Slave_IO_Running: Yes //IO线程状态正常
Slave_SQL_Running: Yes //SQL线程状态正常
[root@host51 ~]#
]# vim /etc/mha/app1.cnf
[server1 ]
hostname=192.168.4.51
port=3306
candidate_master=1
:wq
[root@mgm57 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf //测试SSH
Thu Jun 20 15:33:48 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 15:33:48 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf…
Thu Jun 20 15:33:48 2019 - [info] Reading server configuration from /etc/mha/app1.cnf…
Thu Jun 20 15:33:48 2019 - [info] Starting SSH connection tests…
Thu Jun 20 15:33:49 2019 - [debug]
Thu Jun 20 15:33:48 2019 - [debug] Connecting via SSH from root@192.168.4.51(192.168.4.51:22) to root@192.168.4.52(192.168.4.52:22)…
Thu Jun 20 15:33:49 2019 - [debug] ok.
Thu Jun 20 15:33:49 2019 - [debug] Connecting via SSH from root@192.168.4.51(192.168.4.51:22) to root@192.168.4.53(192.168.4.53:22)…
Thu Jun 20 15:33:49 2019 - [debug] ok.
Thu Jun 20 15:33:50 2019 - [debug]
Thu Jun 20 15:33:49 2019 - [debug] Connecting via SSH from root@192.168.4.52(192.168.4.52:22) to root@192.168.4.51(192.168.4.51:22)…
Thu Jun 20 15:33:49 2019 - [debug] ok.
Thu Jun 20 15:33:49 2019 - [debug] Connecting via SSH from root@192.168.4.52(192.168.4.52:22) to root@192.168.4.53(192.168.4.53:22)…
Thu Jun 20 15:33:49 2019 - [debug] ok.
Thu Jun 20 15:33:50 2019 - [debug] ok.
Thu Jun 20 15:33:50 2019 - [debug] Connecting via SSH from root@192.168.4.53(192.168.4.53:22) to root@192.168.4.52(192.168.4.52:22)…
Thu Jun 20 15:33:50 2019 - [debug] ok.
Thu Jun 20 15:33:51 2019 - [info] All SSH connection tests passed successfully.//成功
[root@mgm57 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf//测试主从同步
Thu Jun 20 15:37:46 2019 - [info] Reading server configuration from /etc/mha/app1.cnf…
Thu Jun 20 15:37:46 2019 - [info] MHA::MasterMonitor version 0.56.
Thu Jun 20 15:37:47 2019 - [info] GTID failover mode = 0
Thu Jun 20 15:37:47 2019 - [info] Dead Servers:
Thu Jun 20 15:37:47 2019 - [info] Alive Servers:
Thu Jun 20 15:37:47 2019 - [info] 192.168.4.51(192.168.4.51:3306)
Thu Jun 20 15:37:47 2019 - [info] 192.168.4.52(192.168.4.52:3306)
Thu Jun 20 15:37:47 2019 - [info] 192.168.4.53(192.168.4.53:3306)
Thu Jun 20 15:37:47 2019 - [info] Alive Slaves:
Thu Jun 20 15:37:47 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Jun 20 15:37:47 2019 - [info] 192.168.4.53(192.168.4.53:3306) Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jun 20 15:37:47 2019 - [info] Replicating from 192.168.4.51(192.168.4.51:3306)
Thu Jun 20 15:37:47 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Jun 20 15:37:47 2019 - [info] Current Alive Master: 192.168.4.51(192.168.4.51:3306)
Thu Jun 20 15:37:47 2019 - [info] Checking slave configurations…
Thu Jun 20 15:37:47 2019 - [info] read_only=1 is not set on slave 192.168.4.52(192.168.4.52:3306).
Thu Jun 20 15:37:47 2019 - [info] read_only=1 is not set on slave 192.168.4.53(192.168.4.53:3306).
Thu Jun 20 15:37:47 2019 - [info] Checking replication filtering settings…
Thu Jun 20 15:37:47 2019 - [info] binlog_do_db= , binlog_ignore_db=
Thu Jun 20 15:37:47 2019 - [info] Replication filtering check ok.
Thu Jun 20 15:37:47 2019 - [info] GTID (with auto-pos) is not supported
Thu Jun 20 15:37:47 2019 - [info] Starting SSH connection tests…
Thu Jun 20 15:37:49 2019 - [info] All SSH connection tests passed successfully.
Thu Jun 20 15:37:49 2019 - [info] Checking MHA Node version…
Thu Jun 20 15:37:50 2019 - [info] Version check ok.
Thu Jun 20 15:37:50 2019 - [info] Checking SSH publickey authentication settings on the current master…
Thu Jun 20 15:37:50 2019 - [info] HealthCheck: SSH to 192.168.4.51 is reachable.
Thu Jun 20 15:37:50 2019 - [info] Master MHA Node version is 0.56.
Thu Jun 20 15:37:50 2019 - [info] Checking recovery script configurations on 192.168.4.51(192.168.4.51:3306)…
Thu Jun 20 15:37:50 2019 - [info] Connecting to root@192.168.4.51(192.168.4.51:22)…
Creating /var/tmp if not exists… ok.
Checking output directory is accessible or not…
ok.
Binlog found at /var/lib/mysql, up to master51.000002
Thu Jun 20 15:37:50 2019 - [info] Binlog setting check done.
Thu Jun 20 15:37:50 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers…
Thu Jun 20 15:37:50 2019 - [info] Connecting to root@192.168.4.52(192.168.4.52:22)…
Checking slave recovery environment settings…
Opening /var/lib/mysql/relay-log.info … ok.
Relay log found at /var/lib/mysql, up to host52-relay-bin.000006
Temporary relay log file is /var/lib/mysql/host52-relay-bin.000006
Testing mysql connection and privileges…mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output… done.
Cleaning up test file(s)… done.
Thu Jun 20 15:37:51 2019 - [info] Connecting to root@192.168.4.53(192.168.4.53:22)…
Checking slave recovery environment settings…
Opening /var/lib/mysql/relay-log.info … ok.
Relay log found at /var/lib/mysql, up to host53-relay-bin.000006
Temporary relay log file is /var/lib/mysql/host53-relay-bin.000006
Testing mysql connection and privileges…mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output… done.
Cleaning up test file(s)… done.
Thu Jun 20 15:37:52 2019 - [info] Slaves settings check done.
Thu Jun 20 15:37:52 2019 - [info]
192.168.4.51(192.168.4.51:3306) (current master)
±-192.168.4.52(192.168.4.52:3306)
±-192.168.4.53(192.168.4.53:3306)
Thu Jun 20 15:37:52 2019 - [info] Checking replication health on 192.168.4.52…
Thu Jun 20 15:37:52 2019 - [info] ok.
Thu Jun 20 15:37:52 2019 - [info] Checking replication health on 192.168.4.53…
Thu Jun 20 15:37:52 2019 - [info] ok.
Thu Jun 20 15:37:52 2019 - [info] Checking master_ip_failover_script status:
Thu Jun 20 15:37:52 2019 - [info] /etc/mha/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.4.51 --orig_master_ip=192.168.4.51 --orig_master_port=3306
Thu Jun 20 15:37:52 2019 - [info] OK.
Thu Jun 20 15:37:52 2019 - [warning] shutdown_script is not defined.
Thu Jun 20 15:37:52 2019 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK. //成功
]# masterha_stop --conf=/etc/mha/app1.cnf //停止管理服务
Stopped app1 successfully.
]# masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf
–ignore_last_failover //启动管理服务
Thu Jun 20 17:05:58 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 17:05:58 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf…
Thu Jun 20 17:05:58 2019 - [info] Reading server configuration from /etc/mha/app1.cnf…
mgm57 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:15806) is running(0:PING_OK), master:192.168.4.52 //服务运行，监视服务器52
[root@mgm57 ~]#