MySQL
假设有如下服务器用于设置 MySQL 主从：
- MySQL 服务器：
192.168.1.4用于作为主库（以下简称 MySQL Master） - MySQL 服务器：
192.168.1.5用于作为从库（以下简称 MySQL Slave）
1. 配置 MySQL Master#
登录 MySQL Master 192.168.1.4 并切换至工作目录
cd /data/tencent-survey-v23.10修改 docker-compose-pass.yaml 文件 mysql-server-cluster 的 command 部分
# 主从模式下, master, slave server-id 不能相同command: - --default-authentication-plugin=mysql_native_password - --server-id=1 - --read-only=02. 配置 MySQL Slave#
登录 MySQL Slave 192.168.1.5 并切换至工作目录
cd /data/tencent-survey-v23.10修改 docker-compose-pass.yaml 文件 mysql-server-cluster 的 command 部分
# 主从模式下, master, slave server-id 不能相同# slave read-only=1command: - --default-authentication-plugin=mysql_native_password - --server-id=2 - --read-only=13. 启动 MySQL 主从模式#
3.1 启动 MySQL Master#
# 进入 Node 1 192.168.1.4docker compose -f docker-compose-paas.yaml up mysql-server-cluster3.2 启动 MySQL Slave#
# 进入 Node 2 192.168.1.5docker compose -f docker-compose-paas.yaml up mysql-server-cluster3.3 设置主从复制#
!!! 注意：是连接到 MySQL Slave 192.168.1.5 执行命令
MySQL 客户端：mysql 或 docker run --rm -t cdchub.tencentcloudcr.com/public/mysql:8.0-oracle mysql
mysql -h192.168.1.5 -uroot -p${MYSQL_ROOT_PASSWORD} -e \"CHANGE MASTER TO MASTER_HOST='192.168.1.4', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='${MYSQL_ROOT_PASSWORD}', MASTER_AUTO_POSITION=1;start slave;"3.4 检测验证#
检测主从复制是否成功
mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 9.134.75.215 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000004 Read_Master_Log_Pos: 1081 # 这里要同 MySQL Master 的 show master status 里的信息一致 Relay_Log_File: VM_79_44_centos-relay-bin.000008 Relay_Log_Pos: 451 Relay_Master_Log_File: binlog.000004 Slave_IO_Running: Yes # 这里要 yes Slave_SQL_Running: Yes # 这里要 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: 1081 Relay_Log_Space: 1185 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 # 这里要 0Master_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: 63351b8d-310a-11ee-8fa2-52540024952d 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: 63351b8d-310a-11ee-8fa2-52540024952d:1-9 Executed_Gtid_Set: 4372da78-310b-11ee-8fb1-5254008eded4:1-5,63351b8d-310a-11ee-8fa2-52540024952d:1-9 Auto_Position: 1 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.01 sec)检测 read_only 是否设置成功
mysql> SHOW global VARIABLES LIKE '%read_only%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_read_only | OFF || read_only | ON || super_read_only | OFF || transaction_read_only | OFF |+-----------------------+-------+4 rows in set (0.01 sec)检测 server_id 是否设置成功, server_uuid 是否唯一
mysql> SHOW global VARIABLES LIKE '%server_%';+---------------------------------+--------------------------------------+| Variable_name | Value |+---------------------------------+--------------------------------------+| innodb_ft_server_stopword_table | || server_id | 2 || server_id_bits | 32 || server_uuid | 4372da78-310b-11ee-8fb1-5254008eded4 |+---------------------------------+--------------------------------------+4 rows in set (0.00 sec)4. 修改数据库连接地址#
登录所有应用服务器并切换至工作目录，修改 .env 中以下配置为 MySQL Master 的实际连接信息：
MAIN_HOST=mysql-server:3306MAIN_DATABASE=ur_mainMAIN_USERNAME=rootMAIN_PASSWORD=$MYSQL_ROOT_PASSWORD
# answer shard1DB2_HOST=mysql-server:3306DB2_DATABASE=new_urDB2_USERNAME=rootDB2_PASSWORD=$MYSQL_ROOT_PASSWORD
# answer shard2DB3_HOST=mysql-server:3306DB3_DATABASE=new_urDB3_USERNAME=rootDB3_PASSWORD=$MYSQL_ROOT_PASSWORD
# answer shard3DB6_HOST=mysql-server:3306DB6_DATABASE=new_urDB6_USERNAME=rootDB6_PASSWORD=$MYSQL_ROOT_PASSWORD
# answer shard4DB7_HOST=mysql-server:3306DB7_DATABASE=new_urDB7_USERNAME=rootDB7_PASSWORD=$MYSQL_ROOT_PASSWORD注意，需要在所有应用服务器中重复执行
修改完成后，使用以下命令重启所有应用服务：
docker compose -f docker-compose.yaml restart