Wang's

Xtrabackup原理 Xtrabackup是基於InnoDB的crash-recovery機制來實現的，它複製InnoDB文件導致內部不一致，然後再對文件進行crash-recovery，以致於讓資料一致性並且是可用的資料庫。 完整備份 [root@mysql-course backup]# xtrabackup --user=root --password=pwd --host=127.0.0.1 \ --port=3306 --parallel=4 --compress --compress-threads=4 --target-dir=/opt/backup/full \ --backup 看到下列訊息表示備份正常完成 161109 20:48:54 Executing UNLOCK BINLOG 161109 20:48:54 Executing UNLOCK TABLES 161109 20:48:54 All tables unlocked 161109 20:48:54 [00] Streaming ib_buffer_pool to 161109 20:48:54 [00] ...done 161109 20:48:54 Backup created in directory '/opt/percona57/data' MySQL binlog position: filename 'mysql-bin.000243', position '872', GTID of the last change '4bb4a153-8312-11e6-95a9-0800278d81c7:1-691' 161109 20:48:54 [00] Streaming backup-my.cnf 161109 20:48:54 [00] ...done 161109 20:48:54 [00] Streaming xtrabackup_info 161109 20:48:54 [00] ...done xtrabackup: Transaction log of lsn (8312487) to (8312512) was copied. 1611...

MySQL監控軟體： PMP(Percona Monitoring Plugin) ：Percona之前的監控是以Plugin或Template的方式加載到其他的監控軟體上，他有支援Zabbix、Nagios和Cacti等3個常用的監控軟體，詳細的介紹可以參考官網： Percona Monitoring Plugins MEM(MySQL Enterprise Monitor) ：官方提供的監控軟體，需購企業版才能使用此功能，可以監控DB內的狀況和InnoDB、NDB、Replication等的內容，另外還可以監控作業系統上的部份內容，例如硬碟、CPU、RAM、網路…等使用狀況，除了監控之外，還有Query Analyzer及發送告警功能，詳細的介紹參考官網： MySQL Enterprise Monitor PMM(Percona Monitoring and Management) ：它是一個用於管理及監控MySQL及MongoDB效能的開源平台，它提供了對MyISAM、InnoDB、TokuDB和PXC/Glarera的監控，另外也提供了與MEM類似的Query Analytics的功能，可以檢視執行了哪些SQL指令，並對執行效能較差的語法進行優化；針對作業系統的部份也提供了硬碟、網路、CPU和RAM的監控，特別的是它提供了Context switches、Processes和interrupts的監控，Context Switches可以看到CPU上下切換文的狀況，Processes可以方便了解系統有多少程序在等待I/O，這3個是MEM沒有提供的。詳細內容參考官方： Percona Monitoring and Management 架構說明： MEM架構 MEM是由Java+Apache Tomcat+MySQL組成的監控軟體 主要安裝Service Manager(也可稱Monitor Server)它有內建一個Agent可以讓你遠端監控 或者是另外在MySQL Server上安裝Agent軟體，其差別在於能否搜集作業系統上的資訊，裝了Agent才能在作業系統上搜集資訊 而DB端的資訊會透過本機或Service Manager的Agent，連到DB裡的Performance Schema中搜集資訊，再儲存到Servi...

◆ 緣起 最近在做個專案，它是一個報表系統 本來要用EDB(Postgres)但它的授權計算下來簡直是天價 所以又找了Greenplum要來做，測試後發現速度也無法滿足要求 最後想用MySQL的方案來試試。 ◆ 關於TokuDB 它的詳細說明，就不一一細數，有興趣直接到官網  Percona 簡單來說呢，是一個高效能、高壓縮比支援ACID和MVCC的儲存引擎，非常適合用在寫密集型且日誌型的應用場景。 在官方的部落中有做了一個 The MySQL Archive Storage Engine-Alternatives 對MyISAM、InnoDB、Archive和TokuDB的比較 在這些表中分別插入約2千萬筆的資料，其壓縮比如下： Engine Compression Table size [MB] InnoDB  none  2272 InnoDB  KEY_BLOCK_SIZE=8  1144 InnoDB  KEY_BLOCK_SIZE=4  584 MyISAM  none  1810 MyISAM  compressed with myisampack  809 Archive  default  211 TokuDB  ZLIB  284 TokuDB  LZMA   208 此表引用於Percona官方部落格 當然會有些限制內容，詳細就到官網上去看看 ◆ 安裝 手冊裡寫說可以單獨下載TokuDB的plugin，並安裝在MySQL或MariaDB的資料庫中，我這次是直接用Percona Server原生的資料庫，所以就包含在整個tar包裡了。 安裝前 記得有幾個設定要先處理，否則在啟動的時候會發生錯誤 libjemalloc library TokuDB需要libjemalloc 3.3.0以上的版本，如果是用tar包安裝的話，可以直接在lib/mysql/找到libjemalloc.so檔案，並且設定在my.cnf中： [mysqld_safe] malloc-lib=/opt/percona57/lib/...

Shared and Exclusive Locks InnoDB標準是使用row-level鎖定，其中又有分2種鎖定類別 shared(S) locks和exclusive(X) locks shared(S) lock允許交易持有讀取行的鎖 exclusive(X) lock允許交易持有更新或刪除行的鎖 如果今天有筆交易T1持有s鎖在資料行r，此時有另一個交易T2也要對資料行r進行鎖定時，情況如下： 如果T2也是使用S鎖定則可立即執行，此時T1和T2對資料行r同時擁有s鎖定。 如果T2是使用 X 鎖定，則無法立即執行 另外，如果今天T1持有X鎖在資料行r，此時T2要對資料行r進行S鎖或X鎖時，則無法立即執行，須等到T1解除鎖定行T2才能對資料行r進行鎖定。 Intention Locks InnoDB支援多粒度鎖定允許row-level鎖定和整表鎖定共存於同張資料表中，為實現多粒度鎖定會額外使用一稱為intention locks的鎖定，此意圖鎖在InnoDB資料表中為table-level lock，用來指示此交易在資料表中的某行之後會使用哪種類型的鎖定(Shared或Exclusive)，它也有兩種鎖定類型： Intention shared (IS)：交易T意圖在資料表a各別資料行上持有S鎖 Intention exclusive (IX)：交易T意圖在某些資料行上持有X鎖 例如：SELECT ... LOCK IN SHARE MODE會設定IS鎖，SELECT ... FOR UPDATE設定IX鎖。 Intention Lock的協議如下： 一個交易在資料表a中的一資料行獲得S鎖之前，必須先取得a資料表的IS鎖或更強的鎖 一個交易在獲得一資料行的X鎖之前，必須先取得a資料表的IX鎖 總結如下表： X IX S IS X 衝突 衝突 衝突 衝突 IX 衝突 相容 衝突 相容 S 衝突 衝突 相容 相容 IS 衝突 相容 相容 相容 在相容的情況下允許在現有的鎖中被授予其他的鎖定，而衝突的情況下則需等待前一個鎖定釋放後，才能對此進行鎖定，否則會發生死鎖或錯誤。 因此，意圖鎖定不會阻止除全表請求任何東西(例如，LOCK TABLES... WRITE)，其主要目的是...

Fabric相關軟體需求 MySQL Server 5.6.17 or later Python2 (2.6 or later) Connector/Pythone 1.2.1 or later Connector/J 5.1.27 or later 安裝mysql fabric (utilities) TAR包安裝 (本次使用版本為mysql-utilities 1.6) unzip mysql-utilities-version.zip cd mysql-utilities-version python ./setup.py build python ./setup.py install RPM安裝 rpm -ivh mysql-utilities-version-el6.noarch.rpm 安裝和啟動資料庫(Backing store和user database) ((安裝過程不再贅述)) 建立使用者及設定權限 需要4種角色：Backing store user、Server user、 Backup and Restore user Backing store就是用在Fabric Server的那個資料庫，Server即使用者資料庫，我個人認為Backup和Restore可以使用同一個使用者即可 Backing Store User： CREATE USER 'fabric_store'@'localhost' IDENTIFIED BY 'password'; GRANT ALTER, CREATE, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON fabric.* TO 'fabric_store'@'localhost'; Server User：(這個使用者在故障轉移時會用到) CREATE USER 'fabric_server'@'localhost' IDENTIFIED BY 'password'; GRANT DELETE, PROCESS, RELOAD, REPLICATION CLIEN...

fabric.cfg設定檔區塊說明： [default] #預設區塊，通常放安裝路徑的資訊 prefix =  /usr #安裝路徑，RPM裝的就指到/usr sysconfdir = /usr/etc #系統設定檔的位置，通常是在prefix下的etc中 logdir =   /var/log/fabric.log #log目錄 [storage] #Fabric backing store，存放fabric中所有群組設定的資料庫 address = 127.0.0.1:3306 #backing store的位置 user = password = database = #backing store的資料庫名稱，通常都會叫fabric auth_plugin = #connector連到backing store的驗證套件(可不使用) connection_timeout = #connector最大等待多少秒後會timeout connect_attempts = #重新連線的次數，超過這個次數就不會自動重新連 connection_delay = #多少秒重新連線一次 [servers] #被管理伺服器 user = password = backup_user = backup_password = restore_user = restore_password = unreachable_timeout = #用來檢查故障或新伺服器的timeout時間，預設是5，可設範圍1-60 [protocol.xmlrpc] #定議終端如何通過XML-RPC協定連線到Fabric節點 address = 127.0.0.1:32274 #XML-RPC伺服器，即connector連到Fabric的這段 threads = #連線數，這決定了Fabric並發請求的連線數 user = password = #如果這裡沒有設定密碼，在連線時會需要另外輸入密碼 disable_authentication = yes #是否不允許身份驗證 realm =MySQL Fabric #XML-RPC伺服器驗證時的識別碼 ssl_ca = #SSL認證機構路徑 ssl_cert =...

安裝 在my.cnf中設定後重開： [mysqld] plugin-load=authentication_pam.so 在Linux中設定 在/etc/pam.d中新增一個文件，名稱與服務相同(若服務為mysql檔名就叫mysql，若叫mysqld就叫mysqld) vim /etc/pam.d/mysql #%PAM-1.0 auth include password-auth account include password-auth 使用PAM來登入MySQL 如果可以讓使用都屬在mysql的群組中，不行的話就要透過proxy來mapping使用者 mysql> create user 'wang'@'127.0.0.1' IDENTIFIED WITH authentication_pam as 'mysql';      ^^^後面as 'mysql'這個就是pam.d裡面設的那個檔案名稱 記得權限要另外設定 除錯 登入時一直出現ERROR 1045 (28000): Access denied for user 'wang'@'127.0.0.1' (using password: YES)，在/var/log/security中記錄： Jun 18 19:23:31 ol65 unix_chkpwd[7097]: check pass; user unknown Jun 18 19:23:31 ol65 unix_chkpwd[7099]: check pass; user unknown Jun 18 19:23:31 ol65 unix_chkpwd[7099]: password check failed for user (wang) Jun 18 19:23:31 ol65 mysqld: pam_unix(mysql:auth): authentication failure; logname= uid=496 euid=496 tty= ruser=wang rhost=127.0.0.1 ...

安裝 注意事項 一旦安裝了就會對資料庫效能產生最小呈度的影響，就算是安裝了沒啟用也是一樣 它無法與Query Cache同時使用，如果有用Query Cache請記得在安裝Firewall前先關閉Query Cache # ./bin/mysql -uroot -proot -h127.0.0.1 -P3322 mysql< share/linux_install_firewall.sql 安裝時同時會在資料庫裡面安裝幾個元件 INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS mysql.firewall_users mysql.firewall_whitelist sp_set_firewall_mode() ->store procedure 檢查是否有啟用 mysql> SHOW GLOBAL VARIABLES LIKE 'mysql_firewall_mode'; 使用Firewall 剛開始啟用時，要先把使用者註冊到INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS裡面，然後再啟用RECORDING模式來記錄平常這個使用者會有哪些操作，這個操作指令會被記錄在INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST，記錄之後就可以把它開成PROTECTING模式，它會將記錄之外的操作給排除掉，並且資料庫裡面會記錄被DENIED的有多少、被授權的有多少…等資訊。 參考網站：http://dev.mysql.com/doc/refman/5.6/en/firewall-usage.html 建立使用者(若已有使用者就不需要) 透過sp_set_firewall_mode()的預存程式來進行註冊 mysql> CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RECORDING'); 切換剛剛註冊的使用者進行記錄操作 ./bin/mysql -uf...

1. 建立資料表 如同一般資料表般，只需要將資料類型指定為"json"即可 CREATE TABLE `j_t` ( `no` int(11) NOT NULL AUTO_INCREMENT, `document` json DEFAULT NULL,  PRIMARY KEY (`no`) ) ENGINE=InnoDB; 2. 新增資料 insert into j_t values(0,'{"id":221, "name":"Joe Yu"}'); insert into j_t values(0,'{"id":8891, "name":"Janne", "mobile":"00-1110-2222", "Address": "Taiwan, Taipei"}'); 3. 其他操作 ．一般查詢 ．jsn_extract()：可以針對json中的特定欄位查詢 ．jsn_insert()：針對條件新增資料 ．jsn_remove()：移除某個欄位 ．jsn_set()：類似於jsn_insert，如果欄位不存在將會新增，若欄位存在則更新內容，如果不想影響其他筆資料則可以下where條件來指定資料 ．jsn_replace()：只會對符合條件的資料進行更新 ．jsn_append()：將資料附加到array的後面 ．jsn_merge()：可以把幾個陣列資料透過jsn_array()合在一起後放入一個陣列中 ．透過json中的欄位建index ．jsn_length()：json欄位的長度 ．jsn_depth()：json欄位的深度，單純key-value深度即是2，若還有陣列在value裡深度就會是3 ．jsn_keys()：意即該json欄位中有多個key  ．jsn_contains_path()：查詢元素存在於哪些資料路徑當中，在下圖的範例中我查詢"mobile...