MySQL 高可用架构：mysql+keepalived 实现

#主机名		 系统版本	 mysql版本	 ip地址mysqlMaster	 <a href="https://www.linuxprobe.com/" title="centos" target="_blank">centos</a>7.4	 mysql5.7	 192.168.1.42mysqlSlave	 centos7.4	 mysql5.7	 192.168.1.43#vip：192.168.1.41

yum install epel* -y && yum clean all && yum makecache rpm -Uvh http://repo.mysql.com/mysql57-community-release-el7.rpmyum clean all && yum makecacheyum install gcc gcc-c++ openssl-devel mysql mysql-server mysql-devel -y

mkdir /data/mysql -pchown -R mysql:mysql /data/mysql

vi /etc/my.cnf[mysqld]server-id = 1		#全局唯一，每台都不能一样log-bin = mysql-bin	#log-bin表示开启二进制日志记录，mysql-bin表示日志文件的命名格式，会生成mysql-bin.0001 等等relay-log = mysql-relay-bin	#指定中继日志格式（拉取主mysql日志后，在从库上生成的日志）replicate-wild-ignore-table=mysql.%	#指定那些库或则表不进行同步，mysql是库名，.%表示下面所有的表，mysql.user 表示不同不mysql库下的user表replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%#replicate-wild-do-table=boke.%		#表示同步那个库#注意：不要在主库上使用binlog-do-db 或　binlog-ignore-db选项#也不要在从库上使用　replicate-do-db 或　replicate-ignore-db 选项，因为这有可能产生跨库更新失败的问题．推荐从库上使用　replicate_wild_do_table 和　replicate_wild_ignore_table 这两个选项来解决复制过滤问题datadir=/data/mysqlsocket=/data/mysql/mysql.sockuser=mysqlsymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidcharacter-set-server=utf8[mysql]socket=/data/mysql/mysql.sockdefault-character-set=utf8[client]socket=/data/mysql/mysql.sockdefault-character-set=utf8user=rootpassword=NCYD-tianyu@0791#若是不写上这个字段，在本机用<a href="https://www.linuxcool.com/" title="命令" target="_blank">命令</a>进入mysql会报错，提示默认路径/var/lib/mysql/mysql.sock找不到

vi /etc/my.cnf[mysqld]server-id = 2log-bin = mysql-binrelay-log = mysql-relay-binreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%datadir=/data/mysqlsocket=/data/mysql/mysql.sockuser=mysqlsymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidcharacter-set-server=utf8[mysql]socket=/data/mysql/mysql.sockdefault-character-set=utf8[client]socket=/data/mysql/mysql.sockdefault-character-set=utf8user=rootpassword=NCYD-tianyu@0791

#mysql5.7.7以后的初始化方法mysqld --initialize --user=mysql --datadir=/data/mysql#mysql5.7.7以前的初始化方法mysql_install_db --user=mysql --datadir=/data/mysql分别启动主从数据库#在centos7里面，必须先关闭se<a href="https://www.linuxprobe.com/" title="linux" target="_blank">linux</a>,否在无法启动mysqldsystemctl start mysqld分别为主从mysql做安全加固#查到上一步，首次启动mysql,系统自动生成的密码（　cat /var/log/mysqld.log |grep pass　）mysql_secure_installation	#使用该命令更改随机root密码（修改为　NCYD-tianyu@0791　）手动同步数据（假如现在的环境，主上已经有数据了，从是新的）1：在主上数据库中创建用于复制的用户，并授权mysql> GRANT REPLICATION SLAVE ON *.* to 'tongbu'@'192.168.1.43' identified by '123456789';2：在主mysql上先锁表（使其所有表变成只读状态）mysql> flush tables with read lock;		#不要退出终端，否在这个锁就失效了3：再开启另一个命令行终端，使用myqldump等工具将数据导出（或则直接打包存储mysql数据的目录，并发送到从机上）4：将导出数据复制到从机上，并创建新库并导入数据互相置从，互相置主（以达到双主模式）

mysql> show master status;	#查看mysqlSlave的状态（记录File名字，和Position）change master tomaster_host = '192.168.1.43',master_user = 'tongbu',master_password = '123456789',master_log_file = 'mysql-bin.000002',master_log_pos = 1006;mysql> start slave; #启动slave端的复制进程(某些版本是：slave start; )mysql> show slave status\G #查看slaves端的I/O进程，与SQL进程

mysql> show master status;	#查看mysqlMaster的状态（记录File名字，和Position）change master tomaster_host = '192.168.1.42',master_user = 'tongbu',master_password = '123456789',master_log_file = 'mysql-bin.000002',master_log_pos = 1006;mysql> start slave; #启动slave端的复制进程mysql> show slave status\G #查看slaves端的I/O进程，与SQL进程

yum install keepalived -y#在mysqlMaster和mysqlSlave上都创建检查mysql的检查<a href="https://www.linuxcool.com/" title="脚本" target="_blank">脚本</a>，并赋予执行权限touch /etc/keepalived/mysql_check.shchmod +x /etc/keepalived/mysql_check.shvi /etc/keepalived/mysql_check.sh#!/bin/bash#slave_is=( $(mysql -uroot -pNCYD-tianyu@0791 -e "show slave status\G" | grep "Slave_.*_Running" | awk '{print $2}') )　在新版本的myql中，直接将密码写在命令行，会进行安全提示，解决办法是将其写在mysql的配置文件里的 [client] 字段里，写法见上面mysql的配置文件slave_is=( $(mysql -e "show slave status\G" | grep "Slave_.*_Running" | awk '{print $2}') )if [ "${slave_is[0]}" = "Yes" -a "$slave_is[1]" = "Yes"]	then	exit 0else	exit 1fi

vi /etc/keepalived/keepalived.confglobal_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from Alexandre.Cassen@firewall.loc smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_DEVEL vrrp_skip_check_adv_addr #vrrp_strict		#注意：当你发现无法ping通虚拟VIP时，建议将此行注释掉 vrrp_garp_interval 0 vrrp_gna_interval 0}vrrp_script mysql_check {	script "/etc/keepalived/mysql_check.sh"	#这个脚本，若是发现不执行，可以检查他的权限，777是不可以的，日志提示不安全，可以直接chmod +x /etc/keepalived/mysql_check.sh 就行	#script "shutdown -r now"	#或者，直接用命令进行调试	interval 2	weight 2}vrrp_instance VI_1 { state MASTER	#mysqlMaster上是MASTER；mysqlSlave上是BACKUP interface ens192 virtual_router_id 52 priority 100	#mysqlMaster上是100；mysqlSlave上是90 advert_int 1 nopreempt		#配置VIP（注意：故障切换抢占模式，尽量关闭，默认是开启的，这个参数只需要在高优先级上设置即可） authentication { auth_type PASS auth_pass 1111 }track_script {	#调用上面定义检查mfsmaster的脚本 mysql_check } virtual_ipaddress { 192.168.1.41/24 dev ens192 }}

systemctl start keepalived