搭建mysql_galera数据库集群
1,Mariadb Galera Cluster 集群介绍
Mariadb Galera Cluster 是MySQL高可用性和可扩展的解决方案
官网：http://galeracluster.com/products/
Mariadb Galera Cluster 是一套在mysql innodb 存储引擎上面实现multi-master 及数据实时同步复制的系统架构，业务层面无需做读写分离工作
特性：
（1）同步复制 Synchronous replication
（2）Active-Active multi-master 拓扑逻辑
（3）可对集群中任一几点进行读写
（4）自动成员控制，故障节点自动从集群中移除
（5）自动节点加入
（6）真正并行复制，基于行级
（7）每个节点都包含原生的数据副本
架构图：
2，配置mariadb的官方源地址
三个节点执行下面操作，目的是安装Galera，保留centos 原有的repo文件
在/etc/yum.repos.d/ 添加一个repo文件，内容如下：
[mariadb]
name=mariadb
baseurl=http://yum.mariadb.org/10.1/centos7-amd64/
gpgcheck=0
enable=1
#yum clean all
#yum makecache
3，安装Mariadb Galera
在三个node上分别执行：
# yum install mariadb-server mariadb mariadb-server-galera galera xinetd rsync
#systemctl start mariadb.service
#mysql_secure_installation (执行设置mysql密码，此次试验密码为teamsun)
然后在每个节点登录mysql测试
#mysql -uroot -p
4，配置MariaDB Galera 集群
第一个节点上添加如下内容
#vim /etc/my.cnf.d/client.cnf
添加内容：
[client]
port = 3306
scoket = /var/lib/mysql/mysql.sock
#vim /etc/my.cnf.d/galera.cnf
添加内容
[isamchk]
key_buffer_size = 16M
[mysqld]
binlog_format = ROW
character-set-server = utf8
collation-server = utf8_general_ci
max_connections = 10000
ignore-db-dirs = lost+found
init-connect = SET NAMES utf8
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_size = 2000M
innodb_doublewrite = 0
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 500
innodb_locks_unsafe_for_binlog = 1
innodb_log_file_size = 2000M
innodb_read_io_threads = 8
innodb_write_io_threads = 8
key_buffer_size = 64
myisam-recover-options = BACKUP
myisam_sort_buffer_size = 64M
open_files_limit = 102400
performance_schema = on
query_cache_limit = 1M
query_cache_type = 0
query_cache_size = 0
skip-external-locking
skip-name-resolve = 1
socket = /var/lib/mysql/mysql.sock
table_open_cache = 10000
thread_cache_size = 8
thread_stack = 256K
tmpdir = /tmp
user = mysql
wait_timeout = 1800
log-error = /var/log/mariadb/mariadb.log
datadir = /var/lib/mysql
pid-file = /var/run/mariadb/mariadb.pid
[galera]
bind-address=172.16.194.36
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = "gcomm://172.16.194.36,172.16.194.37,172.16.194.39"
wsrep_cluster_name = galera_cluster
wsrep_convert_LOCK_to_trx = 0
wsrep_debug = 0
wsrep_drupal_282555_workaround = 0
wsrep_retry_autocommit = 1
wsrep_node_name = controller1
wsrep_node_address = 172.16.194.36
wsrep_on = ON
wsrep_slave_threads = 1
wsrep_sst_method = rsync
default_storage_engine = innodb
[mysqld_safe]
nice = 0
socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mariadb/mariadb.log
#vim mysql-clients.cnf
添加内容：
[mysqldump]
max_allowed_packet = 16M
quick
quote-names
第二个和第三个节点的配置如下，注意更改相关IP和节点名称
#vim /var/lib/mysql/client.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
#vim /etc/my.cnf/server.cnf
[isamchk]
key_buffer_size = 16M
[mysqld]
binlog_format = ROW
character-set-server = utf8
collation-server = utf8_general_ci
max_connections = 10000
ignore-db-dirs = lost+found
init-connect = SET NAMES utf8
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_size = 2000M
innodb_doublewrite = 0
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 500
innodb_locks_unsafe_for_binlog = 1
innodb_log_file_size = 2000M
innodb_read_io_threads = 8
innodb_write_io_threads = 8
key_buffer_size = 64
myisam-recover-options = BACKUP
myisam_sort_buffer_size = 64M
open_files_limit = 102400
performance_schema = on
query_cache_limit = 1M
query_cache_type = 0
query_cache_size = 0
skip-external-locking
skip-name-resolve
socket = /var/lib/mysql/mysql.sock
table_open_cache = 10000
thread_cache_size = 8
thread_stack = 256K
tmpdir = /tmp
user = mysql
wait_timeout = 1800
[galera] 注意修改主机名和ip
bind-address=172.16.194.37
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = "gcomm://172.16.194.36,172.16.194.37,172.16.194.39"
wsrep_cluster_name = openstack
wsrep_node_name = controller2
wsrep_node_address = 172.16.194.37
wsrep_on = ON
wsrep_slave_threads = 4
wsrep_sst_method = rsync
default_storage_engine=InnoDB
[mysqld_safe]
nice = 0
socket = /var/lib/mysql/mysql.sock
syslog = /var/log/mariadb/mariadb.log
#vim mysql-clients.cnf
[mysqldump]
max_allowed_packet = 16M
quick
quote-names
5,设置mysql最大连接数
修改完server.cnf ,然后修改下mysql.service 文件，让数据库支持最大连接数为10000 （当虚拟数量较多负载较大时
，会出现因数据库连接数不够导致访问界面出现各种内容刷不出的情况）
#vim /usr/lib/systemd/system/mariadb.service
在[Service]段添加如下两行
LimitNOFILE=10000
LimitNPROC=10000
都修改完毕后执行：
#systemctl daemon-reload
6，关于mysql服务的启动顺序
三个节点my.cnf.d 都配置完后，全部执行：
#systemctl stop mariadb.service && systemctl disable mariadb.service
然后在第一个节点用下面的命令初始化启动mariadb集群服务
#/usr/libexec/mysqld --wsrep-new-cluster --user=root &
其他两个节点分别启动mariadb
#systemctl start mariadb.service
#systemctl status mariadb.service
最后其他两个节点启动成功，在回第一个节点执行
#pkill -9 mysql
#pkill -9 mysql
#systemctl start mariadb.service
#systemctl status mariadb.service
注意：如果遇到服务启动不了的情况，看下具体错误，如果是[ERROR]Can’t init tc log 错误可通过以下方式解决：
#cd /var/lib/mysql
#chown mysql:mysql *
更改完重启服务即可，因为/var/lib/mysql/tc.log 用户组和用户名不是mysql
7，查看mariadb数据库集群状态
#mysql -uroot -p
#mysql>show status like ‘wsrep_cluster_size%’;
登录这两节点的mysql里，发现mysql集群数变成了3，说明这个集群有三个节点，到这里galera集群就已搭建成功
8，测试
下面来测试一下，在controller3中创建一张表，并插入相关记录，看controller1 和 controller2 中能否查询得到
Mysql@controller3>create database happy;
mysql@controller2>use happy; 在controller2上也能看到创建的库‘happy’.
