mysql galera HA

MariaDB+gelera  HA

环境信息

centos 7.2.1511                         

版本信息

MariaDB 10.1.19-client

MariaDB 10.1.19-common

MariaDB 10.1.19-server

安装 mariadb

yum install MariaDB-server MariaDB-client

测试 mysql 是否正常

Service mysql start

Service mysql stop

创建 mysql 数据存放目录并授权

mkdir -p  /data

chown -R mysql.mysql /data

删除存在的数据库文件

rm -rf /var/lib/mysql/*

修改 /etc/my.cnf

[mysqld]

binlog_format=ROW

bind-address = 172.16.16.1

collation-server = utf8_general_ci

character-set-server = utf8

default_storage_engine=innodb

datadir = /data

max_connections = 4096

pid-file = /data/mysql/mysql.pid

query_cache_size=0

socket = /tmp/mysql.sock

#*Innodb setting

#

innodb_file_per_table

innodb_autoinc_lock_mode=2

innodb_flush_log_at_trx_commit=0

innodb_buffer_pool_size=256M

初始化数据库

mysql_install_db

重新启动数据库

service mysql start

创建链接文件

ln -s /tmp/mysql.sock /var/lib/mysql/

安全安装

mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THISSCRIPT IS RECOMMENDED FOR ALL MariaDB

      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB tosecure it, we‘ll need the current

password for the root user.  If you‘ve just installed MariaDB, and

you haven‘t set the root passwordyet, the password will be blank,

so you should just press enterhere.

Enter current password for root(enter for none):

OK, successfully used password,moving on...

Setting the root password ensuresthat nobody can log into the MariaDB

root user without the properauthorisation.

Set root password? [Y/n] Y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

 ... Success!

By default, a MariaDB installationhas an anonymous user, allowing anyone

to log into MariaDB without havingto have a user account created for

them.  This is intended only for testing, and tomake the installation

go a bit smoother.  You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n] Y

 ... Success!

Normally, root should only beallowed to connect from ‘localhost‘. This

ensures that someone cannot guess atthe root password from the network.

Disallow root login remotely?[Y/n] n

 ... skipping.

By default, MariaDB comes with adatabase named ‘test‘ that anyone can

access.  This is also intended only for testing, andshould be removed

before moving into a productionenvironment.

Remove test database and access toit? [Y/n] y

 - Dropping test database...

 ... Success!

 - Removing privileges on test database...

 ... Success!

Reloading the privilege tableswill ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n] y

 ... Success!

Cleaning up...

All done!  If you‘ve completed all of the above steps,your MariaDB

installation should now be secure.

Thanks for using MariaDB!

创建同步用户

grant all privileges on *.* to ‘wsrep_user‘@‘controller2‘ identified by‘wsreppasswd‘;

flush privileges;

更新 gelera 下 wsrep 信息

编辑 /etc/my.cnf.d/server.cnf

[galera]

wsrep_on=ON

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_cluster_address=gcomm://

wsrep_cluster_name=‘dbcluster‘

wsrep_node_address=‘172.16.16.1‘

wsrep_sst_method=rsync

#wsrep_sst_method=xtrabackup

wsrep_sst_auth=wsrep_user:wsreppasswd

wsrep_node_name=‘HA-1‘

wsrep_node_address=172.16.16.1

wsrep_provider_options="pc.recovery=TRUE;gcache.size=300M"

此时没有同步对象，所以 wsrep_cluster_address=gcomm:// 留空

启动节点

sudo -u mysql mysqld --wsrep-cluster-address=‘gcomm://‘

首先配置节点 2

使用 1 的配置文件直接复制后，修改信息

[mysqld]

bind-address = 172.16.16.2

[gelera]

wsrep_cluster_address=gcomm://172.16.16.1

wsrep_node_address=‘172.16.16.2‘

wsrep_node_name=‘HA-2‘

wsrep_node_address=172.16.16.2

此时数据同步方向为 2 同步 1

启动节点 2 上的服务

同步完成后

关闭节点 1 服务

service mysql stop

修改同步对象 wsrep_cluster_address=gcomm://172.16.16.2

重启服务后，双向同步完成，验证服务

MariaDB

[(none)]>show global status like ‘wsrep_cluster%‘;

从此处可以看出当有其他节点加入到集群中时， wsrep_cluster_size 可以反映出当前集群节点数量， wsrep_cluster_status 可以判定当前节点属于 primary 还是 slave

GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘%‘ WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘‘ WITH GRANT OPTION;

mysqladmin flush-hosts -h172.16.16.1   -P3306  -uroot   -pdftcpass

mysql -uroot -h172.16.16.10 -P3307 -pdftcpass

本文出自 “NB小菜鸟” 博客，谢绝转载！

原文：http://309692348.blog.51cto.com/728319/1924688