目录
- 项目信息
- 项目步骤
- IP规划
- 部署一台ansible服务器，搭建好免密通道并定义主机清单，在四台机器上批量安装MySQL，配置好相关环境
- 规划 MySQL 集群，一台做 master，三台做 slave
- 使用 mysqldump 导出master的基础数据并传到 ansible 上，ansible 下发到所有的 slave 上。
- master 和 slave 上开启 GTID 功能，实现数据库的主从复制，对 slave3 配置延迟备份作为 backup ，从 slave1 上获取二进制日志
- backup 和 ansible 服务器之间建立双向免密通道，把 ansible 作为一台异地备份机
- master 和 slave 上部署MHA，ansible作为管理节点，实现自动的故障切换，确保 master 宕机时，自动提升一台 slave 为新的 master。
- 部署两台 mysqlrouter 中间件机器，实现读写分离。
- mysqlrouter 机器上安装 keepalived ，配置 2 个 vrrp 实例，实现双 vip 的高可用功能。
- 部署监控机，安装prometheus和grafana监控集群性能
- 部署压力测试机，使用 sysbench 软件测试集群的性能。
- 项目心得
项目信息
项目结构
项目描述
构建一个高可用、读写分离的MySQL服务器集群，提高业务数据的稳定性，能够监控、批量部署和维护整个集群
项目环境
8台虚拟机，centos7.9，MySQL-5.7.37，mysql-router-8.0.21，keepalived，Prometheus，ansible等
项目步骤
IP规划
master 192.168.121.131
slave1 192.168.121.132
slave2 192.168.121.133
slave3 192.168.121.134
ansible 192.168.121.135
mysqlrouter1 192.168.121.136
mysqlrouter2 192.168.121.137
sysbench 192.168.121.138
prometheus 192.168.121.139
部署一台ansible服务器，搭建好免密通道并定义主机清单，在四台机器上批量安装MySQL，配置好相关环境
搭建ssh免密通道
[root@ansible ~]# yum install epel-release -y
[root@ansible ~]# yum install ansible -y
[root@ansible ~]# ssh-keygen
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.121.131
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.121.132
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.121.133
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.121.134
[root@ansible ~]# cd /etc/ansible/
[root@ansible ansible]# ls
ansible.cfg hosts roles
[root@ansible ansible]# vim hosts
[mysql]
192.168.121.131 #master
192.168.121.132 #slave1
192.168.121.133 #slave2
192.168.121.134 #slave3
使用ansible批量安装MySQL
[root@ansible ~]# ls
anaconda-ks.cfg mysql-router-community-8.0.21-1.el7.x86_64.rpm
mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz onekey_install_mysql_binary.sh
[root@ansible ~]# vim onekey_install_mysql_binary.sh
#!/bin/bash
# 解决软件的依赖关系并且安装需要工具
yum install net-tools ncurses-devel gcc gcc-c++ vim libaio lsof cmake bzip2 openssl-devel ncurses-compat-libs -y
# 解压二进制安装包
tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
# 移动解压后的文件
mv mysql-5.7.37-linux-glibc2.12-x86_64 /usr/local/mysql
# 新建用户和组mysql
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
# 关闭防火墙服务，设置开机不启动
service firewalld stop
systemctl disable firewalld
# 临时关闭selinux
setenforce 0
# 永久关闭selinux
sed -i '/^SELINUX=/ s/enforcing/disabled/' /etc/selinux/config
# 新建目录用来存放数据
mkdir /data/mysql -p
# 修改目录的权限，mysql用户启动的mysql进程可以对该目录文件进行读写
chown mysql:mysql /data/mysql/
# 只允许mysql用户和mysql组访问
chmod 750 /data/mysql/
# 进入目录
cd /usr/local/mysql/bin/
# 初始化
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql &>passwd.txt
# 让mysql支持ssl方式登录的设置
./mysql_ssl_rsa_setup --datadir=/data/mysql/
# 获取临时密码
tem_passwd=$(cat passwd.txt |grep "temporary"|awk '{print $NF}')
# 临时修改PATH变量的值
export PATH=/usr/local/mysql/bin/:$PATH
# 永久修改
echo 'PATH=/usr/local/mysql/bin:$PATH' >>/root/.bashrc
# 复制support-files/mysql.server到/etc/init.d/mysqld
cp ../support-files/mysql.server /etc/init.d/mysqld
# 修改/etc/init.d/mysqld datadir目录的值
sed -i '70c datadir=/data/mysql' /etc/init.d/mysqld
# /etc/my.cnf 配置文件
cat >/etc/my.cnf <<EOF
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
[mysql]
auto-rehash
prompt=\\u@\\d \\R:\\m mysql>
EOF
# 修改内核open file数量
ulimit -n 1000000
# 设置开机生效配置
echo "ulimit -n 1000000" >>/etc/rc.local
chmod +x /etc/rc.d/rc.local
# 将mysqld添加到linux系统服务管理名单
/sbin/chkconfig --add mysqld
# 设置mysqld服务开机启动
/sbin/chkconfig mysqld on
# 启动mysqld服务
service mysqld start
# 修改密码，初次修改需要--connect-expired-password 选项
mysql -uroot -p$tem_passwd --connect-expired-password -e "set password='mysql123';"
# 检验密码是否修改成功，能看到mysql的数据库，说明成功
mysql -uroot -p'mysql123' -e "show databases;"
# 编写yaml文件
[root@ansible ansible]# vim software_install.yaml
- hosts: mysql
remote_user: root
tasks:
- name: copy file
copy: src=/root/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz dest=/root/
- name: install mysql
script: /root/onekey_install_mysql_binary_v3.sh
- name: change path
shell: export PATH=/usr/local/mysql/bin/:$PATH
[root@ansible ansible]# ansible-playbook --syntax-check /etc/ansible/software_install.yaml
[root@ansible ansible]# ansible-playbook /etc/ansible/software_install.yaml
规划 MySQL 集群，一台做 master，三台做 slave
配置/etc/my.cnf
在master机器上
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
#skip-grant-tables
#log-error = a.err
general_log
slow_query_log = 1
long_query_time = 0.001
log_bin
server_id = 1
expire_logs_days = 15
rpl_semi_sync_slave_enabled=1
log_slave_updates=ON
#gtid-mode=ON
#enforce-gtid-consistency=ON
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
在slave机器上
其他配置相同，server_id按顺序加一
# 刷新服务
service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
使用 mysqldump 导出master的基础数据并传到 ansible 上，ansible 下发到所有的 slave 上。
# 在master上新建一个授权用户，用来给slave来复制二进制日志
mysql> grant replication slave on *.* to 'agent'@'%' identified by '123456';
# 导出master上的基础数据
[root@master ~]# mysqldump -uroot -p'mysql123' --all-databases --triggers --routines --events >all_db.sql
# 把基础数据传到ansible服务器上
[root@master ~]# scp all_db.sql [email protected]:/root/all_db.sql
# 在ansible服务器上把基础数据传到slave上
ansible mysql -m copy -a "src=/root/all_db.sql dest=/root"
# slave导入数据
mysql -uroot -p'Sanchuang123#' <all_db.sql
#
reset master；
stop slave；
reset slave all；
master 和 slave 上开启 GTID 功能，实现数据库的主从复制，对 slave3 配置延迟备份作为 backup ，从 slave1 上获取二进制日志
master和slave上安装半同步插件
mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_slave.so';
mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
# 检查插件是否安装成功
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';"
修改master和slave配置
master上的配置
[root@master ~]# vim /etc/my.cnf
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
#开启二进制日志
log_bin
server_id = 1
#开启半同步功能
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
#开启GTID功能
gtid-mode=ON
enforce-gtid-consistency=ON
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
# 刷新服务
[root@master ~]# service mysqld restart
# 查看进程
[root@master ~]# ps aux|grep mysqld
# 在master上建一个授权用户，用来给slave复制二进制日志
mysql>grant replication slave on *.* to 'slave'@'192.168.121.%' identified by 'mysql123';
# 刷新权限
mysql>flush privileges;
Query OK, 0 rows affected (0.01 sec)
# 清空二进制日志
mysql>reset master;
Query OK, 0 rows affected (0.01 sec)
slave1、2上的配置
# 注意在slave1上的操作，slave1是slave3的master
mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
[root@slave1 ~]# vim /etc/my.cnf
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
# 开启二进制日志
log_bin
# slave1、slave2的id分别是2、3
server_id = 2
# 开启半同步功能
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
rpl_semi_sync_slave_enabled=1
log_slave_updates=ON
# 开启GTID功能
gtid-mode=ON
enforce-gtid-consistency=ON
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
# 重启服务
[root@slave1 ~]# service mysqld restart
# 登录MySQL
[root@slave1 ~]# mysql -uroot -p"mysql123"
# 在 salve1 上新建一个授权用户，给 slave3 来复制二进制日志(slave2,slave3略过此步骤)
grant replication slave on *.* to 'slave'@'192.168.121.%' identified by 'mysql123';
# 清空二进制日志
reset slave all;
# 配置master信息
change master to master_host='192.168.121.131',
master_user='slave',
master_password='mysql123',
master_port=3306,
master_auto_position=1;
# 开启slave
mysql>start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
slave3(backup)上的配置
[root@slave3 ~]# vim /etc/my.cnf
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
server_id = 4
#开启半同步功能
rpl_semi_sync_slave_enabled=1
log_slave_updates=ON
#开启GTID功能
gtid-mode=ON
enforce-gtid-consistency=ON
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
[root@slave3 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
# 注意下面的master_host地址填slave1的
mysql> change master to master_host='192.168.121.132',
master_user='slave',
master_password='mysql123',
master_port=3306,
master_auto_position=1;
mysql>change master to master_delay = 600;
mysql>start slave;
测试
# 在master上建表建库
mysql>create database db1;
mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
# slave上查看
mysql>show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
backup 和 ansible 服务器之间建立双向免密通道，把 ansible 作为一台异地备份机
[root@slave3 ~]# ssh-keygen -t rsa
[root@slave3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected]
[root@slave3 ~]# vim db_backup.sh
#!/bin/bash
# 新建文件夹
mkdir /backup
# 把当前MySQL的数据导出，添加时间前缀
mysqldump -uroot -p'123456' --all-databases --triggers --routines --events >/backup/$(date +%Y%m%d%H%M%S)all_db.sql
# 使用scp把备份数据送到ansible服务器的/backup上
scp /backup/$(date +%Y%m%d%H%M%S)all_db.sql 192.168.121.135:/backup
# 编写计划任务，每两小时执行一次
[root@slave3 ~]# crontab -e
0 */2 * * * bash /backup/db_backup.sh
master 和 slave 上部署MHA，ansible作为管理节点，实现自动的故障切换，确保 master 宕机时，自动提升一台 slave 为新的 master。
安装MHA
[root@ansible ~]# mkdir /masterha
[root@ansible ~]# cd /masterha
[root@ansible masterha]# ls
mha4mysql-node-0.56-0.el6.noarch.rpm
mha4mysql-manager-0.56-0.el6.noarch.rpm
# 先下载依赖包
[root@ansible masterha]# yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager -y
# 安装rpm包
[root@ansible masterha]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
# MySQL集群里的机器都要安装mha-4mysql-node
# ansible把软件包分布到MySQL集群里
[root@ansible ~]# ansible mysql -m copy -a "src=/masterha/mha4mysql-node-0.56-0.el6.noarch.rpm dest=/root"
[root@ansible ~]# ansible mysql -m yum -a "name=perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager state=present"
[root@ansible ~]# ansible mysql -m shell -a "rpm -ivh /root/mha4mysql-node-0.56-0.el6.noarch.rpm"
# ansible安装mha4mysql-manager
[root@ansible ~]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
# 建立免密通道，MySQL集群里的所有机器之间要能免密登录
ssh-keygen -t rsa
ssh-copy-id -i id_rsa.pub [email protected].%
# MySQL集群里的机器创建监控用户
grant all privileges on *.* to 'monitor'@'192.168.121.%' identified by 'mysql123';
# 刷新权限
flush privileges;
配置MHA
# 创建目录
[root@ansible ~]# mkdir /etc/masterha
[root@ansible ~]# mkdir -p /var/log/masterha/app
# 编写脚本
[root@ansible ~]# cd /usr/local/bin
[root@ansible bin]# vim master_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL =>'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
# 改成漂移的VIP
my $vip = '192.168.0.200/24';
my $key = "1";
my $ssh_start_vip = "/sbin/ip a add $vip dev ens33:$key";
my $ssh_stop_vip = "/sbin/ip a del $vip dev ens33:$key";
my $exit_code = 0;
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "\n\n\n***************************************************************\n";
print "Disabling the VIP - $vip on old master: $orig_master_host\n";
print "***************************************************************\n\n\n\n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
my $exit_code = 10;
eval {
print "\n\n\n***************************************************************\n";
print "Enabling the VIP - $vip on new master: $new_master_host \n";
print "***************************************************************\n\n\n\n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print "Usage: master_ip_failover –command=start|stop|stopssh|status –orig_master_host=host –orig_master_ip=ip –orig_master_port=port –new_master_host=host –new_master_ip=ip –new_master_port=port\n";
}
# 赋予可执行权限
chmod 777 /usr/local/bin/master_failover
创建配置文件/etc/mha/app.cnf
[root@ansible ~]# vim /etc/masterha/app.cnf
[server default]
# manager工作目录
manager_workdir=/var/log/masterha/app
# manager日志目录
manager_log=/var/log/masterha/app/manager.log
# MySQL的数据目录
master_binlog_dir=/data/mysql/
# 脚本（需要自己编写）
master_ip_failover_script=/usr/local/bin/master_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
# MySQL管理帐号和密码
user=monitor
password=mysql123
# 监控间隔(秒)
ping_interval=1
remote_workdir=/tmp
# 复制帐号和密码
repl_user=replication
repl_password=mysql123
report_script=/usr/local/send_report
# 故障发生后关闭主机的脚本,不是必须的,但是你要设置为空
shutdown_script=""
ssh_user=root
# master
[server1]
hostname=192.168.121.131
port=3306
# salve
[server2]
hostname=192.168.121.132
port=3306
#设置为候选master，发生主从切换优先将此库选举为master
candidate_master=1
check_repl_delay=0
[server3]
hostname=192.168.121.133
port=3306
查看状态
# 检查管理节点到节点的ssh状态
[root@ansible .ssh]# masterha_check_ssh --conf=/etc/mha/app.cnf
# 检查复制环境
[root@ansible .ssh]# masterha_check_repl --conf=/etc/mha/app.cnf
# 检查管理节点的状态
[root@ansible .ssh]# masterha_check_status --conf=/etc/mha/app.cnf
# 启动和关闭管理节点的监控
[root@ansible .ssh]# nohup masterha_manager --conf=/etc/mha/app.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app/manager.log 2>&1 &
[root@ansible .ssh]# masterha_stop --conf=/etc/mha/app.cnf
测试，关闭master的mysqld
# 关闭服务
[root@master ~]# service mysqld stop
# 查看管理节点上的/etc/masterha/app.cnf
[root@ansible ~]# cat /etc/masterha/app.cnf
***
master_ip_failover_script=/usr/local/bin/master_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
user=monitor #user被删除了
password=mysql123
ping_interval=1
remote_workdir=/tmp
***
# server1被删除了
[server1]
hostname=192.168.121.131
port=3306
部署两台 mysqlrouter 中间件机器，实现读写分离。
部署mysqlrouter
# 安装软件包
[root@mysqlrouter1 ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
# 编辑配置文件
[root@mysqlrouter1 ~]# cd /etc/mysqlrouter/
[root@mysqlrouter1 mysqlrouter]# ls
mysqlrouter.conf
[root@mysqlrouter1 mysqlrouter]# vim mysqlrouter.conf
#read-only
[routing:slaves]
# mysqlrouter的ip
bind_address = 192.168.121.136:7001
# slave1，slave2的ip
destinations = 192.168.121.132:3306,192.168.121.133:3306
mode = read-only
connect_timeout = 1
#write and read
[routing:masters]
# mysqlrouter的ip
bind_address = 192.168.121.136:7002
#master
destinations = 192.168.121.131:3306
mode = read-write
connect_timeout = 1
# 关闭防火墙，设置开机不启动
service firewalld stop
systemctl disable firewalld
# 关闭selinux
setenforce 0
sed -i '/^SELINUX=/ s/enforcing/disabled/' /etc/selinux/config
# 启动mysqlrouter服务
[root@mysqlrouter1 mysqlrouter]# service mysqlrouter start
Redirecting to /bin/systemctl start mysqlrouter.service
监听7001和7002端口
[root@mysqlrouter1 mysqlrouter]# netstat -anplut|grep mysql
tcp 0 0 192.168.121.136:7001 0.0.0.0:* LISTEN 2261/mysqlrouter
tcp 0 0 192.168.121.136:7002 0.0.0.0:* LISTEN 2261/mysqlrouter
测试读写分离
在master上新建2个账号
一个可读可写
mysql>grant all on *.* to 'write'@'%' identified by 'mysql123';
一个只读
mysql>grant select on *.* to 'read'@'%' identified by 'mysql123';
# 使用只读的用户登录mysql
mysql -h 192.168.121.136 -P 7001 -uread -p'myqsl123'
mysql>show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>create database db2;
Access denied for user 'read'@'%' to database 'db2'\
# 在执行<写操作>时被拒绝
mysqlrouter 机器上安装 keepalived ，配置 2 个 vrrp 实例，实现双 vip 的高可用功能。
# 两台mysqlrouter上都要安装
[root@mysqlrouter1 ~]# yum install keepalived -y
[root@mysqlrouter1 ~]# cd /etc/keepalived/
[root@mysqlrouter1 keepalived]# ls
keepalived.conf
[root@mysqlrouter1 keepalived]# vim keepalived.conf
! Configuration File for keepalived
：
global_defs {
notification_email {
[email protected]
[email protected]
[email protected]
}
notification_email_from [email protected]
smtp_server 192.168.121.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
#vrrp_strict 注意这行打注释
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 80 #要和其他保持一样
priority 200 #数字大=优先级大
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.121.31
}
}
vrrp_instance VI_2 {
state BACKUP
interface ens33
virtual_router_id 100
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.121.32
}
}
[root@mysqlrouter1 keepalived]# service keepalived start
# 同理，编辑mysqlrouter2上的keepalived配置文件
[root@mysqlrouter2 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
[email protected]
[email protected]
[email protected]
}
notification_email_from [email protected]
smtp_server 192.168.121.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
#vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 80
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.121.31
}
}
vrrp_instance VI_2 {
state MASTER
interface ens33
virtual_router_id 100
priority 200
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.121.32
}
}
[root@mysqlrouter2 ~]# service keepalived start
# 验证vip的漂移
[root@mysqlrouter1 keepalived]# service keepalived stop
Redirecting to /bin/systemctl stop keepalived.service
[root@mysqlrouter2 keepalived]# ip a
发现mysqlrouter1的vip漂移到mysqlrouter2上了
部署监控机，安装prometheus和grafana监控集群性能
被监控的机器安装mysqld_exporter
# 在所有被监控的MySQL服务器上建立一个授权用户
# 这里以slave1为例
mysql>grant all on *.* to 'mysqld_exporter'@'%' identified by 'mysql123';
# 关闭防火墙，开机不启动
[root@slave1 ~]# service firewalld stop
[root@slave1 ~]# systemctl disable firewalld
# 关闭selinux
[root@slave1 ~]# setenforce 0
[root@slave1 ~]# sed -i '/^SELINUX=/ s/enforcing/disabled/' /etc/selinux/config
# 所有被监控的MySQL上安装mysqld_exporter
[root@slave1 ~]# tar xf mysqld_exporter-0.12.1.linux-amd64.tar.gz
[root@slave1 ~]# mv mysqld_exporter-0.12.1.linux-amd64 /usr/local/mysqld_exporter
[root@slave1 ~]# cd /usr/local/mysqld_exporter/
# 编辑my.cnf配置文件
[root@slave1 mysqld_exporter]# vim my.cnf
[client]
user=mysqld_exporter
password=123456
# 修改环境变量
[root@slave1 mysqld_exporter]# PATH=/usr/local/mysqld_exporter/:$PATH
[root@slave1 mysqld_exporter]# echo "PATH=/usr/local/mysqld_exporter/:$PATH" >>/root/.bashrc
# 后台启动
[root@slave1 mysqld_exporter]# nohup mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/my.cnf &
# 查看进程、端口
[root@slave1 ~]# ps aux|grep exporter
[root@slave1 ~]# netstat -anplut|grep exporter
部署Prometheus
[root@prometheus ~]# ls
anaconda-ks.cfg prometheus-2.43.0.linux-amd64.tar.gz
[root@prometheus ~]# tar xf prometheus-2.43.0.linux-amd64.tar.gz
[root@prometheus ~]# mv prometheus-2.43.0.linux-amd64 /prometheus
[root@prometheus prometheus]# PATH=/prom/prometheus:$PATH
[root@prometheus prometheus]# echo "PATH=/prom/prometheus:$PATH" >>/etc/profile
[root@prometheus prometheus]# service firewalld stop
[root@prometheus prometheus]# systemctl disable firewalld
[root@prometheus prometheus]# setenforce 0
[root@prometheus prometheus]# sed -i '/^SELINUX=/ s/enforcing/disabled/' /e
# 配置Prometheus服务
[root@prometheus ~]# vim /usr/lib/systemd/system/prometheus.service
[Unit]
Description=prometheus
[Service]
ExecStart=/prom/prometheus/prometheus --config.file=/prom/promethe
us/prometheus.yml
ExecReload=/bin/kill -HUP $MAINPID
killMode=process
Restart=on-failure
[Install]
WantedBy=multi-user.target
# 编辑Prometheus的yaml文件
[root@prometheus ~]# cd /prometheus
[root@prometheus prometheus]# vim prometheus.yml
scrape_configs:
# The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
- job_name: "prometheus"
static_configs:
- targets: ["localhost:9090"]
- job_name: "mysqlrouter1"
static_configs:
- targets: ["192.168.121.136:9106"]
- job_name: "mysqlrouter2"
static_configs:
- targets: ["192.168.121.137:9106"]
- job_name: "master"
static_configs:
- targets: ["192.168.121.131:9106"]
- job_name: "slave1"
static_configs:
- targets: ["192.168.121.132:9106"]
- job_name: "slave2"
static_configs:
- targets: ["192.168.121.133:9106"]
[root@prometheus ~]# service prometheus restart
Redirecting to /bin/systemctl start prometheus.service
安装grafana
[root@prometheus ~]# yum install grafana-enterprise-9.1.2-1.x86_64.rpm -y
[root@prometheus ~]# service grafana-server start
Starting grafana-server (via systemctl): [ 确定 ]
# 浏览器访问 ip:3000
默认用户名:admin
默认密码:admin
Configuration --> Add data source -> 选择Prometheus
填 http://192.168.121.139:9090
dashboard -> import -> 导入14057模板 -> 选择Prometheus数据源
部署压力测试机，使用 sysbench 软件测试集群的性能。
# 安装sysbench
[root@test ~]# curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
[root@test ~]# sudo yum install sysbench-1.0.15-1.el7.centos.x86_64
# 测试连接中间件服务器，一定要去连接写的端口7002，不然会导致写数据到从服务器上，因为有2个从服务器，会轮询调度到不同的机器，会出现找不到数据库。使用写的账号连接，不然会没有权限去写入数据
[root@test ~]# sysbench --mysql-host=192.168.121.136 --mysql-port=7002 --mysql-user=write --mysql-password='mysql123' /usr/share/sysbench/oltp_common.lua --tables=10 --table_size=10000 prepare
[root@test ~]# sysbench --threads=4 --time=20 --report-interval=5 --mysql-host=192.168.121.136 --mysql-port=7002 --mysql-user=write --mysql-password='mysql123' /usr/share/sysbench/oltp_read_write.lua --tables=10 --table_size=100000 run
项目心得
1.对集群架构的规划能力得到了提升
2.对MySQL集群有了更深入的理解
3.对ansible自动化运维有了新的认识
4.对keepalived，MHA等高可用组件有了更多的理解