mysql主從複製讀寫分離

標籤：mysql主從   mysql讀寫分離   

mysql主從複製讀寫分離

一、MYSQL讀寫分離的概述

Mysql作為目前世界上使用最廣泛的免費資料庫，相信所有從事系統營運的工程師都一定接觸過。但在實際的生產環境中，由單台Mysql作為獨立的資料庫是完全不能滿足實際需求的，無論是在安全性，高可用性以及高並發等各個方面。
因此，一般來說都是通過 主從複製（Master-Slave）的方式來同步資料，再通過讀寫分離（MySQL-Proxy/Amoeba）來提升資料庫的並發負載能力 這樣的方案來進行部署與實施的。
讀寫分離工作原理：
基本的原理是讓主要資料庫處理事務性增、改、刪操作（INSERT、UPDATE、DELETE），而從資料庫處理SELECT查詢操作。資料庫複寫被用來把事務性操作導致的變更同步到叢集中的從資料庫。

資料內部交換過程：

為什麼要讀寫分離：

面對越來越大的訪問壓力，單台的伺服器的效能成為瓶頸，需要分擔負載

1、        主從只負責各自的寫和讀，極大程度的緩解X鎖和S鎖爭用

2、        從庫可配置myisam引擎，提升查詢效能以及節約系統開銷

3、        增加冗餘，提高可用性

實現讀寫分離的方式：

一般有兩種方式實現

應用程式層實現，網站的程式實現

應用程式層實現指的是在應用程式內部及連接器中實現讀寫分離

優點：
A：應用程式內部實現讀寫分離，安裝既可以使用
B：減少一定部署難度
C：訪問壓力在一定層級以下，效能很好
缺點：
A：架構一旦調整，代碼要跟著變
B：難以實現進階應用程式，如自動分庫，分表
C：無法適用大型應用情境
中介軟體層實現
中介軟體層實現是指在外部中介軟體程式實現讀寫分離
常見的中介軟體程式：
Mysql-proxy    amoeba    Atlas (360)  Cobar(Alibaba)  TDDL(Taobao)
優點：
A：架構設計更靈活
B：可以在程式上實現一些進階控制，如：透明化水平分割，failover,監控
C：可以依靠些技術手段提高mysql效能，
D：對業務代碼的影響小，同時也安全
缺點：
需要一定的開發營運團隊的支援

MYSQL-PROXY概述

MySQL Proxy是一個處於你的client端和MySQL server端之間的簡單程式，它可以監測、分析或改變它們的通訊。它使用靈活，沒有限制，常見的用途包括：Server Load Balancer，故障、查詢分析，查詢過濾和修改等等。
MySQL Proxy就是這麼一個中介層代理，簡單的說，MySQL Proxy就是一個串連池，負責將前台應用的串連請求轉寄給背景資料庫，並且通過使用lua指令碼，可以實現複雜的串連控制和過濾，從而實現讀寫分離和Server Load Balancer。對於應用來說，MySQL Proxy是完全透明的，應用則只需要串連到MySQL Proxy的監聽連接埠即可。當然，這樣proxy機器可能成為單點失效，但完全可以使用多個proxy機器做為冗餘，在應用伺服器的串連池配置中配置到多個proxy的串連參數即可。

MySQL Proxy更強大的一項功能是實現“讀寫分離”，基本原理是讓主要資料庫處理事務性查詢，讓從庫處理SELECT查詢。資料庫複寫被用來把事務性查詢導致的變更同步到叢集中的從庫


Lua概述：
Lua 是一個小巧的指令碼語言。
Lua的速度是最快的。這一切都決定了Lua是作為嵌入式指令碼的最佳選擇。
Lua由標準C編寫而成，幾乎在所有作業系統和平台上都可以編譯，運行。Lua並沒有提供強大的庫，這是由它的定位決定的。所以Lua不適合作為開發獨立應用程式的語言。
lua官網：http://www.lua.org/
下載: mysql-proxy
http://dev.mysql.com/downloads/mysql-proxy/

二、安裝環境

系統內容：CentOS release 6.9 64bit  
軟體名稱：mysql-5.7.18

軟體使用者：mysql

軟體安裝位置：/usr/bin/、/usr/share/、/usr/local/mysql-proxy

資料存放位置：/var/lib/mysql

日誌存放位置：/var/log/mysqld.log

三、主從設計
首先設計主從的安裝分配方式，共需要3台伺服器，伺服器分配如下：

    master節點：        192.168.99.35
    mysql-proxy節點：    192.168.99.36
    slave節點：           192.168.99.37



四、安裝部署

1、三台伺服器線上安裝mysql

[email protected] mysql]# rpm –Uvh https://repo.mysql.com//mysql57-community-release-el6-11.noarch.rpm

[email protected] mysql]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-libs

網速慢可以先下載rpm包本地rpm或yum安裝

[[email protected] home]# mkdir /home/mysql/

[[email protected] home]# cd /home/mysql/

[[email protected] mysql]# ls

mysql-community-client-5.7.18-1.el6.x86_64.rpm

mysql-community-common-5.7.18-1.el6.x86_64.rpm

mysql-community-libs-5.7.18-1.el6.x86_64.rpm

mysql-community-server-5.7.18-1.el6.x86_64.rpm

[email protected] mysql]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-libs

2、修改密碼原則配置簡單密碼便於測試

第一次通過#grep "password" /var/log/mysqld.log 命令擷取MySQL的臨時密碼

用該密碼登入到服務端後，必須馬上修改密碼，不然操作查詢時報錯誤

剛開始設定的密碼必須符合長度，且必須含有數字，小寫或大寫字母，特殊字元。

如果想設定簡單密碼，如下操作：

首先，修改validate_password_policy參數的值

[[email protected] mysql]# grep "password" /var/log/mysqld.log 

2017-05-28T23:15:52.739913Z 1 [Note] A temporary password is generated for [email protected]: xnzb:ff-h1G_

mysql> set global validate_password_policy=0;  #定義複雜度

mysql> set global validate_password_policy=0;  #定義複雜度

mysql> set global validate_password_length=1;  #定義長度 預設是8

mysql>set password for ‘root‘@‘localhost‘=password(‘123456‘);

3、在99.36上安裝lua

[[email protected] ~]# yum -y install lua

4、在99.36上安裝mysql-proxy

推薦採用已經編譯好的二進位版本，因為採用源碼包進行編譯時間，最新版的MySQL-Proxy對automake，glib以及libevent的版本都有很高的要求，而這些軟體包都是系統的基礎套件，不建議強行進行更新。並且這些已經編譯好的二進位版本在解壓後都在統一的目錄內，因此建議選擇以下版本：

1）下載安裝mysql-proxy

[[email protected] home]# wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

[[email protected] mysql-proxy]# tar -xvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/ && cd /usr/local/ && mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ ./mysql-proxy && cd /usr/local/mysql-proxy

2）修改系統內容變數

vim /etc/profile

export PATH=/usr/local/mysql-proxy/bin/:/usr/local/mysql/bin:$PATH    //增加這個變數

[[email protected] local]# source !$    //使系統內容變數生效

source /etc/profile

3）修改mysql-proxy設定檔實現讀寫分離

vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

將預設配置

min_idle_connections = 4,

max_idle_connections = 8,

修改為

min_idle_connections = 1,

max_idle_connections = 8,

修改預設串連，進行快速測試，預設最小4個以上的用戶端串連才會實現讀寫分離,最大連結數為8。 
註：為了驗證實驗效果將他改成1 .就是當有一個連結的時候，就實現讀寫分離的功能。

4）分別在99.35和99.37上建立資料庫和表，分別用於實現寫和讀操作

mysql -uroot -p123456

mysql> create database db;

mysql> use db;

mysql> create table test(id int);

mysql> insert into test values(35);

mysql> grant all on db.* to [email protected]‘%‘ identified by ‘123456‘;

mysql>flush privileges;

mysql -uroot -p123456

mysql> create database db;

mysql> use db;

mysql> create table test(id int);

mysql> insert into test values(37);

mysql> grant all on db.* to [email protected]‘%‘ identified by ‘123456‘;

mysql>flush privileges;

5）在99.36啟動MYSQL-PROXY服務

[[email protected] local]# mysql-proxy --proxy-read-only-backend-addresses=192.168.99.37:3306 --proxy-backend-addresses=192.168.99.35:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &

[1] 2551

[[email protected] local]# 2017-06-11 10:34:09: (critical) plugin proxy 0.8.5 started

6）參數說明

--proxy-read-only-backend-addresses=192.168.99.37:3306  # 定義後端唯讀伺服器 
--proxy-backend-addresses=192.168.99.35:3306   #定義後端mysql主伺服器地址，指定mysql寫主伺服器的連接埠

--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &  #指定lua指令碼，在這裡，使用的是rw-splitting指令碼，用於讀寫分離

當有多個唯讀伺服器時，可以寫多個以下參數：

當有多個唯讀伺服器時，可以寫多個以下參數：
--proxy-read-only-backend-addresses=192.168.1.64:3306  # 定義後端唯讀伺服器
--proxy-read-only-backend-addresses=192.168.1.65:3306  # 定義後端唯讀伺服器
#--proxy-address=192.168.1.62:3307 指定mysql proxy的監聽連接埠，預設為：4040

完整的參數可以運行以下命令查看

mysql-proxy --help-all

7）查看mysql-proxy是否啟動

[[email protected] local]# lsof -i :4040

COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME

mysql-pro 3144 root    9u  IPv4  19597      0t0  TCP *:yo-main (LISTEN)

8）測試讀寫分離

第一台伺服器登入99.36查詢

[[email protected] ~]# mysql -uuser1 -p123456 -P4040 -h192.168.99.36

mysql> use db;

Database changed

mysql> select * from test;

+------+

| id   |

+------+

|   35 |

+------+

1 row in set (0.00 sec)

mysql> 

插入後再進行查詢

mysql> insert into test values(36);

Query OK, 1 row affected (0.04 sec)

mysql> select * from test;

+------+

| id   |

+------+

|   35 |

|   36 |

+------+

1 row in set (0.00 sec)

mysql> 

第二台伺服器登入99.36查詢

[[email protected] ~]# mysql -uuser1 -p123456 -P4040 -h192.168.99.36

mysql> use db;

Database changed

mysql> select * from test;

+------+

| id   |

+------+

|   37 |

+------+

1 row in set (0.00 sec)

mysql>

說明讀寫分離已成功；

9）查看用戶端理解狀態

mysql> show processlist;

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

| Id | User  | Host                | db   | Command | Time | State    | Info             |

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

|  5 | user1 | 192.168.99.36:45314 | db   | Sleep   |    2 |          | NULL             |

|  6 | user1 | 192.168.99.36:45316 | db   | Query   |    0 | starting | show processlist |

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

2 rows in set (0.00 sec)

mysql> 

10）狀態參數說明；

每列參數說明：
第一列， id ，一個標識 
user列， 顯示目前使用者，如果不是 root ，這個命令就只顯示你許可權範圍內的 sql 語 句。 
host 列，顯示這個語句是從哪個 ip 的哪 個連接埠上發出的。可以用來追蹤出問題語句的使用者。 
db 列，顯示這個進程目前串連的是哪個資料庫 。 
command 列，顯示當前串連的執行的命令，一般就是休眠（ sleep ），查詢（ query ），串連（ connect ）。 
time 列，此這個狀態持續的時間，單位是秒。 
state 列，顯示使用當前串連的 sql 語句的狀態，很重要的列， state 只是語句執行中的某一個狀態，一個 sql 語句，以查詢為例，可能需要經過 copying to tmp table ，Sorting result ， Sending data 等狀態才可以完成。 

info 列，顯示這個 sql 語句，因為長度有限，所以長的 sql 語句就顯示不全，但是一個判斷問題語句的重要依據。

5、在99.35配置mysql master並在99.37配置mysql slave實現主從複製

1）配置主伺服器設定檔，啟用log-bin功能

[[email protected] mysql]#vim /etc/my.cnf

log-bin=mysql-bin-master  #啟用二進位日誌

server-id=1   #本機資料庫ID 標示

binlog-do-db=db #可以被從伺服器複製的庫。二進位需要同步的資料庫名

binlog-ignore-db=mysql  #不可以被從伺服器複製的庫

2）授權允許slave 37訪問35

mysql> grant replication slave on *.* to [email protected]  identified by "123456"; 

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

查看mster狀態

mysql> show master status;

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

| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin-master.000001 |      154 | db           | mysql            |                   |

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

1 row in set (0.00 sec)

3）設定管理員37為slave

[[email protected] ~]# vim /etc/my.cnf

server-id=2   #本機資料庫ID 標示

4）配置37根據master變化

mysql -uroot -p123456

mysql> change master to master_host=‘192.168.99.35‘,master_user=‘slave‘,master_password=‘123456‘;

mysql> flush privileges;

5）啟動slave並查看狀態

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G

6）在35上插入資料

mysql -uroot -p123456

mysql> use db;

Database changed

mysql> insert into test values(36);

Query OK, 1 row affected (0.08 sec)

mysql> select * from test;

+------+

| id   |

+------+

|   35 |

|   36 |

+------+

2 rows in set (0.00 sec)

mysql> 

7）在37上進行查看

[[email protected] ~]# mysql -u root -p123456

mysql> use db;

Database changed

mysql> select * from test;

+------+

| id   |

+------+

|   37 |

|   36 |

+------+

2 rows in set (0.00 sec)

mysql> 

可以看到新插入的36已同步過去；

8）外部登入99.36查詢也可以看到分別的主備資料已同步

[[email protected] ~]# mysql -uuser1 -p123456 -P4040 -h192.168.99.36

mysql>use db;

mysql> select * from test;

+------+

| id   |

+------+

|   35 |

|   36 |

+------+

2 rows in set (0.00 sec)

mysql> 

另外一台伺服器登入99.36的查詢情況

[[email protected] ~]# mysql -uuser1 -p123456 -P4040 -h192.168.99.36

mysql> use db;

Database changed

mysql> select * from test;

+------+

| id   |

+------+

|   37 |

|   36 |

+------+

2 rows in set (0.00 sec)

9）外部登入99.36插入及查詢情況

[[email protected] ~]# mysql -uuser1 -p123456 -P4040 -h192.168.99.36

mysql> use db;

Database changed

mysql> insert into test values(100);

Query OK, 1 row affected (0.07 sec)

mysql> insert into test values(101);

Query OK, 1 row affected (0.08 sec)

mysql> select * from test;

+------+

| id   |

+------+

|   37 |

|   36 |

|  100 |

|  101 |

+------+

4 rows in set (0.00 sec)

mysql> 

在99.35上查詢確認資料已插入並且同步；

mysql> select * from test;

+------+

| id   |

+------+

|   35 |

|   36 |

|  100 |

|  101 |

+------+

4 rows in set (0.00 sec)

mysql> 

10）解決插入的35和37不一致問題

在99.35上禁用同步功能

[[email protected] mysql]# vim /etc/my.cnf

#log-bin=mysql-bin-master  #不啟用二進位日誌

重啟mysql

[[email protected] mysql]# /etc/init.d/mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

刪除35

[[email protected] mysql]# mysql -u root -p123456

mysql> delete from test where id=35;

Query OK, 1 row affected (0.06 sec)

mysql> select * from test;

+------+

| id   |

+------+

|   36 |

|  100 |

|  101 |

+------+

3 rows in set (0.00 sec)

在99.37上禁用salve並刪除37

mysql> stop slave;

Query OK, 0 rows affected (0.02 sec)

mysql> delete from test where id=37;

Query OK, 1 row affected (0.04 sec)

mysql> select * from test;

+------+

| id   |

+------+

|   36 |

|  100 |

|  101 |

+------+

3 rows in set (0.00 sec)

重新啟動同步功能即可

在99.35上啟用同步功能

[[email protected] mysql]# vim /etc/my.cnf

log-bin=mysql-bin-master  #啟用二進位日誌

重啟mysql

[[email protected] mysql]# /etc/init.d/mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

在99.37上啟動slave即可

mysql> stop slave;

外部插入和查詢即可正常

本文出自 “明日靈感” 部落格，請務必保留此出處http://coolner.blog.51cto.com/957576/1934221

mysql主從複製讀寫分離