Linux下MySQL主從複製(Binlog)的部署過程

上古南城發表於2021-08-23

LinuxMySql

什麼是 MySQL 的主從複製

Mysql內建的複製功能是構建大型高效能應用程式的基礎, 將Mysql資料分佈到多個系統上，這種分佈機制是通過將Mysql某一臺主機資料複製到其它主機（slaves）上，並重新執行一遍來實現的。複製過程中一個伺服器充當主伺服器，而一個或多個其它伺服器充當從伺服器。主伺服器將更新寫入二進位制日誌檔案，並維護檔案的一個索引以跟蹤日誌迴圈。這些日誌可以記錄傳送到從伺服器的更新。當一個從伺服器連線主伺服器時，它通知主伺服器從伺服器在日誌中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新，然後封鎖並等待主伺服器通知新的更新。

為什麼需要主從複製

1. 資料分佈 (Data distribution )
2. 負載平衡(load balancing)
3. 據備份(Backups) ，保證資料安全
4. 高可用性和容錯行(High availability and failover)
5. 實現讀寫分離，緩解資料庫壓力

MySQL複製所帶來的優勢在於

擴充套件能力：通過複製功能可以將MySQL的效能壓力分擔到一個或多個slave上。這要求所有的寫操作和修改操作都必須在Master上完成，而讀操作可以被分配到一個或多個slave上；將讀寫分離到不同伺服器執行之後， MySQL的讀寫效能得到提升。
資料庫備份：由於從例項是同步主例項的資料，所以可以將備份作業部署到從庫。
資料分析和報表：同樣，一些資料分析和報表的實現可以在從例項執行，以減少對主庫的效能影響。
容災能力：可以在物理距離較遠的另一個資料中心建立一個slave，保證在主例項所在地區遭遇災難時，在另一個資料中心能快速恢復。

MySQL複製有兩種方法：

傳統方式：基於主庫的bin-log將日誌事件和事件位置複製到從庫，從庫再加以應用來達到主從同步的目的。
Gtid方式： global transaction identifiers是基於事務來複制資料，因此也就不依賴日誌檔案，同時又能更好的保證主從庫資料一致性。

MySQL複製有多種型別：

非同步複製：客戶端傳送DDL/DML語句給master，master執行完畢立即返回成功資訊給客戶端，而不管slave是否已經開始複製。這樣的複製方式導致的問題是，當master寫完了binlog，而slave還沒有開始複製或者複製還沒完成時，slave上和master上的資料暫時不一致，且此時master突然當機，slave將會丟失一部分資料。如果此時把slave提升為新的master，那麼整個資料庫就永久丟失這部分資料。
同步複製：客戶端傳送DDL/DML語句給master，master執行完畢後還需要等待所有的slave都寫完了relay log才認為此次DDL/DML成功，然後才會返回成功資訊給客戶端。同步複製的問題是master必須等待，所以延遲較大，在MySQL中不使用這種複製方式。
半同步複製：在非同步複製的基礎上，確保任何一個主庫上的事務在提交之前至少有一個從庫已經收到該事務並日志記錄下來，即客戶端傳送DDL/DML語句給master，master執行完畢後還要等待一個slave寫完relay log並返回確認資訊給master，master才認為此次DDL/DML語句是成功的，然後才會傳送成功資訊給客戶端。半同步複製只需等待一個slave的回應，且等待的超時時間可以設定，超時後會自動降級為非同步複製，所以在區域網內(網路延遲很小)使用半同步複製是可行的
延遲複製：在非同步複製的基礎上，人為設定主庫和從庫的資料同步延遲時間，即保證資料延遲至少是這個引數

MySQL複製有三種核心格式：

基於語句的複製: 在主伺服器執行SQL語句，在從伺服器執行同樣語句。MySQL預設採用基於語句的複製，效率較高。一旦發現沒法精確複製時, 會自動選基於行的複製。
基於行的複製: 把改變的內容複製過去，而不是把命令在從伺服器上執行一遍. 從mysql5.0開始支援
混合型別的複製: 預設採用基於語句的複製，一旦發現基於語句的無法精確的複製時，就會採用基於行的複製。

MySQL 的複製原理

master 伺服器將資料的改變記錄二進位制 binlog 日誌，當 master 上的資料發生改變時，則將其改變寫入二進位制日誌中；
slave 伺服器會在一定時間間隔內對 master 二進位制日誌進行探測其是否發生改變，如果發生改變，則開始一個 I/OThread 請求 master 二進位制事件；
同時主節點為每個 I/O 執行緒啟動一個 dump 執行緒，用於向其傳送二進位制事件，並儲存至從節點本地的中繼日誌中，從節點將啟動 SQL 執行緒從中繼日誌中讀取二進位制日誌，在本地重放，使得其資料和主節點的保持一致，最後 I/OThread 和 SQLThread 將進入睡眠狀態，等待下一次被喚醒。

也就是：

從庫會生成兩個執行緒,一個 I/O 執行緒,一個 SQL 執行緒；
I/O 執行緒會去請求主庫的 binlog，並將得到的 binlog 寫到本地的 relay-log（中繼日誌）檔案中；主庫會生成一個 log dump 執行緒,用來給從庫 I/O 執行緒傳 binlog；
SQL 執行緒，會讀取 relay log 檔案中的日誌，並解析成sql語句逐一執行。

注意：

master 將操作語句記錄到 binlog 日誌中，然後授予 slave 遠端連線的許可權（master 一定要開啟 binlog 二進位制日誌功能；通常為了資料安全考慮，slave 也開啟binlog功能）；
slave 開啟兩個執行緒：IO 執行緒和 SQL 執行緒。其中：IO 執行緒負責讀取 master 的 binlog 內容到中繼日誌 relay log 裡；SQL 執行緒負責從 relay log 日誌裡讀出 binlog 內容，並更新到 slave 的資料庫裡，這樣就能保證 slave 資料和 master 資料保持一致了；
MySQL 複製至少需要兩個 MySQL 的服務，當然 MySQL 服務可以分佈在不同的伺服器上，也可以在一臺伺服器上啟動多個服務；
MySQL複製最好確保 master 和 slave 伺服器上的 MySQL 版本相同（如果不能滿足版本一致，那麼要保證 master 主節點的版本低於 slave 從節點的版本）；
master 和 slave 兩節點間時間需同步。
預設情況下，MySQL的複製是非同步的。slave可以不用一直連著master，即使中間斷開了也能從斷開的position處繼續進行復制。
複製是基於binlog的position進行的，複製之前必須保證position一致。(注：這是傳統的複製方式所要求的)
二進位制日誌目的是為了恢復定點資料庫和主從複製，所以出於安全和功能考慮，極不建議將二進位制日誌和datadir放在同一磁碟上。

複製全域性：

複製過濾：

具體步驟：

第一步、在主伺服器上記錄二進位制日誌。在每個更新資料的事務完成之前，主伺服器都會將資料更改記錄到二進位制日誌中。即使事務在執行期間是交錯的，mysql也會序列地將事務寫入到二進位制日誌中。在把事件寫入二進位制日誌之後，主伺服器告訴儲存引擎提交事務。
第二步、從伺服器把主伺服器的二進位制日誌拷貝到自己的硬碟上，進入所謂的“中繼日誌”中。首先，它啟動一個工作執行緒，叫I/O執行緒，這個I/O執行緒開啟一個普通的客戶端連線，然後啟動一個特殊的二進位制日誌轉儲程式（它沒有相應的SQL命令）。這個轉儲程式從主伺服器的二進位制日誌中讀取資料。它不會對事件進行輪詢。如果3跟上了主伺服器，就會進入休眠狀態並等待有新的事件發生時主伺服器發出的訊號。I/O執行緒把資料寫入從伺服器的中繼日誌中。
第三步、SQL執行緒讀取中繼日誌，並且重放其中的事件，然後更新從伺服器的資料。由於這個執行緒能跟上I/O執行緒，中繼日誌通常在作業系統的快取中，所以中繼日誌的開銷很低。SQL執行緒執行事件也可以被寫入從伺服器自己的二進位制日誌中，它對於有些場景很實用。

配置主從複製，可以總結為如下的步驟：

1．在主伺服器上，必須開啟二進位制日誌機制和配置一個獨立的ID
2．在每一個從伺服器上，配置一個唯一的ID，建立一個用來專門複製主伺服器資料的賬號
3．在開始複製程式前，在主伺服器上記錄二進位制檔案的位置資訊
4．如果在開始複製之前，資料庫中已經有資料，就必須先建立一個資料快照（可以使用mysqldump匯出資料庫，或者直接複製資料檔案）
5．配置從伺服器要連線的主伺服器的IP地址和登陸授權，二進位制日誌檔名和位置

官方YUM安裝過程：https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html

如何安裝MySQL5.7.34（指定版本)，可參考：https://www.cnblogs.com/zhangwencheng/p/15045074.html , MySQL5.7最新版本5.7.35（2021-08）

準備環境：

屬性	mysql 主	mysql 從
節點	Mysql-Master01	Mysql-Slave01
系統	CentOS Linux release 7.5.1804 (Minimal)	CentOS Linux release 7.5.1804 (Minimal)
核心	3.10.0-862.el7.x86_64	3.10.0-862.el7.x86_64
SELinux	setenforce 0 \| disabled	setenforce 0 \| disabled
Firewlld	systemctl stop/disable firewalld	systemctl stop/disable firewalld
IP地址	172.16.70.37	172.16.70.181

MySQL非同步複製(過濾)部署過程。

Master01和Slave01 同樣的操作；以Master01為例。

# yum安裝MySQL5.7（預設最新版本）
[root@Mysql-Master01 ~]# wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm
[root@Mysql-Master01 ~]# yum localinstall -y mysql57-community-release-el7-10.noarch.rpm
[root@Mysql-Master01 ~]# yum repolist enabled | grep "mysql.*-community.*"
[root@Mysql-Master01 ~]# yum install -y mysql-community-server
--------------------------------------------------------------------------------------------------------------------------
# 安裝MySQL5.7.34（指定版本),必須按順序安裝（common-->libs-->client-->server）
# CentOS7版本需要先清理系統預設自帶安裝了MariaDB。
[root@Mysql-Master01 ~]# ls
mysql-community-client-5.7.34-1.el7.x86_64.rpm  mysql-community-libs-5.7.34-1.el7.x86_64.rpm
mysql-community-common-5.7.34-1.el7.x86_64.rpm  mysql-community-server-5.7.34-1.el7.x86_64.rpm

[root@Mysql-Master01 ~]# rpm -ivh mysql-community-common-5.7.34-1.el7.x86_64.rpm
[root@Mysql-Master01 ~]# rpm -ivh mysql-community-libs-5.7.34-1.el7.x86_64.rpm
[root@Mysql-Master01 ~]# rpm -ivh mysql-community-client-5.7.34-1.el7.x86_64.rpm
[root@Mysql-Master01 ~]# rpm -ivh mysql-community-server-5.7.34-1.el7.x86_64.rpm
==========================================================================================================================
# 啟動mysqld
[root@Mysql-Master01 ~]# systemctl start mysqld
[root@Mysql-Master01 ~]# netstat -nutpl | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      5059/mysqld
[root@Mysql-Master01 ~]# ps -ef | grep mysql
mysql      5059      1  0 14:38 ?        00:00:02 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

# mysql安全初始化
[root@Mysql-Master01 ~]# grep 'temporary password' /var/log/mysqld.log
2021-08-12T02:11:09.461541Z 1 [Note] A temporary password is generated for root@localhost: 2ofTdw8ntD>V

[root@Mysql-Slave01 ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:　　　　# 輸入上面的初始密碼 2ofTdw8ntD>V
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y　　　　# 是否修改root密碼

New password:　　　　　　# 輸入符合密碼策略的root密碼

Re-enter new password:　　　　# 再次輸入

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y　　　　# 是否希望繼續使用所提供的密碼
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y　　　　# 是否刪除匿名賬號
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y　　　　# 是否禁止root遠端登入
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y　　　　# 是否刪除test庫和對test庫的訪問許可權
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y　　　　# 是否重新整理授權表使修改生效
Success.

All done!

Master01上操作。

[root@Mysql-Master01 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using  EditLine wrapper

# 設定master01的my.cnf(必須在[mysqld]配置區域)
[root@Mysql-Master01 ~]# cat /etc/my.cnf
[mysqld]
......
# 新增以下內容
server-id = 37
log-bin = master-bin
binlog-do-db = mydb
binlog-ignore-db = mydb1
binlog-format = mixed
sync_binlog = 1
expire_logs_days = 30
skip_name_resolve = ON

引數說明：
　　server-id 　　　　master01伺服器唯一ID，一般IP最後一段，主從不能重複
　　log-bin 　　　　　開啟bin-log，並可指定檔案檔案目錄和字首
　　binlog-do-db　　 需要同步的資料庫；如需同步多個庫寫多行即可；如不寫此行，預設同步所有庫
　　binlog-ignore-db 無需同步的資料庫；如無需同步多個庫寫多行即可；也可以在一行，各庫名用逗號隔開
　　binlog-format 　　二進位制日誌格式，有row、statement、mixed三種格式，
　　　　　row指的是把改變的內容複製過去，而不是把命令在從伺服器上執行一遍，
　　　　　statement指的是在主伺服器上執行的SQL語句，在從伺服器上執行同樣的語句。MySQL預設採用基於語句的複製，效率比較高。
　　　　　mixed指的是預設採用基於語句的複製，一旦發現基於語句的無法精確的複製時，就會採用基於行的複製；可防止主鍵重複。
　　sync_binlog　　　　確保binlog日誌寫入後與硬碟同步
　　expire_logs_days　自動清理 7 天前的log檔案,可根據需要修改
　　skip_name_resolve　跳過反向域名解析

# 重啟MySQL
[root@Mysql-Master01 ~]# systemctl restart mysqld

# 測試登入msyql
[root@Mysql-Master01 ~]# mysql -uroot -p
Enter password:

# 建立測試庫mydb (對應配置檔案my.cnfd的binlog-do-db)
mysql> CREATE DATABASE IF NOT EXISTS mydb;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb;
Database changed
mysql> CREATE TABLE IF NOT EXISTS tb1 (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO mydb.tb1 VALUES(1,"zhangsan"),(2,"lisi");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mydb.tb1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

# 建立測試庫mydb1 (對應配置檔案my.cnfd的binlog-ignore-db)
mysql> CREATE DATABASE IF NOT EXISTS mydb1;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS tb2 (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO mydb1.tb2 VALUES(1,"wangwu"),(2,"zhaoliu");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FRoM mydb1.tb2;
+----+---------+
| id | name    |
+----+---------+
|  1 | wangwu  |
|  2 | zhaoliu |
+----+---------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mydb1              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> show variables like 'server_id';　　　　# 檢視伺服器唯一ID
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 37    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%log_bin%';　　　　# 檢視是否開啟log_bin
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /var/lib/mysql/master-bin       |
| log_bin_index                   | /var/lib/mysql/master-bin.index |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+
6 rows in set (0.00 sec)

mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000003 |       154 |
+-------------------+-----------+
3 rows in set (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'172.16.%.%' IDENTIFIED BY 'Backup@01';　　　　# 建立backup賬戶並授權slave
Query OK, 0 rows affected, 1 warning (0.00 sec)

語句說明：
(1) replication slave為mysql同步的必須許可權，此處不要授權all許可權
(2) *.* 表示所有庫所有表，也可以指定具體的庫和表進行復制。例如mydb.tb1中，mydb為庫名，tb1為表名
(3) 'backup'@'172.16.%.%' backup為同步賬號。172.16.%.%為授權主機網段，使用了%表示允許整個172.16.0.0網段可以用backup這個使用者訪問資料庫
(4) identified by 'Backup@01';  Backup@01為密碼，實際環境下設定複雜密碼

mysql> flush privileges;　　　　# 重新整理許可權
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user where user='backup';　　　　# 檢視是否存在backup使用者
+--------+------------+
| user   | host       |
+--------+------------+
| backup | 172.16.%.% |
+--------+------------+
1 row in set (0.01 sec)

mysql> show grants for backup@'172.16.%.%';　　　　# 檢視backup使用者授權
+---------------------------------------------------------+
| Grants for backup@172.16.%.%                            |
+---------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'backup'@'172.16.%.%' |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> flush table with read lock;　　　　# 對主資料庫鎖表只讀，防止匯出資料庫的時候有資料寫入。unlock tables命令解除鎖定
Query OK, 0 rows affected (0.00 se

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |　　# 自動解鎖時間受本引數影響
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 28800    |　　# 自動解鎖時間受本引數影響
+-----------------------------+----------+
13 rows in set (0.00 sec)

mysql> show master status;　　　　# 鎖表後檢視主庫狀態
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |      154 | mydb         | mydb1            |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 鎖表後，一定要單開一個新的SSH視窗，匯出mydb資料庫,傳送至slave
[root@Mysql-Master01 ~]# mysqldump -uroot mydb -p > /tmp/mydb.sql
Enter password:
[root@Mysql-Master01 ~]# ls /tmp/mydb.sql
/tmp/mydb.sql
[root@Mysql-Master01 ~]# rsync -avz /tmp/mydb.sql root@172.16.70.181:/tmp/
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.70.181' (ECDSA) to the list of known hosts.
root@172.16.70.181's password:　　# slave伺服器root密碼

#匯出資料完畢後，解鎖主庫
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Slave01上的操作。

[root@Mysql-Slave01 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using  EditLine wrapper

# 設定slave01的my.cnf(必須在[mysqld]配置區域)
[mysqld]
......
# 新增以下內容
server-id = 181
log-bin = slave-bin
replicate-do-db = mydb
replicate-ignore-db = mydb1
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

引數說明：
server-id 　　　　　　slave01伺服器唯一ID，一般IP最後一段，主從不能重複
log-bin 　　　　　　　開啟bin-log，並可指定檔案檔案目錄和字首
replicate-do-db　　　需要同步的資料庫名。如果不指明同步哪些庫，就去掉這行，表示所有庫的同步（除了ignore忽略的庫）
replicate-ignore-db　　無需同步的資料庫
relay-log 　　　　　　　將主伺服器上同步日誌檔案記錄到本地
relay-log-index 　　　定義 relay-log 的位置和名稱

# 重啟MySQL
[root@Mysql-Slave01 ~]# systemctl restart mysqld

# 登入mysql
[root@Mysql-Slave01 ~]# mysql -p
Enter password:

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 181   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/slave-bin       |
| log_bin_index                   | /var/lib/mysql/slave-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

# slave建立mydb空庫，否則下面匯入資料時會報錯說此庫不存在
mysql> CREATE DATABASE IF NOT EXISTS mydb;
Query OK, 1 row affected (0.01 sec)

mysql> source /tmp/mydb.sql

# 配置主從同步指令
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> change master to master_host='172.16.70.37',master_port=3306,master_user='backup',master_password='Backup@01',master_log_file='master-bin.000003',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

引數說明：（提示：字串用單引號括起來，數值不用引號，注意內容前後不能有空格。）
　　change master to 
　　master_host='172.16.70.37'　　# master主庫IP
　　master_port=3306　　　　　　　　# 資料庫埠號　　　　　　　　
　　master_user='backup'　　　　　　# master上建立用於複製的使用者
　　master_password='Backup@01'　　# 複製使用者的密碼
　　master_log_file='master-bin.000003'　　# show master status時檢視到的二進位制日誌檔名稱，注意不能多空格
　　master_log_pos=154;　　　　　　# show master status時檢視到的二進位制日誌偏移量，注意不能多空格

# 上述操作的原理實際上是把使用者密碼等資訊寫入從庫新的master.info檔案中
/var/lib/mysql/master.info
[root@Mysql-Slave01 ~]# cat /var/lib/mysql/master.info
25
master-bin.000004
154
172.16.70.37
backup
Backup@01
3306
60
0
.....

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.70.37
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mydb
          Replicate_Ignore_DB: mydb1
           Replicate_Do_Table:
　　　　　　......................
　　　　　Seconds_Behind_Master: 0
　　　　　　......................

如上，當IO和SQL執行緒的狀態均為Yes，則表示主從已實現同步了！(主從同步是否成功，最關鍵的為下面的3項狀態引數)
Slave_IO_Running: Yes,這個時I/O執行緒狀態，I/O執行緒負責從從庫到主庫讀取binlog日誌，並寫入從庫的中繼日誌，狀態為Yes表示I/O執行緒工作正常。
Slave_SQL_Running: Yes,這個是SQL執行緒狀態，SQL執行緒負責讀取中繼日誌（relay-log）中的資料並轉換為SQL語句應用到從資料庫中，狀態為Yes表示I/O執行緒工作正常。
Seconds_Behind_Master:0,這個是複製過程中從庫比主庫延遲的秒數，這個引數極度重要，但企業裡更準確地判斷主從延遲的方法為：在主庫寫時間戳，然後從庫讀取時間戳，和當前資料庫時間進行比較，從而認定是否延遲。

     測試mysql主從複製效果。

# 先在mastet01上操作，當配置檔案my.cnf存在binlog-do-db引數時，必須進入該庫建立
mysql> use mydb;
mysql> select database();
+------------+
| database() |
+------------+
| mydb       |
+------------+
1 row in set (0.00 sec)

mysql> insert into mydb.tb1 values(11,"chenqi");
mysql> select * from mydb.tb1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
| 11 | chenqi   |
+----+----------+
4 rows in set (0.00 sec)


#然後slave01上檢視是否自動同步
mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> select * from mydb.tb1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
| 11 | chenqi   |
+----+----------+
3 rows in set (0.00 sec)

至此，MySQL主從複製(Binlog)已經實現！

以上都是在Mysql-Master主資料庫和Mysql-Slave01從資料庫之間實現的基於binlog的主從複製,即"一主一從"架構。現在再把Mysql-Slave02的從節點新增進去，調整為"一主兩從"的同步架構。

Slave02上的操作如下：

# 關閉firewalld及SELinux
[root@Mysql-Slave02 ~]# systemctl stop firewalld && systemctl disable firewalld
[root@Mysql-Slave02 ~]# setenforce 0
[root@Mysql-Slave02 ~]# sed -i '7s/enforcing/disabled/' /etc/selinux/config

# 設定相同時區，時間
[root@Mysql-Slave02 ~]# yum install -y ntp
[root@Mysql-Slave02 ~]# systemctl start ntpd && systemctl enable ntpd
[root@Mysql-Slave02 ~]# timedatectl set-timezone Asia/Shanghai

[root@Mysql-Slave02 ~]# hostname -I
172.16.70.182

# yum安裝mysql5.7
[root@Mysql-Slave02 ~]# yum localinstall -y mysql57-community-release-el7-10.noarch.rpm
[root@Mysql-Slave02 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.35, for Linux (x86_64) using  EditLine wrapper

啟動MySQL
[root@Mysql-Slave02 ~]# systemctl start mysqld && systemctl enable mysqld
[root@Mysql-Slave02 ~]# netstat -nutpl | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      2613/mysqld
[root@Mysql-Slave02 ~]# ps -ef | grep mysql
mysql      2613      1  0 15:42 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

# MySQL安全初始化
[root@Mysql-Slave02 ~]# grep 'temporary password' /var/log/mysqld.log
2021-08-18T07:42:42.208560Z 1 [Note] A temporary password is generated for root@localhost: hlu?dn31d)N#

[root@Mysql-Slave02 ~]# mysql_secure_installation
......
......跟前面Slave01同樣的設定

# Slave02的my.cnf與Slave01的只區別於server-id
[mysqld]
......
server-id = 182
log-bin = slave-bin
replicate-do-db = mydb
replicate-ignore-db = mydb1
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

# 重啟MySQL
[root@Mysql-Slave02 ~]# systemctl restart mysqld

# 後面的操作完全與Slave01的一致即可，不再贅述。

Mysql主從複製延時

1、如何檢視主從延遲時間
通過監控 show slave status 命令輸出的Seconds_Behind_Master引數的值來判斷：
Seconds_Behind_Master=0: 表示主從複製良好；
Seconds_Behind_Master=NULL: 表示io_thread或是sql_thread有任何一個發生故障；
Seconds_Behind_Master=n: 數字越大表示從庫延遲越嚴重。

2、影響延遲因素
這裡整理了影響主從複製延遲大致有以下幾個原因：
1）主節點如果執行一個很大的事務，那麼就會對主從延遲產生較大的影響
2）網路延遲，日誌較大，slave數量過多
3）主上多執行緒寫入，從節點只有單執行緒同步
4）機器效能問題，從節點是否使用了“爛機器”
5）鎖衝突問題也可能導致從機的SQL執行緒執行慢

3、優化主從複製延遲
這個沒有說去完全解決，要想解決那麼就只能採用同步複製策略。不過，一般不建議使用這種同步模式。顯而易見，如果寫操作必須等待更新同步完成，肯定會
極大地影響效能，除非你不在乎效能。
1）大事務：將大事務分為小事務，分批更新資料
2）減少Slave的數量，不要超過5個，減少單次事務的大小
3）MySQL 5.7之後，可以使用多執行緒複製，使用MGR複製架構
4）在磁碟、raid卡、排程策略有問題的情況下可能會出現單個IO延遲很高的情況，可用iostat命令檢視DB資料盤的IO情況，再進一步判斷
5）針對鎖問題可以通過抓去processlist以及檢視information_schema下面和鎖以及事務相關的表來檢視

附：如何重置mysql root密碼？

一、 在已知MYSQL資料庫的ROOT使用者密碼的情況下，修改密碼的方法：
在SHELL環境下，使用mysqladmin命令設定：
mysqladmin –u root –p password “新密碼”   回車後要求輸入舊密碼

在mysql>環境中,使用update命令，直接更新mysql庫user表的資料：
Update  mysql.user  set  password=password(‘新密碼’)  where  user=’root’;
flush   privileges;
注意：mysql語句要以分號”；”結束

在mysql>環境中，使用grant命令，修改root使用者的授權許可權。
grant  all  on  *.*  to   root@’localhost’  identified  by  ‘新密碼’；

二、 如忘記了mysql資料庫的ROOT使用者的密碼，又如何做呢？方法如下：
關閉當前執行的mysqld服務程式：service  mysqld  stop（要先將mysqld新增為系統服務）
使用mysqld_safe指令碼以安全模式（不載入授權表）啟動mysqld 服務
/usr/local/mysql/bin/mysqld_safe  --skip-grant-table  &

使用空密碼的root使用者登入資料庫，重新設定ROOT使用者的密碼
＃mysql  -u   root
Mysql> Update  mysql.user  set  password=password(‘新密碼’)  where  user=’root’;
Mysql> flush   privileges;

Windows Mysql主從複製部署
2021-04-11
WindowsMySql
MySQL主從複製歷程
2020-11-16
MySql
Mysql MHA部署-02主從複製
2020-03-15
MySql
mysql主從複製詳細部署
2024-10-22
MySql
MySQL主從複製環境部署
2018-04-23
MySql
redis建立主從複製的過程
2018-11-19
Redis
Windows 環境下，MySQL 的主從複製和主主複製
2019-03-12
WindowsMySql
windows環境下，Mysql的主從複製和主主複製
2018-07-20
WindowsMySql
windows 下mysql主從複製
2018-11-02
WindowsMySql
MySQL 主從複製安裝部署配置
2019-03-27
MySql
MySQL的主從複製
2018-11-29
MySql
MySQL 主從複製，常見的binlog錯誤及解決方法
2024-07-30
MySql
mysql5.7主從複製，主主複製
2018-08-21
MySql
linux下mysql主從複製，實現資料庫同步
2019-01-14
LinuxMySql資料庫
mysql複製--主從複製配置
2018-04-12
MySql
MySQL主從複製
2024-11-11
MySql
MySQL主從複製之GTID複製
2019-07-23
MySql
Linux實現MySql資料庫的主從複製（一主一從）
2018-07-30
LinuxMySql資料庫
MySQL主從複製原理
2019-04-01
MySql
mysql--主從複製
2020-10-23
MySql
mysql 8.4 主從複製
2024-11-01
MySql
mysql主從複製搭建
2020-11-30
MySql
MySQL Xtrabackup真實生產環境搭建主從複製全過程
2018-11-23
MySql
MySQL主從複製之半同步複製
2019-07-23
MySql
MySQL主從複製之非同步複製
2019-07-23
MySql非同步
MySQL 的主從複製實踐
2020-02-13
MySql
mysql資料庫的主從複製和主主複製實踐
2018-03-25
MySql資料庫
聊聊MySQL主從複製的幾種複製方式
2020-08-15
MySql
MySQL++：Liunx - MySQL 主從複製
2020-05-19
MySql
MySQL（13）---MYSQL主從複製原理
2020-12-04
MySql
mysql主從複製(一):一主多從
2018-03-25
MySql
MySQL 主從複製過濾新增庫表過濾方案
2024-12-02
MySql
mysql實現主從複製
2018-11-20
MySql
mysql主從延遲複製
2019-01-29
MySql
MySQL 主從複製實操
2018-10-12
MySql
MYSQL主從複製配置（整理）
2018-09-29
MySql
MySQL-18.主從複製
2024-07-09
MySql
Mysql 傳統主從複製
2020-12-25
MySql

Linux下MySQL主從複製(Binlog)的部署過程

相關文章