1.Galera Cluster部署的前置檢查

在要轉成Galera Cluster的數據庫上執行如下SQL語句:

SELECT DISTINCTCONCAT(t.table_schema,'.',t.table_name) as tbl,t.engine,IF(ISNULL(c.constraint_name),'NOPK','') AS nopk,IF(s.index_type = 'FULLTEXT','FULLTEXT','') as ftidx,IF(s.index_type = 'SPATIAL','SPATIAL','') as gisidxFROM information_schema.tables AS tLEFT JOIN information_schema.key_column_usage AS cON (t.table_schema = c.constraint_schema AND t.table_name = c.table_nameAND c.constraint_name = 'PRIMARY')LEFT JOIN information_schema.statistics AS sON (t.table_schema = s.table_schema AND t.table_name = s.table_nameAND s.index_type IN ('FULLTEXT','SPATIAL'))WHERE t.table_schema NOT IN ('information_schema','performance_schema','mysql')AND t.table_type = 'BASE TABLE'AND (t.engine <> 'InnoDB' OR c.constraint_name IS NULL OR s.index_type IN ('FULLTEXT','SPATIAL'))ORDER BY t.table_schema,t.table_name; 上述SQL檢索數據庫，輸出不符合使用Galera的表的信息，對應的5個字段順序為：表，表引擎，是否無主鍵，是否有全文索引，是否有空間索引。

找到不符合的原因，對應修改即可。

?

2.MySQL Galera監控

????查看MySQL版本:???mysql> SHOW GLOBAL VARIABLES LIKE 'version';

????查看wsrep版本:?mysql> SHOW GLOBAL STATUS LIKE 'wsrep_provider_version';

????查看wsrep有關的所有變量:?mysql> SHOW VARIABLES LIKE 'wsrep%' \G

????查看Galera集群狀態:?mysql> show status like 'wsrep%';

?

監控狀態參數說明:

????????集群完整性檢查:

????????????wsrep_cluster_state_uuid:在集群所有節點的值應該是相同的,有不同值的節點,說明其沒有連接入集群.

????????????wsrep_cluster_conf_id:正常情況下所有節點上該值是一樣的.如果值不同,說明該節點被臨時"分區"了.當節點之間網絡連接恢復的時候應該會恢復一樣的值.

????????????wsrep_cluster_size:如果這個值跟預期的節點數一致,則所有的集群節點已經連接.

????????????wsrep_cluster_status:集群組成的狀態.如果不為"Primary",說明出現"分區"或是"split-brain"狀況.

????????節點狀態檢查:

????????????wsrep_ready: 該值為ON,則說明可以接受SQL負載.如果為Off,則需要檢查wsrep_connected.

????????????wsrep_connected: 如果該值為Off,且wsrep_ready的值也為Off,則說明該節點沒有連接到集群.(可能是wsrep_cluster_address或wsrep_cluster_name等配置錯造成的.具體錯誤需要查看錯誤日志)

????????????wsrep_local_state_comment:如果wsrep_connected為On,但wsrep_ready為OFF,則可以從該項查看原因.

????????復制健康檢查:

????????????wsrep_flow_control_paused:表示復制停止了多長時間.即表明集群因為Slave延遲而慢的程度.值為0~1,越靠近0越好,值為1表示復制完全停止.可優化wsrep_slave_threads的值來改善.

????????????wsrep_cert_deps_distance:有多少事務可以并行應用處理.wsrep_slave_threads設置的值不應該高出該值太多.

????????????wsrep_flow_control_sent:表示該節點已經停止復制了多少次.

????????????wsrep_local_recv_queue_avg:表示slave事務隊列的平均長度.slave瓶頸的預兆.

?

????????最慢的節點的wsrep_flow_control_sent和wsrep_local_recv_queue_avg這兩個值最高.這兩個值較低的話,相對更好.

????????檢測慢網絡問題:

????????????wsrep_local_send_queue_avg:網絡瓶頸的預兆.如果這個值比較高的話,可能存在網絡瓶

????????沖突或死鎖的數目:

????????????wsrep_last_committed:最后提交的事務數目

????????????wsrep_local_cert_failures和wsrep_local_bf_aborts:回滾,檢測到的沖突數目

參考文檔:

MariaDB和Galera Cluster相關文檔
http://blog.secaserver.com/2011/07/install-mysql-replication-and-cluster-using-galera/
http://weetinyworld.blogspot.tw/2013/04/setting-up-mariadb-galera-cluster-from.html
http://blog.wu-boy.com/2013/03/galera-cluster-for-mysql-multi-master-replication/
http://www.sebastien-han.fr/blog/2012/08/29/setup-galera-with-sst-xtrabackup-method/
http://blog.secaserver.com/2011/07/install-mysql-replication-and-cluster-using-galera/
http://blog.secaserver.com/2012/02/high-availability-mysql-cluster-galera-haproxy/
http://www.severalnines.com/resources/clustercontrol-mysql-haproxy-load-balancing-tutorial
http://opentodo.net/2012/12/mysql-multi-master-replication-with-galera/
http://www.planetlarg.net/open-source-cookbook/data-storage/replace-mysql-mysql-galera
http://www.sebastien-han.fr/blog/2012/04/08/mysql-galera-cluster-with-haproxy/
http://www.zrwm.com/?cat=130
http://openquery.com/blog/galera-predeployment-check
http://www.percona.com/files/presentations/percona-live/nyc-2012/PLNY12-galera-cluster-best-practices.pdf
https://groups.google.com/forum/#!msg/codership-team/OUxATjcznPI/H8ftsjZwptAJ
http://www.zrwm.com/?p=5844
http://beerpla.net/2008/09/05/mysql-slave-lag-delay-explained-and-7-ways-to-battle-it/
http://wiki.vps.net/vps-net-features/cloud-servers/template-information/galeramysql-recommended-cluster-configuration/
http://haproxy.1wt.eu/download/1.4/doc/configuration.txt

GTID和mysqlfailover相關文檔
http://scriptingmysql.wordpress.com/2012/12/06/using-the-mysql-script-mysqlfailover-for-automatic-failover-with-mysql-5-6-gtid-replication/
http://svenmysql.blogspot.se/2012/10/failover-and-flexible-replication.html
http://svenmysql.blogspot.tw/2012/10/advanced-use-of-global-transaction.html
http://svenmysql.blogspot.se/2013/03/flexible-fail-over-policies-using-mysql.html
http://www.percona.com/sites/default/files/presentations/MHA_Toronto_Presentation.pdf
http://hickey.in/?p=78
http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html
http://huoding.com/2011/04/05/59
http://www.cnblogs.com/hustcat/archive/2009/12/19/1627525.html
http://dev.mysql.com/doc/refman/5.5/en/replication-howto-masterstatus.html

?

?

?

轉自http://www.360doc.com/content/13/0817/15/834950_307820923.shtml部分

轉載于:https://www.cnblogs.com/likappe/p/9504296.html

總結

以上是生活随笔為你收集整理的PXC 部署前置检查的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。