Mysql主從與分區(qū)技術(shù)分析

1、MySQL主從復(fù)制與分區(qū)技術(shù)主講:李捷QQ:86267659個(gè)人主頁(yè):www.li-jie.me郵箱:微博: bin-log日志3.Mysql主從復(fù)制4.Mysql分區(qū)技術(shù)Taobao MysqlMysql用戶授權(quán)登錄mysql數(shù)據(jù)庫(kù)mysqlmysql -uroot -p test給從服務(wù)器設(shè)置授權(quán)用戶授權(quán)：grant all on *.* to user identified by pass;刷新?tīng)顟B(tài)：flush privileges; 查看用戶授權(quán)表select user,host,password from mysql.user;Mysql bin-log日志開(kāi)

2、啟Mysql bin-log日志vi /etc/f mysqldport = 3306socket = /var/lib/mysql/mysql.socklog-slow-queries=mysql-slow.loglog-error=mysql.errlog=mysql.loglog-bin=mysql-bin查看bin-log日志Show variables like “%log%”;Mysql中查看ls /var/lib/mysql 與bin-log有關(guān)的日志刷新mysqlflush logs;此時(shí)就會(huì)多一個(gè)最新的bin-log日志mysqlshow master status;查看最后

3、一個(gè)bin日志.mysqlreset master;清空所有的bin-log日志mysqlmysqlbinlog -no-defaults mysql-00001.bin用mysqlbinlog mysql-00001.bin時(shí)報(bào)錯(cuò),說(shuō)是字符集有問(wèn)題Mysql備份和bin-log日志備份數(shù)據(jù):mysqldump -uroot -pwei test -l -F /tmp/test.sql-F即flush logs，可以重新生成新的日志文件，當(dāng)然包括log-bin日志 查看binlog日志用mysqlshow master status Mysql恢復(fù)與bin-log日志mysql -uroot

4、-pwei test -v -f show tables;+-+| Tables_in_test |+-+| t1 |+-+1 row in set (0.01 sec)2).添加兩行數(shù)據(jù):mysql select * from t1;+-+| id |+-+| 1 | 2 |+-+2 rows in set (0.00 sec)3).備份現(xiàn)有數(shù)據(jù):rootlocalhost mysql# mysqldump -uroot -pwei test -l -F t1.sql4).在備份完后又添加了三行數(shù)據(jù)mysql insert into t1 values(3);Query OK, 1 row

5、affected (0.01 sec)mysql insert into t1 values(4);Query OK, 1 row affected (0.01 sec)mysql insert into t1 values(6);Query OK, 1 row affected (0.01 sec)5).此時(shí)突然數(shù)據(jù)庫(kù)損壞或者人為刪除:mysql drop table t1;Query OK, 0 rows affected (0.02 sec)6).用已經(jīng)備份的t1.sql來(lái)恢復(fù)數(shù)據(jù):rootlocalhost mysql# mysql -uroot -pwei test mysqlbin

6、log -no-defaults mysql-bin.000004|more#120102 23:55:35 server id 1 end_log_pos 617 Queryinsert into t1 values(6)#120102 23:55:35 server id 1 end_log_pos 644 Xid = 55COMMIT/*!*/;#這上添加6這條記錄的記錄,所以用bin-log恢復(fù)到644即可.8).開(kāi)始恢復(fù):# mysqlbinlog -no-defaults -stop-position=644 mysql-bin.000004|mysql -uroot -pwei

7、test9).最后查看t1的數(shù)據(jù):mysql select * from t1;+-+| id |+-+| 1 | 2 | 3 | 4 | 6 |+-+5 rows in set (0.00 sec)10).ok!恢復(fù)完畢,這注是mysqldump+binlog來(lái)恢復(fù)數(shù)據(jù)的完整過(guò)程.11).以下是mysqlbinlog后跟的主要參數(shù):-stop-position=100-start-position=50-stop-date=2012-01-04 21:17:50-start-date=2012-01-04 19:10:10Mysql主從復(fù)制作用 mysql復(fù)制的優(yōu)點(diǎn)主要包括以下3個(gè)方面:1.

8、如果主服務(wù)器出現(xiàn)問(wèn)題，可以快速切換到從服務(wù)器提供的服務(wù)2.可以在從服務(wù)器上執(zhí)行查詢操作，降低主服務(wù)器的訪問(wèn)壓力3.可以在從服務(wù)器上執(zhí)行備份，以避免備份期間影響主服務(wù)器的服務(wù)注意一般只有更新不頻繁的數(shù)據(jù)或者對(duì)實(shí)時(shí)性要求不高的數(shù)據(jù)可以通過(guò)從服務(wù)器查詢，實(shí)時(shí)性要求高的數(shù)據(jù)仍然需要從主數(shù)據(jù)庫(kù)獲得主服務(wù)器配置 1)登錄mysql數(shù)據(jù)庫(kù)mysqlmysql -uroot -p test2)給從服務(wù)器設(shè)置授權(quán)用戶mysqlgrant all on *.* to user identified by pass;或mysqlgrant replication slave on *.* t

9、o user identified by pass;3)修改主數(shù)據(jù)庫(kù)服務(wù)器的配置文件f，開(kāi)戶binlog，并設(shè)置server-id的值log-bin=mysql-binserver-id=14)在主服務(wù)器上設(shè)置讀鎖定有效,確保沒(méi)有數(shù)據(jù)庫(kù)操作，以便獲得一個(gè)一致性的快照:(選做)mysqlflush tables with read lock;5)查看主服務(wù)器上當(dāng)前的二進(jìn)制日志名和偏移量值mysqlshow master status;6)目前主數(shù)據(jù)庫(kù)服務(wù)器已經(jīng)停止了更新操作,生成主數(shù)據(jù)庫(kù)的備份,備份的方式有兩種:(1)cp全部的數(shù)據(jù)(2)mysqldump備份數(shù)據(jù)方法如果

10、主數(shù)據(jù)庫(kù)的服務(wù)可以停止，那么直接cp數(shù)據(jù)文件應(yīng)該是最快的生成快照的方法:7)主數(shù)據(jù)庫(kù)備份完畢后，主數(shù)據(jù)庫(kù)可以恢復(fù)寫(xiě)操作，剩下的操作只需要在從服務(wù)器上去執(zhí)行:mysqlunlock tables;8)把主數(shù)據(jù)庫(kù)的一致性備份恢復(fù)到從數(shù)據(jù)庫(kù)上,把以上的壓縮包解壓后放到相應(yīng)的目錄即可.Mysql從服務(wù)器配置 1)修改從數(shù)據(jù)庫(kù)的server-id，注意server-id的值必須是唯一的，不能和主數(shù)據(jù)庫(kù)的配置相同，如果有多個(gè)從服務(wù)器，每個(gè)從服務(wù)器必須有自己唯一的server-id值.連接主服務(wù)器(1)在從服務(wù)器上的配置文件中:server-id = 2master-host=mas

11、ter-user=usermaster-password=passmaster-port=3306log-bin=mysql-bin#replicate-do-db=test#replicate-do-table=test.t1(2)重新啟動(dòng)mysqld服務(wù):pkill mysqld/usr/local/mysql/bin/mysqld_safe -user=msyql &3)查看相應(yīng)的主從復(fù)制進(jìn)程列表有兩種:(1)processlistmysqlshow processlist G如出現(xiàn):state:waiting for master to send event/連接主數(shù)據(jù)為成功，

12、而且成功獲取bin-logstate:has read all ready log;waiting for the slave i/o thread to update it /成功執(zhí)行bin-log日志,正在等待著去再次連接主數(shù)據(jù)庫(kù)并更新獲取bin-log日志.(2)status;mysqlshow slave statusG如出現(xiàn):Slave_IO_Running: Yes/此進(jìn)程負(fù)責(zé)從服務(wù)器從主服務(wù)器上讀取binlog日志，并寫(xiě)入從服務(wù)器上的中繼日志中.Slave_SQL_Running: Yes/此進(jìn)程負(fù)責(zé)讀取并且執(zhí)行中繼日志中的binlog日志,#注以上兩個(gè)都為yes則表明成功，只要

13、其中一個(gè)進(jìn)程的狀態(tài)是no，則表示復(fù)制進(jìn)程停止，錯(cuò)誤原因可以從last_error字段的值中看到.4)從數(shù)據(jù)庫(kù)常用命令:(1)start slave#啟動(dòng)復(fù)制線程(2)stop slave#停止復(fù)制線程(3)show slave status#查看從數(shù)據(jù)庫(kù)狀態(tài)(4)show master logs#查看主數(shù)據(jù)庫(kù)bin-log日志(5)change master to #動(dòng)態(tài)改變到主服務(wù)器的配置(6)show processlist#查看從數(shù)據(jù)庫(kù)運(yùn)行進(jìn)程Mysql常見(jiàn)錯(cuò)誤 從數(shù)據(jù)庫(kù)無(wú)法同步:Show slave status顯示Slave_SQL_Running為No,Seconds_Behind

14、_Master為null原因:a.程序可能在slave上進(jìn)行了寫(xiě)操作b.也可能是slave機(jī)器重起后，事務(wù)回滾造成的.解決:方法一Mysqlslave stop;Mysqlset GLOBAL SQL_SLAVE_SKIP_COUNTER=1;Mysqlslave start;解決:方法二Slave 庫(kù)，MySQL slave stop; -停掉slave服務(wù)Master 庫(kù)，MySQL show master status;得到主服務(wù)器上當(dāng)前的二進(jìn)制日志名和偏移量+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+

15、-+-+-+| mysql-bin.000005 | 106 | | |+-+-+-+-+查看狀態(tài) ，然后到slave服務(wù)器上執(zhí)行手動(dòng)同步mysqlchange master tomaster_host=,master_user=user,master_password=pass,master_port=3306,master_log_file=mysql-bin.000005,master_log_pos= 106;啟動(dòng)slave服務(wù)，Mysqlslave start;通過(guò)show slave status查看Slave_SQL_Running為Yes,Seconds

16、_Behind_Master為0 即為正常大數(shù)據(jù)存儲(chǔ)當(dāng) MySQL中一個(gè)表的總記錄數(shù)超過(guò)了1000萬(wàn)，會(huì)出現(xiàn)性能的大幅度下降嗎？答案是肯定的。但性能下降的比率由系統(tǒng)的架構(gòu)、應(yīng)用程序、數(shù)據(jù)庫(kù)索引、服務(wù)器硬件等多種因素而定。數(shù)據(jù)庫(kù)多達(dá)上億的數(shù)據(jù)量，分表之后的單個(gè)表也已經(jīng)突破千萬(wàn)，那么單個(gè)表的更新等均影響著系統(tǒng)的運(yùn)行效率。甚至是一條簡(jiǎn)單的SQL都有可能壓垮整個(gè)數(shù)據(jù)庫(kù)，如整個(gè)表對(duì)某個(gè)字段的排序操作等 解決方案目前，針對(duì)海量數(shù)據(jù)的優(yōu)化主要有2種方法：大表拆小表的方式、SQL語(yǔ)句的優(yōu)化SQL語(yǔ)句的優(yōu)化：可以通過(guò)增加索引等來(lái)調(diào)整，但是數(shù)據(jù)量的增大將會(huì)導(dǎo)致索引的維護(hù)代價(jià)增大 大表拆小表垂直分表對(duì)于垂直分表，它

17、將一個(gè)N1+N2個(gè)字段的表Tab拆分成N1字段的子表Tab1和(N2+1)字段的子表Tab2；其中子表Tab2包含了關(guān)于子表Tab1的主鍵信息，否則兩個(gè)表的關(guān)聯(lián)關(guān)系就會(huì)丟失。當(dāng)然垂直分表會(huì)帶來(lái)程序端SQL的修改，若是應(yīng)用程序已經(jīng)應(yīng)用很長(zhǎng)的一段時(shí)間，然后程序的升級(jí)將是耗時(shí)而且易出錯(cuò)的，即升級(jí)的代價(jià)將會(huì)很大 水平分表水平分區(qū)技術(shù)將一個(gè)表拆成多個(gè)表，比較常用的方式是將表中的記錄按照某種Hash算法進(jìn)行拆分，簡(jiǎn)單的拆分方法如取模方式。同樣，這種分區(qū)方法也必須對(duì)前端的應(yīng)用程序中的SQL進(jìn)行修改方可使用。而且對(duì)于一個(gè)SQL，它可能會(huì)修改兩個(gè)表，那么你必須得寫(xiě)成2個(gè)SQL語(yǔ)句從而可以完成一個(gè)邏輯的事務(wù)，使得

18、程序的判斷邏輯越來(lái)越復(fù)雜，這樣也會(huì)導(dǎo)致程序的維護(hù)代價(jià)高，也就失去了采用數(shù)據(jù)庫(kù)的優(yōu)勢(shì)。因此，分區(qū)技術(shù)可以有力地避免如上的弊端，成為解決海量數(shù)據(jù)存儲(chǔ)的有力方法 Mysql分區(qū)介紹 MySQL的分區(qū)技術(shù)不同與之前的分表技術(shù)，它與水平分表有點(diǎn)類似，但是它是在邏輯層進(jìn)行的水平分表，對(duì)與應(yīng)用程序而言它還是一張表，MySQL5.1有4種分區(qū)類型: RANGE分區(qū):基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)LIST分區(qū):類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇HASH分區(qū):基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行

19、的列值進(jìn)行計(jì)算，這個(gè)函數(shù)可以包含MySQL中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式KEY分區(qū):類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)RANGE分區(qū) 例1：假定你創(chuàng)建了一個(gè)如下的表，該表保存有20家音像店的職員記錄，這20家音像店的編號(hào)從1到20。如果你想將其分成4個(gè)小分區(qū)，那么你可以采用RANGE分區(qū)，創(chuàng)建的數(shù)據(jù)庫(kù)表如下:例2：假定你創(chuàng)建了一個(gè)如下的表，該表保存有20家音像店的職員記錄，這20家音像店的編號(hào)從1到20。你想把不同時(shí)期離職的員工進(jìn)行分別存儲(chǔ)，那么你可以將日期字段separated（即離職時(shí)間）作為一個(gè)key，創(chuàng)建的SQL語(yǔ)

20、句如下 這樣你就可以對(duì)一個(gè)日期類型的字段調(diào)用mysql的日期函數(shù)YEAR()轉(zhuǎn)換為一種整數(shù)類型，從而可以作為RANGE分區(qū)的key。這個(gè)時(shí)候你可以看到，分區(qū)后的物理文件是相對(duì)獨(dú)立的LIST分區(qū) LIST分區(qū)與RANGE分區(qū)有類似的地方，舉個(gè)與例1類似的例子如下：例3：假定你創(chuàng)建了一個(gè)如下的一個(gè)表，該表保存有20家音像店的職員記錄，這20家音像店的編號(hào)從1到20。而這20個(gè)音像店，分布在4個(gè)有經(jīng)銷權(quán)的地區(qū)，如下表所示:那么你可以采用如下的LIST分區(qū)語(yǔ)句創(chuàng)建數(shù)據(jù)表:同樣，它在物理文件上也會(huì)標(biāo)識(shí)不同的分區(qū):HASH分區(qū) HASH分區(qū)主要用來(lái)確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布。它可以基于用戶定義

21、的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算例4：假定你創(chuàng)建了一個(gè)如下的一個(gè)表，該表保存有20家音像店的職員記錄，這20家音像店的編號(hào)從1到20。你想把不同時(shí)期加入的員工進(jìn)行分別存儲(chǔ)，那么你可以將日期字段hired（即入職時(shí)間）作為一個(gè)key，創(chuàng)建的SQL語(yǔ)句如下:KEY分區(qū) 與HASH分區(qū)類似，但它的key可以不是整數(shù)類型，如字符串等類型的字段。MySQL 簇（Cluster）使用函數(shù)MD5()來(lái)實(shí)現(xiàn)KEY分區(qū)；對(duì)于使用其他存儲(chǔ)引擎的表，服務(wù)器使用其自己內(nèi)部的哈希函數(shù)，這些函數(shù)是基于與PASSWORD()一樣的運(yùn)算法則 不同分區(qū)技術(shù)的對(duì)比 如上分別列出了

22、不同的分區(qū)技術(shù)，接下來(lái)進(jìn)行對(duì)比，如下表所示:Mysql分區(qū)實(shí)例創(chuàng)建分區(qū)表,按日期的年份拆分mysql CREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam PARTITION BY RANGE (year(c3) (PARTITION p0 VALUES LESS THAN (1995),PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (19

23、97) ,PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS

24、 THAN (2010),PARTITION p11 VALUES LESS THAN MAXVALUE ); 注意最后一行，考慮到可能的最大值未分區(qū)表創(chuàng)建未分區(qū)表mysql create table no_part_tab (c1 int(11) default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisam;創(chuàng)建load_part_tab存儲(chǔ)通過(guò)存儲(chǔ)過(guò)程灌入800萬(wàn)條測(cè)試數(shù)據(jù)mysql delimiter / /* 設(shè)定語(yǔ)句終結(jié)符為 /，因存儲(chǔ)過(guò)程語(yǔ)句用;結(jié)束 */mysqlCREATE PROCED

25、URE load_part_tab() begin declare v int default 0; while v delimiter ;執(zhí)行l(wèi)oad_part_tab存儲(chǔ)mysql call load_part_tab();Query OK, 1 row affected (8 min 17.75 sec)mysql insert into no_part_tab select * from part_tab;Query OK, 8000000 rows affected (51.59 sec)Records: 8000000 Duplicates: 0 Warnings: 0測(cè)試SQL性

26、能mysql select count(*) from part_tab where c3 date 1995-01-01 and c3 select count(*) from no_part_tab where c3 date 1995-01-01 and c3 explain select count(*) from no_part_tab where c3 date 1995-01-01 and c3 explain select count(*) from part_tab where c3 date 1995-01-01 and c3 create index idx_of_c3

27、on no_part_tab (c3);Query OK, 8000000 rows affected (1 min 18.08 sec)Records: 8000000 Duplicates: 0 Warnings: 0mysql create index idx_of_c3 on part_tab (c3);Query OK, 8000000 rows affected (1 min 19.19 sec)Records: 8000000 Duplicates: 0 Warnings: 0 測(cè)試索引后效果mysql select count(*) from no_part_tab where

28、 c3 date 1995-01-01 and c3 select count(*) from part_tab where c3 date 1995-01-01 and c3 select count(*) from part_tab where c3 date 1995-01-01 and c3 select count(*) from no_part_tab where c3 date 1995-01-01 and c3 date 1996-12-31 and c2=hello;+-+| count(*) |+-+| 0 |+-+1 row in set (11.52 sec)innod

29、b表數(shù)據(jù)結(jié)構(gòu)對(duì)于innodb的數(shù)據(jù)結(jié)構(gòu)，首先要解決兩個(gè)概念性的問(wèn)題: 共享表空間以及獨(dú)占表空間什么是共享表空間和獨(dú)占表空間共享表空間以及獨(dú)占表空間都是針對(duì)數(shù)據(jù)的存儲(chǔ)方式而言的。共享表空間: 某一個(gè)數(shù)據(jù)庫(kù)的所有的表數(shù)據(jù)，索引文件全部放在一個(gè)文件中，默認(rèn)這個(gè)共享表空間的文件路徑在data目錄下。 默認(rèn)的文件名為:ibdata1 初始化為10M。獨(dú)占表空間: 每一個(gè)表都將會(huì)生成以獨(dú)立的文件方式來(lái)進(jìn)行存儲(chǔ)，每一個(gè)表都有一個(gè).frm表描述文件，還有一個(gè).ibd文件。 其中這個(gè)文件包括了單獨(dú)一個(gè)表的數(shù)據(jù)內(nèi)容以及索引內(nèi)容，默認(rèn)情況下它的存儲(chǔ)位置也是在表的位置之中 innodb共享表空間共享表空間：優(yōu)點(diǎn)：可以放表空間分成多個(gè)文件存放到各個(gè)磁盤上（表空間文件大小不受表大小的限制，如一個(gè)表可以分布在不同步的文件上）。數(shù)據(jù)和文件放在一起方便管理。缺點(diǎn)：所有的數(shù)據(jù)和索引存放到一個(gè)文件中以為著將有一個(gè)很常大的文件，雖然可以把一個(gè)大文件分成多個(gè)小文件，但是多個(gè)表及索引在表空間中混合存儲(chǔ)，這樣對(duì)于一個(gè)表做了大量刪除操作后表空間中將會(huì)有大量的空隙，特別是對(duì)于統(tǒng)計(jì)分析，日值系統(tǒng)這類應(yīng)用最不適合用共享表空間 innodb獨(dú)立表空間獨(dú)立表空間：在配置文件（f）中設(shè)置：