keepalived mysq keepalived mysql主从和主主

转载

编程小匠人之魂 2024-04-13 13:02:53

文章标签 keepalived mysq 数据库 mysql 可用性测试 ide 文章分类 运维

概述：

keepalived mysq keepalived mysql主从和主主_可用性测试

MySQL 5.7和Keepalived主主配置架构是一种高可用性架构，它可以确保在一个节点出现故障时，系统仍然能够正常运行。在这种架构中，有两个MySQL节点，每个节点都是主节点和从节点的组合，它们共享相同的数据。 Keepalived是一个开源的软件，可以在多个服务器之间实现负载均衡和故障转移。

在MySQL 5.7和Keepalived主主配置架构中，Keepalived用于监控MySQL节点的状态，并在一个节点出现故障时，将流量转移到另一个节点上。

这种架构的好处是：

1.高可用性：由于有两个节点，因此即使其中一个节点出现故障，系统仍然能够正常运行。

2.负载均衡：两个节点可以共享相同的数据，从而实现负载均衡，提高系统的性能。

3.故障转移：当一个节点出现故障时，Keepalived可以自动将流量转移到另一个节点上，从而保证系统的可用性。

4.数据一致性：由于两个节点共享相同的数据，因此在一个节点上进行的更改会立即反映在另一个节点上，从而保证数据的一致性。

一、mysql安装部分

1.1 创建/mysql文件目录

pvcreate /dev/sdb

vgcreate mysqlvg/dev/sdb

lvcreate -l 100%FREE -n mysqllv mysqlvg

mkfs.xfs /dev/mapper/mysqlvg-mysqllv

mkdir /mysql

mount /dev/mapper/rhel-mysql /mysql

1.2 安装mysql依赖包

rpm -qa|grep mariadb

rpm -e  mariadb-libs     --nodeps

yum -y install numactl libaio

1.3 解压文件

tar -xvf mysql-5.7.29-el7-x86_64.tar

tar -zxvf mysql-5.7.29-el7-x86_64.tar.gz

mv mysql-5.7.29-el7-x86_64 /mysql/mysqlbase

mkdir /mysql/data

mkdir /mysql/logs

1.5 创建用户，用户组

groupadd mysql

useradd -r -g mysql mysql

chown -R mysql.mysql /mysql

1.6 安装mysql

cd /mysql/mysqlbase/bin

./mysqld --initialize --user=mysql --basedir=/mysql/mysqlbase/ --datadir=/mysql/data/

cd /mysql/mysqlbase/support-files/

cp mysql.server /etc/init.d/mysql

mkdir -p /var/lib/mysql

chown -R mysql.mysql /var/lib/mysql

1.7 基本的单点的mysql配置文件

vi /etc/my.cnf

[client]

socket=/var/lib/mysql/mysql.sock

[mysql]

socket=/var/lib/mysql/mysql.sock

[mysqld]

log_error=/mysql/data/err.log

port = 3306

socket=/var/lib/mysql/mysql.sock

basedir=/mysql/mysqlbase

datadir=/mysql/data

1.8启停mysql

/etc/init.d/mysql  start

ln -s /mysql/mysqlbase/bin/mysql /usr/bin

/etc/init.d/mysql  stop

1.8 修改root密码

1.先使用默认密码登录mysql

mysql -uroot -paj!w#:utp7DI

2.修改root密码

设置本地root登录密码

set password for root@localhost = password('root2019!');

设置远程root登录密码

set password for root@'%' = password('root2019!');

flush privileges;

1.9 其他mysql参考命令

1.创建数据库

CREATE DATABASE IF NOT EXISTS tenxcloud_2_0 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

2创建用户

create user 'root0532'@'%' identified by 'root2019!';

注意：

此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。

3用户分配权限

--授予用户test通过外网IP对数据库“tenxcloud_2_0”的全部权限
grant all privileges on *.* to root0532@localhost identified by 'Root2019!';
grant all privileges on root0532.* to root0532@'%' identified by 'Root2019!';
--刷新权限
flush privileges;

4.查看用户权限

show grants for root0532;

5.查询用户

select user,host from mysql.user;

6.删除用户

drop user root0532@localhost;

--若创建的用户允许任何电脑登陆，删除用户如下

drop user root0532@'%';

7.更改密码

--方法1，密码实时更新；修改用户“test”的密码为“1122”

set password for root0532 =password('1122');

--方法2，需要刷新；修改用户“test”的密码为“1234”

update  mysql.user set  password=password('1234')  where user='root0532'

--授予用户“test”通过外网IP对于该数据库“testdb”中表的创建、修改、删除权限,以及表数据的增删查改权限

grant create,alter,drop,select,insert,update,delete on testdb.* to root0532@'%';

二、主主复制配置

2.1、准备配置文件

节点1 /etc/my.cnf配置文件内容

[client]

socket=/var/lib/mysql/mysql.sock

[mysql]

socket=/var/lib/mysql/mysql.sock

[mysqld]

log_error=/mysql/data/err.log

port = 3306

socket=/var/lib/mysql/mysql.sock

basedir=/mysql/mysqlbase

datadir=/mysql/data

server-id = 1

auto_increment_offset = 1

auto_increment_increment = 2                                           

log-bin = mysql-bin                                                    

binlog-format=ROW

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=0

sync_binlog=0

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

#expire_logs_days=5

max_binlog_size=1024M

节点2 /etc/my.cnf配置文件内容

[client]

socket=/var/lib/mysql/mysql.sock

[mysql]

socket=/var/lib/mysql/mysql.sock

[mysqld]

log_error=/mysql/data/err.log

port = 3306

socket=/var/lib/mysql/mysql.sock

basedir=/mysql/mysqlbase

datadir=/mysql/data

server-id = 2

auto_increment_offset = 2

auto_increment_increment =2                                                                                                             

log-bin = mysql-bin

binlog-format=ROW

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=0

sync_binlog=0

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

#expire_logs_days=5

max_binlog_size=1024M

2.2 添加主从同步账户

节点1上：

mysql> grant replication slave on *.* to 'repl'@'10.50.2.58' identified by '123456';

mysql> flush privileges;

节点2上：

mysql> grant replication slave on *.* to 'repl'@'10.50.2.57' identified by '123456';

mysql> flush privileges;

2.3 查看主库的状态

节点1上：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

节点2上

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      437 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.4 配置同步信息：

注意点：节点1要配置节点2上看到的master_log_file和master_log_pos信息。

节点1上：

mysql> change master to master_host='10.50.2.58',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=608;

mysql> start slave;

mysql> show slave status\G;

显示有如下状态则正常：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

节点2上：

#本人是测试环境，可以保证没数据写入，否则需要的步骤是：先节点1锁表-->节点1备份数据-->节点1解锁表 -->节点2导入数据-->节点2设置主从-->查看主从

mysql> change master to master_host='10.50.2.57',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=608;

start slave;

mysql> show slave status\G;

2.5 测试主从同步

3.1 分别在节点1上和节点2上创建一个数据库测试同步效果

三、keepalived安装配置篇

3.1 软件安装并配置开机自启动

yum install keepalived -y

systemctl start keepalived

systemctl enable  keepalived

3.2 配置文件及监听脚本配置

节点1 keepalived.conf文件内容

cat /etc/keepalived/keepalived.conf

global_defs {

}



vrrp_script chk_port {

  script "/etc/keepalived/check_port.sh 3306"

  interval 2

}

vrrp_instance VI_1 {

state MASTER

interface ens192

virtual_router_id 123

priority 150

advert_int 1

authentication {

auth_type PASS

auth_pass proxy776

}

virtual_ipaddress {

       10.50.2.56/24 dev ens192 label ens192:vip

}



track_script {

    chk_port

}



}

节点2 keepalived.conf

global_defs {

}

vrrp_script chk_port {

  script "/etc/keepalived/check_port.sh 3306"

  interval 2

}

vrrp_instance VI_1 {

state BACKUP

interface ens192

virtual_router_id 123

priority 130

advert_int 1

authentication {

auth_type PASS

auth_pass proxy776

}

virtual_ipaddress {

       10.50.2.56/24 dev ens192 label ens192:vip

}



track_script {

    chk_port

}



}

检测mysql活动状态脚本check_port.sh

cat /etc/keepalived/check_port.sh

#!/bin/bash



for p in $*;do

       </dev/tcp/127.0.0.1/$p

       if [ $? -ne 0 ]; then

              systemctl stop keepalived.service

              echo "`date +%Y-%m-%d_%H:%M:%S` [ $p ] connected failed !" >> /var/log/keepalived_check.log

              exit 1

       fi

done