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

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

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有關的日志刷新mysqlflush logs;此時就會多一個最新的bin-log日志mysqlshow master status;查看最后

3、一個bin日志.mysqlreset master;清空所有的bin-log日志mysqlmysqlbinlog -no-defaults mysql-00001.bin用mysqlbinlog mysql-00001.bin時報錯,說是字符集有問題Mysql備份和bin-log日志備份數(shù)據(jù):mysqldump -uroot -pwei test -l -F /tmp/test.sql-F即flush logs，可以重新生成新的日志文件，當然包括log-bin日志 查看binlog日志用mysqlshow master status Mysql恢復與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ù)據(jù)庫損壞或者人為刪除:mysql drop table t1;Query OK, 0 rows affected (0.02 sec)6).用已經備份的t1.sql來恢復數(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恢復到644即可.8).開始恢復:# 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!恢復完畢,這注是mysqldump+binlog來恢復數(shù)據(jù)的完整過程.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主從復制作用 mysql復制的優(yōu)點主要包括以下3個方面:1.

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

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

10、主數(shù)據(jù)庫的服務可以停止，那么直接cp數(shù)據(jù)文件應該是最快的生成快照的方法:7)主數(shù)據(jù)庫備份完畢后，主數(shù)據(jù)庫可以恢復寫操作，剩下的操作只需要在從服務器上去執(zhí)行:mysqlunlock tables;8)把主數(shù)據(jù)庫的一致性備份恢復到從數(shù)據(jù)庫上,把以上的壓縮包解壓后放到相應的目錄即可.Mysql從服務器配置 1)修改從數(shù)據(jù)庫的server-id，注意server-id的值必須是唯一的，不能和主數(shù)據(jù)庫的配置相同，如果有多個從服務器，每個從服務器必須有自己唯一的server-id值.連接主服務器(1)在從服務器上的配置文件中: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)重新啟動mysqld服務:pkill mysqld/usr/local/mysql/bin/mysqld_safe -user=msyql &3)查看相應的主從復制進程列表有兩種:(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ù)庫并更新獲取bin-log日志.(2)status;mysqlshow slave statusG如出現(xiàn):Slave_IO_Running: Yes/此進程負責從服務器從主服務器上讀取binlog日志，并寫入從服務器上的中繼日志中.Slave_SQL_Running: Yes/此進程負責讀取并且執(zhí)行中繼日志中的binlog日志,#注以上兩個都為yes則表明成功，只要其中一個

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

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

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

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

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

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

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

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

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

22、區(qū)技術，接下來進行對比，如下表所示:Mysql分區(qū)實例創(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 (1997)

23、,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 THA

24、N (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存儲通過存儲過程灌入800萬條測試數(shù)據(jù)mysql delimiter / /* 設定語句終結符為 /，因存儲過程語句用;結束 */mysqlCREATE PROCEDURE

25、load_part_tab() begin declare v int default 0; while v delimiter ;執(zhí)行l(wèi)oad_part_tab存儲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測試SQL性能mys

26、ql 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 on n

27、o_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 測試索引后效果mysql select count(*) from no_part_tab where c3

28、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)innodb表數(shù)據(jù)

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