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安全错误
数据库的锁怎样保障安全
软件开发识别法规清单
数据库管理系统崩溃和停电的关系
宸帆网络技术有限公司
网络安全产业高地
工业互联网推动宁夏科技抗疫
数据库在计算机系统中的地位
福州网络安全学院要怎么学
服务器安全狗+免费
视频服务器硬盘
高新区好服务器货源充足
重庆软件开发合理避税怎么做
c list删选数据库
数据库fields代表什么意思
品牌网络技术推广
查服务器的安全防护
消防和网络安全手抄报内容
龙华网站建设软件开发
三级网络技术最新题型
广州网络安全培训包就业
成都市教育系统网络安全周
大学毕业软件开发
工业互联网推动宁夏科技抗疫
女生学计算机网络技术
erp系统的支柱数据库
国企工厂做软件开发
网络安全战队比赛
国师软件开发公司
龙华网站建设软件开发
怎么找出app的后台服务器
文档如何显示数据库地址