Debian 上 MySQL 数据同步实践
一、方案选型与准备
二、主从复制快速搭建(基于 Binary Log 位点)
主库配置(示例路径:/etc/mysql/mysql.conf.d/mysqld.cnf 或 /etc/mysql/my.cnf)
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
# 可选:仅同步指定库
# binlog_do_db = your_database_name
# 建议开启 SSL
ssl-ca = /etc/mysql/cacert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
重启:sudo systemctl restart mysql
主库创建复制用户并获取起始位点
mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'StrongPass!';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' REQUIRE SSL;
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
-- 记录 File(如 mysql-bin.000001)与 Position(如 123)
mysql> UNLOCK TABLES;
从库配置
[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
# 可选:级联复制时从库也写 binlog
# log_bin = /var/log/mysql/mysql-bin.log
# log_slave_updates = 1
read_only = 1
重启:sudo systemctl restart mysql
从库建立复制链路并启动
mysql> CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='replicator',
MASTER_PASSWORD='StrongPass!',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=123,
MASTER_SSL=1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
-- 确认 Slave_IO_Running=Yes 且 Slave_SQL_Running=Yes
验证:在主库写入数据,在从库查询是否一致。
三、常见变体与进阶
binlog_do_db,从库可用 replicate_do_db 或 replicate-wild-do-table=your_db.% 精确控制。read_only=1(不影响复制线程),避免业务误写。log_bin 与 log_slave_updates=1,作为上游主库继续向下游分发。四、一次性迁移或大表初始化
sudo rsync -aAXv \
--exclude={"/dev/*","/proc/*","/sys/*","/tmp/*","/run/*","/mnt/*","/media/*","/lost+found"} \
user@source:/var/lib/mysql/ /var/lib/mysql/
注意:拷贝前停止 MySQL,目标端配置 datadir 路径正确后再启动;确保两端 MySQL 版本兼容 与 网络稳定。五、运维与故障排查
SHOW SLAVE STATUS\G 中关注
STOP SLAVE;,按需 SET GLOBAL sql_slave_skip_counter=1; 跳过错误事件或修正数据/结构后 START SLAVE;;SHOW MASTER STATUS 并用 CHANGE MASTER TO ... 指定新的 File/Position;MASTER_HOST/USER/PASSWORD、网络与防火墙、以及 REQUIRE SSL 配置;