使用keepalived实现数据库的高可用高可靠以及搭建数据库主从配置

这里使用的是keepalived来实现服务器宕机后可快速切换从服务器，对用户而言，是透明的，无感知的

配置环境：

主节点(master)：192.168.78.10

从节点(slave)：192.168.78.20

虚拟IP：192.168.78.40

1，安装以及实现配置keepalived

​ master：
​ 安装keepalived:
​ [root@serverA keepalived]# tar -xvf keepalived-2.0.17.tar.gz //解压
​ [root@serverA keepalived-2.0.17]# yum install openssl libnl-dev gcc-c++ -y //安装下载相应的依赖
​ [root@serverA keepalived-2.0.17]# ./configure --prefix=/usr/local/keepalived
​ [root@serverA keepalived-2.0.17]# make
​ [root@serverA keepalived-2.0.17]# make install
​ [root@serverA keepalived-2.0.17]# ll /usr/local/keepalived/sbin/ 查看这个目录下面生成的文件
​ [root@serverA keepalived-2.0.17]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ 复制这个目录下的keepalived文件到/usr/sbin目录下
​ [root@serverA keepalived-2.0.17]# systemctl enable keepalived.service 设置开机启动
​ [root@serverA keepalived-2.0.17]# systemctl list-unit-files | grep keepalived 查看keepalived服务是否开机启动
​ keepalived.service enabled
​ [root@serverA keepalived-2.0.17]# mkdir /etc/keepalived 创建目录用于放置keepalived服务主配置文件
​ [root@serverA keepalived-2.0.17]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
​ [root@serverA keepalived]# cp keepalived.conf keepalived.conf.bak 对主配置文件进行备份
​ [root@serverA keepalived]# vim keepalived.conf 打开主配置文件**

		  1 onfiguration File for keepalived
		  2 
		  3 global_defs {				
		  4    router_id serverA		//这里可设置为hostname{这里需要改动}
		  5 }
		  6 
		  7 vrrp_instance VI_1 {//VRRP 实例state MASTER // MASTER 或BACKUP，为了防止脑裂现象，主备均需要设置为backup 模式，master 模式会抢占VIP
		  8     state BACKUP
		  9     interface ens33		//节点固有IP（非VIP）的网卡，用来发VRRP 包
		 10     virtual_router_id 51	//取值在0-255 之间，用来区分多个instance 的VRRP 组播，搭建主备服务器id必须一致
		 11     priority 100			//优先级，同一个vrrp_instance 的MASTER优先级必须比BACKUP 高
		 12     nopreempt				//非抢占，配合backup,防止切换后，主库服务恢复正常后，IP 漂移过来
		 13     advert_int 1			// MASTER 与BACKUP 负载均衡器之间同步检查的时间间隔，单位为秒。
		 14     authentication {
		 15         auth_type PASS
		 16         auth_pass 1111
		 17     }
		 18     virtual_ipaddress {	
		 19         192.168.78.40		//虚拟ip 地址,可以有多个地址，每个地址占一行，不需要子网掩码{这里需要修改}
		 20     }
		 21 }
		 22 virtual_server 192.168.78.40 3306 {
		 23     delay_loop 6			//健康检查间隔，单位为秒
		 24     persistence_timeout 50	// 会话保持时间，就是把用户请求转发给同一个服务器
		 25     protocol TCP			//转发协议，有TCP 和UDP 两种，一般用TCP
		 26 
		 27     real_server 192.168.78.10 3306 {		//真实服务器，包括IP和端口号{主服务器IP}
		 28         notify_down /etc/keepalived/kill_keepalived.sh		
		 29         TCP_CHECK {
		 30             connect_timeout 3			//连接超时时间
		 31             nb_get_retry 3				//重连次数
		 32             connect_port 3306			//检测端口
		 33             delay_before_retry 3		//重连时间间隔
		 34         }
		 35     }
		 36 }

​ [root@serverA keepalived]# vim /etc/keepalived/kill_keepalived.sh
​ #!/bin/bash
​ kill -9 $(cat /var/run/keepalived.pid)
​ [root@serverA keepalived]# chmod +x /etc/keepalived/kill_keepalived.sh 给这个文件设置可执行权限

从节点一样的配置一样操作，不过仅修改配置文件里面的ip即可

这里的192.168.78.40ip地址就是对外开放的虚拟ip

2,安装mysql5.7数据库
[root@serverA mysql]# tar xvf mysql-5.7.32-1.el7.x86_64.rpm-bundle.tar
[root@serverA mysql]# ls
mysql-5.7.32-1.el7.x86_64.rpm-bundle.tar mysql-community-devel-5.7.32-1.el7.x86_64.rpm mysql-community-embedded-devel-5.7.32-1.el7.x86_64.rpm mysql-community-server-5.7.32-1.el7.x86_64.rpm
mysql-community-client-5.7.32-1.el7.x86_64.rpm mysql-community-embedded-5.7.32-1.el7.x86_64.rpm mysql-community-libs-5.7.32-1.el7.x86_64.rpm mysql-community-test-5.7.32-1.el7.x86_64.rpm
mysql-community-common-5.7.32-1.el7.x86_64.rpm mysql-community-embedded-compat-5.7.32-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.32-1.el7.x86_64.rpm**

[root@serverA mysql]# rpm -qa | grep mysql 这里先查看是否有mysql之前的rpm包，要将其删除干净
[root@serverA mysql]# yum list mariadb
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
Available Packages
mariadb.x86_64 1:5.5.60-1.el7_5 local-centos

[root@serverA mysql]# yum remove mariadb-lib -y*
[root@serverA mysql]# rpm -e mariadb*
error: package mariadb is not installed*
[root@serverA mysql]# rpm -ivh mysql-community-common-5.7.32-1.el7.x86_64.rpm
warning: mysql-community-common-5.7.32-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:mysql-community-common-5.7.32-1.e################################# [100%]

---

[root@serverA mysql]# rpm -ivh mysql-community-libs-5.7.32-1.el7.x86_64.rpm
warning: mysql-community-libs-5.7.32-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:mysql-community-libs-5.7.32-1.el7################################# [100%]

---

[root@serverA mysql]#
[root@serverA mysql]# rpm -ivh mysql-community-client-5.7.32-1.el7.x86_64.rpm
warning: mysql-community-client-5.7.32-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:mysql-community-client-5.7.32-1.e################################# [100%]

---

[root@serverA mysql]# rpm -ivh mysql-community-server-5.7.32-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.32-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:mysql-community-server-5.7.32-1.e################################# [100%]

同理，另一台服务器安装Mysql也是相同的操作

安装完成之后如果启动Mysql服务

[root@serverA mysql]# systemctl restart mysqld

如果出现Mysqlj拒绝登录
[root@serverA mysql]# mysql
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)
[root@serverA mysql]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)
[root@serverA mysql]#

解决办法：在主配置文件添加一行内容
[root@serverA mysql]# vim /etc/my.cnf

[mysqld]
skip-grant-tables

之后重启Mysql即可

[root@serverA mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> 

设置mysql root用户密码

mysql> update mysql.user set authentication_string=password('qwert')where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 

退出mysql再次重新登录

[root@serverA mysql]# mysql -u root -p			之后发现可以登录，使用刚设置的密码
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.7.32

Copyright (c) 2000, 2020, 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> 

然后进入/etc/my.cnf文件注释掉刚写入的skip-grant-tables,再次重启数据库，使用用户名密码登录即可

3，搭建主从数据库：

​ mysql中有一种日志叫做bin日志，(二进制文件)，这个日志会记录所有的数据库sql语句
​ 主从复制的原理就是把主服务器上的bin日志复制到从服务器上面再执行一次，这样就可以实现从服务器上面的日志和主服务器上面的日志相同
​ 主节点：
​ 1，启用二进制日志
​ 2，设置全局唯一server-id
​ 3，创建复制权限的用户账号
​ 从节点：
​ 1，启动中继日志
​ 2，设置全局唯一server-id
​ 3，使用有复制权限的用户账号连接至主节点

配置环境：

​ 主节点：192.168.78.10
​ 从节点：192.168.78.20

主节点配置：

​ 1，启用二进制日志文件：

[root@serverA ~]# vim /etc/my.cnf
		[mysqld]	添加下面配置
		log-bin=mysql-bin
		binlog_format=mixed
		server-id=1
		innodb-file-per-table=ON
		skip_name_resolve=ON

​ 2，重启服务 systemctl restart mysqld

​ 3，进入数据库

​ 4，再次修改密码，使用如下命令

​ set global validate_password_length=4; 设置密码长度
​ set global validate_password_policy=0; 设置密码长度
​ alter user ‘root’@‘localhost’ identified by ‘密码’; 设置密码

mysql> set global validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5，查看二进制日志是否开启 mysql> show global variables like ‘%log%’;

6，查看主节点二进制日志列表 mysql> show master logs\G;

7，查看主节点server-id mysql> show global variables like ‘%server%’;

8，在主节点上面创建有复制权限的用户和密码 repluser,replpass

mysql> grant replication slave,replication client on *.* to 'repluser'@'192.168.78.20' identified by 'replpass';

9，刷新权限 mysql> flush privileges;
主节点配置完成 grant replication slave,replication client on . to ‘repluser’@‘192.168.78.10’ identified by ‘replpass’;

从节点配置：

1，开启中继日志

[root@serverB ~]# vim /etc/my.cnf
		[mysqld]	添加配置
		relay-log=relay-log
		relay-log-index=relay-log.index
		server-id=2
		innodb_file_per_table=ON
		skip_name_resolve=ON

2，重启服务 systemctl restart mysqld

3，进入数据库

4，再次修改密码，使用如下命令

​ set global validate_password_length=4; 设置密码长度
​ set global validate_password_policy=0; 设置密码长度
​ alter user ‘root’@‘localhost’ identified by ‘密码’; 设置密码

mysql> set global validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5，查看二进制日志是否开启 mysql> show global variables like ‘%log%’;

6，查看从节点server-id mysql> show global variables like ‘%server%’;

7，在从节点配置访问主节点的参数信息

mysql> change master to master_host='192.168.78.10',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000001',master_log_pos=1021;

8，查看从节点的状态信息 mysql> show slave status\G;

9，启动复制线程 mysql> start slave;

10,再次查看从节点状态信息 mysql> show slave status\G;
从节点配置完成

mysql主----从复制配置完成

从数据库里面马上就会生成相应的表

可测试在主数据库中建表，从数据库中查看是否有

之后如果出现了主服务器宕机，切换成从服务器，主服务器再次恢复，数据库自动再次切换至主服务器，从服务器slave状态出现了 Slave_SQL_Running: no

解决方法：
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
show slave status\G; 查看状态即可恢复主从数据传递