docker + mysql + keepalived 双主高可用

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

整体测试