初始化mysql实例
部署目录: /database/mysql
安装包
链接:https://pan.baidu.com/s/1y4UGrHJuALSTQJIR3iABcA?pwd=ew6e
提取码:ew6e
上传mysql5.7安装包解压
tar zxvf mysql-5.7.39-el7-x86_64.tar.gz
mv mysql-5.7.39-el7-x86_64 mysql57
配置环境变量/etc/profile或者~/.bash_profile
export MYSQL_HOME=/database/mysql57
export PATH=$PATH:$MYSQL_HOME/bin:$MYSQL_HOME/lib
创建mysql用户
sudo groupadd -g 27 mysql
sudo useradd -u 27 -g mysql -d /var/lib/mysql -s /bin/false -c "MySQL Server" mysql
创建文件目录
mkdir -p /database/mysql/data
mkdir -p /database/mysql/mariadb
touch /database/mysql/mariadb/mysqld.log
# 修改目录权限给mysql
chwon -R mysql:mysql /database/mysql
创建mysql配置文件-主库
cd /database/mysql
vim my.cnf
# 一下内容直接粘贴
[mysqld]
# mysql数据存放位置
datadir=/database/mysql/data
socket=/database/mysql/mysql.sock
symbolic-links=0
# 日志集pid位置
log-error=/database/mysql/mariadb/mysqld.log
pid-file=/database/mysql/mariadb/mysqld.pid
# id标识,每个实例的id不能相同
server-id=1
# logbin文件前缀
log-bin=mysql_master
# 作为从库时 更新操作是否写入日志 on:写入 其他数据库以此数据库做主库时才能进行同步
log-slave-updates=on
sync_binlog=1
binlog_format=mixed
expire_logs_days=15
max_binlog_size=100m
binlog_cache_size=4m
slave-skip-errors=all
# 同步数据时忽略一下数据库 但是必须在使用use db的情况下才会忽略;如果没有使用use db 比如create user 数据还是会同步的
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
# 忽略MySQL系统库的表 这样在create user时也不会进行同步了
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%
# 忽略系统库日志
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
character-set-server=utf8
collation-server=utf8_general_ci
default-storage-engine=INNODB
lower_case_table_names=1
[mysqld_safe]
default-character-set = utf8
[mysql]
default-character-set = utf8
[mysql.server]
default-character-set = utf8
[client]
default-character-set = utf8
mysql配置文件-从库,只需要修改以下两处就可
# id标识,每个实例的id不能相同
server-id=2
# logbin文件前缀
log-bin=mysql_node
初始化mysql数据库,并启动
# 初始化数据实例
mysqld --initialize --user=mysql --basedir=/database/mysql57 --datadir=/database/mysql/data
# 启动
mysqld_safe --defaults-file=/database/mysql/my.cnf --user=mysql &
# 日志会输出root用户的初始密码,类似于:root@localhost: BFX-osyhl5&a
# 创建软链接
ln -s /database/mysql/mysql.sock /tmp/mysql.sock
首次登录mysql,修改密码及访问权限
mysql -u root -p
# 回车输入初始化时,展示的初始密码
# 修改root用户密码
alter user 'root'@'localhost' IDENTIFIED BY 'xxxxx';
use mysql;
update user set Host='%' where User='root';
FLUSH PRIVILEGES;
配置主从关系
主库配置,创建同步用户,查询节点状态
# mysql -u root -p 进入主库
# 创建用户
CREATE USER 'master_sync'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'xxxx';
# 添加权限
GRANT REPLICATION SLAVE ON *.* TO 'master_sync'@'%';
FLUSH PRIVILEGES;
# 获取主库binlog日志数据,记录下file名称和position节点
show master status ;
从库配置
# mysql -u root -p 进入从库
# 创建同步配置
change master to master_host='192.168.xx.xx',MASTER_PORT=3306,master_user='master_sync',master_password='xxxx',master_log_file='mysql_master.000002',master_log_pos=543;
# 开启主从
start slave;
# 查看主从同步状态
show slave status;
把上面从库当作主库,按照以上主从配置再走一边
# mysql -u root -p 进入数据库
# 创建用户
CREATE USER 'node_sync'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'xxxx';
# 添加权限
GRANT REPLICATION SLAVE ON *.* TO 'node_sync'@'%';
FLUSH PRIVILEGES;
# 获取主库binlog日志数据,记录下file名称和position节点
show master status ;
(原主库)从库配置
# mysql -u root -p 进入从库
# 创建同步配置
change master to master_host='192.168.xx.xx',MASTER_PORT=3306,master_user='node_sync',master_password='xxxx',master_log_file='mysql_node.000001',master_log_pos=1232;
# 开启主从
start slave;
# 查看主从同步状态
show slave status;
配置prometheus的监控
使用mysqld_exporter采集mysql数据 mysql_exporter安装包
链接:https://pan.baidu.com/s/1gPIvVWyLuBUF_BPvl8r4hQ?pwd=kpro
提取码:kpro
安装及配置
# 解压
tar zxvf mysqld_exporter-0.15.0-rc.0.linux-amd64.tar.gz
mv mysqld_exporter-0.15.0-rc.0.linux-amd64 mysqld_exporter
mv mysqld_exporter /usr/local/
cd /usr/local/mysqld_exporter/
# 添加连接信息
vim exporter.cnf
# 将一下内容复制进去
[client]
# 若采集本服务器上数据,可设置为localhost,注意修改用户名和密码
host=localhost
port=3306
user=user
password=passwd
启动
nohup ./mysqld_exporter --config.my-cnf=exporter.cnf &
在Prometheus中增加配置数据 可以在浏览器访问查看采集到的标签数据 http://ip:9104 监控主从看以下指标
# 查询从库落后主库的秒数
mysql_slave_status_seconds_behind_master
# 查询所有从库SQL线程的运行状态,线程都在运行,结果为1
mysql_slave_status_slave_sql_running
# 查询所有从库IO线程的运行状态,io线程都在运行,结果为1
mysql_slave_status_slave_io_running
告警指标配置样例
# mysql从库与主库的延迟
mysql_slave_status_seconds_behind_master >= 300
# mysql从库SQL线程的运行状态
mysql_slave_status_slave_sql_running == 0
# mysql从库IO线程的运行状态
mysql_slave_status_slave_io_running == 0
keepalived高可用
mysql的主主无法做到高可用,借助keepalived实现节点切换。 设置虚拟ip,实现双机的热备。 使用killall -0 检查mysql进程是否存在,若服务器不存在killall,需要安装psmisc
# 检查
rpm -qa |grep psmisc
# 安装
yum install -y psmisc
验证killall是否可以正常检查到mysql进程
if /usr/bin/killall -0 mysqld_safe 2>/dev/null; then
echo "MySQL server is running."
else
echo "MySQL server is not running."
fi
# 因为安装有mysqld_exporter,若使用mysqld做标识符,会出现异常
安装keepalived
# 检查是否存在
rpm -qa |grep keepalived
# 安装keepalived,安装完成后,/etc/keepalived有配置文件
yum -y install keepalived
首先编辑主节点的配置文件 备份: cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf_bak 编辑: vim /etc/keepalived/keepalived.conf
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
# vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
# 检查mysql服务是否存活的脚本
vrrp_script chk_mysqld {
script "/usr/bin/killall -0 mysqld_safe"
}
vrrp_instance VI_1 {
# 角色, 副节点角色设置为backup
state MASTER
# 网卡,确定使用哪个网卡
interface em1
# 虚拟路由
virtual_router_id 51
# 优先级, 副节点优先级设置低些
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
# 虚拟ip
virtual_ipaddress {
192.168.xxx.xxx
}
track_script {
chk_mysqld
}
}
# 以下的不做使用,直接注释
#virtual_server 192.168.200.100 443 {
# delay_loop 6
# lb_algo rr
# lb_kind NAT
# persistence_timeout 50
# protocol TCP
#
# real_server 192.168.201.100 443 {
# weight 1
# SSL_GET {
# url {
# path /
# digest ff20ad2481f97b1754ef3e12ecd3a9cc
# }
# url {
# path /mrtg/
# digest 9b3a0c85a887a256d6939da88aabd8cd
# }
# connect_timeout 3
# nb_get_retry 3
# delay_before_retry 3
# }
# }
#}
#
#virtual_server 10.10.10.2 1358 {
# delay_loop 6
# lb_algo rr
# lb_kind NAT
# persistence_timeout 50
# protocol TCP
#
# sorry_server 192.168.200.200 1358
#
# real_server 192.168.200.2 1358 {
# weight 1
# HTTP_GET {
# url {
# path /testurl/test.jsp
# digest 640205b7b0fc66c1ea91c463fac6334d
# }
# url {
# path /testurl2/test.jsp
# digest 640205b7b0fc66c1ea91c463fac6334d
# }
# url {
# path /testurl3/test.jsp
# digest 640205b7b0fc66c1ea91c463fac6334d
# }
# connect_timeout 3
# nb_get_retry 3
# delay_before_retry 3
# }
# }
#
# real_server 192.168.200.3 1358 {
# weight 1
# HTTP_GET {
# url {
# path /testurl/test.jsp
# digest 640205b7b0fc66c1ea91c463fac6334c
# }
# url {
# path /testurl2/test.jsp
# digest 640205b7b0fc66c1ea91c463fac6334c
# }
# connect_timeout 3
# nb_get_retry 3
# delay_before_retry 3
# }
# }
#}
#
#virtual_server 10.10.10.3 1358 {
# delay_loop 3
# lb_algo rr
# lb_kind NAT
# persistence_timeout 50
# protocol TCP
#
# real_server 192.168.200.4 1358 {
# weight 1
# HTTP_GET {
# url {
# path /testurl/test.jsp
# digest 640205b7b0fc66c1ea91c463fac6334d
# }
# url {
# path /testurl2/test.jsp
# digest 640205b7b0fc66c1ea91c463fac6334d
# }
# url {
# path /testurl3/test.jsp
# digest 640205b7b0fc66c1ea91c463fac6334d
# }
# connect_timeout 3
# nb_get_retry 3
# delay_before_retry 3
# }
# }
#
# real_server 192.168.200.5 1358 {
# weight 1
# HTTP_GET {
# url {
# path /testurl/test.jsp
# digest 640205b7b0fc66c1ea91c463fac6334d
# }
# url {
# path /testurl2/test.jsp
# digest 640205b7b0fc66c1ea91c463fac6334d
# }
# url {
# path /testurl3/test.jsp
# digest 640205b7b0fc66c1ea91c463fac6334d
# }
# connect_timeout 3
# nb_get_retry 3
# delay_before_retry 3
# }
# }
}
启动keepalived
systemctl start keepalived
# 检查
systemctl status keepalived
检查服务器虚拟ip
ip addr
后面可以使用虚拟ip连接数据库,验证mysql的连接情况。
主备切换
可以尝试将主节点服务器上的mysql服务停掉,检查主节点上的ip情况,此时连接数据库感知不到节点已经切换的。