虽然我们常说MySQL这种有状态的服务不适合容器化部署。但现实中我们接触的大部分项目都属于小型项目，QPS可能不过百。这个时候Docker对于数据库的性能损耗其实完全可以无视。本文就来教大家如何三分钟搭建MySQL主从。
准备与目标
我们需要准备两台内网互通的Linux机器
两台服务器都需要做以下准备工作
- 关闭防火墙
- 两台服务器提前安装好Docker
- 创建对应目录
mkdir -p /data/mysql-cluster/conf
mkdir -p /data/mysql-cluster/data
本文中我的机器
- 主服务器，内网ip
10.0.4.9 - 从服务器，内网ip
10.0.4.15
开始搭建
在主服务器执行
主库配置
vim /data/mysql-cluster/conf/my.cnf
[mysqld]
server_id=1
log-bin=mysql-bin
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
binlog_format=STATEMENT
#binlog_format=ROW
#binlog_format=MIXED
default-time-zone='+08:00'
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
[mysql]
default-character-set = utf8mb4
主库容器
[root@master ~]# docker run --name mysql-cluster-master -d -p 4306:3306 -e MYSQL_ROOT_PASSWORD=root -v /data/mysql-cluster/data:/var/lib/mysql -v /data/mysql-cluster/conf/my.cnf:/etc/mysql/conf.d/mysql.cnf mysql:8.0.31-debian
在从服务器执行
从库配置vim /data/mysql-cluster/conf/my.cnf
[mysqld]
server_id=2
log-bin=mysql-bin
read-only=1
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
default-time-zone='+08:00'
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
[mysql]
default-character-set = utf8mb4
从库容器
[root@slave1 ~]# docker run --name mysql-cluster-slave1 -d -p 4306:3306 -e MYSQL_ROOT_PASSWORD=root -v /data/mysql-cluster/data:/var/lib/mysql -v /data/mysql-cluster/conf/my.cnf:/etc/mysql/conf.d/mysql.cnf mysql:8.0.31-debian
配置主从同步
进入主库容器
[root@master ~]# docker exec -it mysql-cluster-master bash
root@c1a3c08ed656:/# mysql -proot
mysql> create user 'slave1'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> grant replication slave on *.* to 'slave1'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 831 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
进入从库，并执行同步语句:
需要注意下面命令中master_host、master_log_file、master_log_pos这几个字段需要改成你刚刚在主库看到的值
[root@slave1 ~]# docker exec -it mysql-cluster-slave1 bash
root@0fb246a42a89:/# mysql -proot
mysql> change master to master_host='10.0.4.9', master_user='slave1', master_password='123456', master_port=4306, master_log_file='mysql-bin.000003', master_log_pos=831;
Query OK, 0 rows affected, 9 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
你还可以在从库上使用下面的命令查看同步状态
show slave status\G
如果发现 status 中有任何错误，你需要马上终止从库同步:
stop slave;
排查完原因后, 可能需要重置同步:
reset slave;
重新 start slave 后当看到如下显示说明成功了:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.4.9
Master_User: slave1
Master_Port: 4306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1016
Relay_Log_File: 0fb246a42a89-relay-bin.000002
Relay_Log_Pos: 511
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,sys,information_schema,performance_schema
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: 1016
Relay_Log_Space: 728
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: 2fb556a9-8e80-11ed-8bd5-0242ac110002
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: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
验证主从同步
进入主库，并创建一个test数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
进入从库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
我们可以看到从库也有test这个数据库了，大功告成！
评论(0)