Mysql5.6.24的主從復(fù)制及用atlas實(shí)現(xiàn)讀寫分離

Mysql5.6.24的主從復(fù)制及用atlas實(shí)現(xiàn)讀寫分離在實(shí)際企業(yè)應(yīng)用環(huán)境當(dāng)中，單臺mysql數(shù)據(jù)庫是不足以滿足日后業(yè)務(wù)需求的。譬如服務(wù)器發(fā)生故障，沒有備份服務(wù)器來提供服務(wù)的話，業(yè)務(wù)就得停止。使用mysql主從復(fù)制的好處有：1、采用主從服務(wù)器這種架構(gòu)，穩(wěn)定性得以提升。如果主服務(wù)器發(fā)生故障，我們可以使用從服務(wù)器來提供服務(wù)。2、在主從服務(wù)器上分開處理用戶的請求，可以提升數(shù)據(jù)處理效率。3、將主服務(wù)器上的數(shù)據(jù)復(fù)制到從服務(wù)器上，保護(hù)數(shù)據(jù)免受意外的損失。環(huán)境描述：新企業(yè)要搭建架構(gòu)為主從復(fù)制的mysql數(shù)據(jù)庫。主服務(wù)器（mysql-master）：IP地址：80，mysql已安裝，沒有用戶數(shù)據(jù)。從服務(wù)器（mysql-slave）：IP地址：81，mysql已安裝，沒有用戶數(shù)據(jù)。主從服務(wù)器均可正常提供服務(wù)。主從復(fù)制配置如下：在主服務(wù)器上操作：1)、確保/etc/f中有如下參數(shù)，沒有的話需手工添加，并重啟mysql服務(wù)。[mysqld]log-bin=/usr/local/mysql/data/mysql/mysql-bin啟動(dòng)二進(jìn)制文件server-id=1服務(wù)器ID2)、登錄mysql，在mysql中添加一個(gè)repl的賬號，并授權(quán)給從服務(wù)器。[root@localhost~]#mysql-uroot–p123456登錄mysqlmysql>grantreplicationslaveon*.*to'repl'@'81'identifiedby'123';創(chuàng)建repl用戶，并授權(quán)給81使用。3)、查詢主數(shù)據(jù)庫狀態(tài)，并記下FILE及Position的值，這個(gè)在后面配置從服務(wù)器的時(shí)候要用到。mysql>showmasterstatus;在從服務(wù)器上操作：1)、確保/etc/f中有l(wèi)og-bin=mysql-bin和server-id=1參數(shù)，并把server-id=1修改為server-id=10。修改之后如下所示：[mysqld]log-bin=/usr/local/mysql/data/mysql/mysql-bin啟動(dòng)二進(jìn)制文件server-id=10服務(wù)器ID、重啟mysql服務(wù)。3)、登錄mysql，執(zhí)行如下語句[root@localhost~]#mysql-uroot–p123456mysql>changemastertomaster_host='80',master_user='repl',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=330;4)、啟動(dòng)slave同步。mysql>startslave;5)、檢查主從同步，如果您看到Slave_IO_Running和Slave_SQL_Running均為Yes，則主從復(fù)制連接正常。mysql>showslavestatus\G驗(yàn)證配置是否正常，mysql主從能否正常復(fù)制。在主數(shù)據(jù)庫上新建一個(gè)庫，并且在庫中寫一個(gè)表和一些數(shù)據(jù)。[root@localhost~]#mysql-uroot–p123456mysql>createdatabasemysqltest;mysql>usemysqltest;mysql>createtableuser(idint(5),namechar(10));mysql>insertintouservalues(00001,'zhangsan');在從數(shù)據(jù)庫中驗(yàn)證一下，是否正常復(fù)制到數(shù)據(jù)。[root@localhost~]#mysql-uroot–p123456mysql>showdatabases;mysql>select*frommysqltest.user;從上圖中的結(jié)果，我們可以看到mysql主從復(fù)制已經(jīng)在起作用了，我們在主數(shù)據(jù)庫中寫入的數(shù)據(jù)已經(jīng)復(fù)制到我們的從數(shù)據(jù)庫中了。環(huán)境如下：單臺數(shù)據(jù)庫存有數(shù)據(jù)，要升級為主從復(fù)制的架構(gòu)。主數(shù)據(jù)庫：mysql-master：IP80，數(shù)據(jù)庫正常提供服務(wù)，有數(shù)據(jù)。從數(shù)據(jù)庫：mysql-slave：IP81，數(shù)據(jù)庫正常提供服務(wù)，無數(shù)據(jù)。升級大致步驟如下：1、修改主數(shù)據(jù)庫配置文件，開啟主從復(fù)制必要的功能。2、在主數(shù)據(jù)庫中創(chuàng)建一個(gè)賬號并授權(quán)給從數(shù)據(jù)庫使用。3、導(dǎo)出主數(shù)據(jù)庫中的數(shù)據(jù)。4、修改從數(shù)據(jù)庫配置文件，開啟主從復(fù)制必要的功能。5、把剛才主數(shù)據(jù)庫中導(dǎo)出的數(shù)據(jù)導(dǎo)入到從數(shù)據(jù)庫。6、在從數(shù)據(jù)庫中配置連接主數(shù)據(jù)庫要用到的賬戶、密碼等。7、啟動(dòng)主從復(fù)制功能，并檢查是否正常復(fù)制。開始升級:在主數(shù)據(jù)庫上操作：1)、確保/etc/f中有如下參數(shù)，沒有的話需手工添加，并重啟mysql服務(wù)。[mysqld]log-bin=/usr/local/mysql/data/mysql/mysql-bin啟動(dòng)二進(jìn)制文件server-id=1服務(wù)器ID2)、登錄mysql，在mysql中添加一個(gè)repl的賬號，并授權(quán)給從服務(wù)器。[root@localhost~]#mysql-uroot–p123456登錄mysqlmysql>grantreplicationslaveon*.*to'repl'@'81'identifiedby'123';創(chuàng)建repl用戶，并授權(quán)給81使用。3)、查看已有的數(shù)據(jù)庫有哪些；mysql>showdatabases;4)、進(jìn)行鎖表操作，不讓數(shù)據(jù)進(jìn)行寫入動(dòng)作，這么做是為了防止從數(shù)據(jù)庫的原始數(shù)據(jù)和主數(shù)據(jù)庫的原始數(shù)據(jù)不一致。mysql>flushtableswithreadlock;5)、查詢主數(shù)據(jù)庫狀態(tài)，并記下FILE及Position的值，這個(gè)在后面配置從服務(wù)器的時(shí)候要用到。mysql>showmasterstatus;6)、切換一個(gè)終端，使用mysqldump命令將剛才查詢到的兩個(gè)庫導(dǎo)出來。mysql>mysqldump–uroot–p123456mysqltest>mysqltest.sqlmysql>mysqldump–uroot–p123456test1234>test1234.sql7)、將導(dǎo)出來的庫文件傳送到從數(shù)據(jù)庫的/root目錄下。[root@localhost~]#scpmysqltest.sqltest1234.sqlroot@81:/root/這中間需要輸入一次驗(yàn)證口令。見下圖8)、切換回之前的終端，進(jìn)行表解鎖操作。mysql>unlocktables;主數(shù)據(jù)庫服務(wù)器上的操作告一段落。在從數(shù)據(jù)庫上操作：1)、確保/etc/f中有l(wèi)og-bin=mysql-bin和server-id=1參數(shù)，并把server-id=1修改為server-id=10。修改之后如下所示：[mysqld]log-bin=/usr/local/mysql/data/mysql/mysql-bin啟動(dòng)二進(jìn)制文件server-id=10服務(wù)器ID2)、重啟mysql服務(wù)。3)、登錄數(shù)據(jù)庫，確認(rèn)要同步的庫名是否存在。[root@localhost~]#mysql-uroot–p123456mysql>showdatabases;4)、創(chuàng)建名為mysqltest和test1234的庫。mysql>createdatabasemysqltest;mysql>createdatabasetest1234;5)、切換一個(gè)終端，將傳過來的兩個(gè)數(shù)據(jù)文件分別導(dǎo)入對應(yīng)的數(shù)據(jù)庫下。[root@localhost~]#mysql-uroot–p123456mysqltest<mysqltest.sql[root@localhost~]#mysql-uroot–p123456test1234<test1234.sql6)、切換回之前的終端，執(zhí)行如下語句mysql>changemastertomaster_host='80',master_user='repl',master_password='123',master_log_file='mysql-bin.000003',master_log_pos=1650;7)、啟動(dòng)主從復(fù)制功能。mysql>startslave;8)、檢查主從同步，如果您看到Slave_IO_Running和Slave_SQL_Running均為Yes，則主從復(fù)制連接正常。mysql>showslavestatus\G驗(yàn)證主從是否正常配置：在主數(shù)據(jù)庫上插入數(shù)據(jù)：mysql>showdatabases;mysql>showtables;mysql>usemysqltest;mysql>select*fromuser;mysql>insertintouservalues(4,'ergou');mysql>insertintouservalues(5,'sanwazi');在從數(shù)據(jù)庫上查看是否正常同步：mysql>showdatabases;mysql>usemysqltest;mysql>select*fromuser;上圖可以看出，主從之間已經(jīng)可以正常復(fù)制了。Atlas安裝和配置：Atlas是由Qihoo360,Web平臺部基礎(chǔ)架構(gòu)團(tuán)隊(duì)開發(fā)維護(hù)的一個(gè)基于MySQL協(xié)議的數(shù)據(jù)中間層項(xiàng)目。它在MySQL官方推出的MySQL-Proxy0.8.2版本的基礎(chǔ)上，修改了大量bug，添加了很多功能特性。目前該項(xiàng)目在360公司內(nèi)部得到了廣泛應(yīng)用，很多MySQL業(yè)務(wù)已經(jīng)接入了Atlas平臺，每天承載的讀寫請求數(shù)達(dá)幾十億條。主要功能：*讀寫分離*從庫負(fù)載均衡*IP過濾*SQL語句黑白名單*自動(dòng)分表一、環(huán)境OS：CentOS-6.5x86_64AtlasIP:82MySQLMasterIP:80MySQLSlaveIP:81二、MySQL授權(quán)MySQL主從已經(jīng)配置好之后，主從對Atlas進(jìn)行授權(quán)mysql>grantallon*.*toatlas@'82'identifiedby'123456';Atlas安裝#rpm-ivhAtlas-2.2.1.el6.x86_64.rpmAtlas安裝后的文件路徑/usr/local/mysql-proxy/Atlas配置加密MySQL用戶密碼，獲取的加密串填寫到配置文件的pwds參數(shù)中。#/usr/local/mysql-proxy/bin/encrypt123456/iZxz+0GRoA=vim/usr/local/mysql_proxy/conf/test.conf[mysql-proxy]#帶#號的為非必需的配置項(xiàng)目#管理接口的用戶名admin-username=user#管理接口的密碼admin-password=pwd#Atlas后端連接的MySQL主庫的IP和端口，可設(shè)置多項(xiàng)，用逗號分隔proxy-backend-addresses=80:3306#Atlas后端連接的MySQL從庫的IP和端口，@后面的數(shù)字代表權(quán)重，用來作負(fù)載均衡，若省略則默認(rèn)為1，可設(shè)置多項(xiàng)，用逗號分隔proxy-read-only-backend-addresses=81:3306@1#用戶名與其對應(yīng)的加密過的MySQL密碼，密碼使用PREFIX/bin目錄下的加密程序encrypt加密，下行的user1和user2為示例，將其替換為你的MySQL的用戶名和加密密碼！pwds=atlas:/iZxz+0GRoA=#設(shè)置Atlas的運(yùn)行方式，設(shè)為true時(shí)為守護(hù)進(jìn)程方式，設(shè)為false時(shí)為前臺方式，一般開發(fā)調(diào)試時(shí)設(shè)為false，線上運(yùn)行時(shí)設(shè)為true,true后面不能有空格。daemon=true#設(shè)置Atlas的運(yùn)行方式，設(shè)為true時(shí)Atlas會啟動(dòng)兩個(gè)進(jìn)程，一個(gè)為monitor，一個(gè)為worker，monitor在worker意外退出后會自動(dòng)將其重啟，設(shè)為false時(shí)只有worker，沒有monitor，一般開發(fā)調(diào)試時(shí)設(shè)為false，線上運(yùn)行時(shí)設(shè)為true,true后面不能有空格。keepalive=true#工作線程數(shù)，對Atlas的性能有很大影響，可根據(jù)情況適當(dāng)設(shè)置event-threads=8#日志級別，分為message、warning、critical、error、debug五個(gè)級別log-level=message#日志存放的路徑log-path=/usr/local/mysql-proxy/log#SQL日志的開關(guān)，可設(shè)置為OFF、ON、REALTIME，OFF代表不記錄SQL日志，ON代表記錄SQL日志，REALTIME代表記錄SQL日志且實(shí)時(shí)寫入磁盤，默認(rèn)為OFF#sql-log=OFF#慢日志輸出設(shè)置。當(dāng)設(shè)置了該參數(shù)時(shí)，則日志只輸出執(zhí)行時(shí)間超過sql-log-slow（單位：ms)的日志記錄。不設(shè)置該參數(shù)則輸出全部日志。#sql-log-slow=10#實(shí)例名稱，用于同一臺機(jī)器上多個(gè)Atlas實(shí)例間的區(qū)分#instance=test#Atlas監(jiān)聽的工作接口IP和端口proxy-address=:1234#Atlas監(jiān)聽的管理接口IP和端口admin-address=:2345#分表設(shè)置，此例中person為庫名，mt為表名，id為分表字段，3為子表數(shù)量，可設(shè)置多項(xiàng)，以逗號分隔，若不分表則不需要設(shè)置該項(xiàng)#tables=person.mt.id.3#默認(rèn)字符集，設(shè)置該項(xiàng)后客戶端不再需要執(zhí)行SETNAMES語句#charset=utf8#允許連接Atlas的客戶端的IP，可以是精確IP，也可以是IP段，以逗號分隔，若不設(shè)置該項(xiàng)則允許所有IP連接，否則只允許列表中的IP連接#client-ips=,192.168.1#Atlas前面掛接的LVS的物理網(wǎng)卡的IP(注意不是虛IP)，若有LVS且設(shè)置了client-ips則此項(xiàng)必須設(shè)置，否則可以不設(shè)置#lvs-ips=啟動(dòng)/usr/local/mysql-proxy/bin/mysql-proxydteststart關(guān)閉/usr/local/mysql-proxy/bin/mysql-proxydteststop五、Atlas參數(shù)備注instance參數(shù)同一臺機(jī)器上的多個(gè)Atlas實(shí)例，使用不同的端口，對應(yīng)不同數(shù)據(jù)庫，使用各自的配置文件默認(rèn)的黑白名單不帶where的delete會屏蔽，"sleep"會屏蔽備注：1.03版本已經(jīng)支持setautocommit語句，另外對于python來說需要在連接上后執(zhí)行一句conn.autocommit(True)pwds參數(shù)配置MySQL用戶名和對應(yīng)的加密過的MySQL密碼，密碼使用/usr/local/mysql-proxy/bin/encrypt程序進(jìn)行加密，用戶名和密碼用冒號分開，可以使用多個(gè)用戶，用逗號空格