MYSQL + KEEPALIVED 双主高可用

MYSQL主从复制原理

1.主库发生data change的时候，把操作写入bin log文件。

2.从库通过io线程查询bin log文件。

3.主库通过dump线程获取bin log文件，并发给从库。

4.从库把解析bin log文件，把写入relay log（ 中继日志）

5.从库读取relay log文件，并执行日志文件的sql

安装MYSQL

在/data/mysql/config/目录下创建my.cnf文件。

My.cnf文件

[mysqld]
user=root
character-set-server=utf8
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
expire_logs_days=7
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_connections=1000
default_time_zone = "+8:00"
# mysql日志显示[Warning] IP address 'xxxx' could not be resolved: Name or service not known
skip-name-resolve

#主从同步配置

log-bin=mysql-bin
server-id=1 #不可重复id
binlog_do_db=test_db #需要同步的数据库
binlog_format=ROW
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
#拉取mysql8.0.22
docker pull mysql:8.0.22

#安装mysql

docker run -it -d --name zibrainMySQL  -p 13306:3306 --privileged=true -v /data/mysql/config/my.cnf:/etc/mysql/my.cnf  -v /data/mysql/files/:/var/lib/mysql-files -v /data/mysql/data/:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=Zhongxy-2021 -e TZ=Asia/Shanghai mysql:8.0.22

创建主从同步账户zibrainprod

#创建账号

CREATE USER 'zibrainprod'@'%' IDENTIFIED BY 'Zhongxy-2021';

给用户权限 

GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, CREATE VIEW, SHOW VIEW ON `zibrain`.* TO 'zibrainprod'@'%';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'zibrainpord'@'%';

权限介绍 

REPLICATION SLAVE （show master status;show slave status命令权限）, REPLICATION （主从同步必要权限）

刷新权限flush privileges;

查看master状态，记录下file、psition值，配mysql从库时使用

需要复制对应的数据库 IP,点位等

change master to 
     master_host='10.251.181.50',            
     master_port=23306,                        
     master_user='zibrinprod',                   
     master_password='Zhongxy-2021',              
     master_log_file='mysql-bin.000002',       
     master_log_pos=1822;

查看复制状态

这两个为yes表示复制成功

常用命令

#开启复制

start slave; 

#停止复制

stop slave; 

#重置复制

reset slave; 

#查看复制状态

show slave status\G; 

#删除用户

drop user 'repl_user'@'%';

50： change master to 

master_host='10.251.181.51',master_port=23306,   master_user='zibrainprod',master_password='Zhongxy-2021',master_log_file='mysql-bin.000019',master_log_pos=6770; 

51： change master to master_host='10.251.181.50',master_port=23306,      master_user='zibrainprod',master_password='Zhongxy-2021',master_log_file='mysql-bin.000026',master_log_pos=156; 

#查看用户，可访问方式等

select user,host,authentication_string from mysql.user; 

常见错误

Q:Mysql主从架构报错-Fatal error,The slave I/O thread stops because master and slave have equal 

MySQL server UUIDs; these UUIDs must be different for replication to work... 

A:解决办法，修改其中一台服务器的 server-uuid，并保证 server-uuid 的格式正确，修改完成

之后重启 Mysql 服务就可以了。

$ select uuid(); 
$ find / -name 'auto.cnf' 
$ vim /data/mysql/auto.cnf 
# 按照 16 进制，修改 UUID，保存，重启 mysql。
#uuid: b33c6ad1-8ed1-11ec-a946-0242ac120003

KEEPALIVED 虚IP漂移

50、51两台服务器安装步骤相同，配置文件不同，下面有介绍。

1.安装相关依赖包，并下载keepalived安装包，解压，配置，编译

cd /opt
yum -y install gcc openssl-devel popt-devel psmisc
wget https://www.keepalived.org/software/keepalived-2.2.7.tar.gz
tar -zxvf keepalived-2.2.7.tar.gz
cd keepalived-2.2.7
./configure --prefix=/opt/keepalived2.2.7
make && make install

2.将文件复制到对应目录下

mkdir /etc/keepalived
cp keepalived/etc/keepalived/keepalived.conf.sample /etc/keepalived/keepalived.conf
cp keepalived/etc/init.d/keepalived /etc/init.d/
cp keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp bin/keepalived /usr/sbin/

3.新建/etc/keepalived/shutdown.sh文件，内容为:

#!/bin/bash

#该脚本是在mysql服务出现异常时，将keepalived应用停止，从而使虚拟vip主机自动连接到另一台mysql上

killall keepalived

4.使用ifconfig命令查看下网卡名称，本机网卡名称为ens192

5.修改50服务器/etc/keepalived/keepalived.conf配置文件，内容如下:

! 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 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}
vrrp_instance VI_1 {
    state MASTER
    #interface为刚才查到的本机网卡名称
    interface ens192
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        #该ip为虚拟出来的vip地址
        10.251.181.56
    }
}

#配置virtual_server  ip为上面配置的虚拟vip地址  端口为mysql的端口

virtual_server 10.251.181.56 23306 {
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP
    #real_server 该配置为实际物理机ip地址 以及实际物理机mysql端口
    real_server 10.251.181.50 23306 {
        #当该ip 端口连接异常时，执行该脚本
        notify_down /etc/keepalived/shutdown.sh
        TCP_CHECK {
            #实际物理机ip地址
            connect_ip 10.251.181.50
            connect_timeuot 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}

51配置不同的地方。需要修改

6.将128、129服务器keepalived加入开机自启，并启动服务

systemctl enable keepalived

systemctl start keepalived

7.启动后keepalived状态为：active(running)则正常

8.启动后相当于虚拟出一个vip 10.251.181.56，可使用远程连接工具，连接该服务器，连接进去后使用ifconfig查看该虚拟vip实际上使用的实体服务器是50服务器。

至此，keepalived部署完成。

常用命令

# 日志

tail -f /var/log/messages

# 状态

systemctl status keepalived

# 重启/stop/start

systemctl restart/stop/start keepalived

常见问题

停止mysql后，停止keepalived脚本未执行

解决办法：

chmod 777 shutdown.sh

修改keepalived.config文件重启不起作用

Killall  keepalived

重启

Systemctl start keepalived

整体测试