Linux MySQL 主從配置

[TOC]

主從數(shù)據(jù)庫(kù)原理

主庫(kù) master

當(dāng) master 每做一次 write 操作，就會(huì)將操作的命令記錄到 master 的 Binary-log（bin-log）日志中。

從庫(kù) slave

slave 中默認(rèn)會(huì)有兩條線程

IO Thread：讀取 master 中的 bin-log 內(nèi)容，寫入到 slave 中的 relay-log。

SQL Thread：slave 讀取 relay-log，將操作寫入到 slave 數(shù)據(jù)庫(kù)。

一、云服務(wù)器配置

1.1 騰訊云

（1）在騰訊云控制臺(tái)上，設(shè)置主數(shù)據(jù)庫(kù)外網(wǎng)訪問權(quán)限

（2）修改 MySQL配置文件，設(shè)置 3306 端口外部訪問權(quán)限

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

將 bind_address 的值由 '127.0.0.1'改成你騰訊云內(nèi)網(wǎng)IP地址。

重啟 MySQL 服務(wù)器，然后輸入 netstat -anpt|grep 3306 檢查3306端口是否顯示騰訊云的內(nèi)網(wǎng)IP。

1.2 阿里云

（1）修改主機(jī) MySQL 配置文件：

vi /etc/mysql/mysql.conf.d/mysqld.cnf

將 bind_address 一行注釋掉。

（2）然后在阿里云 ECS 控制臺(tái)上，為主機(jī)添加網(wǎng)絡(luò)安全組規(guī)則：

入方向
協(xié)議類型選擇 MySQL(3306)
授權(quán)類型改為地址段訪問
授權(quán)對(duì)象輸入：0.0.0.0/0

從機(jī)可以不用對(duì)外開放端口 3306。

二、MySQL 主機(jī) master 配置

2.0 創(chuàng)建主機(jī) MySQL 數(shù)據(jù)庫(kù)

創(chuàng)建主機(jī)數(shù)據(jù)庫(kù)：mytest

create database mytest default character set utf8;

2.1 修改 MySQL 配置文件

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

在 [mysqld] 模塊下添加如下內(nèi)容（默認(rèn)是注釋的，解除注釋即可）：

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = mytest # 需要做主從分離，主服務(wù)器 MySQL 的數(shù)據(jù)庫(kù)名
binlog_ignore_db        = mysql # 在主服務(wù)器上需要忽略的 MySQL 數(shù)據(jù)庫(kù)，避免從服務(wù)器對(duì)主服務(wù)器的安全性影響

數(shù)據(jù)表名忽略大小寫，在 [mysqld] 模塊下添加如下內(nèi)容：

lower_case_table_names=1

修改完之后，記得要重啟 MySQL 服務(wù)：

/etc/init.d/mysql restart;

2.2 創(chuàng)建一個(gè) MySQL 數(shù)據(jù)庫(kù)用戶，提供給從機(jī) slave 訪問

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

說明:

  • username - 你將創(chuàng)建的用戶名

  • host - 指定該用戶在哪個(gè)主機(jī)上可以登陸。如果是本地用戶可用 localhost， 如果想讓該用戶可以從任意遠(yuǎn)程主機(jī)登陸，可以使用通配符 % 。

  • password - 該用戶的登陸密碼，密碼可以為空，如果為空則該用戶可以不需要密碼登陸服務(wù)器.。

舉例：

CREATE USER 'mytest'@'%' IDENTIFIED BY 'mytest123!@#';

2.3 給剛才創(chuàng)建的用戶授權(quán)

grant all privileges on *.* to 'mytest'@'%'identified by 'mytest123!@#' with grant option;
flush privileges;  -- 更新權(quán)限，使之生效
  • all privileges 表示授權(quán)全部的權(quán)限。如果單獨(dú)指定權(quán)限，可以替換成：insert、select、update、delete 等等；
  • *.* 表示這些權(quán)限是對(duì)所有的 數(shù)據(jù)庫(kù).表 等生效。如果要單獨(dú)指定，可以按照此格式 databasename.tablename ；
  • with grant option 表示創(chuàng)建的這個(gè)用戶還可以為其他用戶繼續(xù)賦予這些權(quán)限。

2.4 賦予從機(jī)復(fù)制主機(jī)數(shù)據(jù)的權(quán)力

從機(jī)可以通過主機(jī)創(chuàng)建的這個(gè)用戶連接上主機(jī)數(shù)據(jù)庫(kù)，然后復(fù)制主機(jī)的數(shù)據(jù)到從機(jī)數(shù)據(jù)庫(kù)。

GRANT REPLICATION SLAVE ON *.* TO 'mytest'@'%' IDENTIFIED BY 'mytest123!@#';

2.5 重啟主機(jī) MySQL 服務(wù)

sudo /etc/init.d/mysql restart

2.6 登錄主機(jī) MySQL，檢查主機(jī) master 的狀態(tài)：

show master status;

會(huì)顯示如下內(nèi)容（例子）：

   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      154 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)

注意記錄 File 和 Position 的值，在從機(jī) slave 配置時(shí)會(huì)用到該屬性。

注：執(zhí)行完此步驟后不要再操作主服務(wù)器 MySQL，防止主服務(wù)器 master 狀態(tài)值變化。如果對(duì)主機(jī)數(shù)據(jù)庫(kù)做了增刪改操作，會(huì)導(dǎo)致 Position 值發(fā)生變化。

如果是在主節(jié)點(diǎn)開啟主從配置之前就已經(jīng)創(chuàng)建的表，是不會(huì)同步到從節(jié)點(diǎn)中。

三、MySQL 從機(jī) slave 配置

3.1 修改從機(jī) MySQL 配置文件

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

在 [mysqld] 模塊中添加如下內(nèi)容：

server-id = 2
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index

主機(jī)中是 server-id = 1，而從機(jī)與主機(jī)不一樣，注意區(qū)分。

3.2 重啟從機(jī) MySQL 服務(wù)

sudo /etc/init.d/mysql restart

3.3 登錄從機(jī) MySQL，配置與主服務(wù)器 master 的連接

使用 root 帳號(hào)登錄到從機(jī) MySQL，執(zhí)行以下命令，將從機(jī)與主機(jī)關(guān)聯(lián)：

-- 例子
change master to master_host='120.77.219.39',master_port=3306,master_user='mytest',master_password='mytest123!@#',master_log_file='mysql-bin.000001',master_log_pos=154;

說明：

  • master_host 對(duì)應(yīng)主服務(wù)器的外網(wǎng)IP地址 120.77.219.39
  • master_port 對(duì)應(yīng)主服務(wù)器的端口（3306）
  • master_log_file 對(duì)應(yīng) show master status 顯示的 File 列：mysql-bin.000001
  • master_log_pos 對(duì)應(yīng)前面記錄主機(jī)中顯示的 Position 的值，否則有可能出現(xiàn)同步失敗。

3.4 在從服務(wù)器上創(chuàng)建同名從數(shù)據(jù)庫(kù)和同名用戶

該數(shù)據(jù)庫(kù)名稱需要與主服務(wù)器上 MySQL 配置文件中定義的 binlog_do_db = mytest 名稱一致：

create database mytest default character set utf8;

創(chuàng)建與主數(shù)據(jù)庫(kù)同名的用戶（非必需）：

CREATE USER 'mytest'@'%' IDENTIFIED BY 'mytest123!@#'; 
grant all privileges on *.* to 'mytest'@'%'identified by 'mytest123!@#' with grant option;
flush privileges;  -- 更新權(quán)限，使之生效

注意：“創(chuàng)建同名用戶” 這一步是非必需的，但為了方便讀寫，建議對(duì) master 或 slave 數(shù)據(jù)庫(kù)訪問采用同樣的一套用戶名密碼。

3.5 重啟 從服務(wù)器（slave）上的 MySQL 服務(wù)

/etc/init.d/mysql restart

3.6 登錄從機(jī) MySQL，啟用 slave 數(shù)據(jù)同步

登錄 MySQL 后，執(zhí)行如下命令：

start slave;

如果出現(xiàn)錯(cuò)誤：

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

說明 relay log 即 mysql.slave_relay_log_info 表中保留了以前的主從復(fù)制信息，導(dǎo)致新從庫(kù)啟動(dòng)時(shí)無法找到對(duì)應(yīng)文件，那么我們只需要清理掉該表中的記錄就可以了。注意，不要手動(dòng)刪該表數(shù)據(jù)，MySQL 已經(jīng)提供了解決方法，輸入命令：

reset slave;

執(zhí)行成功后，再來執(zhí)行一遍啟動(dòng) slave 同步：

start slave;

補(bǔ)充幾個(gè)個(gè)與 slave 相關(guān)的命令：

  1. 停止當(dāng)前在運(yùn)行的 slave
stop slave;
  1. 顯示當(dāng)前 slave 的狀態(tài)
show slave status;
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

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