创建网络(测试使用)
由于我的是mac, 并且是个伪主从，所以需要创建一个网络环境，用于主从节点互相通信。
docker network create mysql_network;
1. 安装主节点
1.1 编写docker-compose文件
version: '3.1' services: mysql-master: image: mysql:8.0.20 restart: always environment: MYSQL_ROOT_PASSWORD: root command: --default-authentication-plugin=mysql_native_password --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --explicit_defaults_for_timestamp=true --lower_case_table_names=1 ports: - 3307:3306 volumes: - ./my.cnf:/etc/mysql/my.cnf - ./data:/var/lib/mysql #正常情况无需该配置 networks: default: external: name: mysql_network
1.2 编写my.cnf文件
[mysqld] server-id=1 log_bin=master-bin log_bin-index=master-bin.index skip-name-resolve pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Custom config should go here !includedir /etc/mysql/conf.d/
1.3 启动
docker-compose up -d
2. 安装从节点
2.1 编写docker-compose文件
version: '3.1' services: mysql-slave: image: mysql:8.0.20 restart: always environment: MYSQL_ROOT_PASSWORD: root command: --default-authentication-plugin=mysql_native_password --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --explicit_defaults_for_timestamp=true --lower_case_table_names=1 ports: - 3307:3306 volumes: - ./my.cnf:/etc/mysql/my.cnf - ./data:/var/lib/mysql #正常情况无需该配置 networks: default: external: name: mysql_network
2.2 编写my.cnf文件
[mysqld] server-id=2 #打开MySQL中继日志 relay-log-index=slave-relay-bin.index relay-log=slave-relay-bin #打开从服务二进制日志 log-bin=binlog #使得更新的数据写进二进制日志中 log-slave-updates=1 # 设置3306端口 port=3306 pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Custom config should go here !includedir /etc/mysql/conf.d/
3.3 启动
docker-compose up -d
3. 主从配置
3.1 主节点配置
#进入到主节点 docker exec -it <主节点容器id> /bin/bash #登陆mysql mysql -uroot -p #开启远程登录 use mysql; update user set host='%' where user='root'; flush privileges; GRANT REPLICATION SLAVE ON *.* TO 'root'@'%'; flush privileges; #查看主节点同步状态： show master status;
状态一般长这样：
+---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000005 | 1712 | | | | +---------------+----------+--------------+------------------+-------------------+
File: 当前写的binlog日志的名称
Position: 当前写的binlog日志的位置
3.2 从节点配置
#进入到从节点 docker exec -it <从节点容器id> /bin/bash #登陆mysql mysql -uroot -p #开启远程登录 use mysql; update user set host='%' where user='root'; flush privileges; show master status; #关键步骤！！ #设置同步主节点 CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='root', MASTER_LOG_FILE='master-bin.000005', MASTER_LOG_POS=1712, GET_MASTER_PUBLIC_KEY=1; #MASTER_HOST是主节点的容器service-name, docker网络知识 #MASTER_PORT是主节点的容器端口 #MASTER_LOG_FILE 和 MASTER_LOG_POS是上一步查看主节点状态的两个值 #开启slave start slave; #查看主从同步状态 show slave status; 或者用 show slave status \G; 这样查看比较简洁
或者用 show slave status \G; 这样查看比较简洁
3.3 查看是否配置成功
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql-master Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000004 Read_Master_Log_Pos: 1676 Relay_Log_File: slave-relay-bin.000005 Relay_Log_Pos: 1893 Relay_Master_Log_File: master-bin.000004 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: 1676 Relay_Log_Space: 2271 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: c681fe4e-5fde-11eb-99b5-0242ac170002 Master_Info_File: mysql.slave_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: Master_public_key_path: Get_master_public_key: 1 Network_Namespace: 1 row in set (0.00 sec)
看到状态：Slave_IO_State: Waiting for master to send event
关键的两个值： Slave_IO_Running: Yes, Slave_SQL_Running: Yes
4. 主从集群测试
略
