6. 提昇資料複製和高可用度。

複製(Replication)是MySQL的功能中,最廣泛被實現在資料庫的擴展性和高可用性(HA)的架構中。MySQL 5.6中包含了一些設計給開發人員構建下一代網路,雲計算,社群和手機應用程式和自我修補的複製拓撲和高效能主從架構的新功能。關鍵功能包括:

• 新的全域交易的標識符(GTIDs) – GTIDs讓複製的交易進行的完整性藉由主從的架構下,可以被紀錄下來,進而提供了一個基礎的自我修復,同時使DBA和開發人員可以輕鬆地在”主”(master)伺服器出現故障時,由事件中找到最新的”從”(slave)伺服器。而GTIDs是直接放置在BINLOG裡,如果要消除GTIDs,需要複雜的第三方附加組件(add-ons)來追蹤GTIDs。

• 新的MySQL的複製工具 – 一組新的Python工具的設計,以充分利用新的複製GTIDs,以提供在”主”伺服器故障或維護時,複製(Replication)的自動故障轉移(Auto Fail-over)的管理和監控。

• 新的從伺服器多線程 – 基於資料庫綱要(schema),分割工作線程來處理資料,讓資料的更新可以同時並行處理,而不是順序處理。這對工作負載的好處是可以隔離應用程式來使用資料庫 – 例如,多用戶系統。

• 新的二進制日誌組提交(BGC) – 在MySQL5.6複製主伺服器寫入BINLOG,而不是一次性地將它們提交,的拓撲結構中,這樣的改變在主伺服器上有顯著的性能提昇。 BGC也可以有更精細的鎖定,而減少解鎖的等待,而再次地增加效能。

• 行複製的新優化 – MySQL5.6提供了一個新的選項變數binlog-row-image=minimal,這可以使應用程序只複製在DML操作下而改變的資料成分。這種方式提高了主/從伺服器兩端的複製吞吐量,並最大限度地減少二進制日誌的磁碟空間,網路資源和伺服器所需要的記憶體空間。
• 新的從伺服器故障安全保護(Crash-Save) – MySQL 5.6將二進制日誌的內部資料儲存在資料表中,以便從伺服器可以自動回滾(roll back)複製到故障之前的最後一次交易事件,並不需管理員干預而能繼續複製。這不僅降低了運營的負擔,同時也消除了從伺服器試圖恢復損壞的資料所造成的資料損失的風險。此外,如果主伺服器的故障導致二進制日誌檔損毀,服務器會自動恢復到一個可以正確讀取資料的位置。

• 新的複製檢查 – MySQL 5.6藉由偵測資料是否損毀以及在損毀事件套用在從伺服器前回傳錯誤,來確保資料複製的完整性,以防止從伺服器故障。

• 新的時間延遲(Time-delayed)複製 – MySQL 5.6允許開發人員在複製串流上設定時間延遲,以防止在主伺服器上的操作失誤傳送到從伺服器。隨著配置的主從時間延遲,在發生故障或為了將資料庫恢復到之前的狀態,從伺服器可以提升成為新的主伺服器。它也讓資料庫可以在發生錯誤或電力中斷前就被檢測到,進而保護資料庫的安全以避免需要資料補救。

提昇效能綱要(Performance Schema)。

MySQL校能綱要在MySQL5.5中引入的目的是在關鍵時間點下提供績效指標的衡量標準。 在MySQL 5.6中,預設my.cnf設定檔開啟優化過及自動調整的效能綱要,最大限度地減少系統負荷。因此,在正式環境中使用的監控常用的應用程式用不會造成問題。此外,在計費和或以計費為目的的環境中,新的MySQL計量工具能夠針對消費用戶,主機帳戶,應用程序等捕捉到細部的資源使用狀況。

8. 提昇安全性。

MySQL 5.6引入了一個密碼在內部處理和加密大修改。新的選項和功能,包括:

• master.info的新替代密碼 – MySQL 5.6的複製,延伸了START SLAVE命令,讓DBA可以在複製的從伺服器的選項中指定主用戶名和密碼,來進行帳戶驗證。以往要連結到主伺服器,需借用外部的驗證插件。有了這些選項,用戶名和密碼將不再需要被暴露在純文字的master.info文件中。

• 一般查詢日誌,慢速查詢日誌,二進制日誌中的新加密 – 指令在寫入這些日誌檔時,指令中的密碼,不會再以純文本的格式。

• 使用適當強度的新密碼雜湊 – MySQL伺服器內部驗證的預設密碼雜湊從PASSWORD函數改成使用SHA-256密碼雜湊算法並使用隨機salt值。

• 命令列上新的密碼選項 – MySQL的5.6引入了新的選項/設定檔(.mylogin.cnf),可用於命令列上操作來安全地存儲用戶密碼。

• 下次登錄時改變密碼 – 現在DBA和開發人員可以透過控制mysql.user資料表中的一個新password_expired標誌,讓使用者下次登入時必須更改密碼。

• 新的密碼驗證策略 – 現在密碼可以根據強度,長度,大小寫混合,特殊字符以及用戶自定的低,中,高的策略來驗證密碼是否合適。(細節說明如下)

啟用密碼插件:http://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html
validate_password插件可於MySQL 5.6.6之後的版本使用。可用來測試密碼及提昇安全性。
它提供了三種密碼強度的檢查方式:

i. WEAK: 只檢查密碼長度。
ii. MEDIUM:檢查大小寫和特殊符號。
iii. STRONG:檢查密碼是否存在密碼字典檔中。

步驟如下:

a. 在MySQL configuration file中(e.g. /etc/my.cnf)的mysqld設定中,加入plugin-load=validate_password.so。

1
2
3
4
# cat /etc/mycnf
[mysqld]
plugin-load=validate_password.so

b. 重新啟動mysqld。

c. 檢查環境變數。

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
6 rows in set (0.00 sec)

d. 測試password validation。

1
2
3
4
mysql> set password for 'stanley' @'%s' = password('test');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> set password for 'stanley'@'%' = password('Stanley!1234');
Query OK, 0 rows affected (0.00 sec)

官方文件建議,password函數只適用在MySQL資料庫內部使用,而不要在應用程式中使用MySQL password的加密方法來儲存密碼,因為這樣會有安全性的疑慮。
因為密碼可以將明碼藉由sha1及unhex兩個函數來組成。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select user, host, password from mysql.user where user='stanley' and host='%';
+---------+------+-------------------------------------------+
| user    | host | password                                  |
+---------+------+-------------------------------------------+
| stanley | %    | *76274CDEF97EB4B8B3B5E156F65C09379CF28D63 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select concat("*", sha1(unhex(sha1("Stanley!1234"))));
+------------------------------------------------+
| concat("*", sha1(unhex(sha1("Stanley!1234")))) |
+------------------------------------------------+
| *76274cdef97eb4b8b3b5e156f65c09379cf28d63      |
+------------------------------------------------+
1 row in set (0.00 sec)

 9. 其它的重要的提昇。

• 預設配置的新優化 – MySQL的5.6引入了伺服器的預設配置更改以提供現今的系統架構更好的即裝即用校能。這些新的預設值,是針對常被變更的設定和配置選項而設計,以盡量減少前期所需花費的時間。

• 改進TIME / TIMESTAMP/ DATETIME資料類型:

• TIME / TIMESTAMP/ DATETIME – 現在允許更精確微秒級的精準度的時間/日期比較和資料選擇。

• TIMESTAMP/ DATETIME – 在MySQL 5.5中已經新增此兩種資料類型。此欄位的預設值允許開發人員指定當前的時間戳,自動更新,或兩者。

• TIMESTAMP – 在MySQL 5.6的TIMESTAMP資料欄,其預設值為可以為空。在沒有將欄位設定為確切值或此欄位不允許為空值的情況下,TIMESTAMP列不會在自動設定為DEFAULT NOW()或ON UPDATE NOW()這兩個屬性值。

• 較好的狀況處理 – GET DIAGNOSTICS

藉由新的MySQL診斷區和相應的GET DIAGNOSTICS指令,MySQL 5.6使開發人員能夠輕鬆地檢查錯誤條件和異常代碼。診斷區可以通過多種選項,而提供了2種信息:

• 聲明 – 它提供了受影響的行數和發生的條件數目
• 條件 – 它提供了由之前操作的所有條件所返回錯誤代碼和訊息

新的GET DIAGNOSTICS指令提供了一個標準的診斷區域的接口,能容易地透過CLI或從應用程序代碼來取得和處理的結果,例如:

1
2
3
4
5
6
7
8
9
10
mysql> DROP TABLE test.my_table;
ERROR 1051 (42S02): Unknown table 'test.my_table'
mysql> GET DIAGNOSTICS CONDITION 1
-> @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
mysql> SELECT @p1, @p2;
+-------+-------------------------------+
| @p1   | @p2                           |
+-------+-------------------------------+
| 42S02 | Unknown table 'test.my_table' |
+-------+-------------------------------+

• 改進IPv6的支持

• MySQL 5.6改進了INET_ATON() 函數,使IPv6地址的字串在二進制轉換和儲存資料時,消耗最小的空間。

• MySQL 5.6將bind-address選項的預設值從“0.0.0.0”改為“0::0”,所以MySQL服務器接受連接所有的IPv4和IPv6地址。

10. 結論。

因為MySQL的性能,可靠性和易用性,使得MySQL成為最流行和最被廣泛使用的開源資料庫。這一個階段的MySQL 5.6資料庫,提供DBA和開發人員更高的性能,可擴展性並依據構建下一代基於網絡,基於雲計算和嵌入式應用與服務的需要而全面地改進。快速的摘要如下:

• 在MySQL5.6內部堆疊(stack)已被徹底重構,並充分利用現今計算機的運算能力和跨平台的多處理器的發展框架。

• 在優化性能和擴充性方面,InnoDB除去傳統的瓶頸和爭論。

• 創新的功能,如線上的綱要變更和NoSQL方式的資料庫存取,讓MySQL 不必妥協於ACID的承諾和SQL的查詢力量,而可以滿足最苛刻的應用需求。

• 為更有效地執行查詢和診斷報告而有更好的調校和分析相關的決策路徑,優化器進行了重構。
• 複製(replication)已做了改善,以確保主伺服器和從伺服器能保持同步,更好的自動恢復選項,更高的資料完整性和整體應用程式的高可用性。新的複製工具資料庫管理員提供配置,自動選擇,以確保最高等級的應用程式失敗轉移(fail-over)和恢復(recovery)。

• 最後,新的高性能綱要讓DBA和開發人員可以更容易地偵測與觀察到MySQL資料庫校能指標,進而知道應用程式花費了多少資源,以進行調整並獲得最佳校能。

在MySQL 5.6 中,做了很多效能上的改善,分別介紹如下:

1. 更好的效能和擴充性:提昇InnoDB儲存引擎。

MySQL 5.6在多執行緒和高線程CPU的系統中,提供了線性的效能和擴充性。效能提昇的主要關鍵是在於Oracle InnoDB儲存引擎的效率和同步處理的能力提昇而移除在InnoDB核心中過時的線程競爭和互斥鎖定。這樣的提昇使得MySQL可以完全的使用,現今以x86為架構的COTS(註一)多線程處理器的運算能力。

註一:COTS,commodity-off-the-shelf的縮寫,泛指通用在市場容易購得的商品,而非客制,價格昂貴且難以取得的硬體。

在Oracle內部的SysBench讀/寫和唯讀測試報告中,在同步處理從12個到60個讀寫交易的CPU線程環境中,5.6擴充表現仍是線性比率,遠比5.5的還穩定。

new-features-of-mysql-5-6-1

圖一:SysBench 讀/寫 TPS線性擴充性測試 - 60個CPU線程。
(圖來源:MySQL 5.6 新功能介紹白皮書)

new-features-of-mysql-5-6-2

圖二:SysBench 唯讀TPS線性擴充性測試 - 60個CPU線程
(圖來源:MySQL 5.6 新功能介紹白皮書)

更好的交易處理量

MySQL 5.6 提昇了InnoDB的效能及高同步的擴充性,交易能力和讀取的能力。這些使用案例是藉由測量應用程式在同時多人使用時,是如何的執行和擴充來取得效能的提昇。在支持這樣案例下,InnoDB有一個新重構後的架構可以減少互斥鎖的競爭,降低效能瓶頸和提供一個在資料存取中更一致的路徑。包括有:
• 在核心中,移除單一點的互斥鎖的競爭。
• 為flush的行為建立一個新的線程。
• 多線程的purge。
• 新的雜湊演算法。
• 減少緩衝池的競爭。
• 較好且更一致性的查詢執行,與更頻繁且可預測的時間間隔來持續統計資料的優化器。

上述的MySQL改善,反應在Sysbench的讀/寫的測試報告中:

new-features-of-mysql-5-6-3

圖三:SysBench讀寫比較 – MySQL 5.6 vs. MySQL 5.5
(圖來源:MySQL 5.6 新功能介紹白皮書)

較好的唯讀負載處理

針對InnoDB在唯讀狀態下的效能調校,讓網頁系統在同時處理大量的查詢及產生報表的交易行為中,有顯著的效能提昇。這些最佳化省略了自動提交的步驟,除非在設定了環境變數autocommit=1時才會再被使用,或是在開發者使用新的語法START_TRANSACTION_READ_ONLY的情況下,也可以省略自動提交。

1
2
3
4
SET autocommit = 0;
START_TRANSACTION_READ_ONLY;
SELECT * from test;
COMMIT;

 new-features-of-mysql-5-6-4

圖四:SysBench唯讀比較 – MySQL 5.6 vs. MySQL 5.5
(圖來源:MySQL 5.6 新功能介紹白皮書)

在Linux作業系統中,MySQL 5.6 在唯讀交易展現出來的效能是MySQL 5.5 的230%,相同的測試在Windows 2008則多出65%。

提供固態硬碟更好的效能

旋轉式磁盤硬碟是系統中最常見的瓶頸,最主要的因為是硬碟本身機械的部分,限制了系統擴展的能力。

結果,許多MySQL的應用程式開始佈署在可以提供類似記憶體讀取速度的SSD上,並在今日要求高可靠度的網頁應用系統中,提供了最高等級的同步處理能力。MySQL5.6包含了一些專門為SSD準備的重要增強功能,包括:
• 支援4K和8K page size以符合SSD的標準儲存演算法。
• 可攜式的 .ibd(InnoDB的資料)檔案。允許InnoDB的資料表可以容易的從預設的MySQL資料目錄上搬到SSD或網路儲存裝置
• 將undo日誌與表空間分開。將undo日誌從系統表空間搬到一個或多個獨立的表空間。undo日誌的密集讀取模式,讓這些新的表空間成為適合搬到SSD儲存的檔案,而讓系統表空間繼續留在一般硬碟中。

2. 更好的執行時間和診斷能力:提昇優化器。

在MySQL 5.6中,已經重構優化器,以取得更好的效率和性能,並提供了改進的功能,以獲得更好的查詢執行時間和自我診斷能力。MySQL 5.6主要的優化改進包括:

• 最佳化子查詢。MySQL優化器使用semi-join及子查詢實現(materialization)的方式來提昇子查詢的效能。具體來說,優化器是更有效率地處理FROM子句。目前,FROM子句中的子查詢實現並不會在執行SQL時立刻執行,而會延遲到當需要子查詢的內容時才執行,這樣就產生了極大地效能提昇。此外,優化器可能會添加一組索引來加快執行檢索的時間。

Oracle內部也使用DBT-3的第13號查詢來測試MySQL的運行效能,結果,執行時間從數天縮短到幾秒鐘,效能有顯著的提昇。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice,
sum(l_quantity)
from customer, orders, lineitem
where o_orderkey in (
select l_orderkey
from lineitem
group by l_orderkey
having sum(l_quantity) > 313
)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
order by o_totalprice desc, o_orderdate
LIMIT 100;

• 小限制值(limit)資料文件排序的優化。對於查詢指令的ORDER BY和小的限制值(limit),現在,優化程序會產生一個已排序的結果集,再將其視為單一表格般的掃描資料。這樣的查詢,最常出現在Web的應用程序,從一個大的結果集中只顯示其中幾行,如:

1
SELECT ID, FIRTNAME, LASTNAME FROM STUDENT ORDER BY ID LIMIT 10;

經ORACLE內部測試後表示,這一類的查詢,可以提昇約4倍的執行速度,有助於改善整體用戶的體驗和減少反應時間。

索引條件下推(Index Condition Pushdown, ICP)。預設的情況下,優化器會將WHERE條件下推到存儲引擎以進行評估/表掃描,而將已排序的結果集返回到MySQL的服務器中。

1
2
SELECT ID, FIRSTNAME, LASTNAME FROM STUDENT
WHERE ID BETWEEN 10 AND 100;

經ORACLE內部測試後表示,這一類的查詢,在預設ICP的行為下,可以提昇約15倍的執行速度。

批次的索引鍵的讀取(Batched Key Access, BKA)和多範圍讀取(Multi-Range Read, MRR)。優化器批次提供主鍵給儲存引擎,使得儲存引擎可以讀取/排序,能更有效地縮短了查詢的執行時間。

new-features-of-mysql-5-6-5

圖五:MySQL 5.6 批次/區間的鍵讀取效能比較
(圖來源:MySQL 5.6 新功能介紹白皮書)

在Oracle內部作相同的測試,在硬碟是效能瓶頸的系統下使用DBT-3第十三號查詢,BKA和MMR的架構可以提昇查詢的效能約280倍。

• 更好的優化診斷。優化的MySQL5.6提供下列更好的診斷和調試:
• 解釋INSERT,UPDATE,DELETE的操作行為,
• 解釋計劃輸出更精確的優化指標和更好的JSON格式可讀性,
• 跟踪優化決策過程的優化軌跡。

3. 更好的應用系統可用性:線上DDL/Schema變更。

今天的Web應用程序大多都被設計為快速發展和滿足業務與產生營餘的要求。其結果是,系統的服務水平協議(Service Level Agreement,SLA)最多以分鐘測量基礎,而不是以數天或數週來計算。因此,當一個應用程序需要快速支持新的生產線或新產品時,後端數據庫綱要(schema)必須是一種符合一般性的架構設計,應用程式仍然可以進行正常的業務操作。 MySQL5.6支援了這樣的資料庫靈活性和敏捷性,並提供了以下新的ALTER TABLE DDL語法補充:

1
2
3
4
5
6
7
8
• CREATE INDEX
• DROP INDEX
• Change AUTO_INCREMENT value for a column
• ADD/DROP FOREIGN KEY
• Rename COLUMN
• Change ROW FORMAT, KEY_BLOCK_SIZE for a table
• Change COLUMN NULL, NOT_NULL
• Add, drop, reorder COLUMN

DBA和開發人員可以添加索引和執行標準的InnoDB表格改變而應用系統仍然可以對數據庫作資料更新。MySQL提供綱要的靈活性,以適應不斷變化的業務需求,這對特別需要快速發展應用程序的開發人員有很大的幫助。

4. 更好的敏捷開發:NoSQL方式存取InnoDB。

許多最新一代的網絡,雲計算,社交和移動應用需要快速行動,來處理簡單的鍵/值對。同時,他們必須保持能夠對相同的資料作複雜的查詢,並確保數據能在ACID下獲得保護。藉由InnoDB新的NoSQL API,開發者除了可以得到原本RDBMS交易保護的好處外,還可以再獲得NoSQL獲得儲存效能。

MySQL5.6通過熟悉的Memcached的API,以鍵/值方式與InnoDB溝通取得數據,並實作了一個新的Memcached的插件到mysqld中。新的memcached協議直接指向到InnoDB的API,使開發人員能夠使用現有的Memcached客戶端而省略查詢解析所需花費的時間,並直接進入InnoDB作資料查詢和交易更新。API使開發者可以重用標準的Memcached的函式庫和客戶端,並透過整合一個具備永續性,宕機保護,提供交易保護的後端數據庫,作為Memcached的功能延伸。

5. 更好的敏捷開發:延展InnoDB的使用案例。

新的MySQL5.6優化和功能,擴展InnoDB到更多的使用案例。因此,開發人員可以在單一存儲引擎的標準化下,簡化應用程式。

• 新的全文搜索 – 提供一個更好的替代MyISAM的全文檢索。現在,InnoDB可以讓開發人員在文字內容的資料表中建立全文索引,而加速了應用系統搜索的單字和片語的能力。InnoDB中的全文搜索支援自然語言/布林模式,鄰近搜尋和相關性排序。一個簡單的使用案例如下:

1
2
3
4
5
6
7
8
9
CREATE TABLE STUDENT (
id int unsigned auto_increment primary key,
firstname varchar(16),
lastname varchar(16),
address varchar(128),
fulltext(address)
) engine=innodb;
SELECT id, firstname, lastname, address
where match(address) against ('taipei' in natural language mode);

• 新的可攜式表空間 – 在每個資料表擁有各自的InnoDB的.ibd文件時,所創建的文件可以在任意存儲在不同的設備,不同的資料庫伺服器之中。當在創建資料表時,開發人員可以指定將.ibd的文件儲存在MySQL資料目錄之外。這可以讓無法負荷應用程式/資料庫的忙碌資料表可以很容易地轉移到外部的網絡存儲設備(SSD,HDD)。這個新功能藉由允許使用者輕易地在不同的MySQL伺服器之間匯出/匯入InnoDB資料表,而讓系統可以快速,無接縫地擴充應用程式,如下所示:

匯出範例:

1
2
3
4
5
mysql> use export_database;
mysql> CREATE TABLE my_table(c1 INT) engine=InnoDB;
mysql> FLUSH TABLE my_table FOR EXPORT;
=> [innodb_data_home_dir]/export_database/my_table.cfg
mysql> UNLOCK TABLES;

匯入範例:

1
2
3
4
mysql> use import_database;
mysql> CREATE TABLE my_table(c1 INT) engine=InnoDB;
mysql> ALTER TABLE my_table DISCARD TABLESPACE;
mysql> ALTER TABLE my_table IMPORT TABLESPACE;

rsync用法1:exclude

使用rsync -av –exclude=upload /home/mysql/backup /home/mysql/backup2/ 只能排除upload文件/目录。
但如果要排除多个文件/目录,就需要新建个exclude.list,
然后rsync -av –exclude-from=”exclude.list”指定不需要同步的文件/目录

实现:

rsync -av –exclude-from=/root/exclude.list /home/mysql/backup /home/mysql/backup2/

//将/home/mysql/backup目录拷贝到/home/mysql/backup2目录下,/root/exclude.list中指定文件不拷贝。

注意exclude.list里面填写要排除的文件/目录,一行一个,直接写文件名即可。
这里,可以把/home/mysql/backup看成根目录,所以如果要排除a,b.1,b.2,tmp/g,那么exclude.list里就应该写

a
b.*
tmp/g

而不是填写以下完整url。不然这样还是会同步a,b.1,b.2,tmp/g

/home/mysql/backup/a
/home/mysql/backup/b.*
/home/mysql/backup/tmp/g

当然以下这种写法也是会同步a,b.1,b.2,tmp/g文件的,也是有问题

./home/mysql/backup/a
./home/mysql/backup/b.*
./home/mysql/backup/tmp/g

附录:
–exclude=PATTERN exclude files matching PATTERN
-exclude-from=FILE read exclude patterns from FILE
–include=PATTERN don’t exclude files matching PATTERN
–include-from=FILE read include patterns from FILE
转载请注明:酷喃|coolnull| » rsync通过exclude排除多文件/目录实例