日常运维小记录 - haproxy mysql(docker) - 《Linux》

架构+目录结构
mysql双主模式配置
配置haproxy
user haproxy
group haproxy
option mysql-check use haproxy-check
the below nodes would be hit on 1:1 ratio.if you want it to be 1:2 then add ‘weight 2’ just after the line.
Enable cluster status
- 最终的docker stack的yml文件：
- 启动服务

架构+目录结构

采用haproxy+mysql(双主模式)

mysql-haproxy

.
├── haproxy-mysql.yml
├── haproxy
│   └── haproxy.cfg
├── mysql1
│   ├── data
│   └── my.cnf
├── mysql2
│   ├── data
│   └── my.cnf
└── start.sh

mysql双主模式配置

mysql1配置文件 mysql1/my.cnf

[mysqld]
symbolic-links=0
default-time-zone = '+8:00'
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/lib/mysql/mysql.sock
port=3306
datadir=/var/lib/mysql
lc-messages-dir=/usr/share/mysql
skip-external-locking
bind-address=0.0.0.0
key_buffer_size=16M
max_allowed_packet=16M
thread_stack=192K
thread_cache_size=8
myisam-recover-options=BACKUP
query_cache_limit=1M
query_cache_size=16M
general_log_file=/var/log/mysql/mysql.log
general_log=1
log_error=/var/log/mysql/error.log
server-id=1           # server id设置为1
log_bin=/var/log/mysql/mysql-bin.log
binlog_format=mixed
log-slave-updates = true     #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
expire_logs_days=10
max_binlog_size=100M
log-bin = mysql-bin
relay-log=relay-bin
relay-log-index=slave-relay-bin.index
auto-increment-offset=1      # 避免主键冲突
auto-increment-increment=2   # 增长起始设置为2
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

mysql2 配置文件 mysql2/my.cnf

[mysqld]
symbolic-links=0
default-time-zone = '+8:00'
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/lib/mysql/mysql.sock
port=3306
datadir=/var/lib/mysql
lc-messages-dir=/usr/share/mysql
skip-external-locking
bind-address=0.0.0.0
key_buffer_size=16M
max_allowed_packet=16M
thread_stack=192K
thread_cache_size=8
myisam-recover-options=BACKUP
query_cache_limit=1M
query_cache_size=16M
general_log_file=/var/log/mysql/mysql.log
general_log=1
log_error=/var/log/mysql/error.log
server-id=2            # server id设置为2
log_bin=/var/log/mysql/mysql-bin.log
binlog_format=mixed
expire_logs_days=10
log-slave-updates = true     #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
max_binlog_size=100M
relay-log=relay-bin
log-bin = mysql-bin
relay-log-index=slave-relay-bin.index
auto-increment-offset=2       # 避免主键冲突
auto-increment-increment=2    # 增长起始设置为2
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

使用docker stack 启动mysql 服务
mysql-stack.yml

version: '3.3'
services:
mysql-master1:
  image: mysql:5.7
  environment:
  - "MYSQL_ROOT_PASSWORD=123456"
  - "MYSQL_DATABASE=relicas_db"
  ports:
  - "33066:3306"
  volumes:
  - /data/volumes/mysql-haproxy/mysql1/data:/var/lib/mysql
  - /data/volumes/mysql-haproxy/mysql1/my.cnf:/etc/mysql/my.cnf
  - /etc/localtime:/etc/localtime:ro
  restart: always
  hostname: mysql-master1
mysql-master2:
  image: mysql:5.7
  environment:
  - "MYSQL_ROOT_PASSWORD=123456"
  - "MYSQL_DATABASE=relicas_db"
  ports:
  - "33067:3306"
  volumes:
  - /data/volumes/mysql-haproxy/mysql2/data:/var/lib/mysql
  - /data/volumes/mysql-haproxy/mysql2/my.cnf:/etc/mysql/my.cnf
  - /etc/localtime:/etc/localtime:ro
  restart: always
  hostname: mysql-master2

docker stack deploy -c mysql-stack.yml mysql

首次启动时需要进入容器中配置双主模式

mysql1 主 mysql2 从

mysql1 配置
为mysql2配置授权账号：

> grant replication slave on *.* to 'mysql1'@'%' identified by 'mysql1';

查看master状态获取position:

> show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

mysql2 配置
添加mysql1为主：

> change master to master_host='mysql-master1', master_user='mysql1', master_password='mysql1', master_log_file='mysql-bin.000007', master_log_pos=154;

启动slave，查看状态：

start slave;

show slave status \G;

配置正常状态的显示：

Slave_IO_State: Waiting for master to send event
Master_Host: mysql-master1
Master_User: mysql1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 154
Relay_Log_File: relay-bin.000014
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes      // 正常为yes 
Slave_SQL_Running: Yes     // 正常为yes

mysql1 从 mysql2 主
同上一步，反过来即可，然后查看mysql1-slave的状态为双YES即双主配置成功

配置haproxy

配置文件haproxy/haproxy.cfg``` global log 127.0.0.1 local0
user haproxy
group haproxy
defaults log global retries 2 timeout connect 3000 timeout server 5000 timeout client 5000 listen mysql-cluster bind 0.0.0.0:3306 mode tcp
option mysql-check use haproxy-check
option tcp-check balance roundrobin
the below nodes would be hit on 1:1 ratio.if you want it to be 1:2 then add ‘weight 2’ just after the line.
server mysql1 mysql-master1:3306 check server msyql2 mysql-master2:3306 check
Enable cluster status
listen mysql-clusterstats bind 0.0.0.0:8080 mode http stats enable stats uri / stats realm Strictly\ Private stats auth admin:admin ```

最终的docker stack的yml文件：

haproxy-mysql.yml:

version: '3.3'
services:
  haproxy:
    image: haproxy
    ports:
      - "33060:3306"
      - "38080:8080"
    volumes:
      - /data/volumes/mysql-haproxy/haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro
    restart: always
    hostname: haproxy
  mysql-master1:
    image: mysql:5.7
    environment:
      - "MYSQL_ROOT_PASSWORD=123456"
      - "MYSQL_DATABASE=relicas_db"
    ports:
      - "33066:3306"
    volumes:
      - /data/volumes/mysql-haproxy/mysql1/data:/var/lib/mysql
      - /data/volumes/mysql-haproxy/mysql1/my.cnf:/etc/mysql/my.cnf
      - /etc/localtime:/etc/localtime:ro
    restart: always
    hostname: mysql-master1
  mysql-master2:
    image: mysql:5.7
    environment:
      - "MYSQL_ROOT_PASSWORD=123456"
      - "MYSQL_DATABASE=relicas_db"
    ports:
      - "33067:3306"
    volumes:
      - /data/volumes/mysql-haproxy/mysql2/data:/var/lib/mysql
      - /data/volumes/mysql-haproxy/mysql2/my.cnf:/etc/mysql/my.cnf
      - /etc/localtime:/etc/localtime:ro
    restart: always
    hostname: mysql-master2

启动服务

docker stack deploy -c haproxy-mysql.yml haproxy-mysql

使用swarm任意节点的ip:33060(即hapoxy对外的端口)即可使用mysql服务

haproxy mysql(docker)

架构+目录结构

mysql双主模式配置

配置haproxy

user haproxy

group haproxy

option mysql-check use haproxy-check

the below nodes would be hit on 1:1 ratio.if you want it to be 1:2 then add ‘weight 2’ just after the line.

Enable cluster status

最终的docker stack的yml文件：

启动服务