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端之間的簡單程序，它可以監測、分析或改變它們的通信。它使用靈活，沒有限制，常見的用途包括：負載平衡，故障、查詢分析，查詢過濾和修改等等。
MySQL Proxy就是這么一個中間層代理，簡單的說，MySQL Proxy就是一個連接池，負責將前臺應用的連接請求轉發給后臺的數據庫，并且通過使用lua腳本，可以實現復雜的連接控制和過濾，從而實現讀寫分離和負載平衡。對于應用來說，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

root@centos-6 mysql]# rpm –Uvh https://repo.mysql.com//mysql57-community-release-el6-11.noarch.rpm

root@centos-6 mysql]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-libs

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

[root@centos-6 home]# mkdir /home/mysql/

[root@centos-6 home]# cd /home/mysql/

[root@centos-6 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

root@centos-6 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參數的值

[root@centos-6 mysql]# grep "password" /var/log/mysqld.log 

2017-05-28T23:15:52.739913Z 1 [Note] A temporary password is generated for root@localhost: xnzb:ff-h2G_

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

mysql> set global validate_password_length=1;  #定義長度 默認是8

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

通過my.cnf 配置文件設置密碼策略的級別

validate_password_policy=2

最后一行 validate_password_policy 設置mysql啟動的時候密碼策略級別。 如果設置為3 ，那么需要指定字典文件。

當然你也可以通過 my.cnf 配置文件關閉 validate_password 插件。只需要添加一行 

validate_password = off

MySQL 新版本默認監聽在IPv6的地址族上。

更改為監聽IPv4地址族，修改 my.cnf 添加一行配置：

bind-address = 0.0.0.0

3、在99.36上安裝lua

[root@Centos-6-99 ~]# yum -y install lua

4、在99.36上安裝mysql-proxy

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

1）下載安裝mysql-proxy

[root@Centos-6-99 home]# wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

[root@Centos-6-99 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    //增加這個變量

[root@xuegod62 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 user1@'%' 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 user1@'%' identified by '123456';

mysql>flush privileges;

5）在99.36啟動MYSQL-PROXY服務

[root@Centos-6-99 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

[root@Centos-6-99 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是否啟動

[root@Centos-6-99 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查詢

[root@localhost ~]# mysql -uuser1 -p123456 -P4040 -h292.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查詢

[root@localhost ~]# mysql -uuser1 -p123456 -P4040 -h292.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功能

[root@centos-6 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 slave@192.168.99.37  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

[root@Centos-6-99 ~]# 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上進行查看

[root@Centos-6-99 ~]# 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查詢也可以看到分別的主備數據已同步

[root@localhost ~]# mysql -uuser1 -p123456 -P4040 -h292.168.99.36

mysql>use db;

mysql> select * from test;

+------+

| id   |

+------+

|   35 |

|   36 |

+------+

2 rows in set (0.00 sec)

mysql> 

另外一臺服務器登錄99.36的查詢情況

[root@localhost ~]# mysql -uuser1 -p123456 -P4040 -h292.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插入及查詢情況

[root@localhost ~]# mysql -uuser1 -p123456 -P4040 -h292.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上禁用同步功能

[root@centos-6 mysql]# vim /etc/my.cnf

#log-bin=mysql-bin-master  #不啟用二進制日志

重啟mysql

[root@centos-6 mysql]# /etc/init.d/mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

刪除35

[root@centos-6 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上啟用同步功能

[root@centos-6 mysql]# vim /etc/my.cnf

log-bin=mysql-bin-master  #啟用二進制日志

重啟mysql

[root@centos-6 mysql]# /etc/init.d/mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

在99.37上啟動slave即可

mysql> stop slave;

外部插入和查詢即可正常