MySQL主从配置

挂载目录跟配置文件- 8.0.22

https://blog.csdn.net/u013068184/article/details/107691389?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~Rate-1.pc_relevant_default&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~Rate-1.pc_relevant_default&utm_relevant_index=2

启动命令

# master shell 
docker run -p 7001:3306 --name mysql_master --privileged=true \
    -v /tn/docker-conf/mysql/mysql-master/log:/var/log/mysql \
    -v /tn/docker-conf/mysql/mysql-master/data:/var/lib/mysql \
    -v /tn/docker-conf/mysql/mysql-master/conf:/etc/mysql \
    -v /tn/docker-conf/mysql/mysql-master/mysql-files:/var/lib/mysql-files \
    -e lower_case_table_names=1 \
    -e MYSQL_ROOT_PASSWORD=root \
    -d mysql:8.0.26
# master cmd  
docker run -p 7001:3306 --name mysql_master --privileged=true -v /D/docker/docker-conf/mysql/mysql-master/log:/var/log/mysql -v /D/docker/docker-conf/mysql/mysql-master/data:/var/lib/mysql -v /C/Users/docker-conf/mysql/mysql-master:/etc/mysql -v /D/docker/docker-conf/mysql/mysql-master/mysql-files:/var/lib/mysql-files -e lower_case_table_names=1 -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0.26  
#slave shell
docker run -p 7002:3306 --name mysql_slave --privileged=true \
    -v /tn/docker-conf/mysql/mysql-slave/log:/var/log/mysql \
    -v /tn/docker-conf/mysql/mysql-slave/data:/var/lib/mysql \
    -v /tn/docker-conf/mysql/mysql-slave/conf:/etc/mysql \
    -v /tn/docker-conf/mysql/mysql-slave/mysql-files:/var/lib/mysql-files \
    -e lower_case_table_names=1 \
    -e MYSQL_ROOT_PASSWORD=root \
    -d mysql:8.0.26
#slave cmd     
docker run -p 7002:3306 --name mysql_slave --privileged=true -v /D/docker/docker-conf/mysql/mysql-slave/log:/var/log/mysql -v /D/docker/docker-conf/mysql/mysql-slave/data:/var/lib/mysql -v /C/Users/docker-conf/mysql/mysql-slave:/etc/mysql -v /D/docker/docker-conf/mysql/mysql-slave/mysql-files:/var/lib/mysql-files -e lower_case_table_names=1 -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0.26

mysql_master/conf/my.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
default_authentication_plugin=mysql_native_password
default-storage-engine=INNODB
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
# 主从复制链路(局域网)中服务id不能相同
server-id=101
# 启用二进制日志
log-bin=mysql-master-bin
# 不开启binlog的数据库
binlog-ignore-db=information_schema 
binlog-ignore-db=sys
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
# 开启binlog的数据库，不能用逗号隔开
# binlog-do-db=demo
# binlog-do-db=db1
# binlog-do-db=db2
# binlog-do-db=db3
# binlog格式 mixed statement row
binlog_format=mixed 
# binlog最大大小，超过这个值会创建新的binlog
# max_binlog_size=32m
# binlog缓存大小，执行sql的时候会创建缓存，所有session的内存超过最大值，会报错
binlog_cache_size=1m
# binlog最大缓存大小
max_binlog_cache_size=128m
# 过期清理 默认0，表示不清理
expire_logs_days=7
# 跳过错误,避免slave端复制中断
slave_skip_errors=1062
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
# Custom config should go here
!includedir /etc/mysql/conf.d/

mysql_slave/conf/my.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
default_authentication_plugin=mysql_native_password
default-storage-engine=INNODB
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
# 服务id，不能与其他主、从服务的id相同
server-id=102
# 启用二进制日志
log-bin=mysql-slave1-bin
# 主从服复制忽略的数据库
binlog_ignore_db=information_schema 
binlog_ignore_db=sys
binlog_ignore_db=performance_schema
binlog_ignore_db=mysql
# binlog格式 mixed statement row
binlog_format=mixed 
# binlog最大大小，超过这个值会创建新的binlog
#  max_binlog_size=32m
# binlog缓存大小，执行sql的时候会创建缓存，所有session的内存超过最大值，会报错
binlog_cache_size=1m
# binlog最大缓存大小
max_binlog_cache_size=128m
# 过期清理 默认0，表示不清理
expire_logs_days=7
# 跳过错误,避免slave端复制中断
slave_skip_errors=1062
# slave 将复制事件写进自己的二进制日志
log_slave_updates=1
# slave 设置为只读 （super权限除外）
read_only=1
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
# Custom config should go here
!includedir /etc/mysql/conf.d/

其余步骤

# master (主从最好在创一个用户)
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
FLUSH PRIVILEGES;
show master status \G;
# slave
## 查看主节点容器的内部ip地址, `docker inspect imageID` 
### 我这里用 `docker inspect imageID` 查询的ip用不了，所以使用的时宿主机的IP
-- 1 
stop slave; (8.0.22+ stop REPLICA)
-- 2
## 8.0.23+ masster_  change source_
change master to 
master_host='127.0.0.1', 
master_user='root', 
master_password='root', 
master_port=7001, 
master_log_file='binlog.000005', 
master_log_pos=156, 
master_connect_retry=30;
change master to 
source_host='127.0.0.1', 
source_user='root', 
source_password='root', 
source_port=7001, 
source_log_file='binlog.000005', 
source_log_pos=156, 
source_connect_retry=30;
-- 3
start slave; (8.0.22+ start REPLICA)
-- 4
## 况下的查看结果SlaveIORunning 和 SlaveSQLRunning 都是Yes
show slave status \G; (8.0.22+ SHOW REPLICA STATUS;)

手动设置 server_id

server_id 出现冲突的临时处理方法

show variables like '%server_id%';
 SET GLOBAL server_id=2;

我出现过一次配置文件中设置server-id不生效的问题

• 在挂载的 /xx/mysql/conf/ 文件夹中没有 conf.d 文件夹
• mysqld: [Warning] World-writable config file ‘/etc/mysql/my.cnf’ is ignored.
  • my.cnf的权限不能为777

    # 正确的权限 644  或则 444（chmod a-w my.cnf）
    -rw-r--r--. 1 root root 1320 Jan 23 09:11 my.cnf

查看配置文件使用信息

mysql —help|grep my.cnf

注意

https://blog.csdn.net/kinginblue/article/details/76863962

windows

mysql: [Warning] World-writable config file ‘/etc/mysql/my.cnf’ is ignored

1. 配置文件跟data log等目录分开存放，要不然会出权限问题
   1. 配置文件存放到 C:\Users (Windows) or /Users (Mac)
   2. data等其他的文件 !c 盘就行了