安全提示：如果需要打開防火墻今阳，需要開放防火墻端口：3306, 4444, 4567, 4568 或者對(duì)指定ip允許訪問
機(jī)器列表
192.168.124.137
192.168.124.139
所有機(jī)器操作：
關(guān)閉SELINUX
vi /etc/selinux/config
SELINUX=disabled
setenforce 0
關(guān)閉防火墻
systemctl stop firewalld.service
systemctl disable firewalld.service
設(shè)置mariadb的yum源并安裝（所有節(jié)點(diǎn)都要）
vi /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
enabled=0
yum install MariaDB-server MariaDB-client galera
數(shù)據(jù)庫(kù)配置：
# 配置數(shù)據(jù)庫(kù)密碼，測(cè)試情況使用 root % 123456
/usr/bin/mysql_secure_installation
grant all privileges on *.* to root@"%" identified by "123456";
flush privileges;
# 配置完成之后關(guān)閉數(shù)據(jù)庫(kù)
systemctl stop mariadb
配置節(jié)點(diǎn)：
192.168.124.137 cat /etc/my.cnf.d/server.cnf | grep -v '#' :
[server]
[mysqld]
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.124.137,192.168.124.139"
wsrep_node_name = mariadb_cluster_1
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address=192.168.124.137
wsrep_sst_method=rsync
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1024M
wsrep_sst_auth=root:123456
[embedded]
[mariadb]
[mariadb-10.1]
192.168.124.139 配置 cat /etc/my.cnf.d/server.cnf | grep -v '#' ：
[server]
[mysqld]
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.124.137,192.168.124.139"
wsrep_node_name = mariadb_cluster_2
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address=192.168.124.139
wsrep_sst_method=rsync
wsrep_sst_auth=root:123456
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1024M
[embedded]
[mariadb]
[mariadb-10.1]
啟動(dòng) 192.168.124.137：
/usr/sbin/mysqld --wsrep-new-cluster --user=root
查看集群狀態(tài)：
MariaDB [(none)]> show status like "wsrep_cluster_size";
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 1 |
+--------------------+-------+
1 row in set (0.00 sec)
打開 192.168.124.139 上面的mariadb：
systemctl start mariadb
MariaDB [(none)]> show status like "wsrep_cluster_size";
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 2 |
+--------------------+-------+
1 row in set (0.00 sec)
集群大小已經(jīng)發(fā)生變化了缅疟，數(shù)據(jù)庫(kù)被可以使用了翎蹈，下面進(jìn)行數(shù)據(jù)寫入測(cè)試 192.168.：
MariaDB [(none)]> create database qinfei;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> use qinfei;
Database changed
MariaDB [qinfei]> create table ha;
ERROR 1113 (42000): A table must have at least 1 column
MariaDB [qinfei]> create table ha(id int primary key);
Query OK, 0 rows affected (0.01 sec)
MariaDB [qinfei]> show tables;
+------------------+
| Tables_in_qinfei |
+------------------+
| ha |
+------------------+
1 row in set (0.00 sec)
MariaDB [qinfei]> insert into ha value(1);
Query OK, 1 row affected (0.00 sec)
MariaDB [qinfei]> insert into ha value(2);
Query OK, 1 row affected (0.00 sec)
MariaDB [qinfei]> select * from ha;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
查看從數(shù)據(jù)庫(kù)：
MariaDB [(none)]> show databases;;
+--------------------+
| Database |
+--------------------+
| galera |
| information_schema |
| mysql |
| performance_schema |
| qinfei |
+--------------------+
5 rows in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]> use qinfei;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [qinfei]> select * from ha;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
數(shù)據(jù)已經(jīng)同步淮菠。
現(xiàn)在在另一個(gè)數(shù)據(jù)庫(kù)192.168.124.139上面操作：
MariaDB [qinfei]> insert into ha value(88);
Query OK, 1 row affected (0.01 sec)
查看192.168.124.137:
MariaDB [qinfei]> select * from ha;
+----+
| id |
+----+
| 1 |
| 2 |
| 88 |
+----+
3 rows in set (0.00 sec)
數(shù)據(jù)完全同步，數(shù)據(jù)庫(kù)集群可以正常使用荤堪。
停掉一個(gè)數(shù)據(jù)庫(kù)之后一樣可以正常使用合陵。
遇見的問題1 :
2018-04-26 3:59:56 140697295214848 [ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .
2018-04-26 3:59:56 140697295214848 [ERROR] WSREP: wsrep::connect(gcomm://192.168.124.137,192.168.124.139) failed: 7
2018-04-26 3:59:56 140697295214848 [ERROR] Aborting
解決方案： 按照建議方式修改 vi /var/lib/mysql/grastate.dat
safe_to_bootstrap: 1
可以正常啟動(dòng)。
遇見的問題2:
[ERROR] Can't init tc log
[ERROR] Aborting
tc.log 文件有問題澄阳，調(diào)試環(huán)境直接刪除文件捷克解決問題：
cd /var/lib/mysql/
rm -f ib_logfile*
rm -f tc.log
rm -f localhost.pid
最嚴(yán)重的問題：由于是日志同步 rsync 拥知，每次停止實(shí)例都很可能會(huì)導(dǎo)致mysql 啟動(dòng)失敗，很坑碎赢，不是一般的坑.
遇見的問題3: 備點(diǎn)加入集群一直失敗
原因：在生產(chǎn)環(huán)境中必須打開防火墻低剔，但是我只開放了 4567 和 針對(duì)指定ip開放3306 端口，導(dǎo)致加入集群失敗肮塞，需要開放 3306, 4444, 4567, 4568 四個(gè)端口才可以正常啟動(dòng)
遇見問題4:
Log file ./ib_logfile1 is of different size 5242880 bytes than other log files 27787264 bytes!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[Note] Plugin 'FEEDBACK' is disabled.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting'
解決辦法：
-
1襟齿、修改配置文件
在[mysqld]下添加：
innodb_log_file_size=50331648
-
2、刪除ib_logfile文件
mv ib_logfile0 ib_logfile0_bak
mv ib_logfile1 ib_logfile1_bak
遇見問題5:
pam_unix(su-l:session): session opened for user root by (uid=0)
pam_unix(su-l:session): session closed for user root
[b7929b] <passwd=-1> ldap_result() failed: Can't contact LDAP server: Connection reset by peer
[b7929b] <passwd=-1> ldap_abandon() failed to abandon search: Can't contact LDAP server: Transport endpoint is not connected
(to root) root on none
Started Session c127 of user root.
遇見這個(gè)問題枕赵，我只想說一句 Fuck 道行太淺安缕邸！？酱堋开皿！解決辦法：
sudo systemctl start mariadb.service
啟動(dòng)順序
方法1:
必須先：
/usr/sbin/mysqld --wsrep-new-cluster --user=root
才可以使用 systemctl 命令啟動(dòng)從節(jié)點(diǎn)。
方法2:
第一個(gè)需要啟動(dòng)的節(jié)點(diǎn)配置文件這樣填寫：
wsrep_cluster_address="gcomm://"
不指定集群地址篮昧，這樣就可以使用 systemctl start mariadb.service 啦
注意事項(xiàng)：這個(gè)非常非常重要 在停機(jī)維護(hù)的時(shí)候赋荆，必須最后一個(gè)停止主節(jié)點(diǎn)，否則主節(jié)點(diǎn)會(huì)啟動(dòng)失敗恋谭，然后只能啟動(dòng)最后一個(gè)關(guān)閉的節(jié)點(diǎn)作為此次啟動(dòng)的主節(jié)點(diǎn)糠睡。
http://blog.51cto.com/xianglinhu/1676039 寫道：
其中需要注意的地方有wsrep_cluster_address=gcomm://這條命令，gcomm://是一個(gè)特殊的參數(shù)疚颊，在啟動(dòng)第一臺(tái)數(shù)據(jù)庫(kù)時(shí)需要使用這個(gè)參數(shù)來啟動(dòng)狈孔，否則會(huì)啟動(dòng)失敗，后面的節(jié)點(diǎn)使用wsrep_cluster_address="gcomm://192.168.1.162,192.168.1.163,192.168.1.164"這個(gè)參數(shù)來啟動(dòng)數(shù)據(jù)庫(kù)材义。當(dāng)?shù)谝慌_(tái)數(shù)據(jù)庫(kù)需要重啟時(shí)需要切換到wsrep_cluster_address="gcomm://192.168.1.162,192.168.1.163,192.168.1.164"這個(gè)參數(shù)來啟動(dòng)才能加入到集群中均抽。這樣講有點(diǎn)抽象，換一種方式來講就是最開始啟動(dòng)集群的第一臺(tái)服務(wù)器時(shí)將wsrep_cluster_address="gcomm://192.168.1.162,192.168.1.163,192.168.1.164"這一行注釋掉其掂，使用下面那個(gè)參數(shù)來啟動(dòng)油挥，后面的節(jié)點(diǎn)注釋掉wsrep_cluster_address=gcomm://這個(gè)參數(shù)啟動(dòng)數(shù)據(jù)庫(kù)，加入到集群中來。當(dāng)集群全部起來以后萬一第一臺(tái)數(shù)據(jù)庫(kù)要重啟時(shí)將下面那條參數(shù)注釋掉深寥，開啟上面那條參數(shù)攘乒。這樣就能加入集群中來。至于wsrep_sst_auth=wsrep_sst-user:password這個(gè)參數(shù)就是我們之前設(shè)定的用來同步的用戶名和密碼惋鹅。
實(shí)際運(yùn)行發(fā)現(xiàn)的問題：
上圖的應(yīng)用場(chǎng)景是celery 做分布式任務(wù)则酝，并寫入到mysql中，中間停頓前面部分是啟用雙活數(shù)據(jù)庫(kù)之前的執(zhí)行速度闰集，后面部分是mysql 單點(diǎn)執(zhí)行速度沽讹，大致可以看出運(yùn)行時(shí)間大概延長(zhǎng)了一倍，是不是可以得出這樣的結(jié)論：雙活數(shù)據(jù)庫(kù)寫入吞吐量降低了呢武鲁？尚未測(cè)試爽雄，待閑來測(cè)一下吧
優(yōu)缺點(diǎn)：http://blog.sina.com.cn/s/blog_548c8a830102vrgw.html
