部署xtradb cluster時,建議使用3台及以上伺服器。原因有二:
1、預設情況下,如果一個同伴死去或者兩個節點之間的通信不穩定,兩個節點都将不接受查詢。當然這個可以通過添加忽略仲裁來解決:
set globalwsrep_provider_options=”pc.ignore_quorum=true”;
2、當宕掉的那台啟動時,會進行同步,負責提供資料的節點角色變為Donor, Donor會有一定的時間無法寫入。斷開情況如下:
Mysqldump 适合小庫
Rsync 複制時間内斷開,速度快
XtraBackup 短時間内斷開,速度略慢
node #1
hostname:percona1
IP:192.168.70.71
node #2
hostname:percona2
IP:192.168.70.72
node #3
hostname:percona3
IP:192.168.70.73
三個節點都安裝了centos系統
防火牆放通了端口:3306,4444,4567和4568
關閉selinux
先安裝percona官方源
yum installhttp://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
安裝epel源,以便安裝依賴包socat
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
安裝xtradb
yum installPercona-XtraDB-Cluster-56
官網安裝參考位址:
https://www.percona.com/doc/percona-xtradb-cluster/5.6/installation/yum_repo.html
官網參考位址:
https://www.percona.com/doc/percona-xtradb-cluster/5.6/howtos/virt_sandbox.html
第一個節點的/etc/my.cnf配置内容如下:
[mysqld]
datadir=/var/lib/mysql
user=mysql
# Path to Galeralibrary
wsrep_provider=/usr/lib64/libgalera_smm.so
# Clusterconnection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73
# In order forGalera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storageengine has only experimental support
default_storage_engine=InnoDB
# This changeshow InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #1address
wsrep_node_address=192.168.70.71
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=my_centos_cluster
# Authenticationfor SST method
wsrep_sst_auth="sstuser:s3cret"
在此之後,第一個節點可以啟動以下指令:
[root@percona1 ~]#/etc/init.d/mysql bootstrap-pxc
如果在centos7上運作本教程,系統服務如下:
[root@percona1 ~]# systemctl start [email protected]
此指令将啟動叢集初步wsrep_cluster_address設定為gcomm://。這樣的叢集将自舉以防後面的節點或MySQL重新開始,這不需要改變該配置檔案。
第一個節點啟動後,叢集狀态檢測:
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name |Value |
| wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec |
...
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cluster_size | 1 |
| wsrep_cluster_status | Primary |
| wsrep_connected |ON |
| wsrep_ready |ON |
40 rows in set (0.01 sec)
該輸出顯示叢集已成功自舉
修改root密碼,以及删除空使用者
UPDATE mysql.user SET password=PASSWORD("Passw0rd")where user='root';
delete from mysql.user whereuser='';
FLUSH PRIVILEGES;
為了使xtrabackup成功建立快照,需要給新使用者建立适當的權限。
mysql@percona1> CREATE USER'sstuser'@'localhost' IDENTIFIED BY 's3cret';
mysql@percona1> GRANT RELOAD,LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql@percona1> FLUSH PRIVILEGES;
注:MySQL的root賬戶也可用于建立PerconaXtrabackup的SST,但建議使用不同使用者做這一點。
第二個節點的/etc/my.cnf配置内容如下:
# Path to Galera library
# Cluster connection URLcontains IPs of node#1, node#2 and node#3
# In order for Galera to workcorrectly binlog format should be ROW
# MyISAM storage engine hasonly experimental support
# This changes how InnoDBautoincrement locks are managed and is a requirement for Galera
# Node #2 address
wsrep_node_address=192.168.70.72
#Authentication for SSTmethod
第二個節點的啟動指令
/etc/init.d/mysql start
伺服器已經啟動後,它會自動接收狀态快照傳送。是以,第二個節點不會有空root密碼了。
這是第二個節點的狀态
mysql> show status like 'wsrep%';
| Variable_name | Value |
| wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cluster_size | 2 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_ready | ON |
40 rows in set (0.01 sec)
第三個節點的/etc/my.cnf配置檔案
# Node #3 address
wsrep_node_address=192.168.70.73
然後啟動節點
伺服器已經啟動後,它會自動接收狀态快照傳送。
叢集狀态檢視:
| wsrep_cluster_size | 3 |
該輸出證明了第三個節點加入叢集。
在節點2上建立資料庫
mysql@percona2> CREATE DATABASE percona;
Query OK, 1row affected(0.01 sec)
在節點3上建立表
mysql@percona3> USE percona;
Database changed
mysql@percona3> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0rows affected(0.05 sec)
在節點1上插入資料
mysql@percona1> INSERT INTO percona.example VALUES (1, 'percona1');
QueryOK, 1 rowaffected (0.02 sec)
在第二個節點上檢視資料
mysql@percona2> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
| 1 | percona1 |
1 row in set (0.00 sec)
叢集搭建完成
本文轉自楊雲1028 51CTO部落格,原文連結:http://blog.51cto.com/yangrong/1684121,如需轉載請自行聯系原作者