{"id":5937,"date":"2016-05-16T23:03:25","date_gmt":"2016-05-16T15:03:25","guid":{"rendered":"http:\/\/rmohan.com\/?p=5937"},"modified":"2016-05-16T23:27:42","modified_gmt":"2016-05-16T15:27:42","slug":"mariadb-galera-cluster-on-centos-7","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=5937","title":{"rendered":"MariaDB Galera Cluster on Centos 7"},"content":{"rendered":"

MariaDB is a branch of Mysql, it has been widely used in open source projects, such as hot openstack, therefore, in order to ensure high availability of the service,
\nwhile increasing the load capacity of the system, the cluster deployment is essential.<\/p>\n

MariaDB Galera Cluster Introduction<\/strong><\/p>\n

MariaDB Galera Cluster is an open source MariaDB synchronous multi-master cluster. It supports only XtraDB \/ InnoDB storage engine
\n(although experimental support for MyISAM – see wsrep_replicate_myisam system variable).<\/p>\n

Using MariaDB, you may say, “MariaDB Galera Cluster” to the redundancy of the database on CENTOS 7.2<\/p>\n

Database redundancy (cluster), although construction was just cumbersome MariaDB Galera Cluster is very simple.<\/p>\n

In addition, in order to operate as all the nodes where you want to participate in the Galera Cluster master, you can do the update work of the database at any node.<\/p>\n

In this post, we will introduce how to build a MariaDB Galera Cluster.<\/p>\n

The main function:<\/p>\n

Replication
\nTrue multi-master, that is, all nodes can read and write database
\nAutomatic control node membership, the failed node automatically cleared
\nThe new node is added automatically copied data
\nTrue parallel copy, row-level
\nUsers can connect directly to a cluster, the use of feelings entirely consistent with MySQL<\/p>\n

Advantage:<\/p>\n

Because it is multi-master, there is no Slavelag (delayed)
\nLost affairs absence
\nBoth read and write scalability
\nSmaller clients delay
\nData is synchronized between nodes, and Master \/ Slave mode is asynchronous, binlog on different slave may be different<\/p>\n

\"galera_channel_failover_galera\"<\/a><\/p>\n

\"galera_channel_failover_slave\"<\/a><\/p>\n

\"galera_overview\"<\/a><\/p>\n

\"galera_replication1\"<\/a><\/p>\n

\"galera-cluster\"<\/a><\/p>\n

Technology:<\/p>\n

Galera Cluster replication based Galeralibrary achieve, in order to allow communication with the MySQL library Galera, developed specifically for MySQL wsrep API.<\/p>\n

Galera Cluster Synchronization Plug-assurance data, maintain data consistency, can rely on a certified copy, works as follows:<\/p>\n

When the client sends a commit instruction, before the transaction is committed, all changes to the database will be write-set collected, and the write-set to send the contents of the record to other nodes.<\/p>\n

write-set the certification testing at each node, the node test results determine whether to apply write-set change data.<\/p>\n

If the authentication test fails, the node will discard write-set ; if the authentication test is successful, the transaction commits.
\n1 Prepare the installation environment<\/p>\n

Install MariaDB server cluster requires at least three (if only two words requires special configuration, please refer to the official documentation )<\/p>\n

Here, I list the test machine configuration:<\/p>\n

Work environment<\/p>\n

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
\n::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
\n192.168.1.71 apache1.rmohan.com apache1
\n192.168.1.72 apache2.rmohan.com apache2
\n192.168.1.74 apache3.rmohan.com apache3<\/p>\n

Run the three nodes, respectively:<\/p>\n

systemctl stop firewalld<\/p>\n

You are an expert Firewalld, you do not need to be invalidated. Please open the three ports of 3306,4444,4567.
\n# systemctl stop firewalld
\n# systemctl disable firewalld<\/p>\n

Disable the Selinux. You do only the following command, but we recommend that you restart.<\/p>\n

Then \/etc\/sysconfig\/selinux the selinux set to disabled , this initialize the environment is completed.<\/p>\n

setenforce 0
\nsed -i “s\/^SELINUX\\=enforcing\/SELINUX\\=disabled\/g” \/etc\/selinux\/config<\/p>\n

yum -y install epel-release<\/p>\n

vi \/etc\/yum.repos.d\/mariadb.repo
\n# MariaDB 10.1 CentOS repository list – created 2016-05-16 11:29 UTC
\n# http:\/\/mariadb.org\/mariadb\/repositories\/
\n[mariadb]
\nname = MariaDB
\nbaseurl = http:\/\/yum.mariadb.org\/10.1\/centos7-amd64
\ngpgkey=https:\/\/yum.mariadb.org\/RPM-GPG-KEY-MariaDB
\ngpgcheck=1<\/p>\n

Installation of 2 MariaDB Galera Cluster<\/p>\n

Lets install some Pre-requisites and other interesting tools.<\/p>\n

yum install rsync nmap lsof perl-DBI nc<\/p>\n

yum install -y MariaDB-server MariaDB-client galera rsync MariaDB-Galera-server MariaDB-client<\/p>\n

3. Install MariaDB-server and start the Mariadb service<\/p>\n

yum install MariaDB-server MariaDB-client MariaDB-compat galera socat jemalloc<\/p>\n

Launch the mysql_secure_installation command to set up the username and password. At this point you can start MariaDB normally by using the mysql command and create a database<\/p>\n

Complete!
\n[root@apache1 ~]# ls -ltr \/usr\/lib64\/galera\/
\ntotal 32832
\n-rwxr-xr-x 1 root root 33616730 Mar 15 01:52 libgalera_smm.so<\/p>\n

[root@apache1 ~]# mysql_secure_installation<\/p>\n

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
\nSERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!<\/p>\n

In order to log into MariaDB to secure it, we’ll need the current
\npassword for the root user. If you’ve just installed MariaDB, and
\nyou haven’t set the root password yet, the password will be blank,
\nso you should just press enter here.<\/p>\n

Enter current password for root (enter for none):
\nOK, successfully used password, moving on…<\/p>\n

Setting the root password ensures that nobody can log into the MariaDB
\nroot user without the proper authorisation.<\/p>\n

Set root password? [Y\/n] y
\nNew password:
\nRe-enter new password:
\nPassword updated successfully!
\nReloading privilege tables..
\n… Success!<\/p>\n

By default, a MariaDB installation has an anonymous user, allowing anyone
\nto log into MariaDB without having to have a user account created for
\nthem. This is intended only for testing, and to make the installation
\ngo a bit smoother. You should remove them before moving into a
\nproduction environment.<\/p>\n

Remove anonymous users? [Y\/n] n
\n… skipping.<\/p>\n

Normally, root should only be allowed to connect from ‘localhost’. This
\nensures that someone cannot guess at the root password from the network.<\/p>\n

Disallow root login remotely? [Y\/n] n
\n… skipping.<\/p>\n

By default, MariaDB comes with a database named ‘test’ that anyone can
\naccess. This is also intended only for testing, and should be removed
\nbefore moving into a production environment.<\/p>\n

Remove test database and access to it? [Y\/n] n
\n… skipping.<\/p>\n

Reloading the privilege tables will ensure that all changes made so far
\nwill take effect immediately.<\/p>\n

Reload privilege tables now? [Y\/n] y
\n… Success!<\/p>\n

Cleaning up…<\/p>\n

All done! If you’ve completed all of the above steps, your MariaDB
\ninstallation should now be secure.<\/p>\n

Thanks for using MariaDB!<\/p>\n

After the normal procedure to set up the first database on apache1, you can create a database and dump it, then import the database on apache2 and apache3.<\/p>\n

You have now 3 independant databases on 3 different servers. I would adivce you to dump the whole database with the command<\/p>\n

mysqldump –all-databases > db.sql<\/p>\n

6. We will now start setting up the Galera clustering. On apache1, edit the \/etc\/my.cnf.d\/server.cnf file on apache1 and configure it as follows. The server apache1 is the being setup as the first primary cluster. So every other cluster is going to be set up in the network that is galera2 and galera3 is going to replicate itself from apache1. Add the following parameter under the [galera] option.
\nvim \/etc\/my.cnf.d\/server.cnf<\/p>\n

[galera]
\n# Mandatory settings
\nwsrep_on=ON
\nwsrep_provider=\/usr\/lib64\/galera\/libgalera_smm.so
\nwsrep_cluster_address=’gcomm:\/\/’
\nwsrep_cluster_name=’galera’
\nwsrep_node_address=’apache1′
\nwsrep_node_name=’apache1′
\nwsrep_sst_method=rsync
\nwsrep_slave_threads=4
\nbinlog_format=row
\ndefault_storage_engine=InnoDB
\ninnodb_autoinc_lock_mode=2
\nbind-address=0.0.0.0
\ninnodb_locks_unsafe_for_binlog=1
\n#wsrep_provider_options=”socket.ssl_key=\/etc\/pki\/galera\/galera.key; socket.ssl_cert=\/etc\/pki\/galera\/galera.crt;”<\/p>\n

Tip: If you do not have a way ssl certification, please put wsrep_provider_options commented.<\/p>\n

Copy this file to apache2, apache3, attention should wsrep_node_name and wsrep_node_address into corresponding node hostname and ip<\/p>\n

[root@apache1 ~]# service mysql start –wsrep_cluster_address=gcomm:\/\/
\nStarting mysql (via systemctl): [ OK ]
\n[root@apache1 ~]#<\/p>\n

[root@apache2 systemd]# vi \/etc\/my.cnf.d\/server.cnf
\n[root@apache2 systemd]# service mysql start –wsrep_cluster_address=gcomm:\/\/
\nStarting mysql (via systemctl): [ OK ]
\n[root@apache2 systemd]#
\n[root@apache3 ~]# vi \/etc\/my.cnf.d\/server.cnf
\n[root@apache3 ~]# service mysql start –wsrep_cluster_address=gcomm:\/\/
\nStarting mysql (via systemctl): [ OK ]
\n[root@apache3 ~]#<\/p>\n

May 14 18:47:34 apache2.rmohan.com mysqld[19048]: 2016-05-14 18:47:34 140377978452096 [Note] InnoDB: 128 rollback segment(s) are active.
\nMay 14 18:47:34 apache2.rmohan.com mysqld[19048]: 2016-05-14 18:47:34 140377978452096 [Note] InnoDB: Waiting for purge to start
\nMay 14 18:47:34 apache2.rmohan.com mysqld[19048]: 2016-05-14 18:47:34 140377978452096 [Note] InnoDB: Percona XtraDB (http:\/\/www.percona.com) 5.6.29-76.2 started; log sequence number 1616819
\nMay 14 18:47:34 apache2.rmohan.com mysqld[19048]: 2016-05-14 18:47:34 140377978452096 [Note] Plugin ‘FEEDBACK’ is disabled.
\nMay 14 18:47:34 apache2.rmohan.com mysqld[19048]: 2016-05-14 18:47:34 140377252136704 [Note] InnoDB: Dumping buffer pool(s) not yet started
\nMay 14 18:47:34 apache2.rmohan.com mysqld[19048]: 2016-05-14 18:47:34 140377978452096 [Note] Server socket created on IP: ‘::’.
\nMay 14 18:47:34 apache2.rmohan.com mysqld[19048]: 2016-05-14 18:47:34 140377978452096 [Note] \/usr\/sbin\/mysqld: ready for connections.
\nMay 14 18:47:34 apache2.rmohan.com mysqld[19048]: Version: ‘10.1.14-MariaDB’ socket: ‘\/var\/lib\/mysql\/mysql.sock’ port: 3306 MariaDB Server<\/p>\n

Create a new user to Galera Server, and set the password.<\/p>\n

“New User: root”,: and then to the “password test123”.<\/p>\n

# mysql -e “grant all privileges on *.* to root@’%’ identified by ‘test123’ with grant option;”
\n# mysql -e “grant all privileges on *.* to root@localhost identified by ‘test123′ with grant option;”<\/p>\n

MariaDB [(none)]> grant all privileges on *.* to root@’%’ identified by ‘test123’ with grant option;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n

MariaDB [(none)]> grant all privileges on *.* to root@localhost identified by ‘test123′ with grant option;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n

Create a new user to Galera Server, and set the password.<\/p>\n

“New User: root”,: and then to the “password test123″.<\/p>\n

7. Once this is added, you can now start the first Galera cluster with the command. Make sure that the mysql service is stopped first. as at step5 we have started mysql to create a test database<\/p>\n

service mysql start<\/p>\n

[root@apache2 ~]# netstat -antp
\nActive Internet connections (servers and established)
\nProto Recv-Q Send-Q Local Address Foreign Address State PID\/Program name
\ntcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2605\/mysqld
\ntcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1085\/sshd
\ntcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2605\/mysqld<\/p>\n

Now you would like to make galera2 joined the primary cluster i.e apache1.<\/p>\n

Simple set up the apache2 \/etc\/my.cnf.d\/server.cnf file and under [galera] enter the following parameter.
\nThe only difference is the wsrep_node_name, wsrep_node_address and the gcomm value where you need to add the first cluster.
\ndo not forget to adjust the wsrep_node_address and wsrep_node_name variables.<\/p>\n

[root@apache1 ~]#
\n#Mandatory settings
\nwsrep_on=ON
\nwsrep_provider=\/usr\/lib64\/galera\/libgalera_smm.so
\nwsrep_cluster_address=’gcomm:\/\/apache1.rmohan.com,apache2.rmohan.com,apache3.rmohan.com’
\nwsrep_cluster_name=’galera’
\nwsrep_node_address=’192.168.1.71′
\nwsrep_node_name=’apache1.rmohan.com’
\nwsrep_sst_method=rsync
\nwsrep_slave_threads=4
\nbinlog_format=row
\ndefault_storage_engine=InnoDB
\ninnodb_autoinc_lock_mode=2
\nbind-address=0.0.0.0
\ninnodb_locks_unsafe_for_binlog=1
\n#wsrep_provider_options=”socket.ssl_key=\/etc\/pki\/galera\/galera.key; socket.ssl_cert=\/etc\/pki\/galera\/galera.crt;”<\/p>\n

[root@apache1 ~]#<\/p>\n

[root@apache2 ~]#
\n# Mandatory settings
\nwsrep_on=ON
\nwsrep_provider=\/usr\/lib64\/galera\/libgalera_smm.so
\nwsrep_cluster_address=’gcomm:\/\/apache1.rmohan.com,apache2.rmohan.com,apache3.rmohan.com’
\nwsrep_cluster_name=’galera’
\nwsrep_node_address=’192.168.1.72′
\nwsrep_node_name=’apache2.rmohan.com’
\nwsrep_sst_method=rsync
\nwsrep_slave_threads=4
\nbinlog_format=row
\ndefault_storage_engine=InnoDB
\ninnodb_autoinc_lock_mode=2
\nbind-address=0.0.0.0
\ninnodb_locks_unsafe_for_binlog=1
\n#wsrep_provider_options=”socket.ssl_key=\/etc\/pki\/galera\/galera.key; socket.ssl_cert=\/etc\/pki\/galera\/galera.crt;”
\n[root@apache2 ~]#
\ndo not forget to adjust the wsrep_node_address and wsrep_node_name variables.<\/p>\n

[root@apache3 ~]#
\n# Mandatory settings
\nwsrep_on=ON
\nwsrep_provider=\/usr\/lib64\/galera\/libgalera_smm.so
\nwsrep_cluster_address=’gcomm:\/\/apache1.rmohan.com,apache2.rmohan.com,apache3.rmohan.com’
\nwsrep_cluster_name=’galera’
\nwsrep_node_address=’192.168.1.74′
\nwsrep_node_name=’apache3.rmohan.com’
\nwsrep_sst_method=rsync
\nwsrep_slave_threads=4
\nbinlog_format=row
\ndefault_storage_engine=InnoDB
\ninnodb_autoinc_lock_mode=2
\nbind-address=0.0.0.0
\ninnodb_locks_unsafe_for_binlog=1
\n#wsrep_provider_options=”socket.ssl_key=\/etc\/pki\/galera\/galera.key; socket.ssl_cert=\/etc\/pki\/galera\/galera.crt;”<\/p>\n

[root@apache3 ~]#
\ndo not forget to adjust the wsrep_node_address and wsrep_node_name variables.<\/p>\n

[root@apache2 ~]# less \/var\/log\/messages
\nMay 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: wsrep_sst_grab()
\nMay 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: Start replication
\nMay 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: Setting initial position to 60149e36-1b72-11e6-910f-029ebfb18fbb:0
\nMay 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: protonet asio version 0
\nMay 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: Using CRC-32C for message checksums.
\nMay 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: backend: asio
\nMay 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Warning] WSREP: access file(\/var\/lib\/mysql\/\/gvwstate.dat) failed(No such file or directory)
\nMay 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: restore pc from disk failed
\nMay 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: GMCast version 0
\nMay 16 22:33:25 apache2 mysqld: 2016-05-16 22:33:25 139713247639680 [Warning] WSREP: Failed to resolve tcp:\/\/apache3.rmohan.com:4567
\nMay 16 22:33:25 apache2 mysqld: 2016-05-16 22:33:25 139713247639680 [Note] WSREP: (258ce90a, ‘tcp:\/\/0.0.0.0:4567’) listening at tcp:\/\/0.0.0.0:4567
\nMay 16 22:33:25 apache2 mysqld: 2016-05-16 22:33:25 139713247639680 [Note] WSREP: (258ce90a, ‘tcp:\/\/0.0.0.0:4567’) multicast: , ttl: 1
\nMay 16 22:33:25 apache2 mysqld: 2016-05-16 22:33:25 139713247639680 [Note] WSREP: EVS version 0
\nMay 16 22:33:25 apache2 mysqld: 2016-05-16 22:33:25 139713247639680 [Note] WSREP: gcomm: connecting to group ‘galera’, peer ‘apache1.rmohan.com:,apache2.rmohan.com:,apache3.rmohan.com:’
\nMay 16 22:33:25 apache2 mysqld: 2016-05-16 22:33:25 139713247639680 [Warning] WSREP: (258ce90a, ‘tcp:\/\/0.0.0.0:4567’) address ‘tcp:\/\/192.168.1.72:4567’ points to own listening address, black
\nlisting
\nMay 16 22:33:25 apache2 mysqld: 2016-05-16 22:33:25 139713247639680 [Note] WSREP: (258ce90a, ‘tcp:\/\/0.0.0.0:4567’) turning message relay requesting on, nonlive peers: tcp:\/\/192.168.1.74:4567
\nMay 16 22:33:27 apache2 mysqld: 2016-05-16 22:33:27 139713247639680 [Note] WSREP: declaring 16c80092 at tcp:\/\/192.168.1.74:4567 stable
\nMay 16 22:33:27 apache2 mysqld: 2016-05-16 22:33:27 139713247639680 [Note] WSREP: declaring 1f8d7692 at tcp:\/\/192.168.1.71:4567 stable
\nMay 16 22:33:27 apache2 mysqld: 2016-05-16 22:33:27 139713247639680 [Note] WSREP: Node 16c80092 state prim
\nMay 16 22:33:27 apache2 mysqld: 2016-05-16 22:33:27 139713247639680 [Note] WSREP: view(view_id(PRIM,16c80092,7) memb {
\nMay 16 22:33:27 apache2 mysqld: 16c80092,0
\nMay 16 22:33:27 apache2 mysqld: 1f8d7692,0
\nMay 16 22:33:27 apache2 mysqld: 258ce90a,0
\nMay 16 22:33:27 apache2 mysqld: } joined {
\nMay 16 22:33:27 apache2 mysqld: } left {
\nMay 16 22:33:27 apache2 mysqld: } partitioned {
\nMay 16 22:33:27 apache2 mysqld: })
\nMay 16 22:33:27 apache2 mysqld: 2016-05-16 22:33:27 139713247639680 [Note] WSREP: save pc into disk
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139713247639680 [Note] WSREP: gcomm: connected
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139713247639680 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139713247639680 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139713247639680 [Note] WSREP: Opened channel ‘galera’
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139713247639680 [Note] WSREP: Waiting for SST to complete.
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 2, memb_num = 3
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: STATE EXCHANGE: sent state msg: 27845dea-1b73-11e6-82bb-cbc3208d7287
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: STATE EXCHANGE: got state msg: 27845dea-1b73-11e6-82bb-cbc3208d7287 from 0 (apache3.rmohan.com)
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: STATE EXCHANGE: got state msg: 27845dea-1b73-11e6-82bb-cbc3208d7287 from 1 (apache1.rmohan.com)
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: STATE EXCHANGE: got state msg: 27845dea-1b73-11e6-82bb-cbc3208d7287 from 2 (apache2.rmohan.com)
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: Quorum results:
\nMay 16 22:33:28 apache2 mysqld: version = 3,
\nMay 16 22:33:28 apache2 mysqld: component = PRIMARY,
\nMay 16 22:33:28 apache2 mysqld: conf_id = 6,
\nMay 16 22:33:28 apache2 mysqld: members = 3\/3 (joined\/total),
\nMay 16 22:33:28 apache2 mysqld: act_id = 0,
\nMay 16 22:33:28 apache2 mysqld: last_appl. = -1,
\nMay 16 22:33:28 apache2 mysqld: protocols = 0\/7\/3 (gcs\/repl\/appl),
\nMay 16 22:33:28 apache2 mysqld: group UUID = 60149e36-1b72-11e6-910f-029ebfb18fbb
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: Flow-control interval: [28, 28]
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: Restored state OPEN -> JOINED (0)
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139713247234816 [Note] WSREP: New cluster view: global state: 60149e36-1b72-11e6-910f-029ebfb18fbb:0, view# 7: Primary, number of nodes: 3, my index: 2, protocol version 3
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139713247639680 [Note] WSREP: SST complete, seqno: 0
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: Member 2.0 (apache2.rmohan.com) synced with group.
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
\nMay 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 7f11867ef880 InnoDB: Warning: Using innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be removed in future releases. Please use READ COMMITTED transaction isolation level instead, see http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/set-transaction.html.<\/p>\n

Initialize the first cluster node<\/p>\n

Start MariaDB with the special \u2018-wsrep-new-cluster\u2019 option , Do it on node db1 only so the primary node of the cluster is initialized:
\n[root@apache1 ~]# \/etc\/init.d\/mysql start –wsrep-new-cluster
\nStarting mysql (via systemctl): [ OK ]
\n[root@apache1<\/p>\n

[root@apache1 ~]# mysql -u root -p -e”show status like ‘wsrep%'”
\nEnter password:
\n+——————————+——————————————————-+
\n| Variable_name | Value |
\n+——————————+——————————————————-+
\n| wsrep_apply_oooe | 0.000000 |
\n| wsrep_apply_oool | 0.000000 |
\n| wsrep_apply_window | 0.000000 |
\n| wsrep_causal_reads | 0 |
\n| wsrep_cert_deps_distance | 0.000000 |
\n| wsrep_cert_index_size | 0 |
\n| wsrep_cert_interval | 0.000000 |
\n| wsrep_cluster_conf_id | 7 |
\n| wsrep_cluster_size | 3 |
\n| wsrep_cluster_state_uuid | 60149e36-1b72-11e6-910f-029ebfb18fbb |
\n| wsrep_cluster_status | Primary |
\n| wsrep_commit_oooe | 0.000000 |
\n| wsrep_commit_oool | 0.000000 |
\n| wsrep_commit_window | 0.000000 |
\n| wsrep_connected | ON |
\n| wsrep_evs_delayed | |
\n| wsrep_evs_evict_list | |
\n| wsrep_evs_repl_latency | 0\/0\/0\/0\/0 |
\n| wsrep_evs_state | OPERATIONAL |
\n| wsrep_flow_control_paused | 0.000000 |
\n| wsrep_flow_control_paused_ns | 0 |
\n| wsrep_flow_control_recv | 0 |
\n| wsrep_flow_control_sent | 0 |
\n| wsrep_gcomm_uuid | 1f8d7692-1b73-11e6-8496-8af69a3bf125 |
\n| wsrep_incoming_addresses | 192.168.1.74:3306,192.168.1.71:3306,192.168.1.72:3306 |
\n| wsrep_last_committed | 0 |
\n| wsrep_local_bf_aborts | 0 |
\n| wsrep_local_cached_downto | 18446744073709551615 |
\n| wsrep_local_cert_failures | 0 |
\n| wsrep_local_commits | 0 |
\n| wsrep_local_index | 1 |
\n| wsrep_local_recv_queue | 0 |
\n| wsrep_local_recv_queue_avg | 0.000000 |
\n| wsrep_local_recv_queue_max | 1 |
\n| wsrep_local_recv_queue_min | 0 |
\n| wsrep_local_replays | 0 |
\n| wsrep_local_send_queue | 0 |
\n| wsrep_local_send_queue_avg | 0.000000 |
\n| wsrep_local_send_queue_max | 1 |
\n| wsrep_local_send_queue_min | 0 |
\n| wsrep_local_state | 4 |
\n| wsrep_local_state_comment | Synced |
\n| wsrep_local_state_uuid | 60149e36-1b72-11e6-910f-029ebfb18fbb |
\n| wsrep_protocol_version | 7 |
\n| wsrep_provider_name | Galera |
\n| wsrep_provider_vendor | Codership Oy <info@codership.com> |
\n| wsrep_provider_version | 25.3.15(r3578) |
\n| wsrep_ready | ON |
\n| wsrep_received | 4 |
\n| wsrep_received_bytes | 856 |
\n| wsrep_repl_data_bytes | 0 |
\n| wsrep_repl_keys | 0 |
\n| wsrep_repl_keys_bytes | 0 |
\n| wsrep_repl_other_bytes | 0 |
\n| wsrep_replicated | 0 |
\n| wsrep_replicated_bytes | 0 |
\n| wsrep_thread_count | 5 |
\n+——————————+——————————————————-+
\n[root@apache1 ~]#<\/p>\n

Some important information in the output are the following lines:<\/p>\n

wsrep_local_state_comment | Synced wsrep_incoming_addresses | 192.168.1.74:3306,192.168.1.71:3306,192.168.1.72:3306 wsrep_cluster_size | 1 wsrep_ready | ON all-database.sql<\/p>\n

And then restoring it on the node to be connected with
\n[root@apache1 ~]# mysql -u root -p -e ‘CREATE DATABASE apachetest;’
\nEnter password:
\n[root@apache1 ~]# mysql -u root -p -e ‘CREATE TABLE apachetest.mycluster ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), ipaddress VARCHAR(20), PRIMARY KEY(id));’
\nEnter password:
\n[root@apache1 ~]# mysql -u root -p -e ‘INSERT INTO apachetest.mycluster (name, ipaddress) VALUES (“apache1”, “192.168.1.71”);’
\nEnter password:
\n[root@apache1 ~]# mysql -u root -p -e ‘SELECT * FROM apachetest.mycluster;’
\nEnter password:
\n+—-+———+————–+
\n| id | name | ipaddress |
\n+—-+———+————–+
\n| 2 | apache1 | 192.168.1.71 |
\n+—-+———+————–+
\n[root@apache1 ~]# mysql -u root -p -e ‘INSERT INTO apachetest.mycluster (name, ipaddress) VALUES (“apache2”, “192.168.1.72”);’
\nEnter password:
\n[root@apache1 ~]#<\/p>\n

\"galera-cluster-new-features-deep-dive-webinar-27-638\"<\/a><\/p>\n

\"itfish_40768_0.jpg\"<\/a><\/p>\n

Troubleshooting<\/strong>
\nName Resolution<\/strong><\/p>\n

If the node trying to connect to an establish cluster is reporting the error:<\/p>\n

130307 15:14:52 [Warning] IP address ‘192.168.1.71’ could not be resolved: Name or service not known<\/p>\n

And the node it is trying to connect to is displaying the error:<\/p>\n

‘wsrep_sst_mysqldump –user ‘root’ –password ‘password’ –host ‘192.168.1.72’ –port ‘3306’ –local-port ‘3306’ –socket ‘\/var\/lib\/mysql\/mysql.sock’ –gtid ‘4c754641-e45a-11e2-0800-425dfc14f8f4:390” failed: 1 (Operation not permitted)<\/p>\n

The try adding the following option to the nodes configuration files:<\/p>\n

skip-name-resolve<\/p>\n

Be aware that by doing this, MariaDB will no longer use credentials with host names. That means you’ll have to configure the password on root@127.0.0.1 instead of root@localhost.<\/p>\n

Credentials
\nIf the node trying to connect to an establish cluster is reporting the error:<\/p>\n

WSREP: gcs\/src\/gcs_group.c:gcs_group_handle_join_msg():719: Will never receive state. Need to abort.<\/p>\n

And the node it is trying to connect to is displaying the error:<\/p>\n

[ERROR] WSREP: Try 1\/3: ‘wsrep_sst_mysqldump –user ‘root’ –password ‘password’ –host ‘192.168.1.71’ –port ‘3306’ –local-port ‘3306’ –socket ‘\/var\/lib\/mysql\/mysql.sock’ –gtid ‘4c754641-e45a-11e2-0800-425dfc14f8f4:420” failed: 1 (Operation not permitted)
\nERROR 1045 (28000): Access denied for user ‘root’@’192.168.1.72’ (using password: YES)<\/p>\n

Make sure the account on the node trying to connect to the cluster is correct. The established node that is being connected to is trying to send the database state to the connecting node, and is failing to do so because of an authentication error.<\/p>\n

Rsync<\/p>\n

If you see the error on the established node of
\nrsync: failed to connect to 192.168.1.72: No route to host (113)<\/p>\n

It means that the default state transfer method is Rsync, not mysqldump. To use mysqldump, add the setting
\nwsrep_sst_method=mysqldump<\/p>\n

Or you can open firewall port 4444.<\/p>\n

Rsync Wan
\nIf you see the error<\/p>\n

sh: wsrep_sst_rsync_wan: command not found
\nin the log files of the node trying to connect to a cluster, run
\ncd \/usr\/bin<\/p>\n

ln -s wsrep_sst_rsync wsrep_sst_rsync_wan<\/p>\n

File Access
\nIf you get the error File ‘\/var\/lib\/mysql\/aria_log_control’ not found make sure that the file is owned my the user mysql.
\nI ran into this issue running mysql_install_db as root, which meant that the file was owned by root.<\/p>\n

Tips and Tricks<\/p>\n

Initial Setup<\/p>\n

You can find out how large your SQL database is with the command:<\/p>\n

SELECT table_schema,
\nsum(data_length) \/ 1024 \/ 1024 “data”,
\nsum(index_length) \/ 1024 \/ 1024 “index”,
\nsum( data_length + index_length ) \/ 1024 \/ 1024 “total”
\nFROM information_schema.TABLES
\nGROUP BY table_schema;<\/p>\n

This value includes all the indexes, and represents the amount of data MariaDB will transfer to an empty node when it joins the cluster.<\/p>\n

To save yourself some time, initialise the nodes that will join the cluster by dumping the SQL data into a file with the command:<\/p>\n

mysqldump -u root -ppassword –all-databases > all-database.sql<\/p>\n

And then restoring it on the node to be connected with<\/p>\n

mysql -u root -ppassword < all-database.sql<\/p>\n

Because the backup will not contain the indexes, and can be compressed, you’ll find that you’ll be able to get a remote node up to a relatively recent initial state far more quickly than attempting to sync the entire database through the MariaDB state transfer. In my case a 5GB database could be backed up into a 600 MB compressed SQL file.<\/p>\n

It doesn’t matter if the database changes between when you backed it up and when the node connects, because the state transfer will take care of that for you.<\/p>\n

Save Bandwidth<\/p>\n

Use the setting
\nwsrep_sst_method=rsync_wan
\nTo save some bandwidth for state transfers over a WAN connection.<\/p>\n

mysql -u root -ppassword < all-database.sql<\/p>\n

Because the backup will not contain the indexes, and can be compressed, you’ll find that you’ll be able to get a remote node up to a relatively recent initial state far more quickly than attempting to sync the entire database through the MariaDB state transfer. In my case a 5GB database could be backed up into a 600 MB compressed SQL file.<\/p>\n

It doesn’t matter if the database changes between when you backed it up and when the node connects, because the state transfer will take care of that for you.<\/p>\n

Save Bandwidth<\/p>\n

Use the setting
\nwsrep_sst_method=rsync_wan
\nTo save some bandwidth for state transfers over a WAN connection.<\/p>\n","protected":false},"excerpt":{"rendered":"

MariaDB is a branch of Mysql, it has been widely used in open source projects, such as hot openstack, therefore, in order to ensure high availability of the service, while increasing the load capacity of the system, the cluster deployment is essential.<\/p>\n

MariaDB Galera Cluster Introduction<\/p>\n

MariaDB Galera Cluster is an open source MariaDB synchronous […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[73],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/5937"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5937"}],"version-history":[{"count":4,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/5937\/revisions"}],"predecessor-version":[{"id":5949,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/5937\/revisions\/5949"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5937"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5937"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5937"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}