CentOS 7.9 64位
2.2 禁用selinux(双主节点)
2.3 设置用户的文件句柄及进程数(双主节点)
2.4 系统内核参数优化(双主节点)
Vim /etc/sysctl.conf
vm.swappiness = 0
net.ipv6.conf.all.disable_ipv6 = 1
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 600
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.ip_local_port_range = 40000 65500
kernel.sem = 250 32000 100 128
vm.min_free_kbytes = 524288
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 2542751744
vm.min_free_kbytes=524288
kernel.shmall = free/4
kernel.shmmax = free*1024*0.8
安装mysql数据库
在Oracle官网下载对应版本的mysql免编译二进制安装包解压即可。
配置mysql
配置mysql master节点:
▼▼▼
[mysql]
port=3306
socket=/data/mysql/tmp/mysql.sock
default-character-set=utf8
[mysqld_safe]
log-error=/data/mysql/log/mysql.error
[mysqld]
port=3306
user=shsnc
basedir=/home/shsnc/mysql
datadir=/data/mysql/data
socket=/data/mysql/tmp/mysql.sock
pid-file=/data/mysql/log/mysql.pid
character-set-server=utf8
skip-name-resolve
lower_case_table_names=1
transaction_isolation=READ-COMMITTED
server_id=1
autocommit=1
tmp_table_size=128M
max_heap_table_size=96M
max_connections=1000
max_connect_errors=6000
long_query_time=1
innodb_buffer_pool_size=16G
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=8M
innodb_log_file_size=128M
innodb_log_files_in_group=2
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_write_io_threads=8
innodb_read_io_threads=4
innodb_doublewrite=0
innodb_purge_threads=1
innodb_stats_on_metadata=OFF
innodb_io_capacity=1000
log-bin-trust-function-creators=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER
event_scheduler=on
max_allowed_packet=2G
slow_query_log=1
auto_increment_offset=1
auto_increment_increment=2
#replication
log_bin=/data/mysql/log/bin.log
log_bin_index=/data/mysql/log/bin.index
sync_binlog=1
master_info_repository=TABLE
relay_log_info_repository=TABLE
sync_binlog=1
log_slave_updates
binlog_format=ROW
binlog_rows_query_log_events=1
relay_log=/data/mysql/log/relay.log
relay_log_recovery=1
slave_skip_errors=ddl_exist_errors
slave-rows-search-algorithms=INDEX_SCAN,HASH_SCAN
#gtid
enforce_gtid_consistency=1
gtid_mode=ON
master_info_repository=table
relay_log_info_repository=table
slave_parallel_type=logical_clock
slave_parallel_workers=4
slave_pending_jobs_size_max=1G
slave_preserve_commit_order=1
sync_master_info=1
sync_relay_log_info=1
注:配置slave节点的时候只需修改server_id选项即可。
启动mysql服务
▼▼▼
mysqld_safe --defaults-file=/home/mysql/my_cnf/my.cnf &
创建mysql复制账号
▼▼▼
create userrepl@192.168.1.% identified with mysql_native_password by xxxxxx;
grant REPLICATION SLAVE, REPLICATION CLIENT on *.* to repl@192.168.1.%;
flush privileges;
配置双主同步
配置113同步到115:
▼▼▼
CHANGE MASTER TO
MASTER_HOST=192.168.1.113,
MASTER_USER=repl,
MASTER_PASSWORD=xxxxxx,
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
Start slave;
配置115同步到113:
▼▼▼
CHANGE MASTER TO
MASTER_HOST=192.168.1.115,
MASTER_USER=repl,
MASTER_PASSWORD=xxxxxx,
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
Start slave;
查看slave状态,Show slave statusG,若 IO、SQL进程状态都为YES则配置OK。
▼▼▼
./configure --prefix=/opt/keepalived && make && make install
配置keeepliaved
配置keepalived MASTER路由器
vi /opt/keepalived/etc/keepalived/keepalived.conf
▼▼▼
! Configuration File for keepalived
global_defs {
notification_email {
shsnc@shsnc.com
}
notification_email_from smtp.163.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_script checkk_mysql {
script "/opt/keepalived/script/check_mysql.sh"
interval 5
fall 3
rise 2
}
vrrp_instance VI_1 {
state BACKUP
interface ens160
virtual_router_id 51
priority 100
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass 110120
}
virtual_ipaddress {
135.10.51.8
}
track_script {
checkk_mysql
}
notify_master "/opt/keepalived/script/keepalived_notify.sh master"
notify_backup "/opt/keepalived/script/keepalived_notify.sh backup"
}
注:BACKUP路由器只需修改优先级选项即可。
HA实时监测脚本,检查 mysql 进程是否存在,如果mysql挂了就停止keepalived,使VIP漂移到BACKUP机器上
Vim /opt/keepalived/script/check_mysql.sh
▼▼▼
A=`ps -C mysqld --no-header |wc -l`
if [ $A -eq 0 ]
then
/bin/systemctl stop syskeepalived
fi
HA切换执行脚本:
Vim /opt/keepalived/script/keepalived_notify.sh
▼▼▼
#!/bin/bash
. ~/.bash_profile
#mysql user
DB_USER="root"
#mysql user password
DB_PASSWORD=shsnc!@#
MYSQL_SOCK=""
#mysql_bin
MYSQL_BIN="/home/shsnc/mysql/bin/mysql"
#mysql client command
MYSQL_CMD="${MYSQL_BIN} -u${DB_USER} -p${DB_PASSWORD}"
#query the killed seesions id sql
MYSQL_SQL="select concat(kill ,id,;) from information_schema.processlist where user not in (system user,repl,replic,backup,bkpuser,bomcjk,root,myrobot)"
#关闭事件任务
AMP_EVENT_PARTITION_DISABLE="ALTER event domp.event_add_partitions ON COMPLETION PRESERVE DISABLE"
DOMP_EVENT_PARTITION_DISABLE="ALTER event amp.event_add_partitions ON COMPLETION PRESERVE DISABLE"
#开启事件任务
AMP_EVENT_PARTITION_ENABLE="ALTER event domp.event_add_partitions ON COMPLETION PRESERVE ENABLE"
DOMP_EVENT_PARTITION_ENABLE="ALTER event amp.event_add_partitions ON COMPLETION PRESERVE ENABLE"
#define function: get mysql service information
function get_mysql_infor()
{
for sock in `ps -ef | grep mysqld | grep --socket= | awk -F--socket= {print $2} | awk {print $1}`
do
MYSQL_SOCK="${MYSQL_SOCK}$(echo $sock)"
done
}
#define function: mysql kill sessions
function kill_sessions()
{
#receive a mysql socket file parameter
#my_sock=$(get_mysql_infor)
unset MYSQL_SOCK
get_mysql_infor
# kill session
#..................
#begin to kill mysql sessions
${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${MYSQL_SQL}" 2>/dev/null | ${MYSQL_CMD} > /dev/null 2>&1
}
#define function: set mysql read_only mode
function set_readonly()
{
#receive mysql socket file && read_only sign parameter
#my_sock=$(get_mysql_infor)
unset MYSQL_SOCK
get_mysql_infor
my_sign=$1
#begin to set mysql read_only mode
${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "set global read_only=${my_sign}" 2>/dev/null
}
#define function: keepalived state changed to master
function Keepalived_changed_to_master()
{
#my_sock=$(get_mysql_infor)
unset MYSQL_SOCK
get_mysql_infor
#开始事件任务
${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${AMP_EVENT_PARTITION_ENABLE} 2>/dev/null "
${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${DOMP_EVENT_PARTITION_ENABLE} 2>/dev/null "
Seconds_Behind_Master=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Seconds_Behind_Master | awk -F": " {print $2})
Slave_IO_Running=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Slave_IO_Running | awk -F": " {print $2})
Slave_SQL_Running=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Slave_SQL_Running | awk -F": " {print $2})
Master_Log_File=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Master_Log_File | awk -F": " {print $2})
Relay_Master_Log_File=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Relay_Master_Log_File | awk -F": " {print $2})
Read_Master_Log_Pos=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Read_Master_Log_Pos | awk -F": " {print $2})
Exec_Master_Log_Pos=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Exec_Master_Log_Pos | awk -F": " {print $2})
echo
#
if [ "${Slave_IO_Running}" = "Yes" -a "${Slave_SQL_Running}" = "Yes" ]
then
if [ $Seconds_Behind_Master -eq 0 ]
then
echo 判断复制无延迟,即将切换
#.........set mysql no read_only mode.........
set_readonly 0
exit 0
fi
fi
if [ "${Slave_IO_Running}" = "Connecting" -a "${Slave_SQL_Running}" = "Yes" ]
then
if [ "${Master_Log_File}" = "${Relay_Master_Log_File}" -a "${Read_Master_Log_Pos}" = "${Exec_Master_Log_Pos}" ]
then
echo "主库宕机,即将切换"
#.........set mysql no read_only mode.........
set_readonly 0
exit 0
fi
fi
}
#define function: keepalived state changed to backup
function Keepalived_changed_to_backup()
{
#my_sock=$(get_mysql_infor)
unset MYSQL_SOCK
get_mysql_infor
${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${AMP_EVENT_PARTITION_DISABLE} 2>/dev/null "
${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${DOMP_EVENT_PARTITION_DISABLE} 2>/dev/null "
#set mysql read_only mode
set_readonly 1
#kill mysql sessions
kill_sessions
}
#start this shell
case $1 in
master)
Keepalived_changed_to_master
;;
backup)
Keepalived_changed_to_backup
;;
*)
;;
esac
启动keepalived
systemctl start keepalived
观察113/115机器上的日志情况
tailf /var/logs/message
查看113/115机器上IP信息,查看VIP地址是否起来
ip addr
同时模拟keepalived故障时ip漂移情况以及主从节点只读状态设置情况:
▼▼▼
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO shsnc@135.10.66.125 identified by xxxxxx;
使用mysql客户端命令行通过VIP地址访问数据库:
mysql -h135.10.51.8 -ushsnc -p
经测试,通过VIP地址能正常访问到mysql数据库。
应用修改数据库配置文件,并启动应用及测试:
将配置文件中数据库连接中配置IP 改为 HA方案的虚拟VIP: 135.10.51.8
经测试,启动应用后,测试业务数据正常,且能正常写入和读取数据,MySQL高可用配置ok。
更多精彩干货分享
点击下方名片关注
IT那活儿
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129882.html
摘要:与大数据体系交互上报运行统计数据自带了运行结果的统计数据,我们希望把这些统计数据上报到元数据系统,作为的过程元数据存储下来。基于我们的开发策略,不要把有赞元数据系统的嵌入源码,而是在之外获取,截取出打印的统计信息再上报。一、需求 有赞大数据技术应用的早期,我们使用 Sqoop 作为数据同步工具,满足了 MySQL 与 Hive 之间数据同步的日常开发需求。 随着公司业务发展,数据同步的场景越...
摘要:访问令牌为提升系统安全性,配置中心和客户端进行安全性校验,双方匹配才允许通讯启动时,优先全量加载镜像数据到层,避免逐个请求耗时简介是一个轻量级分布式配置管理平台,拥有轻量级秒级动态推送多环境多语言配置监听权限控制版本回滚等特性。 Release Notes 1、轻量级改造:废弃ZK,改为 DB + 磁盘 + long polling 方案,部署更轻量,学习更简单;集群部署更方便,与单...
摘要:但你是否知道分库分表需要哪些要素拆分过程是复杂的,提前计划,不要等真正开工,各种意外的工作接踵而至,以至失控。在实施分库分表策略时,这些个性会造成策略过大不好维护。 更多文章关注微信公众号《小姐姐味道》 https://mp.weixin.qq.com/s?__... 数据库中间件之分库分表 恭喜你,贵公司终于成长到一定规模,需要考虑高可用,甚至分库分表了。但你是否知道分库分表需要哪...
摘要:大家好,我是系统工程师王煜,今天由来分享在云计算平台上构建稳定可靠的分布式系统架构。接下来我来给大家介绍如果利用云计算的优势,结合企业的业务特点构建稳定可靠的分布式系统。 本次分享 William 将从技术角度分析在云计算环境中,当用户业务面对流量激增、数据量翻番、访问量指数级攀升的烦恼时,如何利用云计算平台的弹性,结合业务自身特点,设计和构建一个高可用、高伸缩性的后端系统架构。同时会...
阅读 1249·2023-01-11 13:20
阅读 1557·2023-01-11 13:20
阅读 1011·2023-01-11 13:20
阅读 1680·2023-01-11 13:20
阅读 3971·2023-01-11 13:20
阅读 2517·2023-01-11 13:20
阅读 1310·2023-01-11 13:20
阅读 3481·2023-01-11 13:20