docker_mysql双主部署文档

一、master

建立文件夹
sudo vi /data/xxxxxxx/mysql/conf/mysql.cnf

配置文件
[mysqld]
log-bin=mysql-bin 
binlog_format=mixed
server-id=1 
relay-log=relay-bin
relay-log-index=slave-relay-bin.index
auto_increment_increment=2
auto_increment_offset=1 


启动容器
sudo docker run -p 7306:3306 --name    xxxxxxx_mysql_8.0 \
--restart always \
-v /data/xxxxxxx/mysql/conf:/etc/mysql/conf.d \
-v /data/xxxxxxx/mysql/logs:/logs \
-v /dataxxxxxxx/mysql/data:/var/lib/mysql \
-v /etc/localtime:/etc/localtime:ro \
-e MYSQL_ROOT_PASSWORD=xxxxxxx -d mysql:8.0 --lower_case_table_names=1 --skip-name-resolve

sudo docker restart xxxxxxx_mysql_8.0

进入容器

sudo docker exec -it xxxxxxx_mysql_8.0 /bin/bash
mysql -uroot -pxxxxxxx



CREATE USER 'reader'@'%' IDENTIFIED WITH mysql_native_password BY 'xxxxxxx';
GRANT REPLICATION SLAVE ON *.* TO 'reader'@'%';


FLUSH PRIVILEGES;

二、slave

创建文件夹
sudo vi /data/xxxxxxx/mysql/conf/mysql.cnf

配置文件
[mysqld]
log-bin=mysql-bin 
binlog_format=mixed
server-id=2  
relay-log=relay-bin 
relay-log-index=slave-relay-bin.index 
auto_increment_increment=2 
auto_increment_offset=2  

启动容器

sudo docker run -p 7306:3306 --name xxxxxxx_mysql_8.0 \
--restart always \
-v /data/xxxxxxx/mysql/conf:/etc/mysql/conf.d \
-v /data/xxxxxxx/mysql/logs:/logs \
-v /data/xxxxxxx/mysql/data:/var/lib/mysql \
-v /etc/localtime:/etc/localtime:ro \
-e MYSQL_ROOT_PASSWORD=xxxxxxx -d mysql:8.0 --lower_case_table_names=1 --skip-name-resolve




sudo docker restart  xxxxxxx_mysql_8.0


#进入容器开启root 远程访问
sudo docker exec -it  xxxxxxx_mysql_8.0 /bin/bash
mysql -uroot -pxxxxxxx


CREATE USER 'reader'@'%' IDENTIFIED WITH mysql_native_password BY 'xxxxxxx';
GRANT REPLICATION SLAVE ON *.* TO 'reader'@'%';

三、master

show master status\G;
change master to master_host='192.168.0.186',master_user='reader',master_password='xxxxxxx',master_port=7306,master_log_file='mysql-bin.000003',master_log_pos=157;
FLUSH PRIVILEGES;
start slave;
show slave status\G;

四、slave

show master status\G;
change master to master_host='192.168.0.185',master_user='reader',master_password='xxxxxxx',master_port=7306,master_log_file='mysql-bin.000006',master_log_pos=157;

FLUSH PRIVILEGES;
start slave;
show slave status\G;

测试
master
create database 
show databases;

五、keepalived 搭建

yum install -y keepalived

sudo cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.bak
cd /etc/keepalived
vim /etc/keepalived/keepalived.conf

[root@hosta keepalived]# cat keepalived.conf
! Configuration File for keepalived
       
global_defs {
notification_email {
ops@wangshibo.cn
tech@wangshibo.cn
}
       
notification_email_from ops@wangshibo.cn
smtp_server 127.0.0.1 
smtp_connect_timeout 30
router_id MASTER-HA
}
       
vrrp_script chk_mysql_port {       #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
    script "/mysql/chk_mysql.sh"   #这里通过脚本监测
    interval 2                     #脚本执行间隔，每2s检测一次
    weight –5                      
#脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5

    fall 2                         
#检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）

    rise 1                         #检测1次成功就算成功。但不修改优先级
}
       
vrrp_instance VI_1 {
    state MASTER                  #这里所有节点都定义为BACKUP
    interface ens33               #指定虚拟ip的网卡接口
    mcast_src_ip 192.168.0.185    #本地IP 
    virtual_router_id 51          #路由器标识，MASTER和BACKUP必须是一致的
    priority 101                  #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。 
    advert_int 1
    nopreempt
    authentication {   
        auth_type PASS 
        auth_pass 123456     
    }
    virtual_ipaddress {    
        192.168.0.250/24             #虚拟IP 
    }
      
track_script {               
   chk_mysql_port             
}
}



[root@hostb keepalived]# cat keepalived.conf
! Configuration File for keepalived
       
global_defs {
notification_email {
ops@wangshibo.cn
tech@wangshibo.cn
}
       
notification_email_from ops@wangshibo.cn
smtp_server 127.0.0.1 
smtp_connect_timeout 30
router_id MASTER-HA
}
       
vrrp_script chk_mysql_port {
    script "/mysql/chk_mysql.sh"
    interval 2            
    weight -5                 
    fall 2                 
    rise 1               
}
       
vrrp_instance VI_2 {
    state BACKUP
    interface ens33 
    mcast_src_ip 192.168.0.186
    virtual_router_id 51    
    priority 99
    nopreempt          
    advert_int 1         
    authentication {   
        auth_type PASS 
        auth_pass 123456     
    }
    virtual_ipaddress {    
        192.168.0.250/24
    }
      
track_script {               
   chk_mysql_port             
}
}



sudo vim /mysql/chk_mysql.sh
[root@hosta ~]# cat /mysql/chk_mysql.sh 

#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "7306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    systemctl stop keepalived
fi


sudo chmod +x /mysql/chk_mysql.sh 

sudo systemctl start keepalived
sudo systemctl status keepalived
sudo systemctl enable keepalived

sudo systemctl stop keepalived
sudo docker restart zhaotoubiao_mysql_8.0

sudo docker exec xxxxxxx_mysql_8.0 sh -c 'exec mysqldump --all-databases -uroot -p"xxxxxxx"' > ~/xxxxxxx-all-databases.sql

sudo docker exec -i xxxxxxx_mysql_8.0 sh -c 'exec mysql -uroot -p"xxxxxxx"' < ./xxxxxxx-all-databases.sql