Error message here!

Hide Error message here!

忘记密码?

Error message here!

请输入正确邮箱

Hide Error message here!

密码丢失?请输入您的电子邮件地址。您将收到一个重设密码链接。

Error message here!

返回登录

Close

004.MySQL双主+Keepalived高可用

木二 2019-01-30 14:52:00 阅读数:191 评论数:0 点赞数:0 收藏数:0

 

一 基础环境

主机名
系统版本
MySQL版本
主机IP
Master01
CentOS 6.8
MySQL 5.6
172.24.8.10
Master02
CentOS 6.8
MySQL 5.6
172.24.8.11
VIP
172.24.8.12

二 实际部署

2.1 安装MySQL

 1 [root@Master01 ~]# yum list installed | grep mysql #查看是否存在其他MySQL组件
 2 [root@Master01 ~]# yum -y remove mysql-libs.x86_64 #为避免冲突引发报错,卸载已存在的组件
 
25
 1 [root@Master01 study]# yum localinstall mysql-commu* -y
 2 [root@Master01 ~]# chkconfig --list | grep mysqld #查看MySQL是否加入启动项
 3 [root@Master01 ~]# chkconfig mysqld on
 

2.2 初始化MySQL

 1 [root@Master01 ~]# service mysqld start
 2 [root@Master01 ~]# mysql_secure_installation
 3 [root@Master01 ~]# service iptables stop
 4 [root@Master01 ~]# chkconfig iptables off
 5 [root@Master01 ~]# vi /etc/sysconfig/selinux
 6 SELINUX=disabled
 
注意:以上操作在Master02主机上也需要执行。
安装参考:http://www.cnblogs.com/007sx/p/7083143.html

2.3 master01 my.cf配置

 1 [root@Master01 ~]# vi /etc/my.cnf
 2 [mysqld]
 3 ……
 4 server-id=1 #设置主服务器master的id
 5 log-bin=mysql-bin #配置二进制变更日志命名格式
 6 replicate-wild-ignore-table=mysql.%
 7 replicate-wild-ignore-table=test.%
 8 replicate-wild-ignore-table=information_schema.%
 
注意:
master开启二进制日志后默认记录所有库所有表的操作,可以通过配置来指定只记录指定的数据库甚至指定的表的操作,具体在mysql配置文件的[mysqld]可添加修改如下选项:
方法一:
 1 # 不同步哪些数据库
 2 binlog-ignore-db = mysql
 3 binlog-ignore-db = test
 4 binlog-ignore-db = information_schema
 5 # 只同步哪些数据库,除此之外,其他不同步
 6 binlog-do-db = mysqltest
 
方法二:
建议采用以下方式配置过滤:
 1 replicate-wild-ignore-table=mysql.% #从库配置不同步表
 2 replicate-wild-do-table=test.% #从库配置需要同步的表
 

提示:不要在主库使用binlog-ignore-db和binlog-do-db,也不要在从库使用replicate-wild-ignore和replicate-wild-do-table。

2.4 创建账号

 1 [root@Master01 ~]# mysql -uroot -p
 2 Enter password:
 3 mysql> grant replication slave on *.* to 'repl_user'@'172.24.8.11' identified by 'x12345678';
 4 mysql> grant all privileges on *.* to 'root'@'172.24.8.%' identified by 'x120952576' with grant option;
 5 mysql> flush privileges;
 6 [root@Master01 ~]# service mysqld restart
 7 [root@Master01 ~]# mysql -uroot -p
 8 Enter password:
 9 mysql> show master status;
 
26
master01:
file:mysql-bin.000001
position:120

2.5 smaster02 my.cf配置配置

 1 [root@Master02 ~]# vi /etc/my.cnf
 2 [mysqld]
 3 server-id=2 #设置主服务器master的id
 4 log-bin=mysql-bin #配置二进制变更日志命名格式
 5 replicate-wild-ignore-table=mysql.%
 6 replicate-wild-ignore-table=test.%
 7 replicate-wild-ignore-table=information_schema.%
 8 read_only=1
 

2.6 创建账号

 1 [root@Master02 ~]# mysql -uroot -p
 2 Enter password:
 3 mysql> grant replication slave on *.* to 'repl_user'@'172.24.8.10' identified by 'x12345678';
 4 mysql> grant all privileges on *.* to 'root'@'172.24.8.%' identified by 'x120952576' with grant option;
 5 mysql> flush privileges;
 6 [root@Master02 ~]# service mysqld restart
 7 [root@Master02 ~]# mysql -uroot -p
 8 Enter password:
 9 mysql> show master status;
27

master02:

file:mysql-bin.000001
position:120

三 启动主从

3.1 手动同步

如果Master01和Master02已经存在数据,则在开启主备复制之前,需要将Master01和Master02手动同步一次(/var/lib/mysql整个目录打包tar.gz),具体方法略。
注意:本实验都是重新安装的MySQL,可直接启动同步。

3.2 启动Master01的slave功能

 1 [root@Master01 ~]# service mysqld restart
 2 [root@Master01 ~]# mysql -uroot -p
 3 Enter password:
 4 mysql> change master to master_host='172.24.8.11',
 5 master_user='repl_user',
 6 master_password='x12345678',
 7 master_log_file='mysql-bin.000001',
 8 master_port=3306,
 9 master_log_pos=120;
 10 mysql> start slave;
 11 mysql> show slave status\G #查看slave状态
 
28

3.3 启动Master02的slave功能

 1 [root@Master02 ~]# service mysqld restart
 2 [root@Master02 ~]# mysql -uroot -p
 3 Enter password:
 4 mysql> change master to master_host='172.24.8.10',
 5 master_user='repl_user',
 6 master_password='x12345678',
 7 master_log_file='mysql-bin.000001',
 8 master_log_pos=120;
 9 mysql> start slave;
 10 mysql> show slave status\G #查看slave状态
 
29
提示:
slave的I/O和SQL线程都已经开始运行,而且Seconds_Behind_Master不再是NULL。日志的位置增加了,意味着一些事件被获取并执行了。如果你在master上进行修改,你可以在slave上看到各种日志文件的位置的变化,同样,你也可以看到数据库中数据的变化。

四 安装Keepalived

4.1 下载

 1 [root@Master01 ~]# wget http://www.keepalived.org/software/keepalived-1.3.6.tar.gz
 2 [root@Master01 ~]# tar -zvxf keepalived-1.3.6.tar.gz -C /tmp/
 3 [root@Master01 ~]# cd /tmp/keepalived-1.3.6
 4 [root@Master01 keepalived-1.3.6]# ./configure --prefix=/usr/local/keepalived/ --sysconf=/etc --with-init=SYSV
 5 #注:(upstart|systemd|SYSV|SUSE|openrc) #根据你的系统选择对应的启动方式
 6 [root@Master01 keepalived-1.3.6]# make && make install
 7 [root@Master01 ~]# ln -s /usr/local/keepalived/sbin/keepalived /sbin
 8 [root@Master01 ~]# chmod u+x /etc/init.d/keepalived
 9 [root@Master01 ~]# chkconfig --add keepalived
 10 [root@Master01 ~]# chkconfig --level 35 keepalived on
 
注意:Master02上也需要如上操作。
若出现以下提示,需要执行:yum -y install openssl-devel。
30
提示:也可采用yum install -y keepalived安装(个人不建议)。

4.2 Master01配置Keepalived

默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件。
 1 [root@Master01 ~]# vim /etc/keepalived/keepalived.conf
 2 ! Configuration File for keepalived
 3 global_defs {
 4 notification_email {
 5 x120952576@126.com
 6 #表示keepalived在发生诸如切换操作时发送Email给哪些地址,邮件地址可以多个,每行一个。
 7 }
 8 notification_email_from Alexandre.Cassen@firewall.loc
 9 smtp_server 172.24.8.10
 10 smtp_connect_timeout 30
 11 router_id LVS_DEVEL
 12 }
 13 vrrp_instance VI_1 {
 14 state BACKUP
 15 interface eth0
 16 virtual_router_id 51
 17 #这里设置VRID,这里非常重要,相同的VRID为一个组,他将决定多播的MAC地址
 18 priority 100
 19 advert_int 1
 20 nopreempt
 21 #不抢占,只在优先级高的机器上设置即可,优先级低的机器不设置
 22 authentication {
 23 auth_type PASS
 24 auth_pass 1111
 25 }
 26 track_script {
 27 check_mysqld #执行定义的监控脚本
 28 }
 29 virtual_ipaddress {
 30 172.24.8.12
 31 }
 32 }
 33 vrrp_script check_mysqld {
 34 script"/etc/keepalived/mysqlcheck/keepalived_check_mysql.sh "
 35 interval 2
 36 }
 

4.3 创建检测脚本

 1 [root@Master01 ~]# mkdir -p /etc/keepalived/mysqlcheck/
 2 [root@Master01 ~]# vi /etc/keepalived/mysqlcheck/keepalived_check_mysql.sh
 3 #!/bin/bash
 4 MYSQL=/usr/bin/mysql
 5 MYSQL_HOST=localhost
 6 MYSQL_USER=root
 7 MYSQL_PASSWORD=x120952576
 8 CHECK_TIME=3
 9 #mysql is workingMYSQL_OK is 1 , mysql down MYSQL_OK is 0
 10 MYSQL_OK=1
 11 function check_mysql_helth (){
 12 $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1
 13 if [ $? = 0 ] ;then
 14 MYSQL_OK=1
 15 else
 16 MYSQL_OK=0
 17 fi
 18 return $MYSQL_OK
 19 }
 20 while [ $CHECK_TIME -ne 0 ]
 21 do
 22 let "CHECK_TIME -= 1"
 23 check_mysql_helth
 24 if [ $MYSQL_OK = 1 ] ; then
 25 CHECK_TIME=0
 26 exit 0
 27 fi
 28 if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
 29 then
 30 /etc/init.d/keepalived stop
 31 exit 1
 32 fi
 33 sleep 1
 34 done
 35 [root@Master01 ~]# chmod u+x /etc/keepalived/mysqlcheck/keepalived_check_mysql.sh
 36 [root@Master01 ~]# service keepalived start
 

4.4 Master02配置Keepalived

 1 [root@Master01 ~]# scp /etc/keepalived/keepalived.conf root@127.24.8.11:/etc/keepalived/
参考Master01配置,去掉nopreempt选项,priority设置比Master01低即可。

4.5 创建检测脚本

 1 [root@Master02 ~]# mkdir -p /etc/keepalived/mysqlcheck/
 2 [root@Master01 ~]# scp /etc/keepalived/mysqlcheck/keepalived_check_mysql.sh root@172.24.8.11:/etc/keepalived/mysqlcheck/
 3 [root@Master02 ~]# chmod u+x /etc/keepalived/mysqlcheck/keepalived_check_mysql.sh
 4 [root@Master02 ~]# service keepalived start
 

五 功能测试

5.1 测试vip

 1 [root@test ~]# mysql -uroot -h172.24.8.12 -p
31
 1 mysql> show variables like "%hostname%";
 2 mysql> show variables like "%server_id%";
 
31

5.2 同步测试

 1 [root@test ~]# mysql -uroot -p
 2 Enter password:
 3 mysql> create database mysqltest;
 4 mysql> use mysqltest;
 5 mysql> create table user(id int(5),name char(10));
 6 mysql> insert into user values (00001,'zhangsan');
 7 在Slave从服务器上进行验证:
 8 [root@Master02 ~]# mysql -uroot -p
 9 Enter password:
 10 mysql> show databases;
 11 mysql> select * from mysqltest.user;
 
33

5.3 测试Keepalived切换

 1 [root@Master01 ~]# service mysqld stop #停止Master01的MySQL
 2 [root@Master01 ~]# tail -f /var/log/messages #观察Master01的日志
 
34
 1 [root@Master02 ~]# tail -f /var/log/messages #观察Master02的日志
35
 1 [root@Client ~]# mysql -uroot -h172.24.8.12 -px120952576 #客户端连接VIP
36
注意:已经成功切换,在切换过程中可能中断几秒。
 
版权声明
本文为[木二]所创,转载请带上原文链接,感谢
https://www.cnblogs.com/itzgr/p/10338088.html

编程之旅,人生之路,不止于编程,还有诗和远方。
阅代码原理,看框架知识,学企业实践;
赏诗词,读日记,踏人生之路,观世界之行;

支付宝红包,每日可领