MYSQL一個設備上的主從複製實現-windows

• 2021 年 7 月 28 日
• 筆記
• He_資料庫

只記錄一次在一個設備上實現mysql主從複製的過程，很詳細，建議收藏，用到的時候照著步驟做就可以，會記錄所有需要注意到的細節和一些容易遇到的坑以及解決辦法！

如果需要在同一台電腦（伺服器）上實現mysql的主從複製備份，需要一個主資料庫和一個從資料庫。

本文使用的是mysql-5.6.39-winx64免安裝版。安裝版的也可以，需要自行找到安裝生成的data所在目錄以及my.ini文件位置。

因為在這之前我已經安裝好了主資料庫，本文只記錄從資料庫的安裝過程，（主資料庫也是個全新的，會記錄所有的配置）。

主資料庫埠號 port = 3307。

從資料庫埠號 port = 3308 。

安裝第二個從mysql：

找到第一個安裝的mysql目錄，完整的複製到另一個磁碟里（因為我的是免安裝的，如果有安裝包或者壓縮包可以重新安裝一個，我懶。）（我這裡放到了一起，好操作，實際看情況為了好區分，我在後面加了後綴-copy

編輯copy資料庫的my.ini:

port = 3308

添加環境變數：1、右擊我的電腦-2、點擊屬性-3、點擊高級系統設置-4、點擊環境變數

在系統變數里添加兩個資料庫的變數，不添加的話兩個mysql容易亂（兩個mysql後面分別加了個1、2區分） 

 然後在系統變數里的PATH里追加變數值：

%MYSQL_HOME1%\bin     %MYSQL_HOME2%\bin

將兩個mysql註冊成windows服務：

使用管理員命令窗口進入mysql安裝目錄下的bin目錄里：

兩個資料庫分別操作輸入服務安裝命令：

（MYSQL2_copy 是服務名字，可以隨意自定義）

E:\MYSQL\mysql-5.6.39-winx64-copy\bin\mysqld install MYSQL2_copy --defaults-file="E:\MYSQL\mysql-5.6.39-winx64-copy\my.ini"

（補充：移除服務的命令：mysqld remove MYSQL2_copy）

運行提示下面內容表示服務安裝成功：

Service successfully installed.

可以進入服務頁面查看（服務頁面進入方法：右擊我的電腦，點擊管理，然後點擊服務和應用下的服務即可）：

 啟動mysql服務：

一：命令啟動：

net start MYSQL1

二：服務列表啟動（右擊服務，點擊啟動）

修改主資料庫的my.ini配置：

在[mysqld]標籤下添加：

# 伺服器id 可自定義
server-id = 3307
# 開啟二進位日誌
log-bin=mysql-bin
log-bin-index=master-bin.index
relay_log=mysql-relay-bin
# 需要備份的資料庫
binlog-do-db=test
# 不需要備份的資料庫
binlog-ignore-db=mysql

保存退出，重啟主資料庫服務

binlog-do-db用於指定需要同步的資料庫，binlog-ignore-db指定不需要同步的資料庫，如果這兩個參數都不設置，則從伺服器會複製主伺服器的所有資料庫

一般同步的話會新建一個專門用在該場景的用戶，所以我們需要在主伺服器上創建一個新的用戶（如user_cp,密碼123456）

方法如下：

打開管理員命令窗口，切換至主資料庫目錄下的bin目錄下，用 root 賬戶連接MySQL主伺服器(-u帳號 -p密碼)

mysql -uroot -proot --protocol=tcp --host=localhost --port=3307

創建新用戶（@後面的ip地址為允許連接的客戶端的ip地址。）： 

create user 'user_cp'@'127.0.0.1' identified by '123456';

給新用戶配置主從複製的許可權（@後面的ip地址為允許連接的客戶端的ip地址，如果改為 ‘%’，就表示客戶端沒有ip地址的限制）：

grant replication slave on *.* to 'user_cp'@'127.0.0.1' identified by '123456';

如果主伺服器中的資料庫「test」中，已經有數據，我們需要先手動把主伺服器中的數據複製到從伺服器中，為了防止我們複製數據的時候，資料庫中的test里的數據發生更新變化，我們可以先鎖定資料庫。

方法：

使用全局鎖

flush tables with read lock;

這個命令是全局讀鎖定，它會給主伺服器中的所有資料庫都加上讀鎖，這裡順便說一下讀鎖和寫鎖的區別：
read lock（讀鎖）：也叫共享鎖，允許所有的讀操作，但阻塞寫操作，即所有連接只可以讀數據，但不允許寫數據。
write lock（寫鎖）：也叫排它鎖、獨佔鎖，只允許當前連接的讀和寫，不允許其他並發的讀操作和寫操作。
鎖定主伺服器的資料庫後，我們在從伺服器中，也創建一個資料庫test，並將所有的表（包括表結構和表數據）都導入。
然後，我們執行下面的命令，解鎖：

unlock tables;  

查看主伺服器的master狀態（記住列表中的File 和 Position的值，等會配置從伺服器的時候需要用到）：

show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 |     497  |     test     | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+

從伺服器配置：

修改從伺服器的資料庫配置文件E:\MYSQL\mysql-5.6.39-winx64-copy\my.ini：

[mysqld]標籤下添加：

# 埠號
port = 3308
# 服務id 可自定義
server_id = 3308
# 開啟二進位日誌（從伺服器不是必須要開啟二進位日誌）
log-bin=mysql-bin 

保存退出，重啟MySQL服務。

使用管理員命令窗口登錄:

mysql -uroot -proot --protocol=tcp --host=localhost --port=3308

然後複製以下內容運行（需要修改裡面的內容，不要複製直接運行）：

change master to master_host='127.0.0.1',master_user='user_cp',master_password='123456',master_port=3307,master_log_file='mysql-bin.000013',master_log_pos=497;

參數詳解：

master_host： 主伺服器的IP

master_user： 主伺服器上新創建的用戶名

master_password： 用戶的密碼

master_port： 主伺服器的埠，如果未曾修改，默認即可。

master_log_file： 主伺服器二進位日誌文件的名稱，填寫查看主伺服器的master狀態時顯示的File的值

master_log_pos： 日誌的位置，填寫查看主伺服器的master狀態時顯示的Position的值

啟動從伺服器的slave複製功能：

start slave;

停止主從複製功能：

stop slave;

查看從伺服器的slave狀態：

show slave status \G;

如果 Slave_IO_Running 和 Slave_SQL_Running 的值都為 Yes，則說明主從複製的所有配置已成功，即從伺服器已經可以自動與主伺服器的資料庫數據實現同步了。

此後，只要主伺服器的數據有更新（比如：在 test資料庫中新建了一張表或者表中的數據發生了變化），從伺服器都會自動與主伺服器保持一致。但如果有人刻意改變了從伺服器的數據，主伺服器中的數據並不會同步更新，除非我們把這兩個MySQL伺服器設置為互為主從。