快捷導(dǎo)航

MySQL主從同步配置(一主一從)的實(shí)現(xiàn)

更新時(shí)間：2026年07月17日 08:33:59 作者：心靜自然涼800

前MySQL主從同步是生產(chǎn)環(huán)境中最常用的高可用方案之一,本文基于實(shí)際生產(chǎn)經(jīng)驗(yàn),整理出一套標(biāo)準(zhǔn)化的 MySQL 主從同步操作流程,具有一定的參考價(jià)值,感興趣的可以了解一下

前言：

MySQL 主從同步是生產(chǎn)環(huán)境中最常用的高可用方案之一，也是運(yùn)維工程師必備的基礎(chǔ)技能。然而在實(shí)際搭建過(guò)程中，備份時(shí)機(jī)、binlog 位置、用戶創(chuàng)建順序等細(xì)節(jié)問(wèn)題常常導(dǎo)致復(fù)制鏈路失敗。

本文基于實(shí)際生產(chǎn)經(jīng)驗(yàn)，整理出一套標(biāo)準(zhǔn)化的 MySQL 主從同步操作流程（SOP），每一步都經(jīng)過(guò)驗(yàn)證，確保“按步驟做就能成功”。

一、環(huán)境準(zhǔn)備

主機(jī)	IP地址
mysql1	192.168.88.10
mysql2	192.168.88.11

二、主服務(wù)器配置

#1.啟用binlog并為binlog起名：
[root@mysql1 ~]# sed -i '$a log_bin=mysql-bin' /etc/my.cnf.d/mysql-server.cnf
#2.檢查binlog日志是否開啟：
[root@mysql1 ~]# mysql -e "show variables like 'log_bin'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
#ON為已開啟binlog，OFF則是沒有開啟
#3.配置server_id，全局唯一標(biāo)識(shí)，不允許重復(fù)：
[root@mysql1 ~]# sed -i '$a server_id=10' /etc/my.cnf.d/mysql-server.cnf
#4.重啟mysqld服務(wù)
[root@mysql1 ~]# systemctl restart mysqld
#5.創(chuàng)建用于主從同步的用戶：
[root@mysql1 ~]# mysql -e "create user replicauser1@'192.168.88.11' identified by'123456';"
#注意：MySQL的用戶完整標(biāo)識(shí)是 用戶名@登錄主機(jī)，用戶名相同、host不同 = 兩個(gè)完全獨(dú)立賬號(hào)。
#6.給主從同步的用戶授權(quán)：
[root@mysql1 ~]# mysql -e "grant replication slave on *.* to replicauser1@'192.168.88.11'"
#*.*代表所有庫(kù)和所有表
#7.檢查主服務(wù)器是否配置ssl證書：
[root@mysql1 ~]# mysql -e "show variables like '%ssl'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
| have_ssl      | YES   |
+---------------+-------+
#YES表示配置了ssl證書
8.對(duì)數(shù)據(jù)全量備份：
[root@mysql1 ~]# mysqldump  -A > all.sql
#-A表示備份全部數(shù)據(jù)
9.將備份的數(shù)據(jù)發(fā)給192.168.88.11：
[root@mysql1 ~]# scp all.sql root@192.168.88.11:/root
10.查看主服務(wù)器信息：
[root@mysql1 ~]# mysql -e "show  master status;"
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      729 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
#file：當(dāng)前正在寫入的binlog文件名（從庫(kù)配置source_log_file要填這個(gè)名字）
#Position：binlog當(dāng)前寫入位置偏移量（從庫(kù)source_log_pos填這個(gè)）

三、從服務(wù)器配置

1.配置server_id，全局唯一標(biāo)識(shí)，不允許重復(fù)：
[root@mysql2 ~]# sed -i '$a server_id=11' /etc/my.cnf.d/mysql-server.cnf
2.重啟mysqld服務(wù)：
[root@mysql2 ~]# systemctl restart mysqld
3.導(dǎo)入備份過(guò)來(lái)的數(shù)據(jù)：
[root@mysql2 ~]# mysql < all.sql
4.驗(yàn)證數(shù)據(jù)是否導(dǎo)入成功：
[root@mysql2 ~]# mysql -e "select user,host from mysql.user;"
+------------------+---------------+
| user             | host          |
+------------------+---------------+
| replicauser1     | 192.168.88.11 |
| mysql.infoschema | localhost     |
| mysql.session    | localhost     |
| mysql.sys        | localhost     |
| root             | localhost     |
+------------------+---------------+
#replicauser1@192.168.88.11是我們剛才創(chuàng)建的用戶，出現(xiàn)代表恢復(fù)成功了
5.進(jìn)入mysql配置主服務(wù)器信息：
[root@mysql2 ~]# mysql
mysql> change replication source to          #修改主從同步主庫(kù)信息
       source_host="192.168.88.10",          #主庫(kù)地址
       source_port=3306,                     #主庫(kù)端口
       source_user="repilicauser1",          #用于主從同步的用戶（故意寫錯(cuò)用戶）
       source_password="123456",             #用于主從同步用戶的密碼
       source_ssl=1,                         #主服務(wù)器沒開啟則不需要加
       source_log_file="mysql-bin.000001",   #主服務(wù)器的binlog文件名
       source_log_pos=729;                   #主服務(wù)器的段偏移量
Query OK, 0 rows affected, 2 warnings (0.44 sec)
6.啟動(dòng)IO/SQL線程：
mysql> start replica;
7.驗(yàn)證主從同步是否設(shè)置成功：
mysql> show replica status\G
*************************** 1. row ***************************
             Replica_IO_State: Connecting to source       #IO線程一直嘗試連接主庫(kù)
                  Source_Host: 192.168.88.10              #主庫(kù)IP地址
                  Source_User: repilicauser1              #連接主庫(kù)的用戶名
                  Source_Port: 3306                       #主庫(kù)mysql的端口號(hào)
                Connect_Retry: 60                         #連接失敗再次連接的間隔
              Source_Log_File: mysql-bin.000001           #主庫(kù)binlog日志文件名
          Read_Source_Log_Pos: 729                        #IO線程讀到主庫(kù)的段偏移量
               Relay_Log_File: mysql2-relay-bin.000001    #從庫(kù)中繼日志文件名
                Relay_Log_Pos: 4                          #中繼日志回放位置
        Relay_Source_Log_File: mysql-bin.000001           #中繼日志對(duì)應(yīng)主庫(kù)原始的binlog文件
           Replica_IO_Running: Connecting                 #正在嘗試連接
          Replica_SQL_Running: Yes                        #sql回放線程運(yùn)行狀態(tài)
8.下拉查看錯(cuò)誤信息：
Replica_IO_Running: Connecting
Replica_SQL_Running: Yes
#IO線程正在連接
#SQL線程狀態(tài)正常
Last_IO_Errno: 1045
# IO線程最近一次錯(cuò)誤編號(hào)：1045
Last_IO_Error: Error connecting to source 'repilicauser1@192.168.88.10:3306'. 
This was attempt 1/86400, with a delay of 60 seconds between attempts.
Message: Access denied for user 'repilicauser1'@'mysql2' (using password: YES)
#錯(cuò)誤信息可以直接發(fā)給AI，由AI解讀
Last_SQL_Errno: 0
#回放線程無(wú)錯(cuò)誤
Last_SQL_Error:
#回放線程無(wú)錯(cuò)誤信息
9.停止IO/SQL線程
mysql> stop replica;
10.重置從服務(wù)器信息：
mysql> reset replica all;
11.重新配置主服務(wù)器信息：
[root@mysql2 ~]# mysql
mysql> change replication source to          #修改主從同步主庫(kù)信息
       source_host="192.168.88.10",          #主庫(kù)地址
       source_port=3306,                     #主庫(kù)端口
       source_user="replicauser1",           #用于主從同步的用戶（寫正確的用戶名）
       source_password="123456",             #用于主從同步用戶的密碼
       source_ssl=1,                         #主服務(wù)器沒開啟則不需要加
       source_log_file="mysql-bin.000001",   #主服務(wù)器的binlog文件名
       source_log_pos=729;                   #主服務(wù)器的段偏移量
Query OK, 0 rows affected, 2 warnings (0.44 sec)
12.啟動(dòng)IO/SQL線程：
mysql> start replica;
13.驗(yàn)證主從同步是否設(shè)置成功：
mysql> show replica status\G
*************************** 1. row ***************************
     Replica_IO_State: Waiting for source to send event
#等待主庫(kù)推送新數(shù)據(jù)，表示連接主庫(kù)成功
14.下拉查看信息：
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
#一切正常
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error:
#一切正常

四、驗(yàn)證主從同步效果

1.mysql1創(chuàng)建庫(kù)表，插入數(shù)據(jù)
[root@mysql1 ~]# mysql -e "create database hello;"
[root@mysql1 ~]# mysql -e "create table hello.hi(id int,name char(10));"
[root@mysql1 ~]# mysql -e "insert into hello.hi values (1,zhangsan)"
[root@mysql1 ~]# mysql -e "insert into hello.hi values (1,'zhangsan')"

2.mysql2查看數(shù)據(jù)，驗(yàn)證結(jié)果
[root@mysql2 ~]# mysql -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| hello              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@mysql2 ~]# mysql -D hello -e "show tables;"
+-----------------+
| Tables_in_hello |
+-----------------+
| hi              |
+-----------------+
[root@mysql2 ~]# mysql -e "select * from hello.hi"
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+