MySQL主主复制+Keepalived 打造高可用MySQL集群 柔光的暖阳◎ 2022-08-08 17:38 140阅读 0赞 转载地址：http://www.linuxidc.com/Linux/2014-09/106570.htm 为了响应公司需求，打造出更安全的mysql集群，能够实现mysql故障后切换，研究了几天终于有了成果，一起分享一下。 首先介绍一下这套集群方案实现的功能 1、mysql服务器故障后自动转移，修好后自动切回 2、mysql服务故障自动转移，修好后自动切回 3、可以实现在几秒钟内转移 以下内容均是实验环境，请根据实际情况修改响应参数 生产环境MySQL主主同步主键冲突处理 [http://www.linuxidc.com/Linux/2013-07/86890.htm][http_www.linuxidc.com_Linux_2013-07_86890.htm] MySQL + KeepAlived + LVS 单点写入主主同步高可用架构实验 [http://www.linuxidc.com/Linux/2013-05/84002.htm][http_www.linuxidc.com_Linux_2013-05_84002.htm] MySQL 主主同步配置 [http://www.linuxidc.com/Linux/2013-05/83815.htm][http_www.linuxidc.com_Linux_2013-05_83815.htm] [CentOS][] 6.3下MySQL主从复制笔记 [http://www.linuxidc.com/Linux/2013-06/85983.htm][http_www.linuxidc.com_Linux_2013-06_85983.htm] Linux下的MySQL主主复制 [http://www.linuxidc.com/Linux/2013-10/91683.htm][http_www.linuxidc.com_Linux_2013-10_91683.htm] **实验环境**： mysql1 ip：10.1.1.20 mysql2 ip：10.1.1.21 mysql vip：10.1.1.25 三台机器均安装centos 6.5 32位（虚拟机环境） 实验开始！！！ 一、安装mysql，并打造主主同步。 相信主从同步大家都会做，一样的道理，主主同步就是两台机器互为主的关系，在任何一台机器上写入都会同步。 安装mysql的过程不解释，yum就好啦 配置主主同步 1.配置 /etc/my.cnf \[mysqld\] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql \# Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-bin=binlog \#开启binlog功能 log-bin-index=binlog.index sync\_binlog=0 server\_id = 1 \#两台机器不能重复，一个1 一个2 就好 \[mysqld\_safe\] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid 2.分别在两台机器上配置同步账号 10.1.1.20机器上： \[root@localhost ~\]\# mysql Welcome to the MySQL monitor. Commands end with; or \\g. Your MySQL connection id is 2 Server version: 5.0.77-log Sourcedistribution Type 'help;' or '\\h' for help. Type '\\c' toclear the buffer. mysql> GRANT replication slave ON \*.\* TO'ab'@'%' identified by '123'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 10.1.1.21机器上： \[root@localhost ~\]\# mysql Welcome to the MySQL monitor. Commands end with; or \\g. Your MySQL connection id is 2 Server version: 5.0.77-log Sourcedistribution Type 'help;' or '\\h' for help. Type '\\c' toclear the buffer. mysql> GRANT replication slave ON \*.\* TO'ab'@'%' identified by '123'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 注:由于本文是实验环境下编写，所以没考虑任何安全性问题，同步账号也是最高权限，请根据实际情况设置响应权限！！ 3.设置同步 10.1.1.20机器上: mysql> flush tables with read lock; mysql> show master status; \+---------------+----------+--------------+------------------+ | File | Position | Binlog\_Do\_DB |Binlog\_Ignore\_DB | \+---------------+----------+--------------+------------------+ | binlog.000003 | 365 | | | \+---------------+----------+--------------+------------------+ 1 row in set (0.03 sec) mysql> unlock tables; Query OK, 0 rows affected (0.03 sec) 10.1.1.21机器上： mysql> change master tomaster\_host='10.1.1.20', master\_port=3306, master\_user='ab',master\_password='123', master\_log\_file='binlog.000003',master\_log\_pos=365; Query OK, 0 rows affected (0.06 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \\G \#执行这命令后 注意观察下面这两个参数，必须要都是yes才行 Slave\_IO\_Running: Yes Slave\_SQL\_Running: Yes 同样的 反过来做相同操作 10.1.1.21机器上: mysql> flush tables with read lock; mysql> show master status; \+---------------+----------+--------------+------------------+ | File | Position | Binlog\_Do\_DB |Binlog\_Ignore\_DB | \+---------------+----------+--------------+------------------+ | binlog.000004 | 207 | | | \+---------------+----------+--------------+------------------+ 1 row in set (0.03 sec) mysql> unlock tables; Query OK, 0 rows affected (0.03 sec) 10.1.1.20机器上： mysql> change master tomaster\_host='10.1.1.21', master\_port=3306, master\_user='ab',master\_password='123', master\_log\_file='binlog.000004',master\_log\_pos=207; Query OK, 0 rows affected (0.06 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \\G \#执行这命令后 注意观察下面这两个参数，必须要都是yes才行 Slave\_IO\_Running: Yes Slave\_SQL\_Running: Yes 介此，主主同步打造完成，可以简单测试一下，分别在两个机器上写数据 看看会不会同步到另一台机器上 PS：如果报错 Slave\_IO\_Running: NO 可以检查同步的账号是否创建正常！ 二、安装keepalived 并设置监控 keepalived是安装在两台MySQL服务器上的 首先安装keepalived 过程不解释就正常解压安装就好 安装后配置 vim /etc/keepalived/keepalived.conf 内容如下 10.1.1.20的配置文件 ! Configuration File for keepalived global\_defs \{ notification\_email \{ acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc \} notification\_email\_from Alexandre.Cassen@firewall.loc smtp\_server 127.0.0.1 smtp\_connect\_timeout 30 router\_id LVS\_DEVEL \} vrrp\_instance VI\_1 \{ state backup \#两台配置此处均是BACKUP interface eth0 virtual\_router\_id 51 priority 100 \#优先级，另一台改为90 advert\_int 1 nopreempt \#不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置 authentication \{ auth\_type PASS auth\_pass 1111 \} virtual\_ipaddress \{ 10.1.1.25 \} \} virtual\_server 10.1.1.25 3306 \{ delay\_loop 6 lb\_algo wrr lb\_kind DR persistence\_timeout 50 \#会话保持时间 protocol TCP real\_server 10.1.1.20 3306 \{ weight 3 notify\_down /tmp/nimei.sh \#检测到mysql服务挂了就执行这个脚本（脚本要自己写哈） TCP\_CHECK \{ connect\_timeout 10 \#连接超时时间 nb\_get\_retry 3 \#重连次数 delay\_before\_retry 3 \#重连间隔时间 connect\_port 3306 \#健康检查端口 \} \} \} 10.1.1.21 的配置文件 ! Configuration File for keepalived global\_defs \{ notification\_email \{ acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc \} notification\_email\_from Alexandre.Cassen@firewall.loc smtp\_server 127.0.0.1 smtp\_connect\_timeout 30 router\_id LVS\_DEVEL \} vrrp\_instance VI\_1 \{ state backup interface eth0 virtual\_router\_id 51 priority 90 advert\_int 1 authentication \{ auth\_type PASS auth\_pass 1111 \} virtual\_ipaddress \{ 10.1.1.25 \} \} virtual\_server 10.1.1.25 3306 \{ delay\_loop 6 lb\_algo wrr lb\_kind DR persistence\_timeout 50 protocol TCP real\_server 10.1.1.21 3306 \{ weight 3 notify\_down /tmp/nimei.sh TCP\_CHECK \{ connect\_timeout 10 nb\_get\_retry 3 delay\_before\_retry 3 connect\_port 3306 \} \} \} 编写监控mysql服务是否挂了的脚本，按照上面配置文件的位置编写脚本。 vim /tmp/nimei.sh \#!/bin/sh pkill keepalived 脚本很简单啊 就一句，目的是当keepalived检测到mysql服务挂了之后触发这个脚本，杀死keepalived进程，让另一台机器接管 好 修改后启动keeplived服务 介此整个集群搭建完成 三、测试 找一台机器用虚拟ip连接mysql \[root@localhost html\]\# mysql -uab -h 10.1.1.25 -p123 Welcome to the MySQL monitor. Commands end with ; or \\g. Your MySQL connection id is 736 Server version: 5.1.66-log Source distribution Copyright (c) 2000, 2012, [Oracle][] and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement. mysql> 这样成功连上了，然后你可以任意关闭某台机器，或者某台机器的mysql服务，看看还能不能连上！！ 谢谢！！ [http_www.linuxidc.com_Linux_2013-07_86890.htm]: http://www.linuxidc.com/Linux/2013-07/86890.htm [http_www.linuxidc.com_Linux_2013-05_84002.htm]: http://www.linuxidc.com/Linux/2013-05/84002.htm [http_www.linuxidc.com_Linux_2013-05_83815.htm]: http://www.linuxidc.com/Linux/2013-05/83815.htm [CentOS]: http://www.linuxidc.com/topicnews.aspx?tid=14 [http_www.linuxidc.com_Linux_2013-06_85983.htm]: http://www.linuxidc.com/Linux/2013-06/85983.htm [http_www.linuxidc.com_Linux_2013-10_91683.htm]: http://www.linuxidc.com/Linux/2013-10/91683.htm [Oracle]: http://www.linuxidc.com/topicnews.aspx?tid=12
还没有评论，来说两句吧...