mysql mha 逻辑图_MHA集群 mysql视图

最新推荐文章于 2022-03-10 10:53:43 发布

weixin_39809584

最新推荐文章于 2022-03-10 10:53:43 发布

阅读量96

点赞数

文章标签： mysql mha 逻辑图

本文链接：https://blog.csdn.net/weixin_39809584/article/details/113908881

版权

Top

NSD DBA2 DAY04

1 案例1：测试MHA集群

1.1 问题

查看MHA集群状态

测试节点之间的SSH登录

测试集群VIP的故障切换功能

1.2 步骤

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

步骤一：验证配置

1)检查配置环境，在主机52，53检查是否有同步数据的用户repluser

主机52:

mysql> select user,host from mysql.user where user="repluser";

+----------+------+

| user | host |

+----------+------+

| repluser | % |

+----------+------+

1 row in set (0.00 sec)

mysql> show grants for repluser@"%";

+--------------------------------------------------+

| Grants for repluser@% |

+--------------------------------------------------+

| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |

+--------------------------------------------------+

1 row in set (0.00 sec

主机53:

mysql> select user,host from mysql.user where user="repluser";

+----------+------+

| user | host |

+----------+------+

| repluser | % |

+----------+------+

1 row in set (0.00 sec)

mysql> show grants for repluser@"%";

+--------------------------------------------------+

| Grants for repluser@% |

+--------------------------------------------------+

| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |

+--------------------------------------------------+

1 row in set (0.00 sec)

2)在51的主机上做root的授权，其他的会同步(如果不做，在验证数据节点的主从同步配置时会出错)

mysql> grant all on *.* to root@"%" identified by "123456";

mysql> select user,host from mysql.user where user="root";

+------+-----------+

| user | host |

+------+-----------+

| root | % |

| root | localhost |

+------+-----------+

2 rows in set (0.00 sec)

3)验证ssh 免密登陆数据节点主机

[root@mgm56 mha4mysql-manager-0.56]# cd /usr/local/bin/

[root@mgm56 bin]# masterha_check_ssh --conf=/etc/mha_manager/app1.cnf

Wed Sep 19 09:09:33 2018 - [info] All SSH connection tests passed successfully.

//出现这个为成功

4)验证数据节点的主从同步配置(先把自动failover时候的切换脚本注释掉)

[root@mgm56 bin]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf

MySQL Replication Health is OK. //验证成功

5)启动管理服务MHA_Manager

--remove_dead_master_conf //删除宕机主库配置

--ignore_last_failover //忽略xxx.health文件

[root@mgm56 bin]# masterha_manager --conf=/etc/mha_manager/app1.cnf \

--remove_dead_master_conf --ignore_last_failover

Wed Sep 19 09:24:41 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Wed Sep 19 09:24:41 2018 - [info] Reading application default configuration from /etc/mha_manager/app1.cnf..

Wed Sep 19 09:24:41 2018 - [info] Reading server configuration from /etc/mha_manager/app1.cnf..

6)查看状态(另开一个终端)

[root@mgm56 ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf

app1 (pid:15745) is running(0:PING_OK), master:192.168.4.51

7)停止服务

[root@mgm56 ~]# masterha_stop --conf=/etc/mha_manager/app1.cnf

Stopped app1 successfully.

步骤二：测试故障转移

1)在主库51上面配置VIP地址

[root@master51 ~]# ifconfig eth0:1 192.168.4.100/24

2)在配置文件里面把自动failover时候的切换脚本去掉注释

3)修改 master_ip_failover 脚本，设置如下内容

34 my $vip = '192.168.4.100/24';

35 my $key = "1";

36 my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";

37 my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

4)启动服务

[root@mgm56 bin]# masterha_manager --conf=/etc/mha_manager/app1.cnf \

--remove_dead_master_conf --ignore_last_failover

Wed Sep 19 09:50:33 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Wed Sep 19 09:50:33 2018 - [info] Reading application default configuration from /etc/mha_manager/app1.cnf..

Wed Sep 19 09:50:33 2018 - [info] Reading server configuration from /etc/mha_manager/app1.cnf..

5)查看状态

[root@mgm56 ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf

app1 master is down and failover is running(50:FAILOVER_RUNNING). master:192.168.4.52

验证数据节点的主从同步配置报错，如图-3所示：

[root@mgm56 bin]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf

图-3

解决办法：

root用户没有授权，默认只能本地连接，在主机51上面授权root用户可以远程登录，其他主机会同步

mysql> grant all on *.* to root@"%" identified by "123456";

2 案例2：视图的基本使用

2.1 问题

把/etc/passwd文件的内容存储到db9库下的user表里

添加新字段id 存储记录的行号(在所有字段的前边)

创建视图v1 结构及数据user表的字段、记录一样。

创建视图v2 只有user表shell是/bin/bash用户信息。

分别对视图表和基表执行insert update delete 操作。

删除视图v1 和 v2

2.2 步骤

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

步骤一：视图的基本使用

什么是视图：是一种虚拟存在的表

内容与真实的表相似，包含一系列带有名称的列和行数据。

视图并不在数据库中以存储的数据的形式存在。

行和列的数据来自定义视图时查询所引用的基本表，并且在具体引用视图时动态生成。

更新视图的数据，就是更新基表的数据

更新基表数据，视图的数据也会跟着改变

1)把/etc/passwd文件的内容存储到db9库下的user表里

[root@mysql51 ~]# mysql -u root -p123456

mysql> create database db9;

Query OK, 1 row affected (10.00 sec)

mysql> create table db9.user(username char(20),password char(1),uid \

int(2),gid int(2),comment char(100),homedir char(100),shell char(50));

//创建存储数据的表结构

Query OK, 0 rows affected (0.02 sec)

[root@mysql51 ~]# cp /etc/passwd /var/lib/mysql-files/

[root@mysql51 ~]# ls /var/lib/mysql-files/

passwd

mysql> load data infile "/var/lib/mysql-files/passwd" into table db9.user fields terminated by ":" lines terminated by "\n"; //导入文件内容到db9.user

Query OK, 41 rows affected (0.02 sec)

Records: 41 Deleted: 0 Skipped: 0 Warnings: 0

2)添加新字段id 存储记录的行号(在所有字段的前边)

mysql> alter table db9.user add id int(2) primary key auto_increment first;

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> use db9;

mysql> desc user;

+----------+-----------+------+-----+---------+----------------+

+----------+-----------+------+-----+---------+----------------+

| id | int(2) | NO | PRI | NULL | auto_increment |

| uid | int(2) | YES | | NULL | |

| gid | int(2) | YES | | NULL | |

+----------+-----------+------+-----+---------+----------------+

8 rows in set (0.00 sec)

3)创建视图v1 结构及数据user表的字段、记录一样

mysql> create view v1 as select * from user;

Query OK, 0 rows affected (0.00 sec)

4)创建视图v2 只有user表shell是/bin/bash用户信息

mysql> create view v2 as select shell from user;

Query OK, 0 rows affected (0.01 sec)

5)分别对视图表和基表执行insert update delete 操作

mysql> insert into v1(username,uid) values("jarry",9); //插入记录

Query OK, 1 row affected (0.00 sec)

mysql> update v1 set uid=9 where username="adm"; //更新记录

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> delete from v1 where uid=9; //删除记录

Query OK, 2 rows affected (0.01 sec)

6)删除视图v1 和 v2

mysql> drop view v1;

Query OK, 0 rows affected (0.00 sec)

mysql> drop view v2;

Query OK, 0 rows affected (0.00 sec)

注意：对视图操作即是对基本操作，反之亦然！！！

3 案例3：视图进阶操作

3.1 问题

练习OR REPLACE的选项使用

练习WITH LOCAL CHECK OPTION 选项的使用

练习WITH CASCADED CHECK OPTION 选项的使用

3.2 步骤

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

步骤一：视图进阶操作

1)创建视图完全格式

mysql> create table user2 select username,uid,gid from user limit 3;

//快速建表(user2表)

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> create table info select username,uid,homedir,shell from user limit 5;

//快速建表(info表)

Query OK, 5 rows affected (0.02 sec)

Records: 5 Duplicates: 0 Warnings: 0

查询user2.username=info.username的字段

mysql> select * from user2 left join info on user2.username=info.username;

+----------+------+------+----------+------+---------+---------------+

| username | uid | gid | username | uid | homedir | shell |

+----------+------+------+----------+------+---------+---------------+

| root | 0 | 0 | root | 0 | /root | /bin/bash |

| bin | 1 | 1 | bin | 1 | /bin | /sbin/nologin |

| daemon | 2 | 2 | daemon | 2 | /sbin | /sbin/nologin |

+----------+------+------+----------+------+---------+---------------+

3 rows in set (0.00 sec)

2)关联查询建的视图默认不允许修改视图字段的值

mysql> create view v4 as select * from user2 left join info on user2.username=info.username; //创建失败

ERROR 1060 (42S21): Duplicate column name 'username'

mysql> create view v4 as select a.username as ausername,b.username as busername, a.uid as auid,b.uid as buid from user2 a left join info b on a.username=b.username;

//创建成功

Query OK, 0 rows affected (0.00 sec)

mysql> select * from v4;

+-----------+-----------+------+------+

+-----------+-----------+------+------+

| root | root | 0 | 0 |

| bin | bin | 1 | 1 |

| daemon | daemon | 2 | 2 |

+-----------+-----------+------+------+

3 rows in set (0.00 sec)

mysql> desc v4;

+-----------+----------+------+-----+---------+-------+

+-----------+----------+------+-----+---------+-------+

| auid | int(2) | YES | | NULL | |

| buid | int(2) | YES | | NULL | |

+-----------+----------+------+-----+---------+-------+

4 rows in set (0.00 sec)

3)OR REPLACE的选项使用

创建时，若视图已存在，会替换已有的视图

语法格式：create or replace view视图名as select 查询； //达到修改已有视图的目的

mysql> create or replace view v4 as select a.username as ausername,b.username as busername, a.uid as auid,b.uid as buid from user2 a left join info b on a.username=b.username;

Query OK, 0 rows affected (0.00 sec)

4)WITH LOCAL CHECK OPTION

LOCAL和CASCADED关键字决定检查的范围

LOCAL 仅检查当前视图的限制

CASCADED 同时要满足基表的限制(默认值)

mysql> create table user1 select username,uid,shell from user where uid>=5 and uid <=40;

Query OK, 11 rows affected (0.01 sec)

Records: 11 Duplicates: 0 Warnings: 0

mysql> create view v1 as select username,uid from user1 where uid<=20;

Query OK, 0 rows affected (0.01 sec)

mysql> update v1 set uid=21 where username="sync";

//操作超过视图表的条件限制(uid<=20)之后，在视图表里面查看不到，在基表里可以查看到