首页 技术 正文
技术 2022年11月16日
0 收藏 865 点赞 3,676 浏览 7502 个字

一、测试环境

1.1 结构图 

Docker mysql主主互备和高可用

1.2 版本

  1. 操作系统:CentOS 7.3
  2. MySQL版本:5.6.35
  3. Docker版本:18.06.1-ce
  4. 使用root用户操作
  5. IP地址说明
IP地址 用途  备注
172.16.10.83 MySQL mysqla
172.16.10.62 MySQL mysqlb
172.16.10.199 VIP(keepalived)  

二、安装mysql

2.1 创建目录

mkdir -p /data/mysqldb
mkdir -p /data/docker-compose/mysql-compose

2.2 编写docker-compose.yml文件

cd /data/docker-compose/mysql-compose
vim docker-compose.ymlversion: '3'
services:
mysql:
image: mysql:5.6.35
ports:
- "3306:3306" volumes:
- ./my.cnf:/etc/mysql/conf.d/my.cnf
- /data/mysqldb:/var/lib/mysql
- /etc/localtime:/etc/localtime:ro environment:
- MYSQL_ROOT_PASSWORD=123456 restart: always

  将自己的my.cnf文件放置在/data/docker-compose/mysql-compose目录下

  my.cnf 

[client]
default-character-set = utf8
port = 3306
[mysql]
port = 3306
default-character-set = utf8
[mysqld]
port = 3306
basedir = /var/lib/mysql
datadir = /var/lib/mysql
character-set-server = utf8
log-bin = mysql-bin
binlog_cache_size = 1M
expire_logs_days = 10
max_binlog_size = 128M
server_id = 1235
binlog_format=MIXED
read-only=0
auto-increment-increment=10
auto-increment-offset=1
skip-external-locking
slow-query-log = on
long_query_time = 1
slow_query_log_file = /var/lib/mysql/slow.log
lower_case_table_names = 1
max_connections=1100
max_user_connections=100
max_connect_errors=1000
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 8
innodb_log_file_size = 200M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 90
innodb_support_xa = 0
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
log_bin_trust_function_creators = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 2000
innodb_file_format = Barracuda
innodb_purge_threads=1
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_stats_on_metadata=OFF
sql_mode=ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONlog-error=/var/lib/mysql/mysql.log
pid-file=/var/lib/mysql/mysql.pid

  配置文件中需要增加(以上文件已加) 

server-id=123
log-bin=mysql-bin
binlog_format=MIXEDread-only=0
auto-increment-increment=10
auto-increment-offset=1read-only:标识数据库是否为只读,这里我们设置为0即非只读,该参数针对用户没有SUPER权限设置。auto-increment-increment和auto-increment-offset这两个参数主要控制MySQL自增列的值,
用于Master-Master之间的复制,防止出现重复值。做了如上配置后,我们向该MySQLA服务中插入第一个id就是1,
第二行的id就是11,而不是2,那么在MySQLB服务插入第一个id就是2,第二行的id就是12,这样就不会出现主键冲突。

  

  

2.3 启动mysql

docker-compose up -d#docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bba3674e9c44 mysql:5.6.35 "docker-entrypoint.s…" 3 hours ago Up 41 minutes 0.0.0.0:3306->3306/tcp mysqlcompose_mysql_1

2.4 同样方法启动另一个mysql

# 在另一台启动mysql前,其配置文件my.cnf修改server-id=190
log-bin=mysql-bin
binlog_format=MIXEDrelay_log=mysql-relay-bin
log-slave-updates=ONread-only=0
auto-increment-increment=10
auto-increment-offset=2

  

三、MySQLA – > MySQLB同步配置

3.1 创建MySQL同步账号

GRANT REPLICATION SLAVE ON *.* TO 'mysqla'@'172.16.%' IDENTIFIED BY 'mysqla';#该同步账号主要是给MySQLB使用。

  Docker mysql主主互备和高可用

3.2 查看MySQLA的master状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.3 登入MySQLB,执行如下命令

change master to
master_host='172.16.10.83',
master_user='mysqla',
master_password='mysqla',
master_log_file='mysql-bin.000006',
master_log_pos=120;

3.4 在MySQLB中执行同步命令

mysql> start slave;

3.5 在MySQLB中执行,查看是否配置成功

mysql> show slave status \G;*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.10.83
Master_User: mysqla
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:

  Docker mysql主主互备和高可用

四、MySQLB – > MySQLA同步配置

4.1 创建MySQL同步账号

GRANT REPLICATION SLAVE ON *.* TO 'mysqlb'@'172.16.%' IDENTIFIED BY 'mysqlb';#该同步账号主要是给MySQLA使用。

  

4.2 登入MySQLB查看master状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 473 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

  

4.3 登录MySQLA数据库执行如下同步命令

change master to
master_host='172.16.10.62',
master_user='mysqlb',
master_password='mysqlb',
master_log_file='mysql-bin.000006',
master_log_pos=473;

  

4.4 在MySQLA库中执行启动同步命令

mysql> start slave;

  

4.5 登录MySQLA验证同步命令是否执行成功

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.10.62
Master_User: mysqlb
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 756
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 467
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

  Docker mysql主主互备和高可用

 

五、主主互备验证

5.1 登录MySQLA数据库创建test库

mysql> create database test;
Query OK, 1 row affected (0.00 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

  

5.2 在MySQLB上查看新建的数据库test  

  Docker mysql主主互备和高可用

  数据库同步过来了

5.3 在MySQLB上删除数据库test

  Docker mysql主主互备和高可用

5.4 查看MySQLA中数据库test是否被删除

  Docker mysql主主互备和高可用

  确实已经不存在了,到此MySQL主主互备完成。

六、keepalived

6.1 分别在两台mysql服务器上执行安装 

yum -y install keepalivedsystemctl enable keepalived

  

6.2 MySQL服务状态监控脚本

  MySQL服务状态监测脚本,主要监控MySQL服务状态是否正常,如果不正常则将该MySQL所在服务的Keepalived服务杀死,监控MySQL服务是否正常的方法有多种可以通过端口号、进程ID以及执行MySQL命令,这里我们使用mysladmin执行命令来监测MariaDB服务是否正常,脚本内容如下(check_mysql.sh)

cd /etc/keepalivedvim check_mysql.sh#!/bin/bashMYSQL_PING=`docker exec mysqlcompose_mysql_1 mysqladmin -h127.0.0.1 -uroot -p123456 ping 2>/dev/null`
MYSQL_OK="mysqld is alive"
if [[ "$MYSQL_PING" != "$MYSQL_OK" ]];then
echo "mysql is not running."
killall keepalived
else
echo "mysql is running"
fi

 Docker mysql主主互备和高可用

 注:两台服务器都要配置

6.4 MySQLA服务器Keepalived配置

! Configuration File for keepalived
global_defs {
}
vrrp_script check_mysql {
script "/etc/keepalived/check_mysql.sh"
interval 2
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.16.10.199
}
track_script {
check_mysql
}
}

  注:这个是master

6.5 MySQLB服务器Keepalived配置 

! Configuration File for keepalived
global_defs {
}
vrrp_script check_mysql {
script "/etc/keepalived/check_mysql.sh"
interval 2
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.16.10.199
}
track_script {
check_mysql
}
}

6.6 分别启动keepalived

systemctl start keepalived

  

6.7 查看VIP是否被绑定在MASTER的网卡上

  Master在172.16.10.83这个服务器上

  Docker mysql主主互备和高可用

七、验证

7.1 验证联通性

  在两台服务器上pingVIP

  172.16.10.83

  Docker mysql主主互备和高可用

  172.16.10.62

  Docker mysql主主互备和高可用

7.2 验证VIP是否会漂移

  将keepalive的master这个停掉,VIP就应该不在这个服务器上了  

# MySQLA服务器
systemctl stop keepalived

  Docker mysql主主互备和高可用

  查看BACKUP服务器,确实已经漂移过来了

  Docker mysql主主互备和高可用

7.3 验证mysql停止VIP是否会漂移

  在MySQLA服务器上停止mysql容器  

#docker stop mysqlcompose_mysql_1
mysqlcompose_mysql_1[root@node1 /etc/keepalived]
#docker ps | grep mysqlcompose_mysql_1[root@node1 /etc/keepalived]
#ip addr list eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether fa:36:67:e1:b4:00 brd ff:ff:ff:ff:ff:ff
inet 172.16.10.83/24 brd 172.16.10.255 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::f836:67ff:fee1:b400/64 scope link
valid_lft forever preferred_lft forever

  可见VIP已经不在了

  Docker mysql主主互备和高可用

  事实上keepalived也停止了

  Docker mysql主主互备和高可用

  VIP自动漂移到MySQLB这台服务器上了

  Docker mysql主主互备和高可用

相关推荐
python开发_常用的python模块及安装方法
adodb:我们领导推荐的数据库连接组件bsddb3:BerkeleyDB的连接组件Cheetah-1.0:我比较喜欢这个版本的cheeta…
日期:2022-11-24 点赞:878 阅读:9,085
Educational Codeforces Round 11 C. Hard Process 二分
C. Hard Process题目连接:http://www.codeforces.com/contest/660/problem/CDes…
日期:2022-11-24 点赞:807 阅读:5,560
下载Ubuntn 17.04 内核源代码
zengkefu@server1:/usr/src$ uname -aLinux server1 4.10.0-19-generic #21…
日期:2022-11-24 点赞:569 阅读:6,409
可用Active Desktop Calendar V7.86 注册码序列号
可用Active Desktop Calendar V7.86 注册码序列号Name: www.greendown.cn Code: &nb…
日期:2022-11-24 点赞:733 阅读:6,182
Android调用系统相机、自定义相机、处理大图片
Android调用系统相机和自定义相机实例本博文主要是介绍了android上使用相机进行拍照并显示的两种方式,并且由于涉及到要把拍到的照片显…
日期:2022-11-24 点赞:512 阅读:7,819
Struts的使用
一、Struts2的获取  Struts的官方网站为:http://struts.apache.org/  下载完Struts2的jar包,…
日期:2022-11-24 点赞:671 阅读:4,902