linux MySQL 5.7+keepalived 主备服务器自主切换
一、环境准备
1、关闭防火墙与selinux
systemctl stop firewalld
setenforce 0
sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config
二、mysql5.7.20安装
#MySQL5.6与MySQL5.7的区别在于初始化的时候，其他安装步骤一致
1、创建目录
mkdir -p /data/mysql/{data,logs,tmp}
touch /data/mysql/{mysql.pid,mysql.sock}
2、创建用户
useradd mysql
3、安装依赖包
yum install perl perl-devel perl-Data-Dumper libaio-devel -y
4、下载包
cd /usr/local/src
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
tar zxf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
cd /usr/local
mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql
chown -R mysql:mysql /usr/local/mysql /data/mysql/
三、配置文件
========================================================
[client]
port = 16205
default-character-set=utf8mb4
socket = /data/mysql/mysql.sock
[mysqld]
user = mysql
port = 16205
server-id = 111
character_set_server=utf8mb4
socket = /data/mysql/mysql.sock
datadir = /data/mysql/data/
pid-file = /data/mysql/mysql.pid
auto-increment-increment = 2 #主主参数，主从可注释（必填）
auto-increment-offset = 1 #主主参数，主从可注释（必填）主：1；从：2
#bind-address = 10.26.25.28
#skip-name-resolve=1
#event_scheduler = on
federated
skip-ssl
secure-file-priv = /home/mysql
disable-partition-engine-check=1
explicit_defaults_for_timestamp=false
max_allowed_packet = 32M
innodb_file_per_table = 1
back_log = 300
max_connections = 8000
max_connect_errors = 1000
table_open_cache = 4096
open_files_limit = 10240
max_allowed_packet = 512M
wait_timeout = 300
interactive_timeout=300
sort_buffer_size = 16M
join_buffer_size = 16M
query_cache_size = 128M
transaction_isolation = REPEATABLE-READ
thread_stack = 512K
innodb_buffer_pool_size = 4G
innodb_data_file_path = ibdata1:512M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 16
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 120
binlog-ignore-db=information_schema,mysql,performance_schema #主主参数，主从可注释
#replicate-ignore-db=information_schema,mysql,performance_schema
binlog_format=row
expire_logs_days=10
binlog_cache_size = 7M
log-error = /data/mysql/logs/error.log
log-bin = /data/mysql/logs/mysql_bin.log
log-slave-updates=true #主主参数，主从可注释
#slow log
slow-query-log = 1
long_query_time = 2
slow-query-log-file = /data/mysql/logs/slowquery.log
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[mysqld_safe]
pid-file=/data/mysql/mysql.pid
================================================================================
四、初始化
1.MySQL5.7初始化:#注意: 要先初始化，再启动
初始化: bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql --initialize
查看随机密码: cat /data/mysql/logs/error.log | grep password（查看后保存密码）
2.启动mysql
bin/mysqld_safe --defaults-file=/etc/my.cnf &
3.修改密码: /usr/local/mysql/bin/mysqladmin -uroot -p password #输入随机密码，再输入两次新密码即可
# 初始化后数据目录包含: mysql、information_schema、sys、performache_schema四个数据库
登录数据库：
/usr/local/mysql/bin/mysql -uroot -p
退出数据库：exit;
五、主从配置
# Master
1、my.cnf
server-id=1111
log-bin=（产生binglog日志。）
2、创建用于主从同步的账号
repl（主从同步用户名）192.168.28.135（从库所在服务器的IP）
mysql> grant replication slave on *.* to 'repl'@'192.168.28.135' identified by 'a123456';（a123456表示密码）
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> flush privileges;（刷新权限）
Query OK, 0 rows affected (0.02 sec)
3、查看主库当前bin_log文件和Position
mysql> show master status;
+---------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+--------------------------+-------------------+
| binlog.000008 | 25616 | | information_schema,mysql | |
+---------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
#以下是从库操作
登录数据库
/usr/local/mysql/bin/mysql -uroot -p
4、与主库建立连接
mysql> stop slave;（停止同步）
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.28.136',master_user='repl',master_password='a123456',master_port=16205,master_log_file='mysql_bin.000007',master_log_pos=25616;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
master_host='192.168.28.136’, （主库所在IP地址）
master_user='repl', （主从同步用户名）
master_password='a.123456', （主从同步密码）
master_port=16205, （主库对外访问端口号）
master_log_file='mysql_bin.000007',（主库配置文件）
master_log_pos=25616; （主库配置文件）
5、start slave;（开启从库同步主库）
6、show slave status;（查看主从同步状态）show slave status\G
。。。。。。
Slave_IO_Running: Yes （IO线程是否正常）
Slave_SQL_Running: Yes （sql线程是否正常）
#这两个参数都为Yes的时候表示主从配置完成
==============================================================================
六、双主配置（就是把主从反过在配一遍）
登录数据库
/usr/local/mysql/bin/mysql -uroot -p
从库：
2、创建用于主从同步的账号
repl（主从同步用户名）192.168.28.135（从库所在服务器的IP）
mysql> grant replication slave on *.* to 'repl1'@'192.168.28.135' identified by 'a123456';（a123456表示密码）
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> flush privileges;（刷新权限）
Query OK, 0 rows affected (0.02 sec)
3、查看主库当前bin_log文件和Position
mysql> show master status;
+---------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+--------------------------+-------------------+
| binlog.000008 | 25616 | | information_schema,mysql | |
+---------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
#以下是从库操作
4、与主库建立连接
mysql> stop slave;（停止同步）
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.28.135',master_user='repl1',master_password='a123456',master_port=16205,master_log_file='mysql_bin.000008',master_log_pos=25616;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
master_host='172.16.1.20’, （主库所在IP地址）
master_user='repl', （主从同步用户名）
master_password='a123456', （主从同步密码）
master_port=16205, （主库对外访问端口号）
master_log_file='mysql_bin.000004',（主库配置文件）
master_log_pos=25618; （主库配置文件）
5、start slave;（开启从库同步主库）
6、show slave status;（查看主从同步状态）show slave status\G
。。。。。。
Slave_IO_Running: Yes （IO线程是否正常）
Slave_SQL_Running: Yes （sql线程是否正常）
#这两个参数都为Yes的时候表示主从配置完成（两台数据库服务器都为YES方为通过，一定要验证！！！！！！！！！！）
。。。。。。
===========================================================================
如遇访问权限问题：以下可以解决
mysql -uroot -p root
mysql->use mysql
mysql->update user set host = '%' where user ='root';
mysql->grant all privileges on *.* to 'root'@'%' with grant option;
mysql->flush privileges;
mysql->exit;
sudo service mysql restart;
**********************************************************************************************************************************
keepalived部署配置（安装在mysql双主服务器当中）
一、安装keepalived
下载keepalived
官网: https://keepalived.org/download.html
上传并解压keepalived
/home
cd /home/
tar -zxvf keepalived-2.0.18.tar.gz -C /usr/local/src/
进入目录/usr/local/src/keepalived-2.0.18
cd /usr/local/src/keepalived-2.0.18/
检查安装环境
./configure --prefix=/usr/local/keepalived
第一次检查：
configure: error:
!!! OpenSSL is not properly installed on your system. !!!
!!! Can not include OpenSSL headers files. !!!
安装openssl openssl-devel解决问题
yum -y install openssl openssl-devel
第二次检查
*** WARNING - this build will not support IPVS with IPv6. Please install libnl/libnl-3 dev libraries to support IPv6 with IPVS.
安装libnl libnl-devel解决问题
yum -y install libnl libnl-devel
其他问题
configure: error: libnfnetlink headers missing
安装libnfnetlink-devel解决问题
yum -y install libnfnetlink-devel
编译并安装
make && make install
将keepalived添加到系统服务中
拷贝执行文件
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
将init.d文件拷贝到etc下,加入开机启动项
cp /usr/local/src/keepalived-2.0.18/keepalived/etc/init.d/keepalived /etc/init.d/keepalived
将keepalived文件拷贝到etc下
cp /usr/local/src/keepalived-2.0.18/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
创建keepalived文件夹
mkdir -p /etc/keepalived
将keepalived配置文件拷贝到etc下
cp /usr/local/src/keepalived-2.0.18/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf
添加keepalived到开机启动（看个人可随意）
chkconfig --add keepalived
添加可执行权限
chmod +x /etc/init.d/keepalived
二、部署keepalived双击自主切换（一下配置文件和脚本，两台机器都要重新布）
备份keepalived配置文件
cp keepalived.conf keepalived.conf.back
重新编辑配置文件
vim keepalived.conf
配置文件
===============================================================
! COnfiguration File for keepalived
global_defs {
router_id MASTER-HA #主机标识
#router_id BACKUP #备机标识
script_user root
enable_script_security
}
vrrp_script chk_mysql_port {
script "/etc/keepalived/chk_mysql.sh" #脚本地址和名字，此处调用改脚本
interval 2
weight -5
fall 2
rise 1
}
vrrp_instance VI_1 {
state MASTER #主机MASTER、备机BACKUP
interface eth0 #本机的网卡
mcast_src_ip 192.168.28.136 #网卡IP
virtual_router_id 51
priority 101 #主机101，备机小于101便可
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.28.188 #新的IP地址，需要在同机网段内
}
track_script {
chk_mysql_port
}
===============================================================================================
编写脚本
vim chk_mysql.sh
========================================================
#!/bin/bash
#This scripts is check for Mysql Slave status
counter=$(netstat -na|grep "LISTEN"|grep "16205"|wc -l)
if [ "${counter}" -eq 0 ]; then
/etc/init.d/keepalived keepalived stop
killall keepalived
fi
ping 192.168.28.136 -w1 -c1 &>/dev/null
if [ $? -ne 0 ]
then
/etc/init.d/keepalived keepalived stop
killall keepalived
fi
========================================================
添加可执行权限
chmod +x /etc/init.d/keepalived
启动keepalived
/etc/init.d/keepalived start
查看是否启动成功
ps -ef | grep keepalived
启动成功之后会生成一个新的服务器IP地址，可通过新IP直接连接数据库
查看ip地址 ip addr
连接数据库地址
至此，数据库连接成功。
可以自由关闭其中一台数据库服务，keepalived会自动切换到另外一台数据库服务器当中。
