Enterprise Private cloud MARIADB cluster high availability

The previous article introduced the OpenStack component RABBITMQ high availability, and now introduces another important component of MySQL high availability.

I am this time select MySQL version is mariadb, cluster method is Galera cluster multi-master cluster.

In fact, there are many other solutions, such as PXC, MHA and so on, the choice of Galera is easy to install, use and maintenance is convenient, multi-master mode any one node can be seen in the other node data, and OpenStack components also support the configuration of the configuration cluster mode.

Brief introduction

The MariaDB Galera Cluster is a system architecture that implements multi-master and real-time synchronization of data on the MySQL InnoDB storage engine, without the need for read and write separation at the business level, and the database read and write pressures can be distributed to each node in accordance with established rules. Fully compatible with MariaDB and MySQL in terms of data.

Characteristics

(1). Synchronous Replication Synchronous Replication (2). Active-active multi-master topology Logic (3). You can read and write data to any node in the cluster (4). Automatic member control, the fault node is automatically removed from the cluster (5). Automatic node join (6). True parallel replication, based on row level (7). Direct Client Connection, Native MySQL Interface (8). Each node contains a complete copy of the data (9). Data synchronization in multiple databases is implemented by the Wsrep interface

However, there are many limitations, such as replication support only Innode, the node must be 3, and so on, specific crossing network introduction.

Here is the installation

Environment

System CentOS 7.1

/etc/hosts is

Node1 192.168.1.18node2 192.168.1.20node3 192.168.1.19

First, install the base library (all nodes run)

yum -y install make cmake bc gcc  gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-devel freetype  freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel  Glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel  e2fsprogs e2fsprogs-devel krb5-devel libidn libidn-devel openssl  openssl-devel nss_ldap openldap openldap-devel  openldap-clients  openldap-servers libxslt-devel libevent-devel ntp  libtool-ltdl bison  libtool vim-enhanced tar wget readline-devel libyaml-devel  patch  telnet lrzsz sysstat screen parted rsync libselinux-python dmidecode  Ntpdate sar openssh-clients man 

Second, configure the MARIADB source (all nodes run)

Cat >/etc/yum.repos.d/mariadb.repo <<eof# mariadb 10.1 CentOS repository list-created 2016-05-24 07:59 utc# htt P://mariadb.org/mariadb/repositories/[mariadb]name = Mariadbbaseurl = Http://yum.mariadb.org/10.1/centos7-amd64 GPGKEY=HTTPS://YUM.MARIADB.ORG/RPM-GPG-KEY-MARIADB gpgcheck=1eof

Three, installation (all nodes running)

Yum Install Mariadb-server mariadb-client-y

Iv. Configuration

1. Running in Node1

Mainly modify Bind-address, Wsrep_cluster_name, wsrep_node_address, Wsrep_node_name

[Email protected] my.cnf.d]# cat/etc/my.cnf.d/server.cnf |grep-v ' ^# ' |sed '/^$/d ' [server][mysqld]collation-server = Utf8_general_ciinit-connect = ' SET NAMES utf8 ' character-set-server = Utf8skip-name-resolveskip-host-cacheopen_files_ Limit = 65535max_connections = 5000bind-address=192.168.1.18binlog_format=rowdefault_storage_engine=innodbinnodb_ Autoinc_lock_mode=2innodb_file_per_tablecharacter-set-server = utf8[galera]wsrep_on=onwsrep_provider=/usr/lib64/ galera/libgalera_smm.sowsrep_provider_options= "pc.recovery=true;gcache.size=300m" wsrep_cluster_address= ' Gcomm ://' wsrep_cluster_name= ' Ck-galera ' wsrep_node_address= ' 192.168.1.18 ' wsrep_node_name= ' m-1 ' Wsrep_sst_method=rsync [Embedded] [MARIADB] [mariadb-10.1]

Start

Systemctl Enable Mariadbsystemctl start mariadb

Initialization

[[email protected] log]# mysql_secure_installationnote: running all parts  of this script is recommended for all mariadb       servers in production use!  please read each step carefully ! In order to log into mariadb to secure it, we ' ll need  The currentpassword for the root user.  if you ' ve just  Installed mariadb, andyou haven ' t set the root password yet, the  password will be blank,so you should just press enter here. enter current password for root  (enter for none): OK, successfully  used password, moving on ... Setting the root password ensures that nobody can log into the mariadbroot user without the proper authorisation. set root password? [y/n] ynew password:re-enter new password:password  updated successfully! Reloading privilege tables.  ... success! by default, a mariadb installation has an anonymous user,  allowing anyoneto log into mariadb without having to have a  user account created forthem.  this is intended only for  Testing, and to make the installationgo a bit smoother.  you  should remove them before moving into aproduction environment. remove anonymous users? [y/n] y ... success! Normally, root should only be allowed to connect from  ' localhost '.   thisensures that someone cannot guess at the  root password from the network. Disallow root login remotely? [y/n] n ... skipping. by default, mariadb comes with a database named  ' Test '  that  anyone canaccess.  this is also intended only for testing,  And should be removedbefore moving into a production environment. Remove test database and access to it? [y/n] y - dropping  test database... ... success! - removing privileges on test  database... ... success! Reloading the privilege tables will ensure that all changes made  so farwill 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 mariadbinstallation should now be secure. thanks for using mariadb!

Query the number of clusters

[Email protected] log]# mysql-uroot-p-e "show status where Variable_name like ' wsrep_cluster_size '" Enter password:+--- -----------------+-------+| variable_name | Value |+--------------------+-------+| Wsrep_cluster_size | 1 |+--------------------+-------+

can see only one

View current Cluster IP

[[email protected] log]# mysql-uroot-p-e "show status where Variable_name like ' wsrep_incoming_addresses '" Enter passwor d:+--------------------------+-------------------+| variable_name | Value |+--------------------------+-------------------+| wsrep_incoming_addresses | 192.168.1.18:3306 |+--------------------------+-------------------+

2. Configure in Node

Also modify Bind-address, Wsrep_cluster_name, wsrep_node_address, Wsrep_node_name

[Email protected] log]# grep-v ' ^# '/etc/my.cnf.d/server.cnf |sed '/^$/d ' [server][mysqld]collation-server = utf8_ General_ciinit-connect = ' SET NAMES utf8 ' character-set-server = utf8skip-name-resolveskip-host-cacheopen_files_limit = 65535max_connections = 5000bind-address=192.168.1.20binlog_format=rowdefault_storage_engine=innodbinnodb_ Autoinc_lock_mode=2innodb_file_per_tablecharacter-set-server = utf8[galera]wsrep_on=onwsrep_provider=/usr/lib64/ galera/libgalera_smm.sowsrep_provider_options= "pc.recovery=true;gcache.size=300m" wsrep_cluster_address= ' Gcomm ://192.168.1.18 ' wsrep_cluster_name= ' ck-galera ' wsrep_node_address= ' 192.168.1.20 ' wsrep_node_name= ' m-2 ' wsrep_sst_ method=rsync[embedded][mariadb][mariadb-10.1]

Start

Systemctl Enable Mariadbsystemctl start mariadb

View the number of clusters

[Email protected] log]# mysql-uroot-p-e "show status where Variable_name like ' wsrep_cluster_size '" Enter password:+--- -----------------+-------+| variable_name | Value |+--------------------+-------+| Wsrep_cluster_size | 2 |+--------------------+-------+

You can see that there are already 2 nodes.

View Cluster IP

[[email protected] log]# mysql-uroot-p-e "show status where Variable_name like ' wsrep_incoming_addresses '" Enter passwor d:+--------------------------+-------------------------------------+| variable_name | Value |+--------------------------+-------------------------------------+| wsrep_incoming_addresses | 192.168.1.20:3306,192.168.1.18:3306 |+--------------------------+-------------------------------------+

You can see NODE1 and Node2 are in there.

3, in the NODE3 configuration

Modify the same as Node1 and Node2

[Email protected] ~]# grep-v ' ^# '/etc/my.cnf.d/server.cnf |sed '/^$/d ' [server][mysqld]collation-server = Utf8_general _ciinit-connect = ' SET NAMES utf8 ' character-set-server = Utf8skip-name-resolveskip-host-cacheopen_files_limit = 65535max_connections = 5000bind-address=192.168.1.19binlog_format=rowdefault_storage_engine=innodbinnodb_autoinc _lock_mode=2innodb_file_per_tablecharacter-set-server = utf8[galera]wsrep_on=onwsrep_provider=/usr/lib64/galera/ libgalera_smm.sowsrep_provider_options= "pc.recovery=true;gcache.size=300m" wsrep_cluster_address= ' gcomm:// 192.168.1.18,192.168.1.20 ' wsrep_cluster_name= ' ck-galera ' wsrep_node_address= ' 192.168.1.19 ' wsrep_node_name= ' m-3 ' wsrep_sst_method=rsync[embedded][mariadb][mariadb-10.1]

Start

Systemctl Enable Mariadbsystemctl start mariadb

View the number of clusters

[Email protected] ~]# mysql-uroot-p-e "show status where Variable_name like ' wsrep_cluster_size '" Enter password:+----- ---------------+-------+| variable_name | Value |+--------------------+-------+| Wsrep_cluster_size | 3 |+--------------------+-------+

View Cluster IP

[[email protected] ~]# mysql -uroot -p -e   "show status where variable_name like  ' wsrep_incoming_addresses '" Enter  password:+--------------------------+-------------------------------------------------------+| variable_ name            | value                                                    |+--------------------------+--------------------- ----------------------------------+| wsrep_incoming_addresses |  192.168.1.19:3306,192.168.1.20:3306,192.168.1.18:3306 |+--------------------------+---------------------- ---------------------------------+

You can see that there are 3 nodes in it.

Next, if you want to test, you can create a database and a table in any node, after inserting the data, in the other node to see if there is a corresponding information, I do not list here.

This article is from the "Yin-Technical Exchange" blog, please be sure to keep this source http://dl528888.blog.51cto.com/2382721/1865285

Enterprise Private cloud MARIADB cluster high availability