【MySQL】MySQL Replication 一主一備搭建步驟（傳統方式）

本文使用mysqldump 工具，以傳統備份恢復的方式搭建 MySQL Replication

一、  複製原理（一主一備）

step1: 主庫必須開啟 binlog ，主庫對資料的任何修改，都寫進二進位制日誌（ binlog ）中；

step2: 備庫的 IO 執行緒去讀主庫的二進位制日誌，然後將讀取的內容寫進備庫的中繼日誌（ relay log ）中

step3: 備庫的 SQL 執行緒讀取中繼日誌內容，並將內容重放到備庫資料庫中，以達到與主庫資料一致的目的。

二、  M-S 搭建步驟

1.  規劃

MySQL 版本 : 5.7

兩個節點已經安裝完MySQL 軟體，並完成資料庫初始化

 

2.  修改兩節點/etc/hosts 檔案

172.16.71.111 node1

172.16.71.112 node2

3.  主庫建立測試庫，用於驗證資料

mysql> create database test_db;

mysql> create table test_db.test_t1(id int,name varchar(50));

mysql> insert into test_db.test_t1 values(1,'a');

mysql> insert into test_db.test_t1 values(2,'b');

mysql> select * from test_db.test_t1;

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | b    |

+------+------+

2 rows in set (0.00 sec)

4.  master （主庫）部署配置

1）  開啟log-bin

編輯/etc/my.cnf 檔案，新增以下內容

log-bin

server-id=1

重啟mysql

# service mysqld restart

2）  建立新使用者並授權，用於slave 機器訪問 master 庫

mysql> grant replication slave,replication client on *.*

to 'rep'@'172.16.71.%' identified by 'P@ssw0rd123';

mysql> flush privileges;

（在授權過程中，就會建立相應的使用者）

3）  驗證建立的使用者

mysql> select user,host   from mysql.user;

+---------------+-------------+

| user           | host        |

+---------------+-------------+

| rep               | 172.16.71.% |

| mysql.session | localhost   |

| mysql.sys        | localhost   |

| root              | localhost   |

+---------------+-------------+

4 rows in set (0.00 sec)

4）  master 庫執行備份

$ mysqldump -p'P@ssw0rd' --all-databases --single-transaction --master-data=1 --flush-logs > `date +%F`-mysql-all.sql

注：這裡的 master-data=1 會在備份檔案中新增一行

CHANGE MASTER TO MASTER_LOG_FILE='node1-bin.000005', MASTER_LOG_POS=154;

這樣在備庫匯入資料時，已經指定了 MASTER_LOG_FILE 和 MASTER_LOG_POS 引數。

5）  將備份檔案傳給slave 節點

$ scp 2021-02-25-mysql-all.sql node2:/home/mysql/

6）  master 庫再次插入資料

注：這次插入的資料並不在之前的備份檔案中

mysql> insert into test_db.test_t1 values(3,'c');

mysql> insert into test_db.test_t1 values(4,'d');

mysql> select * from test_db.test_t1;

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | b    |

|    3 | c    |

|    4 | d    |

+------+------+

4 rows in set (0.00 sec)

5.  slave （備庫）部署配置

1）  測試主庫新建的用於複製的使用者“ rep ”在備庫是否可用

$ mysql -hnode1 -urep -p'P@ssw0rd123'

mysql> show grants;

+---------------------------------------------------------------------------+

| Grants for rep@172.16.71.%                                                |

+---------------------------------------------------------------------------+

| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rep'@'172.16.71.%' |

+---------------------------------------------------------------------------+

2）  備庫編輯/etc/my.cnf 檔案

新增以下內容

relay_log=/usr/local/mysql/data/node2-relay-bin

server-id= 2

read_only=1

重啟 mysql

# service mysqld restart

3）  將主庫傳過來的sql 檔案匯入到備庫

（備庫執行）

$ mysql -uroot -p'P@ssw0rd'

mysql> set sql_log_bin=0;

mysql> source /home/mysql/2021-02-25-mysql-all.sql

mysql> set sql_log_bin= 1 ;

mysql> select * from test_db.test_t1;

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | b    |

+------+------+

2 rows in set (0.00 sec)

4）  配置備庫啟動連線主庫

mysql> change master to

master_host='node1',

master_user='rep',

master_password='P@ssw0rd123';

注：如果匯入資料使用作業系統命令執行，那麼還需要指定以下兩個引數：

master_log_file='node1-bin.000005'

master_log_pos=154

5）  開啟slave

mysql> start slave;

mysql> show slave status\G;

Slave_IO_Running: Yes

      Slave_SQL_Running:   Yes

上面的兩個程式需要yes ，狀態就是正常的。

6）  查詢備庫測試表

mysql> select * from test_db.test_t1;

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | b    |

|    3 | c    |

|    4 | d    |

+------+------+

4 rows in set (0.00 sec)

此時，MySQL replication 已經配置完成（一主一備）

7）  再次驗證一下資料

主庫插入一條資料

mysql> insert into test_db.test_t1 values(5,'e');

備庫查詢

mysql> select * from test_db.test_t1;

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | b    |

|    3 | c    |

|    4 | d    |

|     5 | e    |

+------+------+

5 rows in set (0.00 sec)

注： read_only=1 只讀模式，限定的是普通使用者進行資料修改的操作，但不會限定具有 super 許可權的使用者的資料修改操作 ( 但是如果設定了 "super_read_only=on" ， 則就會限定具有 super 許可權的使用者的資料修改操作了 ) 。

 

---- end ----