-
mysql主备集群（高可用）
docker-compose启动mysql双机热备互为主从
1. 环境说明
IP地址 服务 10.1.xxx.65 mysql-01 10.1.xxx.66 mysql-02 2. 启动 mysql-01
创建master主节点，通过下面的docker编排配置创建响应的目录和文件，目录下边创建
docker-compose.yml和my.cnf和mysql-common.env三个文件如下：- docker-compose.yml
version: "3" services: mysql: image: hub.xxx.net/library/mysql:5.7 container_name: mysql restart: always network_mode: host ports: - "3311:3306" env_file: - ./env/mysql-common.env volumes: - ./mysql/data:/var/lib/mysql - ./mysql/my.cnf:/etc/mysql/my.cnf # - ./init:/docker-entrypoint-initdb.d/ - /etc/localtime:/etc/localtime environment: - TZ=Asia/Shanghai- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- my.cnf
[client] port=3426 socket=/usr/local/mysql/mysql.sock #default-character-set=gbk [mysqld] basedir=/usr/local/mysql datadir=/data/mysql socket=/usr/local/mysql/mysql.sock lower_case_table_names=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 port=3426 group_concat_max_len = 4096 expire_logs_days = 7 max_user_connections = 4096 max_connections = 4096 innodb_buffer_pool_size = 6G innodb_log_file_size = 1024M log_timestamps = SYSTEM server-id = 1 log-bin = /data/mysql/mysql-bin binlog_format = ROW log_slave_updates = 1 gtid_mode = ON enforce_gtid_consistency = ON max_user_connections = 4096 max_connections = 4096 slave-skip-errors = 1062,1032,1060,1007,1050 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd [mysqld_safe] #log-error=/var/log/mariadb/mariadb.log #pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory #- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- mysql-common.env
MYSQL_ROOT_PASSWORD=root- 1
- 启动
docker-compose up -d- 1
3. 启动 mysql-02
创建master主节点，通过下面的docker编排配置创建响应的目录和文件，目录下边创建
docker-compose.yml和my.cnf和mysql-common.env三个文件如下：- docker-compose.yml
version: "3" services: mysql: image: hub.xxx.net/library/mysql:5.7 container_name: mysql restart: always network_mode: host ports: - "3311:3306" env_file: - ./env/mysql-common.env volumes: - ./mysql/data:/var/lib/mysql - ./mysql/my.cnf:/etc/mysql/my.cnf # - ./init:/docker-entrypoint-initdb.d/ - /etc/localtime:/etc/localtime environment: - TZ=Asia/Shanghai- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- my.cnf
[client] port=3426 socket=/usr/local/mysql/mysql.sock #default-character-set=gbk [mysqld] basedir=/usr/local/mysql datadir=/data/mysql socket=/usr/local/mysql/mysql.sock lower_case_table_names=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 port=3426 group_concat_max_len = 4096 expire_logs_days = 7 max_user_connections = 4096 max_connections = 4096 innodb_buffer_pool_size = 6G innodb_log_file_size = 1024M log_timestamps = SYSTEM server-id = 1 log-bin = /data/mysql/mysql-bin binlog_format = ROW log_slave_updates = 1 gtid_mode = ON enforce_gtid_consistency = ON max_user_connections = 4096 max_connections = 4096 slave-skip-errors = 1062,1032,1060,1007,1050 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd [mysqld_safe] #log-error=/var/log/mariadb/mariadb.log #pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory #- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 启动
docker-compose up -d- 1
4. 配置主从同步
4.1 mysql-01（master） ==> mysql-02（slave）
登录10.1.xxx.65操作
1）确定slave设置
- 进入mysql-01容器
[root@VM-AICS-IF01 env]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 50a501a7e7dc hub.commchina.net/library/mysql:5.7 "docker-entrypoint.s…" 3 days ago Up 3 days mysql [root@db-02 ~]# docker exec -it mysql bash- 1
- 2
- 3
- 4
- 从该容器登录mysql-02服务查看其master状态
通过65服务器查看66服务器主节点的状态信息，顺便可以测试一下两个容器的连通性。
root@ead2301cd20d:/# mysql -h10.1.xxx.66 -uroot -proot mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> exit- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
2）配置主从同步
- 根据mysql-02 的master 状态拼接mysql-01 设置 slave的命令：（配置66为主节点）
配置66为主节点
CHANGE MASTER TO master_host = '10.1.xx.66', master_port = 3306, master_user = 'root', master_password = 'root', master_log_file = 'mysql-bin.000002', // 指定66服务器的binlog日志文件名 master_log_pos = 154;- 1
- 2
- 3
- 4
- 5
- 6
- 启动slave
mysql> start slave; Query OK, 0 rows affected (0.00 sec)- 1
- 2
- 3
- 查看主从状态
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.1.30.65 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: mysql-relay.000003 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 523 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 150 Master_UUID: af701e96-0279-11ed-a999-0242ac130002 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
4.2 mysql-02（master） ==> mysql-01（slave）
登录10.1.xxx.66操作
1）确定slave设置
- 进入mysql-01容器
[root@VM-AICS-IF01 env]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 50a501a7e7dc hub.commchina.net/library/mysql:5.7 "docker-entrypoint.s…" 3 days ago Up 3 days mysql [root@db-02 ~]# docker exec -it mysql bash- 1
- 2
- 3
- 4
- 从该容器登录mysql-02服务查看其master状态
通过66服务器查看65服务器主节点的状态信息，顺便可以测试一下两个容器的连通性。
root@ead2301cd20d:/# mysql -h10.1.xxx.65 -uroot -proot mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> exit- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
2）配置主从同步
- 根据mysql-01 的master 状态拼接mysql-02 设置 slave的命令：（配置66为主节点）
配置65为主节点
CHANGE MASTER TO master_host = '10.1.xx.65', master_port = 3306, master_user = 'root', master_password = 'root', master_log_file = 'mysql-bin.000002', // 指定65服务器的binlog日志文件名 master_log_pos = 154;- 1
- 2
- 3
- 4
- 5
- 6
- 启动slave
mysql> start slave; Query OK, 0 rows affected (0.00 sec)- 1
- 2
- 查看主从状态
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.1.30.65 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: mysql-relay.000003 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 523 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 150 Master_UUID: af701e96-0279-11ed-a999-0242ac130002 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
5. 验证
我们可以在mysql-01 上创建数据库，然后看看mysql-02上有没有；
然后在mysql-02上创建表，看看mysql-01上有没有。 -
相关阅读:
【App自动化测试】（九）移动端复杂测试环境模拟——来电、短信、网络切换
工信部教考中心：什么是《研发效能(DevOps)工程师》认证，拿到证书之后有什么作用！（上篇）丨IDCF
协程（四）——Android中使用协程
git 冲突格式
NodeJS V8引擎的内存和垃圾回收器（GC）
一文掌握Python多线程与多进程
基于Docker来部署Nacos的注册中心
Thread类的start()方法创建线程的底层分析
sumatrapdf反向搜索功能设置
【23真题】C9无歧视，专业课均分130！
- 原文地址：https://blog.csdn.net/qq_38129621/article/details/126469064
