MariaDB Galera Cluster 部署

1.环境centos7

参考：

http://www.unixmen.com/setup-mariadb-galera-cluster-10-0-centos/  

注10.0根10.1 不同

http://www.open-open.com/lib/view/open1435888783513.html

rsync 命令：

http://www.jb51.net/article/74589.htm

1.1 Adding the MariaDB YUM Repository

https://mariadb.com/kb/en/mariadb/yum/#installing-mariadb-galera-cluster-with-yum
https://mariadb.com/kb/en/mariadb/yum/#adding-the-mariadb-yum-repository
#vim /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

1.2 Installing MariaDB with YUM

sudo yum install MariaDB-server MariaDB-client
yum install MariaDB-server MariaDB-client
#yum install -y mariadb mariadb-galera-server mariadb-galera-common galera rsync

#After Installation
#After the installation completes, start MariaDB with:

sudo systemctl start mariadb

#or if your system is not using systemctl:

sudo /etc/init.d/mysql start

#database path

/var/lib/mysql/ 

1.3环境准备

#vim /etc/hosts
192.168.1.32 node4
192.168.1.33 node5
192.168.1.34 node6

为了保证节点间相互通信，需要禁用防火墙设置（如果需要防火墙，则参照 官方网站 增加防火墙信息设置）
在三个节点分别执行命令：

#systemctl stop firewalld

#setenforce 0 
然后将/etc/sysconfig/selinux的selinux设置成disabled,这样初始化环境就完成了。

2. 配置 MariaDB Galera Cluster
参考(10 跟 10.1不同): http://www.unixmen.com/setup-mariadb-galera-cluster-10-0-centos/
初始化数据库服务

[root@node4 mariadb]# mysql_install_db
...
[root@node4 mysql]#chown -R mysql.mysql /var/lib/mysql 
[root@node4 ~]# vim /etc/my.cnf.d/server.cnf

[galera]  #下面添加
wsrep_on=ON
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_cluster_name='galera_cluster'
wsrep_cluster_name='node4'
wsrep_node_address='192.168.1.32'
wsrep_sst_method=rsync
wsrep_sst_auth=sst:sst #账户


[root@node4 mysql]#/etc/init.d/mysql restart
[root@node4 mysql]#mysql
mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'sst';
mysql> GRANT USAGE ON *.* to sst@'%' IDENTIFIED BY 'sst';
mysql> GRANT ALL PRIVILEGES on *.* to sst@'%';
mysql> GRANT ALL PRIVILEGES on *.* to root@'%';
mysql> FLUSH PRIVILEGES;
mysql> quit

-----------node5
初始化数据库服务

[root@node5 mariadb]# mysql_install_db
...
[root@node5 mysql]#chown mysql.mysql /var/lib/mysql 
[root@node5 ~]# vim /etc/my.cnf.d/server.cnf

[galera]  #下面添加
wsrep_on=ON
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name='galera_cluster' 
wsrep_cluster_address="gcomm://node4"
wsrep_node_address='192.168.1.33'
wsrep_node_name='node5'
wsrep_sst_method=rsync
wsrep_sst_auth=sst:sst #账户

[root@node5 mysql]/etc/init.d/mysql restart

-------------node6 
初始化数据库服务

[root@node5 mariadb]# mysql_install_db
...
[root@node5 mysql]#chown mysql.mysql /var/lib/mysql 
[root@node5 ~]# vim /etc/my.cnf.d/server.cnf

[galera]  #下面添加
wsrep_on=ON
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://node4"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='192.168.1.34'
wsrep_node_name='node6'
wsrep_sst_method=rsync
wsrep_sst_auth=sst:sst #账户


[root@node6 mysql]/etc/init.d/mysql restart

3.注意：
集群节点都起来时修改node4 节点 ：防止node4节点掉线从连后数据不同步
#node4 集群地址指向node5 node6

wsrep_cluster_address="gcomm://"  ==> wsrep_cluster_address="gcomm://node5,node6"

查看集群状态：

MariaDB [(none)]> show status like 'wsrep_%';

4.大数据差异不同步时,处理
DBA备份工具Xtrabackup使用指南（转
http://blog.itpub.net/23721637/viewspace-1054355/
http://www.jb51.net/article/74589.htm


或者删除/var/lib/mysql/* 重新mysql_install_db 并 chown -R mysql.mysql /var/lib/mysql/ 然后/etc/init.d/mysql start

大杀器：

rsync -avz tomcat7 192.168.1.241:~

-a选项是rsync里比较霸道的一个选项，因为你使用-a选项，就相当于使用了-rlptgoD这一坨选项
-z gzip压缩
-r recursive(循环递归)同步文件夹
-v verbose
-I 可以确保数据的一致性，代价便是速度上会变慢
-l选项后，rsync会完全保持软链接文件类型，


5.工具使用
Navicat for mysql
1.连接到源数据库,打开数据库,右击转储SQL文件到桌面
2.连接到源数据库,右击数据库属性(数据库名,字符集,排序规则),目标数据库新建一样的数据库
3.目标机器右击新建的数据库,运行SQL文件选择之前转储到桌面的SQL文件