mysql主主+keepalived高可用
发表于:2024-10-23 作者:千家信息网编辑
千家信息网最后更新 2024年10月23日,mysql主主+keepalived高可用1.两台主机host:db1 外网:11.0.0.51 内网:172.16.1.51db2 外网:11.0.0.52 内网:172.16.1.522.
千家信息网最后更新 2024年10月23日mysql主主+keepalived高可用
mysql主主+keepalived高可用
1.两台主机
host:db1 外网:11.0.0.51 内网:172.16.1.51db2 外网:11.0.0.52 内网:172.16.1.52
2.两台主机分别下载mysql
cd /usr/chris/srcwget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.taruseradd mysql -u 550 -s /sbin/nologin -Mtar xf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gzmv mysql-5.7.24-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.24
3.db1数据库操作
ln -s /usr/local/mysql-5.7.24 /usr/local/mysqlvim /etc/my.cnf #begin[mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONdatadir=/data/mysql/databasedir=/usr/local/mysqlsocket=/tmp/mysql.sockuser=mysqlsymbolic-links=0#slow logslow-query-log=ONslow_query_log_file=/data/mysql/data/log/mysql_slow_query.loglong_query_time=3log_queries_not_using_indexes = OFFlog-slave-updates=truegtid-mode=onenforce-gtid-consistency=trueft_min_word_len = 2net_buffer_length = 8Kmax_allowed_packet = 64Mwait_timeout = 180000interactive_timeout=180000thread_cache_size = 128thread_stack = 512Ktable_open_cache = 512join_buffer_size = 16Msort_buffer_size = 16Mlog-error = /data/mysql/log/error.loglog_slave_updates = 1#character_name:valuecharacter-set-server=utf8 collation-server=utf8_general_cilog_bin = OFFlog-bin = mysql-bin#relay-log = mysql-binserver-id = 20#auto_increment_offset = 2#auto_increment_increment = 2max_connections = 2000group_concat_max_len = 2000max_allowed_packet = 1024Minnodb_buffer_pool_size = 512Mread-only=0binlog-ignore-db=mysqlbinlog-ignore-db=information_schemabinlog-ignore-db=performance_schemabinlog-ignore-db=amh[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid###end
4.创建数据目录,修改启动
mkdir /data/mysql/{data,log} -pcp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld#启动脚本修改vim /etc/init.d/mysqldbasedir=/usr/local/mysqldatadir=/data/mysql/data#环境变量修改(加入mysql路径)vim ~/.bash_profileMYSQL_HOME=/usr/local/mysqlexport PATH=$JAVA_HOME/bin:$PATH:$MYSQL_HOME/binsource ~/.bash_profile#初始化启动chown -R mysql:mysql /data/mysql/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data#如果看见错误提示error while loading shared libraries: libaio.so.1,代表缺少了库文件,安装一下就可以了yum install libaio-devel.x86_64 -y/etc/init.d/mysqld startmysql -uroot -p输入初始密码:mysql> set password for root@localhost = PASSWORD('123456');mysql> flush privileges;###这里db1数据库就配置完成了。
5.db2配置大致相同,就是配置文件需要修改
vim /etc/my.cnf[mysqld]sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONdatadir=/data/mysql/databasedir=/usr/local/mysqlsocket=/tmp/mysql.sockuser=mysqlsymbolic-links=0#slow logslow-query-log=ONslow_query_log_file=/data/mysql/data/log/mysql_slow_query.loglong_query_time=3log_queries_not_using_indexes = OFFlog-slave-updates = trueft_min_word_len = 2net_buffer_length = 8Kmax_allowed_packet = 64Mwait_timeout = 180000interactive_timeout=180000thread_cache_size = 128thread_stack = 512Ktable_open_cache = 512join_buffer_size = 16Msort_buffer_size = 16Mlog-error = /data/mysql/log/error.loglog_slave_updates = 1#character_name:valuecharacter-set-server=utf8 collation-server=utf8_general_cilog_bin = OFFlog-bin = mysql-bin#relay-log = mysql-binserver-id = 21#auto_increment_offset = 2#auto_increment_increment = 2max_connections = 2000group_concat_max_len = 2000max_allowed_packet = 1024Minnodb_buffer_pool_size = 512Mgtid-mode=onenforce-gtid-consistency=trueread-only=0binlog-ignore-db=mysqlbinlog-ignore-db=information_schemabinlog-ignore-db=performance_schemabinlog-ignore-db=amh[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid#启动修改密码:/etc/init.d/mysqld startmysql -uroot -ppassword:mysql> set password for root@localhost = PASSWORD('123456');mysql> flush privileges;
6.数据库主主设置(互为主从)
--------------------------db1操作:--------------------------mysql -uroot -p123456mysql> grant replication slave on *.* to 'rep'@'172.16.1.52' identified by '123456';mysql> flush privileges;mysql> show master status\G*************************** 1. row *************************** File: mysql-bin.000015 Position: 234 Binlog_Do_DB: Binlog_Ignore_DB: mysql,information_schema,performance_schema,amhExecuted_Gtid_Set: 79a90ed3-fa86-11e7-ac66-000c29542a93:1-3,9238586c-fa86-11e7-acab-000c29f6ed58:1-51 row in set (0.00 sec)###【这一步需要自己查看主库最新mysql-bin和pos,下面有查看】mysql> change master to > master_host='172.16.1.52', > master_user='rep', > master_password='123456', > master_log_file='mysql-bin.000024', #这个地方是主库的最新mysql-bin > master_log_pos='234'; #主库的最新posmysql> start slave;mysql> show slave status\G###生产环境如果要修改数据库架构不建议这么做,适合新部署,开启gtid功能###mysql> stop slave;###mysql> change master to MASTER_AUTO_POSITION=1;--------------------------db2操作:----------------------------mysql> grant replication slave on *.* to 'rep'@'172.16.1.51' identified by '123456';mysql> flush privileges;mysql> show master status\G*************************** 1. row *************************** File: mysql-bin.000024 Position: 234 Binlog_Do_DB: Binlog_Ignore_DB: mysql,information_schema,performance_schema,amhExecuted_Gtid_Set: 79a90ed3-fa86-11e7-ac66-000c29542a93:1-3,9238586c-fa86-11e7-acab-000c29f6ed58:1-51 row in set (0.00 sec)###【这一步需要自己查看主库最新mysql-bin和pos,上面有查看】mysql> change master to > master_host='172.16.1.51', > master_user='rep', > master_password='123456', > master_log_file='mysql-bin.000015', > master_log_pos='234';mysql> start slave;mysql> show slave status\G###生产环境不建议这么做,开启gtid功能###mysql> stop slave;###mysql> change master to MASTER_AUTO_POSITION=1;
7.keepalived安装配置
###db1和db2分别安装yum install keepalived -y###db1的keepalived.conf配置mkdir /etc/keepalived/scripts -pvim /etc/keepalived/keepalived.confglobal_defs {router_id db}vrrp_instance VI_1 { state MASTER interface eth2 virtual_router_id 61 priority 150 advert_int 1 nopreempt authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 172.16.1.3 }}virtual_server 172.16.1.3 3306 { delay_loop 2 lb_algo wrr lb_kind DR nat_mask 255.255.255.0 persistence_timeout 50 protocol TCP real_server 172.16.1.51 3306 { weight 3 notify_down /etc/keepalived/scripts/mysql_check.sh TCP_CHECK { connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } }}###此处还要配置一个触发脚本,用来检测数据库是否存活(我的很简单粗暴,你们自己可以写连接数据库判断)vim /etc/keepalived/scripts/mysql_check.sh#!/bin/bashDb1_Num=`netstat -luntp|grep 3306|wc -l`if [ $Db1_Num -eq 0 ];then /etc/init.d/keepalived stop echo "Mysql vip is moved."else echo "Mysql is running." exit 1fi###脚本需要加执行权限chmod +x /etc/keepalived/scripts/mysql_check.sh###db2的keepalived.conf配置 global_defs { router_id db}vrrp_instance VI_1 { state BACKUP interface eth2 virtual_router_id 61 priority 100 advert_int 1 nopreempt authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 172.16.1.3 }}virtual_server 172.16.1.3 3306 { delay_loop 2 lb_algo wrr lb_kind DR nat_mask 255.255.255.0 persistence_timeout 50 protocol TCP real_server 172.16.1.52 3306 { weight 3 notify_down /etc/keepalived/scripts/mysql_check.sh TCP_CHECK { connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } }}###触发脚本同上(在环境配置一样的情况下,类似端口都是3306,自己注意,还有脚本的执行权限)
8.老衲不测试了
###自己停掉数据库查看vip是否飘移
数据
配置
数据库
脚本
环境
主机
功能
密码
建议
权限
生产
相同
粗暴
主从
代表
变量
地方
就是
库文件
情况
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
网络安全普法课
网络公司数据库谁管理
河北软件开发涉密信息系统集成
魔兽世界正式服安苏服务器怎么样
我的世界创造建筑服务器手机版
浪潮服务器管理卡做系统
笔记本电脑显示服务器正在运行
学前班网络技术有限公司
抓网络安全
sql数据库类型数值型
网页创建数据库语句
小米手机恢复服务器错误怎么办啊
网络安全密码
软件开发行业的工作环境
股市服务器龙头股
四川富利隆互联网科技
网络安全手抄报有颜色
有关网络安全的词汇
网络安全事件责任追究的方式包括
卧室设计图软件开发
茂名软件开发公司建设方案
日常网络安全提醒
流量统计 数据库设计
网址数据库
G60网络安全
服务器内存容量不够
电子科技大学网络技术课本
软件开发后端java工程师
广安软件开发大概费用
网络安全防控通报