MySQL性能優化（五）-- 主從複製

  • 2019 年 12 月 10 日
  • 筆記

一、概念　　

Mysql複製（replication）是一個非同步的複製，從一個Mysql 實例（Master）複製到另一個Mysql 實例（Slave）。實現整個主從複製，需要由Master伺服器上的IO進程，和Slave伺服器上的Sql進程和IO進程共從完成。要實現主從複製，首先必須打開Master端的binary log（bin-log）功能，因為整個 MySQL 複製過程實際上就是Slave從Master端獲取相應的二進位日誌，然後再在自己slave端完全順序的執行日誌中所記錄的各種操作。（二進位日誌幾乎記錄了除select以外的所有針對資料庫的sql操作語句）

二、複製解決的問題

(1) 數據分布 (Data distribution )

(2) 負載平衡(load balancing)

(3) 備份(Backups)

(4) 高可用性和容錯行 High availability and failover

三、複製的原理

(1) master將改變記錄到二進位日誌(binary log)中（這些記錄叫做二進位日誌事件，binary log events）；

(2) slave將master的binary log events拷貝到它的中繼日誌(relay log)；

(3) slave重做中繼日誌中的事件，將更改應用到自己的數據上。

下圖描述了複製的過程：

該過程的第一部分就是master記錄二進位日誌。在每個事務更新數據完成之前，master在二日誌記錄這些改變。MySQL將事務串列的寫入二進位日誌，即使事務中的語句都是交叉執行的。在事件寫入二進位日誌完成後，master通知存儲引擎提交事務。

下一步就是slave將master的binary log拷貝到它自己的中繼日誌。首先，slave開始一個工作執行緒——I/O執行緒。I/O執行緒在master上打開一個普通的連接，然後開始binlog dump process。Binlog dump process從master的二進位日誌中讀取事件，如果已經跟上master，它會睡眠並等待master產生新的事件。I/O執行緒將這些事件寫入中繼日誌。

SQL slave thread（SQL從執行緒）處理該過程的最後一步。SQL執行緒從中繼日誌讀取事件，並重放其中的事件而更新slave的數據，使其與master中的數據一致。只要該執行緒與I/O執行緒保持一致，中繼日誌通常會位於OS的快取中，所以中繼日誌的開銷很小。

此外，在master中也有一個工作執行緒：和其它MySQL的連接一樣，slave在master中打開一個連接也會使得master開始一個執行緒。複製過程有一個很重要的限制——複製在slave上是串列化的，也就是說master上的並行更新操作不能在slave上並行操作。

四、配置

1.創建一個資料庫 ：create database testXbq;

2.創建表：create table student(id int primary key auto_increment,name varchar(20));

3.先將master上的數據複製到slave上（基於二進位日誌備份（默認是關閉的））

（1）先開啟二進位日誌：

編輯配置文件：vi /etc/my.cnf，在裡面增加

log-bin=mysql-bin            // 二進位存放的目錄和名字  binlog_format=mixed          //  server-id=1                  // 標誌 master的唯一標識

（2）重啟mysql：service mysql restart

（3）查看master的狀態：show master status;

（4）查看目錄下的二進位文件：ll /var/lib/mysql

其中，mysql-bin.index存的是：

（5）基於 位置 備份

mysqldump -uroot -p --single-transaction --master-data=2  --triggers --routines --all-databases > xbq.sql

// 基於獨立的事務 // =2 注釋掉 =1 不注釋 // 備份到當前目錄下xbq.sql

查看 xbq.sql，會發現如下（或者 直接重啟master後 登錄mysql，執行 show master status也可以看到 從 mysql-bin.000001的579位置開始複製）：

後續的複製工作會從 mysql-bin.000001的579位置開始複製。

（6）master需要授權 一個用戶給 slave，即建立一個複製帳號，在master上執行（可以通過 ? grand 查看 grand的語法）：

grant replication slave on . to 'reppc'@'%' identified by 'xbq123'; // 一般不用root帳號，% 表示所有客戶端都可能連，只要帳號，密碼正確，此處可用具體客戶端IP代替，如192.168.1.112，加強安全。

需要刷新許可權 才可以生效：flush privileges;（此時，在salve上連接 master的資料庫）

（7）重啟master

（8）在slave上應用 xbq.sql，有兩種方式：（master中執行下面命令,ip換成slave的ip）

方法一：遠程拷貝到 slaver， scp [email protected]:/usr/xbq.sql /usr/xbq.sql ，機器上需安裝openssh-client，yum install -y openssh-client

方法二：遠程連接 執行sql， mysql -h192.168.242.129 -uroot -p < /usr/xbq.sql，前提，需要在slave上給master遠程連接的許可權，如下：

grant all privileges on *.* to 'root'@'master的IP' identified by 'master的mysql密碼' with grant option;  FLUSH PRIVILEGES;

4.slave 應用sql（前提：slave中已經有了master導出的sql文件，以下命令在slave中執行。如果使用 (8)中的方法二的話，忽略此步驟）

方法一：source /usr/xbq.sql

方法二：mysql -uroot -p < /usr/xbq.sql

經過上面的操作 master上的數據 就到了 slave中

5.複製的過程：

（1）slave開始二進位日誌，和 master一樣的配置 ，但是 server-id 需要 =2。然後重啟salve上的mysql。

驗證在slave上是否可以登錄master，mysql -u root -h master的IP -p ，輸入密碼後，可以登錄 則證明可以登錄。否則，需要在 master上給slave授權，如下：

grant all privileges on *.* to 'root'@'slave的IP' identified by 'slave的mysql密碼' with grant option;  FLUSH PRIVILEGES;

（2）讓slave連接master，並開始重做master二進位日誌中的事件（可以通過 ? change master to 查看語法）：

change master to master_host='192.168.242.129', master_user='reppc',master_password='xbq123', master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=579;

從 伺服器的哪一個日誌的哪一個位置 複製。

其中，masterhost是master的IP，masteruser是在master中賦予複製許可權的用戶，masterlogfile是master中二進位文件，masterlogpos是開始複製的位置。

（3）查看IOThread和SQLThread是否成功，只有 這兩個狀態 都是 YES才可以複製。

（4）查看maste上的用戶表 select * from mysql.user G，看到多了一個 我們剛剛指定的用戶，看到 此記錄 的 reppc：Y ，即說明該用戶 具有複製的許可權

此時，master已經配置ok了，查看狀態：show master status;

（5）先重啟slave:

(6) 查看salve的狀態 show slave status ：

出現 Slave-IO-Running：YES，Slave-SQL-Running :YES ，則主從配置成功！

五.測試

（1）在matser中的 student 表中新增加一條記錄：

insert into student(name) values('徐邦啟');

（2）在slave中查看student表：

OK！

六、常用命令

1.查看二進位日誌：

方法一：mysql> show binlog events in 『日誌文件』 from 位置;

方法二：使用mysql工具查看日誌文件（二進位日誌中包含中各種DML和DDL）

mysqlbinlog --no-defaults mysql-bin.000001 查看指定文件binlog

mysqlbinlog --start-position=位置 日誌路徑，例如：mysqlbinlog -start-position=120 /var/lib/mysql/mysql-bin.000001

# 查看某個時間段的二進位日誌，並且輸出到指定的文件  mysqlbinlog --no-defaults --start-datetime="2018-12-12 13:00:00" --stop-datetime="2018-12-12 14:40:00" mysql-bin.000085 -vv --base64-output=decode-rows | more  >> target.txt    # 查看某個二進位日誌中的指定命令，並且輸出到指定的文件  mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000085  | sed -n '/### DELETE FROM `資料庫名`.`表名`/,/COMMIT/p' > target.txt    # 將@1、@2等一系列看不懂的符號轉換為SQL語句  cat target.txt | sed -n '/###/p' | sed 's/### //g;s//*.*/,/g;s/DELETE FROM/INSERT INTO/g;' | sed -r 's/(@4.*),/1;/g' | sed 's/@[0-9]*=//g' > test.sql    # 查看某個二進位日誌的某個時間段內 DELETE 語句出現的次數  mysqlbinlog --no-defaults --start-datetime="2018-12-12 13:00:00" --stop-datetime="2018-12-12 14:40:00" mysql-bin.000085 -d 資料庫名 mysql-bin.000085 -v|grep UPDATE | wc -l

2.查看中繼日誌（和查看二進位日誌是同一個命令）：

mysql> show relaylog events in 『日誌文件』 from 位置;

3.查看mysql執行緒列表：

mysql> processlist;

4.查看mysql的配置文件（my.cnf）目錄：whereis my.cnf

5.查看mysql的配置文件（my.cnf）目錄：mysql --verbose --help | grep -C 1 'Default opt'

6.清空master：reset master;

七、說明

本文是一主一從，當然，一主多從也是一樣的道理，在增加一台slave，然後在my.cnf修改，然後 change master to，在master上 修改 change master to master_host= '192.168.242.%'。

Tags:

VirMach 便宜 VPS