MySQL主從

MySQL主從同步配置

1. 主從同步的定義

主從同步使得數(shù)據(jù)可以從一個數(shù)據(jù)庫服務(wù)器復(fù)制到其他服務(wù)器上，在復(fù)制數(shù)據(jù)時，一個服務(wù)器充當(dāng)主服務(wù)器（master），其余的服務(wù)器充當(dāng)從服務(wù)器（slave）。因為復(fù)制是異步進行的，所以從服務(wù)器不需要一直連接著主服務(wù)器，從服務(wù)器甚至可以通過撥號斷斷續(xù)續(xù)地連接主服務(wù)器。通過配置文件，可以指定復(fù)制所有的數(shù)據(jù)庫，某個數(shù)據(jù)庫，甚至是某個數(shù)據(jù)庫上的某個表。

使用主從同步的好處：

  • 通過增加從服務(wù)器來提高數(shù)據(jù)庫的性能，在主服務(wù)器上執(zhí)行寫入和更新，在從服務(wù)器上向外提供讀功能，可以動態(tài)地調(diào)整從服務(wù)器的數(shù)量，從而調(diào)整整個數(shù)據(jù)庫的性能。
  • 提高數(shù)據(jù)安全，因為數(shù)據(jù)已復(fù)制到從服務(wù)器，從服務(wù)器可以終止復(fù)制進程，所以，可以在從服務(wù)器上備份而不破壞主服務(wù)器相應(yīng)數(shù)據(jù)
  • 在主服務(wù)器上生成實時數(shù)據(jù)，而在從服務(wù)器上分析這些數(shù)據(jù)，從而提高主服務(wù)器的性能

2. 主從同步的機制

[圖片上傳失敗...(image-699086-1558281788204)]

Mysql服務(wù)器之間的主從同步是基于二進制日志機制，主服務(wù)器使用二進制日志來記錄數(shù)據(jù)庫的變動情況，從服務(wù)器通過讀取和執(zhí)行該日志文件來保持和主服務(wù)器的數(shù)據(jù)一致。

在使用二進制日志時，主服務(wù)器的所有操作都會被記錄下來，然后從服務(wù)器會接收到該日志的一個副本。從服務(wù)器可以指定執(zhí)行該日志中的哪一類事件（譬如只插入數(shù)據(jù)或者只更新數(shù)據(jù)），默認(rèn)會執(zhí)行日志中的所有語句。

每一個從服務(wù)器會記錄關(guān)于二進制日志的信息：文件名和已經(jīng)處理過的語句，這樣意味著不同的從服務(wù)器可以分別執(zhí)行同一個二進制日志的不同部分，并且從服務(wù)器可以隨時連接或者中斷和服務(wù)器的連接。

主服務(wù)器和每一個從服務(wù)器都必須配置一個唯一的ID號（在my.cnf文件的[mysqld]模塊下有一個server-id配置項），另外，每一個從服務(wù)器還需要通過CHANGE MASTER TO語句來配置它要連接的主服務(wù)器的ip地址，日志文件名稱和該日志里面的位置（這些信息存儲在主服務(wù)器的數(shù)據(jù)庫里）

3. 配置主從同步的基本步驟

有很多種配置主從同步的方法，可以總結(jié)為如下的步驟：

  1. 在主服務(wù)器上，必須開啟二進制日志機制和配置一個獨立的ID
  2. 在每一個從服務(wù)器上，配置一個唯一的ID，創(chuàng)建一個用來專門復(fù)制主服務(wù)器數(shù)據(jù)的賬號
  3. 在開始復(fù)制進程前，在主服務(wù)器上記錄二進制文件的位置信息
  4. 如果在開始復(fù)制之前，數(shù)據(jù)庫中已經(jīng)有數(shù)據(jù)，就必須先創(chuàng)建一個數(shù)據(jù)快照（可以使用mysqldump導(dǎo)出數(shù)據(jù)庫，或者直接復(fù)制數(shù)據(jù)文件）
  5. 配置從服務(wù)器要連接的主服務(wù)器的IP地址和登陸授權(quán)，二進制日志文件名和位置

4. 詳細(xì)配置主從同步的方法

主和從的身份可以自己指定，我們將虛擬機Ubuntu中MySQL作為主服務(wù)器，將Windows中的MySQL作為從服務(wù)器。 在主從設(shè)置前，要保證Ubuntu與Windows間的網(wǎng)絡(luò)連通。

4.1 備份主服務(wù)器原有數(shù)據(jù)到從服務(wù)器

如果在設(shè)置主從同步前，主服務(wù)器上已有大量數(shù)據(jù)，可以使用mysqldump進行數(shù)據(jù)備份并還原到從服務(wù)器以實現(xiàn)數(shù)據(jù)的復(fù)制。

4.1.1 在主服務(wù)器Ubuntu上進行備份，執(zhí)行命令：

mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql

[圖片上傳失敗...(image-3db9b1-1558281788204)]

說明

  • -u ：用戶名
  • -p ：示密碼
  • --all-databases ：導(dǎo)出所有數(shù)據(jù)庫
  • --lock-all-tables ：執(zhí)行操作時鎖住所有表，防止操作時有數(shù)據(jù)修改
  • ~/master_db.sql :導(dǎo)出的備份數(shù)據(jù)（sql文件）位置，可自己指定

4.1.2 在從服務(wù)器Windows上進行數(shù)據(jù)還原

找到Windows上mysql命令的位置

[圖片上傳失敗...(image-56348a-1558281788204)]

新打開的命令窗口，在這個窗口中可以執(zhí)行類似在Ubuntu終端中執(zhí)行的mysql命令

[圖片上傳失敗...(image-c1e223-1558281788204)]

將從主服務(wù)器Ubuntu中導(dǎo)出的文件復(fù)制到從服務(wù)器Windows中，可以將其放在上面mysql命令所在的文件夾中，方便還原使用

[圖片上傳失敗...(image-fc3348-1558281788204)]

在剛打開的命令黑窗口中執(zhí)行還原操作:

mysql –uroot –pmysql < master_db.sql

[圖片上傳失敗...(image-f2c87c-1558281788204)]

4.2 配置主服務(wù)器master（Ubuntu中的MySQL）

4.2.1 編輯設(shè)置mysqld的配置文件，設(shè)置log_bin和server-id

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[圖片上傳失敗...(image-5a2770-1558281788204)]

[圖片上傳失敗...(image-b204e6-1558281788204)]

4.2.2 重啟mysql服務(wù)

sudo service mysql restart

[圖片上傳失敗...(image-ef5225-1558281788203)]

4.2.3 登入主服務(wù)器Ubuntu中的mysql，創(chuàng)建用于從服務(wù)器同步數(shù)據(jù)使用的帳號

mysql –uroot –pmysql


GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';


FLUSH PRIVILEGES;

[圖片上傳失敗...(image-5a0275-1558281788203)]

4.2.4 獲取主服務(wù)器的二進制日志信息

SHOW MASTER STATUS;

[圖片上傳失敗...(image-452c28-1558281788203)]

File為使用的日志文件名字，Position為使用的文件位置，這兩個參數(shù)須記下，配置從服務(wù)器時會用到

4.3 配置從服務(wù)器slave（Windows中的MySQL）

4.3.1 找到Windows中MySQL的配置文件

[圖片上傳失敗...(image-ed97b3-1558281788203)]

4.3.2 編輯my.ini文件，將server-id修改為2，并保存退出。

[圖片上傳失敗...(image-c14c63-1558281788203)]

4.3.3 打開windows服務(wù)管理

可以在開始菜單中輸入services.msc找到并運行

[圖片上傳失敗...(image-e31850-1558281788203)]

4.3.4 在打開的服務(wù)管理中找到MySQL57，并重啟該服務(wù)

[圖片上傳失敗...(image-cee4a8-1558281788203)]

5. 進入windows的mysql，設(shè)置連接到master主服務(wù)器

change master to master_host='10.211.55.5', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=590;

注：

  • master_host：主服務(wù)器Ubuntu的ip地址
  • master_log_file: 前面查詢到的主服務(wù)器日志文件名
  • master_log_pos: 前面查詢到的主服務(wù)器日志文件位置

[圖片上傳失敗...(image-502e9-1558281788203)]

6. 開啟同步，查看同步狀態(tài)

[圖片上傳失敗...(image-be7870-1558281788203)]

7. 測試主從同步

在Ubuntu的MySQL中（主服務(wù)器）創(chuàng)建一個數(shù)據(jù)庫 [圖片上傳失敗...(image-34095c-1558281788203)]

在Windows的MySQL中（從服務(wù)器）查看新建的數(shù)據(jù)庫是否存在 [圖片上傳失敗...(image-c57f6a-1558281788203)]

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容