Linux下MySQL主從復制(Binlog)的部署過程

什么是 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（指定版本)，可參考：http://www.rzrgm.cn/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（指定版本),必須按順序安裝。
# 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&gt;V

[root@Mysql-Slave01 ~] # mysql_secure_installation

Securing the MySQL server deployment.

Enter password  for user root:　　　　 # 輸入上面的初始密碼 2ofTdw8ntD&gt;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;

posted @ 2021-08-23 09:04 講文張字閱讀(711) 評論(0) 收藏舉報

刷新頁面返回頂部

返回頂部