docker下的mysql主从同步
先介绍一下我搭建使用的环境：centos7
坑1：mysqld: Can't read dir of '/etc/mysql/conf.d/'，这是因为Centos7安全Selinux禁止了一些安全权限，导致mysql和mariadb在进行挂载/var/lib/mysql的时候会提示如下信息
logs命令查看，发现提示信息是：chown: changing ownership of ‘/var/lib/mysql/....‘: Permission denied
于是，解决方法有这么两个：
- 在docker run中加入 --privileged=true 给容器加上特定权限
- 关闭selinux
本人采用的是第一种方式
坑2：CHANGE MASTER TO MASTER_HOST='192.168.150.129',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='binlog.000002',MASTER_LOG_POS=761; 我在linux上配置了主机名与ip的映射，因此我再master_host填写的是我的主机名，但是一直连不上，后来改为ip地址即可
主从配置文件
master配置文件/home/mysql/etc/master/master.cnf
- [mysqld]
- server_id = 1
-
- character-set-server=utf8mb4
- collation-server=utf8mb4_unicode_ci
- default-storage-engine=INNODB
-
- #Optimize omit
-
- sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
-
- log-bin = /var/lib/mysql/binlog
- log_bin_trust_function_creators=1
- binlog_format = ROW
- expire_logs_days = 99
- sync_binlog = 0
-
- slow-query-log=1
- slow-query-log-file=/var/log/mysql/slow-queries.log
- long_query_time = 3
- log-queries-not-using-indexes
slave配置文件/home/mysql/etc/slave/slave.cnf
- [mysqld]
- server_id = 2
-
- character-set-server=utf8mb4
- collation-server=utf8mb4_unicode_ci
- default-storage-engine=INNODB
-
- #Optimize omit
-
- sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
-
- log-bin = /var/lib/mysql/binlog
- log_bin_trust_function_creators=1
- binlog_format = ROW
- expire_logs_days = 99
- sync_binlog = 0
-
- relay_log=slave-relay-bin
- log-slave-updates=1
- slave-skip-errors=all
-
- slow-query-log=1
- slow-query-log-file=/var/log/mysql/slow-queries.log
- long_query_time = 3
log-queries-not-using-indexes
启动容器,配置主从复制
创建数据目录：
- mkdir -p /home/mysql/data/master
- mkdir -p /home/mysql/data/slave
-
- MASTER_DIR=/home/mysql/data/master
- SLAVE_DIR=/home/mysql/data/slave
启动MySQL容器：
- docker run --name mysql-master \
- -v /home/mysql/etc/master:/etc/mysql/conf.d \
- -v /home/mysql/data/master:/var/lib/mysql \
- -e MYSQL_ROOT_PASSWORD=root \
- -d \
- mysql:5.7
-
-
- docker run --name mysql-slave \
- -v /home/mysql/etc/slave:/etc/mysql/conf.d \
- -v /home/mysql/data/slave:/var/lib/mysql \
- -e MYSQL_ROOT_PASSWORD=root \
- --link mysql-master:master \
- -d mysql:5.7
Master上创建复制用户：
- docker exec -it mysql-master mysql -p
-
- CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
- GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
查看Master节点 Binlog状态：
- show master status\G
- *************************** 1. row ***************************
- File: binlog.000008
- Position: 595
- Binlog_Do_DB:
- Binlog_Ignore_DB:
- Executed_Gtid_Set:
- 1 row in set (0.00 sec)
在Slave节点上配置复制：
- docker exec -it mysql-slave mysql -p
-
- CHANGE MASTER TO \
- MASTER_HOST='master',\
- MASTER_PORT=3306,\
- MASTER_USER='repl',\
- MASTER_PASSWORD='repl',\
- MASTER_LOG_FILE='binlog.000008',\
- MASTER_LOG_POS=595;
-
- start slave;
-
查看Salve状态：
- show slave status\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: master
- Master_User: repl
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: binlog.000008
- Read_Master_Log_Pos: 595
- Relay_Log_File: slave-relay-bin.000002
- Relay_Log_Pos: 317
- Relay_Master_Log_File: binlog.000008
- 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: 595
- Relay_Log_Space: 524
- 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: 7a5a452f-fca9-11e6-be18-0242ac110002
- 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:
- 1 row in set (0.00 sec)
