MySQL的復制架構與優化

###########原理###########

1.主服務器將更新的數據的sql語句(例如，insert，update，delete等)寫入到

二進制文件中(由log-bin選項開啟)。此二進制文件由一個索引文件跟蹤維護。

2.從服務器連接(使用I/O線程連接)主服務器，將自己最后一次更新的位置通知

主服務器。然后，主服務器將把從‘從服務器’得知的位置開始之后的所有更新發

送給‘從服務器’(使用Binlog Dump線程來發送)，而后‘從服務器’再次使用I/O

線程讀取由Binlog Dump線程發送過來的數據，并將數據拷貝到本地的‘中繼二進

制文件'中。最后，再由SQL線程讀取’中繼二進制文件‘并執行其中的更新。

注：mysql的復制由三個線程來完成，一是，主服務器上的Binlog Dump線程；二

是，從服務器上的I/O線程(用來連接和讀取主服務更新，并拷貝到中繼二進制文

件)和SQL線程(用來讀取中繼二進制日志和執行更新)。

#######################################

#? ????? 主從架構????? #

#######################################

#############配置#############

注：此處使用的是 mysql-5.5.28的二進制包。安裝過程略。直接進行主從復制配置

##主服務器

1. 更改/etc/my.cnf：

server-id = 1???? #設置服務器唯一標識

log-bin=mysql-bin #開啟二進制日志功能

2. 添加復制用戶：

GRANT REPLICATION CLIENT,REPLICATION SLAVE TO 'repl'@'192.168.1.103'

IDENTIFIED BY '123';

##從服務器

1. 更改/etc/my.cnf:

server-id = 2?????#同主服務器

relay-log=relay-bin??? ?#開啟中繼日志

relay-log-index=relay-bin.index #開啟跟蹤中繼日志的索引，若未設置此選

項系統也會自動生成索引文件。

2. 啟動mysql并設置為從服務器

1. mysql -uroot -p

2. CHANGE MASTER TO MASTER_HOST='192.168.1.102',

MASTER_USER='repl',

MASTER_PASSWORD='123',

MASTER_PORT='3306';

3. START SLAVE;

4. SHOW SLAVE STATUS \G; 若Slave_IO_Running:和Slave_SQL_Running: 均顯示

Yes則說明從服務器配置成功。

注: SHOW SLAVE STATUS \G;顯示信息中的Seconds_Behind_Master: 表示從服務

器和主服務器數據相差的時間間隔。

5. 測試：在主服務上創建表或數據庫，查看是否在從服務器上有相同的表和數據庫。

若有，則主從復制搭建成功。

#############安全############

##阻止寫從服務器

1.修改/etc/my.cnf

[mysqld]

read-only = 1 # 此選項只對普通用戶起作用，對有SUPER權限的用戶無效。

2. FLUSH TABLES WITH READ LOCK；#為全局讀鎖命令，此時除了讀操作，其他操作無法執行

##實現半同步

說明：主——>從，為異步模式。mysql從5.5開始支持半同步模式復制，半同步插件為semisync,存儲

在/usr/local/mysql/plugin下。

1. 在主服務器，安裝semisync插件

CHANGE INSTALL rpl_semi_sync_master SONAME 'semisync_master.so';

查看是否安裝成功:

SHOW PLUGINS; #若有rpl_semi_sync_master 則安裝成功。

啟用半同步功能和設置超時時間：

SET GLOBAL rpl_semi_sync_master_enabled=1;

SET GLOBAL rpl_semi_sync_master_timeout=1000; #單位是ms，如果半同步在此設置的

時間內無法同步，則自動降回異步模式。

注：若使設置永久有效，把以上兩項寫入my.cnf的[mysqld]下即可。

2. 在從服務器，安裝semisync插件

CHANGE INSTALL rpl_semi_sync_slave SONAME 'semisync_slave.so';

查看是否安裝成功:

SHOW PLUGINS; #若有rpl_semi_sync_slave 則安裝成功。

啟用半同步功能和設置超時時間：

SET GLOBAL rpl_semi_sync_slave_enabled=1;

重啟slave：

stop slave;

start slave;

3. 檢測半同步功能是否已經生效

SHOW STATUS LIKE ‘rpl_%';

若Rpl_semi_sync_master_clients 的值不為0，則說明半同步功能已經生效。

##如何讓從服務器的mysql服務在啟動的時候，不自動啟動從服務線程？

說明：從服務器之所以在啟動的時候會自動啟動線程，是因為master.info和relay-log.info文件的存在。

master.info記錄的是CHANGE MASTER TO命令傳遞的參數；relay-log.info記錄的是當前從服務器所使用的

中繼日志的位置和從主服務器復制的二進制文件和所處的位置。

1. 在從服務器上，禁止自動啟動線程

更改my.cnf，加入以下選項：

[mysqld]

skip-slave-start=1

##數據庫復制過濾

主服務器：

1.[mysqld]

binlog-do-db=test ? #只復制test數據庫，相當于白名單。

binlog-ignore-db=mysql #除了mysql數據庫外不復制外，其他的都要復制，相當于黑名單。

注：一般這兩項不同時使用，若同時存在，則白名單生效。不過，在主服務器上做過濾有個缺陷，就是任何

涉及不到的數據庫，都不會記錄在二進制日志中。因此，大多情況下不在主服務器上做過濾。

從服務器：

1.[mysqld]

replicate-do-db=test1

replicate-ignore-db=test1

replicate-do-table=test2.t1

replicate-ignore-table=test2.t2

replicate-wild-do-table=test3.ta%

replicate-wild-ignore-table=test3.tb%

##防止事務提交和寫入日志，期間的服務器崩潰問題

主服務器：

1. [mysqld]

sync_binlog=1 #每次事件后立即同步到磁盤上的二進制日志文件中

innodb_flush_logs_at_trx_commit=1 #

#######################################

#? ????? 主主架構????? #

#######################################

說明：主主架構，即服務器互為主從。配置基本上和主從差不多。此處關鍵的是如果

數據庫的表中使用了auto_incremnet 關鍵字，則需要設置auto-increment-increment

和auto-increment-offset兩項以防止鍵值沖突。

##主服務器

1. GRANT REPLICATION CLIENT,REPLICATION SLAVE TO 't1'@'192.168.1.103'

IDENTIFIED BY '123';

2.?[mysqld]

server-id=10

log-bin=mysql-bin

auto-increment-increment=2

auto-increment-offset=1

3. mysql -uroot -p

4. CHANGE MASTER TO MASTER_HOST='192.168.1.102',

MASTER_USER='t2',

MASTER_PASSWORD='123',

MASTER_PORT='3306';

##從服務器

1. GRANT REPLICATION CLIENT,REPLICATION SLAVE TO 't2'@'192.168.1.102'

IDENTIFIED BY '123';

2.?[mysqld]

server-id=10

log-bin=mysql-bin

auto-increment-increment=2

auto-increment-offset=1

3. mysql -uroot -p

4. CHANGE MASTER TO MASTER_HOST='192.168.1.103',

MASTER_USER='t1',

MASTER_PASSWORD='123',

MASTER_PORT='3306';

#################MySQL復制架構解決方案###############

1.主——>從(解決應用程序與耦合度較高的問題)

1.分三層：

1.讀寫分離器,產品有：MySQL Proxy和Amoeba

2.主服務器

3.從服務器

2.分四層：

1.讀寫分離器

2.主服務器

3.偽從服務器(所用引擎BLACKHOLE)

4.從服務器

2.主——>主(解決更新數據時，數據不一致的情況)

1.主動/被動模式

即，將兩個主機server-id設置為相同值。

產品：mmm，Multi Master Manager

#####################故障解決################

##解決：出現錯誤時，不能啟動從服務器

1. SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; #此語句可以跳過來自主服務的下一個語句

START SLAVE;

或?2. 使用pt-slave-restart工具，來自percona-toolkit包。

##解決：數據出現不一致

1. 檢查一致性使用：

pt-table-checksum #此工具四種功能：1.校驗主從數據

2.監控復制延遲時間

3.系統開銷很小

4.檢查數據一致性

2. 修復不一致性使用：

pt-table-sync

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

######################MySQL的優化#######################

##技巧

1.使用正則表達式REGEXP,取出匹配數據

例：SELECT name,email FROM t WHERE email REGEXP '@126[.,]com$';

如果使用like方式查詢

例:SELECT name,email FROM t WHERE email LIKE '%126.com' or email LIKE '%126,com';

注：使用正則比使用like的一個缺點是系統資源的開銷會更大一下。

2.使用RAND()隨機取出數據

例：SELECT * FROM t ORDER BY RAND();

SELECT * FROM t ORDER BY RAND() LIMIT 3;

3.使用GROUP BY的WITH ROLLUP，進一步分組聚合數據。

例：SELECT cname,pname,COUNT(cname) FROM demo GROUP BY cname,pname WITH ROLLUP;

注：WITH ROLLUP 不能與ORDER BY 同時使用

##優化

一.優化SQL語句常用命令

1.通過SHOW STATUS命令查詢各種SQL的執行頻率。

SHOW [SESSION|GLOBAL] STATUS;

其中：SESSION(默認)表示當前連接。

GLOBAL表示自數據庫啟動至今。

@@主要查詢以com開頭的參數：

SHOW STATUS LIEK 'com_%'; #Com_XXX表示每個XXX語句執行的次數

@@需要查看的主要的以com開頭的參數

com_select:執行select操作的次數，一次查詢只累計加1

com_update:執行update操作的次數

com_insert:執行insert操作的次數，對批量插入只算一次

com_delete:執行delete操作的次數

注：以上參數是對所有引擎的。

@@以下是只針對InnoDB存儲引擎的。

InnoDB_rows_read:執行select操作的次數

InnoDB_rows_updated:執行update操作的次數

InnoDB_rows_inserted:執行insert操作的次數

InnoDB_rows_deleted:執行delete操作的次數

注:以上針對InnoDB的操作次數是影響的數據的“行”數，而不是相應語句的次數。

@@其他重要參數

connections：連接mysql的次數，包括成功和不成功的。

uptime:服務器已經工作的秒數。

slow_queries:慢查詢的次數。#可通過SHOW VARIABLES LIKE '%slow_queries%';查看是否開啟

2.定位執行效率較低的SQL語句

1.explain(或describe) select * from table where id=1000;

2.優化SQL語句

1.查詢慢查詢日志

2.解析查詢語句

3.判斷是否要加索引和索引是否可使用上

3.索引優化

1.添加索引，主要是在WHERE，HAVING，GROUP BY，OREDER BY后所使用的字段上。

2.使用LIKE時，不要把%通配符放在前面，否則索引就無法使用的到。

3.在使用OR和AND時，前后的兩個條件都要使用索引，否則索引就用不到

4.如果給定的條件表達式的值的數據類型和定義的不一樣，則無法用到索引

5.查看索引使用情況：SHOW STATUS LIKE 'Handler_read%';

其中所顯示的參數：Handler_read_key的值，表示讀取索引的次數。

Handler_read_rnd_next的值越高則，需要添加索引的列越多。

4.表優化

1.分析和檢查表

CHECK TABLE t1; #檢查表t1是否有錯誤

2.優化表空間

OPTIMIZE TABLE t1; #最好在非工作時間使用

5.常用SQL優化

1.導入導出優化

@@導出使用：SELECT * FROM table INTO OUTFILE '/tmp/table.txt';

@@導入使用：LOAD DATA INFILE ‘/tmp/table.txt' INTO TABLE table;

2.關閉索引使導入速度更快

1.@@關閉索引：ALTER TABLE tbl_name DISABLE KEYS;

@@導入數據

@@開啟索引：ALTER TABLE tbl_name ENABLE KEYS;

注：以上只對MyISAM表的數據導入能提高速度，對InnoDB無效

2.@@關閉唯一索引：SET unique_checks=0

@@導入數據

@@恢復唯一索引：SET unique_checks=1

注：如果能確定數據的唯一性，則可以使用關閉唯一索引來提高速度。否則不建議關閉。

3.針對InnoDB表類型的數據導入的優化

1.將導入的數據按主鍵的順序來排列，可提高導入速度

2.@@關閉自動提交：SET autocommit=0

@@導入數據

@@恢復自動提交：SET autocommit=1

6.INSERT語句的優化

1.插入數據時，使用INSERT INTO tbl_name VALUES('aa'),('bb')......('zz');

7.GROUP BY語句的優化

1.禁用分組排序，使用SELECT * FROM tbl_name GROUP BY cloumn ORDER BY NULL;

8.嵌套優化查詢

1.使用嵌套查詢，內部嵌套的查詢會用到索引，而外層的用不到。

將嵌套查詢改為，內連接或是外連接，則可優化查詢。

二.數據庫優化

1.使用中間表

@@創建新表。#不夠靈活

@@創建視圖。#推薦做法

2.分區(海量數據的優化，在Mysql5.1及以后提供)

##MyISAM引擎：

@@RANGE類型：

CREATE TABLE t1(id int,name varchar(30))

-->PARTITION BY RANGE(id)(

-->PARTITION p0 VALUES LESS THAN (11),

-->PARTITION p1 VALUES LESS THAN (21)

-->);

@@LIST類型：

CREATE TABLE t1(id int,name varchar(30))

-->PARTITION BY LIST(id)(

-->PARTITION p0 VALUES IN(1，3，6，7，10),

-->PARTITION p1 VALUES IN(2，4，5，8，11)

-->);

@@HASH類型：

CREATE TABLE t1(id int,name varchar(30))

-->PARTITION BY HASH(id)

-->PARTITIONS 2;

##InnoDB引擎

@@修改my.cnf

[mysqld]

innodb_file_per_table=1 #開啟InnoDB的獨立存儲空間

@@其他的和MyISAM相同

三. Mysql服務器優化

##鎖機制

1.MyISAM讀鎖定

@@命令：LOCK TABLE tbl_name READ #所有用戶只能讀，不能更新，刪除等。

2.MyISAM寫鎖定

@@命令：LOCK TABLE tbl_name WRITE #只有當前用戶可增刪改查，其他用戶無法進行任何操作。

3.解鎖：UNLOCK TABLES;

##字符集

1.@@使用：STATUS或\s,可查看基本信息和字符集。

其中，有服務器字符集、數據庫字符集、客戶端字符集、連接字符集，可設置。

@@客戶端和連接字符集設定

[client]

default-character-set=utf8

@@服務器和數據庫字符集設定

[mysqld]

character-set-server=utf8

@@校驗字符集

[mysqld]

collation-server=utf8_general_ci

注：可使用SHOW CHARACTER SET;查看字符集對應的校驗字符集。

##開啟慢查詢日志

1.@@使用：SHOW VARIABLES LIKE '%slow%';查看慢查詢日志是否開啟

@@開啟：[mysqld]

slow_query_log=slow.log

@@慢查詢時間：[mysqld]

long_query_time=5

##socket問題

1.如果mysql.sock丟失，則可使用mysql -uroot -p --protocol tcp -h localhost

注：只是臨時的啟動解決方法。

2. Mysql 密碼丟失

@@跳過授權表：mysqld_safe --skip-grant-tables --user=mysql &