mysql主主同步

适用于 Docker 环境以挂载目录为部署方式mysql主要是通过bin_log日志同步下面以这两个ip为示例直接部署上部署一、服务器ipNode A:11.16.212.196Node B:11.16.212.197二、配置my.cnf文件Node A (11.16.212.196) —/root/bim/mysql/conf/my.cnf[client]default_character_setutf8mb4[mysql]default_character_setutf8mb4[mysqld]server_id 1log_bin mysql-binlog_replica_updates ONgtid_mode OFFenforce_gtid_consistency OFFskip_name_resolve 1character_set_server utf8mb4collation_server utf8mb4_unicode_ciinit_connect SET NAMES utf8mb4auto_increment_increment 2auto_increment_offset 1caching_sha2_password_auto_generate_rsa_keys ONrequire_secure_transport OFFbinlog_expire_logs_seconds 604800Node B (11.16.212.197) —/root/bim/mysql/conf/my.cnf[client]default_character_setutf8mb4[mysql]default_character_setutf8mb4[mysqld]server_id 2log_bin mysql-binlog_replica_updates ONgtid_mode OFFenforce_gtid_consistency OFFskip_name_resolve 1character_set_server utf8mb4collation_server utf8mb4_unicode_ciinit_connect SET NAMES utf8mb4auto_increment_increment 2auto_increment_offset 2caching_sha2_password_auto_generate_rsa_keys ONrequire_secure_transport OFFbinlog_expire_logs_seconds 604800三、使用启动命令启动mysql两台都执行docker run \--name mysql8 \-p 3306:3306 \--privilegedtrue \--restartalways \-v /root/bim/mysql/conf/my.cnf:/etc/mysql/my.cnf \-v /root/bim/mysql/logs:/var/log/mysql \-v /root/bim/mysql/data:/var/lib/mysql \-v /etc/localtime:/etc/localtime \-e MYSQL_ROOT_PASSWORDAdmin123! \-e TZAsia/Shanghai \-d mysql四、创建复制用户两台都执行执行前需要确定mysql已经启动成功需要确定账号密码是否是Admin123!使用navicat可以连接上docker exec -i mysql8 mysql -h 127.0.0.1 -uroot -pAdmin123! EOF CREATE USER IF NOT EXISTS repl% IDENTIFIED BY Pssw0rd; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES; EOF五、获取 binlog 位置关键需要确定账号密码是否是Admin123!执行命令后记录拍照或复制出来输出信息在11.16.212.196Node A执行docker exec -i mysql8 mysql -h 127.0.0.1 -uroot -pAdmin123! -e SHOW BINARY LOG STATUS;执行后会输出------------------------------------| File | Position |-------------------------------------| mysql-bin.000001 | 157 |------------------------------------在11.16.212.197Node B执行docker exec -i mysql8 mysql -h 127.0.0.1 -uroot -pAdmin123! -e SHOW BINARY LOG STATUS;执行后会输出------------------------------------| File | Position |-------------------------------------| mysql-bin.000001 | 156 |------------------------------------假设得到实际值以你执行结果为准Node A: Filemysql-bin.000001, Position157Node B: Filemysql-bin.000001, Position156六、配置双向复制使用真实 IP 和 Position执行前对命令进行更改结合自己实际IP 和Position将 SOURCE_HOST、SOURCE_LOG_FILE、SOURCE_LOG_POS替换为查到的真实值注意账号、密码、端口为自己实际配置在Node B (11.16.212.197)上执行 → 同步Node A 的数据docker exec -i mysql8 mysql -h 127.0.0.1 -uroot -pAdmin123! EOF STOP REPLICA; CHANGE REPLICATION SOURCE TO SOURCE_HOST11.16.212.196, SOURCE_PORT3306, SOURCE_USERrepl, SOURCE_PASSWORDPssw0rd, SOURCE_LOG_FILEmysql-bin.000004, SOURCE_LOG_POS1896322, SOURCE_SSL0, GET_SOURCE_PUBLIC_KEY1; START REPLICA; EOF在Node A (11.16.212.196)上执行 → 同步Node B 的数据docker exec -i mysql8 mysql -h 127.0.0.1 -uroot -pAdmin123! EOF STOP REPLICA; CHANGE REPLICATION SOURCE TO SOURCE_HOST11.16.212.197, SOURCE_PORT3306, SOURCE_USERrepl, SOURCE_PASSWORDPssw0rd, SOURCE_LOG_FILEmysql-bin.000003, SOURCE_LOG_POS1896267, SOURCE_SSL0, GET_SOURCE_PUBLIC_KEY1; START REPLICA; EOF七、验证复制状态两台都执行docker exec -i mysql8 mysql -h 127.0.0.1 -uroot -pAdmin123! -e SHOW REPLICA STATUS\G | grep -E Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Source|Last_IO_Error|Last_SQL_Error正常输出Replica_IO_Running: YesReplica_SQL_Running: YesSeconds_Behind_Source: 0八、防火墙放行如启用------两台都执行firewall-cmd --permanent --add-rich-rulerule familyipv4 source address11.16.212.0/24 port port3306 protocoltcp accept firewall-cmd --reload九、测试两边是否同步在11.16.212.196中创建一个表在11.16.212.197中查看是否有相同的表如果有这个表在11.16.212.197相同表中添加一个数据在11.16.212.196相同表中查看是否有相同数据以上验证没问题就是同步了完结