MySQL主从配置全攻略

MySQL主从配置详解

环境准备

确保主从服务器已安装相同版本的MySQL，建议使用5.7或更高版本。检查服务器网络互通性，关闭防火墙或开放3306端口。在主从服务器上创建专用复制账号，例如：

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

主服务器配置

修改my.cnf文件（通常位于/etc/my.cnf或/etc/mysql/my.cnf），添加以下参数：

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_do_db = 需要复制的数据库名
sync_binlog = 1

重启MySQL服务使配置生效。使用命令查看主服务器状态：

SHOW MASTER STATUS;

记录File和Position值，从服务器配置时需要。

从服务器配置

修改从服务器my.cnf文件：

[mysqld]
server-id = 2
relay_log = mysql-relay-bin
read_only = 1
replicate_do_db = 需要复制的数据库名

重启从服务器MySQL服务。配置复制链路：

CHANGE MASTER TO
MASTER_HOST='主服务器IP',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='记录的File值',
MASTER_LOG_POS=记录的Position值;

启动复制进程：

START SLAVE;

验证配置

检查从服务器复制状态：

SHOW SLAVE STATUS\G

确认Slave_IO_Running和Slave_SQL_Running均为Yes，Seconds_Behind_Master值正常。在主服务器执行数据变更，观察从服务器是否同步。

常见问题处理

若出现复制错误，可临时跳过：

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

如需重建复制，先在主服务器锁表备份：

FLUSH TABLES WITH READ LOCK;

使用mysqldump导出数据后解锁：

UNLOCK TABLES;

高级配置建议

对于生产环境，建议启用GTID复制：

gtid_mode = ON
enforce_gtid_consistency = ON

配置半同步复制提高数据安全性：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

BbS.okacop030.info/PoSt/1120_867459.HtM
BbS.okacop031.info/PoSt/1120_466716.HtM
BbS.okacop032.info/PoSt/1120_014863.HtM
BbS.okacop033.info/PoSt/1120_510884.HtM
BbS.okacop034.info/PoSt/1120_566873.HtM
BbS.okacop035.info/PoSt/1120_508533.HtM
BbS.okacop036.info/PoSt/1120_057400.HtM
BbS.okacop037.info/PoSt/1120_741818.HtM
BbS.okacop038.info/PoSt/1120_644211.HtM
BbS.okacop039.info/PoSt/1120_455204.HtM
BbS.okacop040.info/PoSt/1120_651808.HtM
BbS.okacop041.info/PoSt/1120_571930.HtM
BbS.okacop042.info/PoSt/1120_916306.HtM
BbS.okacop043.info/PoSt/1120_849679.HtM
BbS.okacop044.info/PoSt/1120_764047.HtM
BbS.okacop045.info/PoSt/1120_556835.HtM
BbS.okacop046.info/PoSt/1120_760313.HtM
BbS.okacop047.info/PoSt/1120_769708.HtM
BbS.okacop048.info/PoSt/1120_708556.HtM
BbS.okacop049.info/PoSt/1120_756333.HtM
BbS.okacop040.info/PoSt/1120_502768.HtM
BbS.okacop041.info/PoSt/1120_970383.HtM
BbS.okacop042.info/PoSt/1120_483616.HtM
BbS.okacop043.info/PoSt/1120_331135.HtM
BbS.okacop044.info/PoSt/1120_837819.HtM
BbS.okacop045.info/PoSt/1120_504894.HtM
BbS.okacop046.info/PoSt/1120_639266.HtM
BbS.okacop047.info/PoSt/1120_974542.HtM
BbS.okacop048.info/PoSt/1120_155469.HtM
BbS.okacop049.info/PoSt/1120_487152.HtM
BbS.okacop040.info/PoSt/1120_141898.HtM
BbS.okacop041.info/PoSt/1120_240434.HtM
BbS.okacop042.info/PoSt/1120_009155.HtM
BbS.okacop043.info/PoSt/1120_100000.HtM
BbS.okacop044.info/PoSt/1120_180003.HtM
BbS.okacop045.info/PoSt/1120_794385.HtM
BbS.okacop046.info/PoSt/1120_156150.HtM
BbS.okacop047.info/PoSt/1120_331689.HtM
BbS.okacop048.info/PoSt/1120_178565.HtM
BbS.okacop049.info/PoSt/1120_664755.HtM
BbS.okacop040.info/PoSt/1120_452046.HtM
BbS.okacop041.info/PoSt/1120_700403.HtM
BbS.okacop042.info/PoSt/1120_521103.HtM
BbS.okacop043.info/PoSt/1120_007949.HtM
BbS.okacop044.info/PoSt/1120_756812.HtM
BbS.okacop045.info/PoSt/1120_677677.HtM
BbS.okacop046.info/PoSt/1120_146828.HtM
BbS.okacop047.info/PoSt/1120_864358.HtM
BbS.okacop048.info/PoSt/1120_082688.HtM
BbS.okacop049.info/PoSt/1120_106317.HtM
BbS.okacop040.info/PoSt/1120_740492.HtM
BbS.okacop041.info/PoSt/1120_551251.HtM
BbS.okacop042.info/PoSt/1120_013595.HtM
BbS.okacop043.info/PoSt/1120_914235.HtM
BbS.okacop044.info/PoSt/1120_212011.HtM
BbS.okacop045.info/PoSt/1120_874019.HtM
BbS.okacop046.info/PoSt/1120_782718.HtM
BbS.okacop047.info/PoSt/1120_634004.HtM
BbS.okacop048.info/PoSt/1120_467489.HtM
BbS.okacop049.info/PoSt/1120_601101.HtM
BbS.okacop040.info/PoSt/1120_295001.HtM
BbS.okacop041.info/PoSt/1120_223183.HtM
BbS.okacop042.info/PoSt/1120_473132.HtM
BbS.okacop043.info/PoSt/1120_625981.HtM
BbS.okacop044.info/PoSt/1120_863124.HtM
BbS.okacop045.info/PoSt/1120_192870.HtM
BbS.okacop046.info/PoSt/1120_102555.HtM
BbS.okacop047.info/PoSt/1120_314584.HtM
BbS.okacop048.info/PoSt/1120_127272.HtM
BbS.okacop049.info/PoSt/1120_344229.HtM
BbS.okacop040.info/PoSt/1120_340000.HtM
BbS.okacop041.info/PoSt/1120_496796.HtM
BbS.okacop042.info/PoSt/1120_281931.HtM
BbS.okacop043.info/PoSt/1120_606139.HtM
BbS.okacop044.info/PoSt/1120_109152.HtM
BbS.okacop045.info/PoSt/1120_658573.HtM
BbS.okacop046.info/PoSt/1120_987189.HtM
BbS.okacop047.info/PoSt/1120_717583.HtM
BbS.okacop048.info/PoSt/1120_778016.HtM
BbS.okacop049.info/PoSt/1120_389604.HtM