mysql 高可用
使用 docker compose 部署主从 mysql 的主从架构
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
| services:
mysql-master:
image: dockerhub.qingcloud.com/doubao/mysql:8.0.39
container_name: mysql-master
restart: always
environment:
MYSQL_ROOT_PASSWORD: root
MYSQL_USER: app
MYSQL_PASSWORD: app
ports:
- "3306:3306"
volumes:
- ./master/data:/var/lib/mysql
- ./master/config/my.cnf:/etc/mysql/my.cnf
- ./master/logs:/var/log/mysql
networks:
- mysql-cluster
mysql-slave:
image: dockerhub.qingcloud.com/doubao/mysql:8.0.39
container_name: mysql-slave
restart: always
environment:
MYSQL_ROOT_PASSWORD: root
ports:
- "3307:3306"
volumes:
- ./slave/data:/var/lib/mysql
- ./slave/config/my.cnf:/etc/mysql/my.cnf
- ./slave/logs:/var/log/mysql
depends_on:
- mysql-master
networks:
- mysql-cluster
networks:
mysql-cluster:
driver: bridge
|
master config
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
| [mysqld]
server-id=1
log-bin=mysql-bin
user=mysql
port=3306
socket=/var/run/mysqld/mysqld.sock
pid-file=/var/run/mysqld/mysqld.pid
secure-file-priv=/var/lib/mysql-files
datadir=/var/lib/mysql
key_buffer_size=16M
max_allowed_packet=64M
table_open_cache=400
sort_buffer_size=4M
read_buffer_size=4M
read_rnd_buffer_size=4M
myisam_sort_buffer_size=64M
thread_cache_size=8
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1
innodb_open_files=400
innodb_io_capacity=400
innodb_flush_method=O_DIRECT
log_error=/var/log/mysql/error.log
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=2slave config
|
slave config
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
| [mysqld]
server-id=2
relay-log=relay-bin
log-bin=mysql-bin
user=mysql
port=3306
socket=/var/run/mysqld/mysqld.sock
pid-file=/var/run/mysqld/mysqld.pid
secure-file-priv=/var/lib/mysql-files
datadir=/var/lib/mysql
key_buffer_size=16M
max_allowed_packet=64M
table_open_cache=400
sort_buffer_size=4M
read_buffer_size=4M
read_rnd_buffer_size=4M
myisam_sort_buffer_size=64M
thread_cache_size=8
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1
innodb_open_files=400
innodb_io_capacity=400
innodb_flush_method=O_DIRECT
log_error=/var/log/mysql/error.log
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=2
|
部署服务
使用 docker compose 启动服务后，先进去容器，然后连接 mysql
1
2
3
4
|
docker exec -it mysql-master bash
mysql -h localhost -u root -proot
|
连接完成后，执行下面的 sql 新建用户。
1
2
3
| CREATE USER 'replica'@'%' IDENTIFIED BY 'replica-password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;
|
查看 master 的 binlog 信息，slave 同步的时候需要。
1
2
3
4
5
6
7
8
| mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 862
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
|
同样的方式进入到 slave 容器中，然后执行同步命令然后启动同步
1
| mysql> CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_USER='replica', MASTER_PASSWORD='replica-password', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=157, get_master_public_key=1;
|
get_master_public_key=1;是为了解决 slave 连接 master 的认证问题。因为 mysql8 以后使用'caching_sha2_password密码加密。所以特殊处理。
MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=157,这两个信息就是从master status中获取到的。
执行START SLAVE启动同步；
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
62
63
64
65
66
67
| mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: mysql-master
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 157
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000005
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: 157
Relay_Log_Space: 530
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: 1
Master_UUID: 377a786f-5262-11ef-8bef-0242ac120002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
|
验证同步
在 master 上新建一个库
1
2
| mysql> create database test;
Query OK, 1 row affected (0.00 sec)
|
在 slave 上查看库是否同步过来
1
2
3
4
5
6
7
8
9
10
11
| mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.01 sec)
|