MySQL Galera HA

Mariadb+gelera HA

Environmental information

CentOS 7.2.1511

Version information

MariaDB 10.1.19-client

MariaDB 10.1.19-common

MariaDB 10.1.19-server

installation mariadb

Yum Install Mariadb-server mariadb-client

Test MySQL is normal

Service MySQL Start

Service MySQL Stop

Create MySQL data storage directory and authorization

Mkdir-p/data

Chown-r Mysql.mysql/data

Delete the existing database file

rm-rf/var/lib/mysql/*

Modify /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

Initializing the database

mysql_install_db

Restarting the database

Service MySQL Start

Create a linked file

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

Secure installation

Mysql_secure_installation

Note:running all PARTS of Thisscript are 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 would be blank,

So you should just press Enterhere.

Enter current password to 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 a anonymous user, allowing anyone

To log into MariaDB without havingto has a user account created for

them. This was 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-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, the MariaDB comes with the adatabase named ' test ' that anyone can

Access. This was also intended only for testing, Andshould was 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

would 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 is secure.

Thanks for using mariadb!

Create a synced user

Grant all privileges on * * to ' wsrep_user ' @ ' controller2 ' identified by ' wsreppasswd ';

Flush privileges;

Update Gelera under Wsrep Information

Edit /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"

There are no synchronization objects at this time, so wsrep_cluster_address=gcomm:// Leave Blank

Start node

sudo-u MySQL mysqld--wsrep-cluster-address= ' gcomm://'

Configure the node first 2

Use 1 after the configuration file is copied directly, modify the information

[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

at this point the data synchronization direction is 2 Sync 1

Start Node 2 on the service

After synchronization is complete

Close Node 1 Service

Service MySQL Stop

Modifying a synchronization object wsrep_cluster_address=gcomm://172.16.16.2

After restarting the service, the two-way synchronization is complete, the authentication service

MariaDB

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

as you can see from here, when other nodes are added to the cluster, wsrep_cluster_size can reflect the current number of cluster nodes, Wsrep_cluster_status you can determine if the current node belongs to Primary or is 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

This article from "NB Small rookie" blog, declined reprint!

MySQL Galera HA