MySQL 双主 + keepalived 实现 HA

lyd2004888

　　2个节点 (互为主从)
　　1、MySQLSrv1  192.168.1.41  (MySQL主从节点)
　　2、MySQLSrv2  192.168.1.42  (MySQL主从节点)
　　VIP: 192.168.1.47
　　软件版本说明：
　　1、操作系统  CentOS6.6_x86_64
　　2、MySQLServer  5.6.33
　　一、部署MySQL主从复制
　　1、用户及相关目录创建
　　--MySQLSrv1 节点
　　[root@mysqlsrv1 ~]# groupadd mysql
　　[root@mysqlsrv1 ~]# useradd mysql -g mysql
　　[root@mysqlsrv1 ~]# mkdir -p /opt/mysql
　　[root@mysqlsrv1 ~]# mkdir /mytmp
　　[root@mysqlsrv1 ~]# passwd mysql
　　--MySQLSrv2 节点
　　[root@mysqlsrv2 ~]# groupadd mysql
　　[root@mysqlsrv2 ~]# useradd mysql -g mysql
　　[root@mysqlsrv2 ~]# mkdir -p /opt/mysql
　　[root@mysqlsrv2 ~]# mkdir /mytmp
　　[root@mysqlsrv2 ~]# passwd mysql
　　安装依赖程序包 libaio
　　[root@mysqlsrv1 mytmp]# yum install libaio*
　　[root@mysqlsrv2 mytmp]# yum install libaio*
　　2、设置用户操作系统资源的限制
　　--以避免在启动mysql服务是会报：
　　2017-04-11 17:27:03 3175 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
　　2017-04-11 17:27:03 3175 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)
　　之类的警告信息
　　[root@mysqlsrv1 ~]# vi /etc/security/limits.conf
　　在文件的最后添加如下内容
　　mysql soft nproc 2047
　　mysql hard nproc 16384
　　mysql soft nofile 1024
　　mysql hard nofile 65535
　　3、解压上传的二进制安装包程序并复制到目标目录下
　　--MySQLSrv1 节点
　　[root@mysqlsrv1 mytmp]# tar -xzvf mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz
　　[root@mysqlsrv1 mytmp]# mv mysql-5.6.33-linux-glibc2.5-x86_64 /opt/mysql/3306
　　--MySQLSrv2 节点
　　[root@mysqlsrv2 mytmp]# tar -xzvf mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz
　　[root@mysqlsrv2 mytmp]# mv mysql-5.6.33-linux-glibc2.5-x86_64 /opt/mysql/3306
　　4、便于方便管理创建单独的日志文件存放目录
　　--MySQLSrv1 节点
　　[root@mysqlsrv1 3306]# mkdir logs
　　--MySQLSrv2 节点
　　[root@mysqlsrv2 3306]# mkdir logs
　　5、数据库初始化安装
　　--MySQLSrv1 节点
　　[root@mysqlsrv1 ~]# cd /opt/mysql/3306
　　[root@mysqlsrv1 3306]# scripts/mysql_install_db --user=mysql --basedir=/opt/mysql/3306 --datadir=/opt/mysql/3306/data
　　--MySQLSrv2 节点
　　[root@mysqlsrv2 ~]$ cd /opt/mysql/3306
　　[root@mysqlsrv2 3306]$ scripts/mysql_install_db --user=mysql --basedir=/opt/mysql/3306 --datadir=/opt/mysql/3306/data
　　6、目录权限修改
　　--MySQLSrv1 节点
　　[root@mysqlsrv1 ~]# chown -R mysql:mysql /opt/mysql
　　--MySQLSrv2 节点
　　[root@mysqlsrv2 ~]# chown -R mysql:mysql /opt/mysql
　　注：1、为了使用方便创建mysql用户环境变量，如下所示：
　　[mysql@mysqlsrv1 ~]$ vi ./.bash_profile
　　添加如下内容：
　　MYSQL3306_HOME=/opt/mysql/3306
　　PATH=$PATH:$MYSQL3306_HOME/bin
　　export MYSQL3306_HOME PATH
　　[mysql@mysqlsrv2 ~]$ vi ./.bash_profile
　　添加如下内容：
　　MYSQL3306_HOME=/opt/mysql/3306
　　PATH=$PATH:$MYSQL3306_HOME/bin
　　export MYSQL3306_HOME PATH
　　7、参数文件配置
　　--MySQLSrv1 节点
　　[mysql@mysqlsrv1 ~]$ vi /opt/mysql/3306/my.cnf
　　[client]
　　default_character_set = utf8
　　port = 3306
　　socket = /tmp/mysql3306.sock
　　[mysqld]
　　basedir = /opt/mysql/3306
　　datadir = /opt/mysql/3306/data
　　user = mysql
　　port = 3306
　　server_id = 13306
　　character_set_server = utf8
　　socket = /tmp/mysql3306.sock
　　pid-file = /tmp/mysql3306.pid
　　log-bin = /opt/mysql/3306/logs/bin_log
　　relay-log = /opt/mysql/3306/logs/relay_log
　　log-error = /opt/mysql/3306/logs/mysql_error.log
　　explicit_defaults_for_timestamp = true
　　expire_logs_days = 10
　　max_binlog_size = 100M
　　binlog-do-db = testdb   #复制数据库名称
　　binlog-ignore-db = mysql
　　slave-skip-errors = all
　　auto-increment-increment = 2
　　auto-increment-offset = 1
　　--MySQLSrv2 节点
　　[mysql@mysqlsrv2 ~]$ vi /opt/mysql/3306/my.cnf
　　[client]
　　default_character_set = utf8
　　port = 3306
　　socket = /tmp/mysql3306.sock
　　[mysqld]
　　basedir = /opt/mysql/3306
　　datadir = /opt/mysql/3306/data
　　user = mysql
　　port = 3306
　　server_id = 23306
　　character_set_server = utf8
　　socket = /tmp/mysql3306.sock
　　pid-file = /tmp/mysql3306.pid
　　log-bin = /opt/mysql/3306/logs/bin_log
　　relay-log = /opt/mysql/3306/logs/relay_log
　　log-error = /opt/mysql/3306/logs/mysql_error.log
　　explicit_defaults_for_timestamp = true
　　expire_logs_days = 10
　　max_binlog_size = 100M
　　binlog-do-db = testdb   #复制数据库名称
　　binlog-ignore-db = mysql
　　slave-skip-errors = all
　　auto-increment-increment = 2
　　auto-increment-offset = 2
　　8、清理配置参数文件
　　在Linux平台下，mysql程序默认会按照以下顺序扫描路径寻找配置文件
　　/etc/my.cnf
　　/etc/mysql/my.cnf
　　SYSCONFDIR/my.cnf   #通过CMake源代码编译时指定的SYSCONFDIR的参数指定的路径
　　$MYSQL3306_HOME/my.cnf  #MYSQL_HOME环境变量所在路径，即mysql安装路径(basedir)
　　~/.my.cnf  #~表示到当更前用户根目录下寻找
　　通过命令(root用户只需查询)  find  /  -iname  my.cnf  查询配置文件的存在性，仅保留 MYSQL3306_HOME/my.cnf 配置文件。
　　注：使用如下命令可查看mysql实例的配置文件搜索顺序
　　[root@mysqlsrv1 bin]# mysql --help | grep '/my.cnf'
　　9、配置启动脚本
　　--MySQLSrv1 节点实例
　　[root@mysqlsrv1 3306]# cp support-files/mysql.server /etc/init.d/mysql3306
　　[root@mysqlsrv1 3306]# vi /etc/init.d/mysql3306
　　将如下内容
　　#basedir=...
　　#datadir=...
　　修改为：
　　basedir=/opt/mysql/3306
　　datadir=/opt/mysql/3306/data
　　--MySQLSrv2 节点实例
　　[root@mysqlsrv2 3306]# cp support-files/mysql.server /etc/init.d/mysql3306
　　[root@mysqlsrv2 3306]# vi /etc/init.d/mysql3306
　　将如下内容
　　#basedir=...
　　#datadir=...
　　修改为：
　　basedir=/opt/mysql/3306
　　datadir=/opt/mysql/3306/data
　　10、数据库服务启动
　　--MySQLSrv1 节点
　　[mysql@mysqlsrv1 ~]$ service  mysql3306  start
　　--MySQLSrv2 节点
　　[mysql@mysqlsrv2 ~]$ service  mysql3306  start
　　注：启动若报如下错误
　　Starting MySQL.2017-04-13T07:19:57.334037Z mysqld_safe The file /usr/local/mysql/bin/mysqld
　　does not exist or is not executable. Please cd to the mysql installation
　　directory and restart this script from there as follows:
　　./bin/mysqld_safe&
　　See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information
　　ERROR! The server quit without updating PID file (/tmp/mysql3306.pid).
　　解决方法：
　　[root@mysqlsrv1 ~]# mkdir -p /usr/local/mysql/bin
　　[root@mysqlsrv1 ~]# ln -s /opt/mysql/3306/bin/mysqld /usr/local/mysql/bin/mysqld
　　[root@mysqlsrv2 ~]# mkdir -p /usr/local/mysql/bin
　　[root@mysqlsrv2 ~]# ln -s /opt/mysql/3306/bin/mysqld /usr/local/mysql/bin/mysqld
　　11、登录数据库以添加远程登录帐号信息
　　[mysql@mysqlsrv1 ~]$ mysql -u root -p  -P 3306 --socket=/tmp/mysql3306.sock
　　[mysql@mysqlsrv2 ~]$ mysql -u root -p  -P 3306 --socket=/tmp/mysql3306.sock
　　注：使用mysql用户执行上述命令前，先配置相关环境变量值
　　/home/mysql/.bash_profile
　　添加如下内容：
　　MYSQL3306_HOME=/opt/mysql/3306
　　PATH=$PATH:$MYSQL3306_HOME/bin
　　export MYSQL3306_HOME PATH
　　注：上述登录数据库是需有参数  --socket ,否则会报出如下错误:
　　Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
　　--为了安全起见，先删除默认生成的用户相关信息在创建一个指定名称的数据库管理员帐号
　　mysql> use mysql;
　　mysql> delete from db;
　　mysql> delete from user;

　　mysql> grant all privileges on *.* to 'sysadmin'@'localhost'>
　　mysql> grant all privileges on *.* to 'sysadmin'@'127.0.0.1'>　　mysql> flush privileges;
　　mysql> select host,user from user where user='sysadmin';
　　12、在两个节点创建复制专用帐号

　　mysql> grant replication slave on *.* to 'repl'@'%'>　　Query OK, 0 rows affected (0.01 sec)
　　mysql> flush privileges;
　　Query OK, 0 rows affected (0.00 sec)
　　13、查看 master 主机信息
　　[mysql@mysqlsrv1 ~]$ mysql -u sysadmin -p -P 3306 -S /tmp/mysql3306.sock
　　mysql> show master status \G
　　*************************** 1. row ***************************
　　File: bin_log.000002
　　Position: 120
　　Binlog_Do_DB: testdb
　　Binlog_Ignore_DB: mysql
　　Executed_Gtid_Set:
　　1 row in set (0.00 sec)
　　mysql>
　　[mysql@mysqlsrv2 ~]$ mysql -u sysadmin -p -P 3306 -S /tmp/mysql3306.sock
　　mysql> show master status \G
　　*************************** 1. row ***************************
　　File: bin_log.000002
　　Position: 120
　　Binlog_Do_DB: testdb
　　Binlog_Ignore_DB: mysql
　　Executed_Gtid_Set:
　　1 row in set (0.00 sec)
　　mysql>
　　14、配置两个节点之间互为主从角色，并启动复制
　　--MySQLSrv1 节点
　　[mysql@mysqlsrv1 ~]$ mysql -u sysadmin -p -P 3306 -S /tmp/mysql3306.sock
　　mysql> show slave status \G
　　Empty set (0.00 sec)
　　mysql> change master to
　　-> master_host='192.168.1.42',
　　-> master_port=3306,
　　-> master_user='repl',
　　-> master_password='repl',
　　-> master_log_file='bin_log.000002',
　　-> master_log_pos=120;
　　Query OK, 0 rows affected, 2 warnings (0.06 sec)
　　mysql> start slave;
　　Query OK, 0 rows affected (0.17 sec)
　　mysql>
　　--MySQLSrv2 节点
　　[mysql@mysqlsrv2 ~]$ mysql -u sysadmin -p -P 3306 -S /tmp/mysql3306.sock
　　mysql> show slave status \G
　　Empty set (0.00 sec)
　　mysql> change master to
　　-> master_host='192.168.1.41',
　　-> master_port=3306,
　　-> master_user='repl',
　　-> master_password='repl',
　　-> master_log_file='bin_log.000002',
　　-> master_log_pos=120;
　　Query OK, 0 rows affected, 2 warnings (0.06 sec)
　　mysql> start slave;
　　Query OK, 0 rows affected (0.17 sec)
　　mysql>
　　15、复制功能测试
　　--MySQLSrv1 节点
　　mysql> create database testdb;
　　mysql> use testdb;
　　Database changed
　　mysql> select database();
　　+------------+
　　| database() |
　　+------------+
　　| testdb |
　　+------------+
　　1 row in set (0.00 sec)
　　--MySQLSrv2 节点
　　mysql> use testdb;
　　mysql> create table t1(id int, name varchar(20));
　　Query OK, 0 rows affected (1.71 sec)
　　mysql> select * from t1;
　　Empty set (0.00 sec)
　　--MySQLSrv1 节点
　　mysql> insert into t1 (id,name) values(1,'重庆');
　　Query OK, 1 row affected (0.00 sec)
　　mysql> select * from t1;
　　+------+----------+

　　|>　　+------+----------+
　　| 1 | 重庆 |
　　--MySQLSrv2 节点
　　mysql> select * from t1;
　　Empty set (0.01 sec)
　　mysql> select * from t1;
　　+------+----------+

　　|>　　+------+----------+
　　| 1 | 重庆 |
　　+------+----------+
　　1 row in set (0.00 sec)
　　mysql>
　　-------------- 两个节点之间的互为主从的复制功能正常 ---------------------------
　　二、部署keepalived服务
　　1、安装程序文件
　　[root@mysqlsrv1 ~]#  yum install keepalived
　　[root@mysqlsrv2 ~]#  yum install keepalived
　　注：
　　配置文件  /etc/keepalived/keepalived.conf
　　日志文件  /var/log/messages
　　2、修改配置文件内容
　　--MySQLSrv1 节点
　　global_defs {
　　notification_email {
　　yangchaoaccp@163.com
　　}
　　notification_email_from  admin@163.com
　　smtp_server 127.0.0.1
　　smtp_connect_timeout 30
　　router_id MYSQL_HA      #标识，双主相同
　　}
　　vrrp_instance VI_1 {
　　state BACKUP          #两台都设置BACKUP
　　interface eth0
　　virtual_router_id 51      #主备相同
　　priority 100          #优先级，backup设置90
　　advert_int 1
　　nopreempt            #不主动抢占资源，只在master这台优先级高的设置，backup不设置
　　authentication {
　　auth_type PASS
　　auth_pass 1111
　　}
　　virtual_ipaddress {
　　192.168.1.47
　　}
　　}
　　virtual_server 192.168.1.41 3306 {
　　delay_loop 2
　　#lb_algo rr              #LVS算法，用不到，我们就关闭了
　　#lb_kind DR              #LVS模式，如果不关闭，备用服务器不能通过VIP连接主MySQL
　　persistence_timeout 50  #同一IP的连接60秒内被分配到同一台真实服务器
　　protocol TCP
　　real_server 192.168.1.41 3306 {  #检测本地mysql，backup也要写检测本地mysql
　　weight 3
　　notify_down /usr/local/keepalived/mysql_check.sh    #当mysq服down时，执行此脚本，杀死keepalived实现切换
　　TCP_CHECK {
　　connect_timeout 3    #连接超时
　　nb_get_retry 3      #重试次数
　　delay_before_retry 3 #重试间隔时间
　　}
　　}
　　--MySQLSrv2 节点
　　global_defs {
　　notification_email {
　　yangchaoaccp@163.com
　　}
　　notification_email_from  admin@163.com
　　smtp_server 127.0.0.1
　　smtp_connect_timeout 30
　　router_id MYSQL_HA      #标识，双主相同
　　}
　　vrrp_instance VI_1 {
　　state BACKUP          #两台都设置BACKUP
　　interface eth0
　　virtual_router_id 51      #主备相同
　　priority 90          #优先级，backup设置90
　　advert_int 1
　　#nopreempt            #不主动抢占资源，只在master这台优先级高的设置，backup不设置
　　authentication {
　　auth_type PASS
　　auth_pass 1111
　　}
　　virtual_ipaddress {
　　192.168.1.47
　　}
　　}
　　virtual_server 192.168.1.42 3306 {
　　delay_loop 2
　　#lb_algo rr              #LVS算法，用不到，我们就关闭了
　　#lb_kind DR              #LVS模式，如果不关闭，备用服务器不能通过VIP连接主MySQL
　　persistence_timeout 50  #同一IP的连接60秒内被分配到同一台真实服务器
　　protocol TCP
　　real_server 192.168.1.42 3306 {  #检测本地mysql，backup也要写检测本地mysql
　　weight 3
　　notify_down /usr/local/keepalived/mysql_check.sh    #当mysq服down时，执行此脚本，杀死keepalived实现切换
　　TCP_CHECK {
　　connect_timeout 3    #连接超时
　　nb_get_retry 3      #重试次数
　　delay_before_retry 3 #重试间隔时间
　　}
　　}
　　--mysql_check.sh 文件(两个节点相同)
　　#!/bin/bash
　　pkill keepalived
　　3、创建测试用户：
　　MySQLSrv1 节点
　　mysql  -u  sysadmin  -p  -P 3306  -S  /tmp/mysql3306.sock

　　mysql> grant all privileges on *.* to 'yangchao'@'%'>　　mysql> flush privileges;
　　4、keepalived 测试
　　启动 MySQLSrv1 节点 keepalived 服务
　　[root@mysqlsrv1 ~]# service  keepalived  start
　　启动 MySQLSrv2 节点 keepalived 服务
　　[root@mysqlsrv2 ~]# service  keepalived  start
　　在 MySQLSrv1 节点查看VIP

　　在 MySQLSrv2 节点查看VIP

　　由上测试可知, keepalived 成功在节点 MySQLSrv1 上绑定VIP 192.168.1.47
　　在 MySQLSrv1 节点重新启动 keepalived 服务：

　　由上测试可知，keepalived 服务绑定的IP地址成功漂移到 MySQLSrv2 节点。
　　5、MySQL 服务测试
　　--在 MySQLSrv1 节点登录

　　由上测试可知，虽然在 MySQLSrv1 节点登录，但登录到的节点是VIP实际绑定的 MySQLSrv2 节点。
　　插入数据：

　　在 MySQLSrv2 节点上停止 mysql 服务

　　停止mysql服务后，自动停止了所在节点的keepalived服务。
　　在 MySQLSrv1 节点查看

　　keepalived 服务成功漂移到 MySQLSrv1 节点。