用途
- 快速运行一个mysql-slave 的容器，同步 mysql主库的数据
- 可用于一些从库数据的调试
- 可用于临时的主从数据备份
项目地址
- 源码地址： https://cnb.cool/srebro/mysql-slave
使用方法
MYSQL_ROOT_PASSWORD: "srebro@2024" # 从库root密码
MYSQL_REPLICATION_USER: "repluser" # 主从同步账户
MYSQL_REPLICATION_PASSWORD: "srebro@test" # 同步账户密码
MYSQL_MASTER_HOST: "172.16.8.137" # 主库IP地址
MYSQL_MASTER_PORT: 3306 # 主库端口
MYSQL_BACKUP_USER: "user" #数据库备份用户主库root一般不让远程登录()
MYSQL_BACKUP_USER_PASSWORD: "srebro@2024" #数据库备份用户密码
MYSQL_BACKUP_DATABASES: "cmdb01,cmdb02,cmdb03" # 需要备份的数据库，需要主从同步的数据库（多个数据库逗号分隔）
TZ: 'Asia/Shanghai' # 时区
command: --server-id=138 --relay-log=mysqld-relay-bin --replicate-do-db=cmdb01 --replicate-do-db=cmdb02 --replicate-do-db=cmdb03 #指定server-id 值； 指定--relay-log 名称； 指定同步的数据库，--replicate-do-db=cmdb01 --replicate-do-db=cmdb02 --replicate-do-db=cmdb03 多个库要分开写,有几个写几个
- 定义 healthcheck 健康检查的 MYSQL_ROOT_PASSWORD
healthcheck:
test: ["CMD", "mysqladmin", "ping", "-u", "root", "-psrebro@2024"]
- 根据自己的需求定义my.cnf 配置文件[以下参数仅供参考]
[mysqld]
# MySQL服务运行的用户
user=mysql
# MySQL服务监听的端口
port=3306
# 最大连接数
max_connections=1000
# 默认的存储引擎
default-storage-engine=INNODB
# 服务器默认字符集
character-set-server=utf8mb4
# 服务器默认的校对规则
collation_server = utf8mb4_general_ci
# 禁用域名解析，提高性能，只允许IP地址进行客户端登录
skip-name-resolve=1
# 表名不区分大小写，1表示开启
lower_case_table_names=1
# 日志时间戳格式，SYSTEM表示使用系统时间
log_timestamps=SYSTEM
# 设置默认的时区
default-time-zone = '+8:00'
# 错误日志文件的存储路径
log-error=/var/lib/mysql/mysqld.log
# 从库不进行同步的数据库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=sys
replicate-ignore-db=performance_schema
# 禁用SSL
skip_ssl
# MySQL服务的Unix套接字文件
socket=/var/run/mysqld/mysqld.sock
# 启用的SQL模式，定义了MySQL的行为
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# 最大允许的数据包大小
max_allowed_packet=512M
[mysql]
# 客户端使用的Unix套接字文件
socket=/var/run/mysqld/mysqld.sock
# 客户端默认字符集
default-character-set = utf8mb4
[client]
# 客户端程序默认使用的字符集
default-character-set = utf8mb4
运行
docker-compose up -d
bash-4.2# mysql -uroot -psrebro@2024 -e "show slave status\G;"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.8.137
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000034
Read_Master_Log_Pos: 56784180
Relay_Log_File: mysqld-relay-bin.000070
Relay_Log_Pos: 56784393
Relay_Master_Log_File: mysql-bin.000034
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: cmdb01,cmdb02,cmdb03
Replicate_Ignore_DB: mysql,information_schema,sys,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: 56784180
Relay_Log_Space: 56784648
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: 37
Master_UUID: 2740c8cc-b6b4-11ed-909f-000c29ae36d9
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:
其他
mysql> grant replication slave on *.* to repluser@"%" identified by "srebro@test";
