實戰項目——mysql主從架構的實現

標籤：就會   ash   ack   使用者登入   串連   mysqld   user   通用   插入資料   

一主一從1.1 環境準備：

　　centos系統伺服器2台、 一台使用者做Mysql主伺服器， 一台用於做Mysql從伺服器， 配置好yum源、 防火牆關閉、 各節點時鐘服務同步、 各節點之間可以通過主機名稱互相通訊

1.2 準備步驟：

1）iptables -F && setenforce 清空防火牆策略，關閉selinux
2）拿兩台伺服器都使用yum方式安裝Mysql服務， 要求版本一致
3）分別啟動兩台伺服器mysql服務， 確保服務正常
　　架構圖:

1.3 實現步驟:1.3.1 配置master主伺服器

　　對master進行配置，包括開啟二進位日誌，指定唯一的servr ID。例如，在設定檔加入如下值

    server-id=1　　　　　 //配置server-id，讓主伺服器有唯一ID號    log-bin=mysql-bin　　//開啟Mysql日誌，日誌格式為二進位    skip-name-resolve　　//關閉名稱解析，（非必須）

1.3.2 建立複製帳號

　　在 Master 的資料庫中建立一個備份帳戶：每個 slave 使用標準的 MySQL 使用者名稱和密碼串連 master 。進行複製操作的使用者會授予 REPLICATION SLAVE 許可權。

    MariaDB [(none)]> grant replication slave,replication client on *.* to [email protected]'192.168.%.%' identified by 'keer';

1.3.3 查看主伺服器狀態

　　在 Master 的資料庫執行 show master status，查看主伺服器二進位日誌狀態及位置號。

1.3.4 配置slave從伺服器

　　對 slave 進行配置，開啟中繼日誌，指定唯一的 servr ID，設定唯讀許可權。在設定檔加入如下值：

    server-id=2                 //配置server-id，讓從伺服器有唯一ID號    relay_log = mysql-relay-bin //開啟Mysql日誌，日誌格式為二進位    read_only = 1               //設定唯讀許可權    log_bin = mysql-bin         //開啟從伺服器二進位日誌    log_slave_updates = 1       //使得更新的資料寫進二進位日誌中

1.3.5 啟動從伺服器複製線程

　　讓 slave 串連 master ，並開始重做 master 二進位日誌中的事件。

MariaDB [(none)]> change master to master_host='192.168.37.111',    >master_user='slave',    >master_password='keer',    >master_log_file='mysql-bin.000001',    >master_log_pos=413;

　　執行start slave；，來啟動複製線程。
　　

1.3.6 查看從伺服器狀態

　　可使用SHOW SLAVE STATUS\G;查看從伺服器狀態，如下所示，也可用show processlist \G;查看當前複製狀態：

    Slave_IO_Running: Yes       //IO線程正常運行    Slave_SQL_Running: Yes      //SQL線程正常運行

1.3.7 測試

1）測試主從同步
　　我們在 master 伺服器上建立一個資料庫，再使用該資料庫建立一個表，添加一條記錄，來看一看 slave 伺服器有沒有同步成功。
　　首先，我們先來查看一下兩個伺服器上有什麼資料庫：
　　master：

　　slave：

　　現在，我們在主伺服器上建立一個資料庫“keer”：

　　然後，我們來看看從伺服器是否已經更新：

　　我們可以看出，資料庫已經同步，接下來，我們就來建立表，並在表中插入一條記錄：

　　然後，我們來看看從伺服器是否更新：

2）測試唯讀屬性　　
　　我們在從伺服器上設定了唯讀，所以我們來試試看能不能插入資料：

　　這就很尷尬了，為什麼我們設定唯讀還是可以插入資料呢？這要如何解決呢？
　　因為我們登入的時候使用的是root超級使用者，這個大變態是不受唯讀設定影響的，所以，我們還是可以往裡面去插入資料，當然，如果我們換成其他的普通使用者就不會出現這樣的問題了。
　　解決方式也很簡單，我們只需要把表鎖起來就可以了：

MariaDB [keer]> flush tables with read lock;

　　進行鎖表操作以後，我們再來插入一條資料：

　　這樣的話，即使我們是超級使用者登入進來的，也不能再插入資料了。這樣會安全很多。但是，有利就有弊，這樣的話，我們的主從就不能即時同步了，因為我們的主從同步是通過把主上的 sql 語句放在從上再執行一遍來實現的，可是我們鎖表的話，sql 語句就不能執行了。想要同步方法也很簡單，直接把表解鎖就可以了。
　　我們在主上插入一條資料：

　　此時，我們的從上就要解表以後才能實現同步：

1.4 擴充——實現一主多從1.4.1 需求分析

　　當我們的伺服器運行一段時間後，流量變得越來越多，這時，一主一從能夠實現的高可用性和負載平衡不能滿足我們的需求，我們就要選擇再添加一台從伺服器。
　　可是現在我們的 master 已經運行很久了，我們也需要對新安裝的 slave 進行資料同步，甚至它沒有 master 的資料。
　　此時，有幾種方法可以使 slave 從另一個服務開始，例如，從 master 拷貝資料，從另一個 slave 複製，從最近的備份開始一個 slave 。為了加快 slave 與 master 同步，可用以下方式先進行資料同步：

（1）master的某個時刻的資料快照；
（2）資料庫的備份資料；
（3）master的二進位記錄檔。

　　架構圖：

　　接下來，我們就來實現一下這個過程：

1.4.2 具體實現過程

1）對 master 進行完全備份

[[email protected] ~]# mkdir /backup[[email protected] ~]# mysqldump --all-database > /backup/mysql-backup-`date +%F-%T`-all.sql

照例查看一下主的二進位日誌狀態及位置號。

2）把完全備份的資料發到新增的從上

[[email protected] ~]# scp /backup/mysql-backup-2017-11-21-10\:28\:29-all.sql @192.168.37.133:~[email protected]'s password: mysql-backup-2017-11-21-10:28:29-all.sql                                                           100%  504KB 503.9KB/s   00:00 

3）把這個完全備份匯入從的資料庫：

4）查看從是否恢複成功：


　　我們來查看一下從的二進位日誌狀態及位置號：

　　我們的資料已經恢複成功

5）設定主從
接下來就是按照上述步驟，對該從伺服器進行設定就好：

[[email protected] ~]# vim /etc/my.cnf    [mysqld]    server-id = 3    relay-log = mysql-relay-bin    read-only = 1    log-bin = mysql-bin    log-slave-updates = 1

　　
　　然後，我們就來設定從伺服器：

[[email protected] ~]# mysql -uroot -pEnter password: MariaDB [(none)]> change master to master_host='192.168.37.111',    >master_user='slave',    >master_password='keer',    >master_log_file='mysql-bin.000001',    >master_log_pos=330;MariaDB [(none)]> start slave;

　　然後我們來查看一下從伺服器的狀態：

6）測試
　　設定完畢，我們來測試一下，當主上有操作時，從上是否更新：


　　至此，我們就完成了一主兩從。如果有需要，我們還可以繼續添加從伺服器。

1.5 擴充——實現半同步複製1.5.1 三種複製方式

1）同步複製
　　指當主庫執行完一個事務，所有的從庫都執行了該事務才返回給用戶端。因為需要等待所有從庫執行完該事務才能返回，所以全同步複製的效能必然會收到嚴重的影響。需要有逾時時間。
2）非同步複製
　　MySQL預設的複製即是非同步，主庫在執行完用戶端提交的事務後會立即將結果返給給用戶端，並不關心從庫是否已經接收並處理，這樣就會有一個問題，主如果crash掉了，此時主上已經提交的事務可能並沒有傳到從上，如果此時，強行將從提升為主，可能導致新主上的資料不完整。
3）半同步複製
　　介於非同步複製和全同步複製之間，主庫在執行完用戶端提交的事務後不是立刻返回給用戶端，而是等待至少一個從庫接收到並寫到relay log中才返回給用戶端。相對於非同步複製，半同步複製提高了資料的安全性，同時它也造成了一定程度的延遲，這個延遲最少是一個TCP/IP往返的時間。所以，半同步複製最好在低延時的網路中使用。

1.5.2 實現半同步複製

　　半同步複製是基於特定的模組來實現。不過非常 nice 的一點就是，在我們的 mariadb5.5 以上的版本預設是帶有這個模組的。
　　如果我們的 centos7 上安裝了 mariadb ，我們就可以進入目錄去查看一下：

[[email protected] ~]# cd /usr/lib64/mysql/plugin/

　　找到我們的模組後，我們就可以通過命令來安裝，然後進行簡單配置即可使用了。下面就是具體方法：
master 伺服器上：

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=ON;

slave 伺服器上：

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled = ON ;MariaDB [(none)]> stop slave;   MariaDB [(none)]> start slave;

　　現在，我們的半同步複製就開啟了，我們可以來查看一下日誌：

[[email protected] ~]# tail -200 /var/log/mariadb/mariadb.log 

　　從日誌裡也可以看出我們的半同步複製已經開啟。

　　以上，實驗一完成。

互為主從（雙主）

　　還有一種資料庫的架構為雙主模式，也就是互為主從，這種設定在企業中雖不常用，但是我們還是需要掌握的。
　　Master-Master複製的兩台伺服器，既是master，又是另一台伺服器的slave。這樣，任何一方所做的變更，都會通過複製應用到另外一方的資料庫中。
　　架構圖如下：

2.1 環境準備：

　　centos系統伺服器2台、 兩台使用者實現Mysql互為主從， 配置好yum源、 防火牆關閉、 各節點時鐘服務同步、 各節點之間可以通過主機名稱互相通訊

2.2 準備步驟：

1）iptables -F && setenforce 清空防火牆策略，關閉selinux
2）拿兩台伺服器都使用yum方式安裝Mysql服務， 要求版本一致
3）分別啟動兩台伺服器mysql服務， 確保服務正常

2.3 實現步驟：2.3.1 配置第一台 master 主伺服器

    server-id = 1　　　　　 //配置server-id，讓主伺服器有唯一ID號    log-bin = mysql-bin　　//開啟Mysql日誌，日誌格式為二進位    skip-name-resolve  　　//關閉名稱解析，（非必須）    relay_log = mysql-relay-bin //開啟Mysql日誌，日誌格式為二進位    read_only = 1               //設定唯讀許可權    log_slave_updates = 1       //使得更新的資料寫進二進位日誌中    auto_increment_offset = 1　　　 //表示自增長欄位從那個數開始    auto_increment_increment = 2　　//表示自增長欄位每次遞增的量

2.3.2 配置第二台 master 主伺服器

    server-id = 2　　　　　 //配置server-id，讓主伺服器有唯一ID號    log-bin = mysql-bin　　//開啟Mysql日誌，日誌格式為二進位    skip-name-resolve　  　//關閉名稱解析，（非必須）    relay_log = mysql-relay-bin //開啟Mysql日誌，日誌格式為二進位    read_only = 1               //設定唯讀許可權    log_slave_updates = 1       //使得更新的資料寫進二進位日誌中    auto_increment_offset = 2　　　 //表示自增長欄位從那個數開始    auto_increment_increment = 2　　//表示自增長欄位每次遞增的量

2.3.3 建立複製帳號

　　因為我們實現的是雙主模式，所以我們每一台機器上都要建立複製帳號：

2.3.4 查看伺服器狀態

　　我們分別查看兩台伺服器的狀態，從而為我們下一步的設定奠下基礎：

2.3.5 啟動複製線程

　　我們來分別對兩台機器進行的設定：
　　首先，先對第一台 master 機器進行設定：

MariaDB [(none)]> change master to master_host='192.168.37.222',master_user='slave',master_password='keer',master_log_file='mysql-bin.000003',master_log_pos=245;MariaDB [(none)]> start slave;

　　接著，我們來查看一下他的狀態：

　　現在，我們來對第二台 master 進行設定：

MariaDB [(none)]> change master to master_host='192.168.37.111',master_user='slave',master_password='keer',master_log_file='mysql-bin.000002',master_log_pos=245;MariaDB [(none)]> start slave;

　　我們來查看一下他的狀態：
　　
　　可以看出，我們的兩個機器都設定完畢，接下來，我們就可以來測試一下了。

2.3.5 測試

　　在我們的設定檔中，我們設定了自增長欄位的起始值和增長值，現在我們去建立一個自增長的表：

MariaDB [keer]> create table test(id int PRIMARY KEY AUTO_INCREMENT,name char(20) not null);

　　因為我們設定了主從，所以在任一機器使用該命令即可。
　　建立好了表以後，我們就可以插入資料進行測試了，我們一個插入多條資料來看看效果：
第一個伺服器：

MariaDB [keer]> insert into test(name) value('zhao'),('qian'),('sun'),('li');Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0

第二個伺服器：

MariaDB [keer]> insert into test(name) value('zhou'),('wu'),('zheng'),('wang');Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0

　　資料我們都插入了，我們來看看效果如何：

　　可以看出，兩個表插入的資料是不會重複的。
　　但是現在也不是我們想象的連續的 id ，這是因為，我們兩台機器都是同時插入4條資料，而且不是同步插入的，這樣就會有一個先後順序，第一台機器上的 sql 語句執行結束以後才執行第二台機器上的。所以會出現這樣的情況。
　　但是，這樣已經不會插入重複的資料了。我們的目的也是達到了。

　　以上，實驗二完成。

　　如有不足之處還請大家多多指教喏?(?>?<?)?

實戰項目——mysql主從架構的實現