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.
MariaDB Galera Cluster Introduction
MariaDB Galera Cluster is an open source MariaDB synchronous multi-master cluster. It supports only XtraDB / InnoDB storage engine
(although experimental support for MyISAM – see wsrep_replicate_myisam system variable).
Using MariaDB, you may say, “MariaDB Galera Cluster” to the redundancy of the database on CENTOS 7.2
Database redundancy (cluster), although construction was just cumbersome MariaDB Galera Cluster is very simple.
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.
In this post, we will introduce how to build a MariaDB Galera Cluster.
The main function:
Replication
True multi-master, that is, all nodes can read and write database
Automatic control node membership, the failed node automatically cleared
The new node is added automatically copied data
True parallel copy, row-level
Users can connect directly to a cluster, the use of feelings entirely consistent with MySQL
Advantage:
Because it is multi-master, there is no Slavelag (delayed)
Lost affairs absence
Both read and write scalability
Smaller clients delay
Data is synchronized between nodes, and Master / Slave mode is asynchronous, binlog on different slave may be different
Technology:
Galera Cluster replication based Galeralibrary achieve, in order to allow communication with the MySQL library Galera, developed specifically for MySQL wsrep API.
Galera Cluster Synchronization Plug-assurance data, maintain data consistency, can rely on a certified copy, works as follows:
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.
write-set the certification testing at each node, the node test results determine whether to apply write-set change data.
If the authentication test fails, the node will discard write-set ; if the authentication test is successful, the transaction commits.
1 Prepare the installation environment
Install MariaDB server cluster requires at least three (if only two words requires special configuration, please refer to the official documentation )
Here, I list the test machine configuration:
Work environment
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.71 apache1.rmohan.com apache1
192.168.1.72 apache2.rmohan.com apache2
192.168.1.74 apache3.rmohan.com apache3
Run the three nodes, respectively:
systemctl stop firewalld
You are an expert Firewalld, you do not need to be invalidated. Please open the three ports of 3306,4444,4567.
# systemctl stop firewalld
# systemctl disable firewalld
Disable the Selinux. You do only the following command, but we recommend that you restart.
Then /etc/sysconfig/selinux the selinux set to disabled , this initialize the environment is completed.
setenforce 0
sed -i “s/^SELINUX\=enforcing/SELINUX\=disabled/g” /etc/selinux/config
yum -y install epel-release
vi /etc/yum.repos.d/mariadb.repo
# MariaDB 10.1 CentOS repository list – created 2016-05-16 11:29 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Installation of 2 MariaDB Galera Cluster
Lets install some Pre-requisites and other interesting tools.
yum install rsync nmap lsof perl-DBI nc
yum install -y MariaDB-server MariaDB-client galera rsync MariaDB-Galera-server MariaDB-client
3. Install MariaDB-server and start the Mariadb service
yum install MariaDB-server MariaDB-client MariaDB-compat galera socat jemalloc
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
Complete!
[root@apache1 ~]# ls -ltr /usr/lib64/galera/
total 32832
-rwxr-xr-x 1 root root 33616730 Mar 15 01:52 libgalera_smm.so
[root@apache1 ~]# mysql_secure_installation
NOTE: 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 current
password for the root user. If you’ve just installed MariaDB, and
you 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 MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] n
… skipping.
Normally, root should only be allowed to connect from ‘localhost’. This
ensures 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 can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] n
… skipping.
Reloading the privilege tables will 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!
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.
You have now 3 independant databases on 3 different servers. I would adivce you to dump the whole database with the command
mysqldump –all-databases > db.sql
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.
vim /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=’gcomm://’
wsrep_cluster_name=’galera’
wsrep_node_address=’apache1′
wsrep_node_name=’apache1′
wsrep_sst_method=rsync
wsrep_slave_threads=4
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
innodb_locks_unsafe_for_binlog=1
#wsrep_provider_options=”socket.ssl_key=/etc/pki/galera/galera.key; socket.ssl_cert=/etc/pki/galera/galera.crt;”
Tip: If you do not have a way ssl certification, please put wsrep_provider_options commented.
Copy this file to apache2, apache3, attention should wsrep_node_name and wsrep_node_address into corresponding node hostname and ip
[root@apache1 ~]# service mysql start –wsrep_cluster_address=gcomm://
Starting mysql (via systemctl): [ OK ]
[root@apache1 ~]#
[root@apache2 systemd]# vi /etc/my.cnf.d/server.cnf
[root@apache2 systemd]# service mysql start –wsrep_cluster_address=gcomm://
Starting mysql (via systemctl): [ OK ]
[root@apache2 systemd]#
[root@apache3 ~]# vi /etc/my.cnf.d/server.cnf
[root@apache3 ~]# service mysql start –wsrep_cluster_address=gcomm://
Starting mysql (via systemctl): [ OK ]
[root@apache3 ~]#
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.
May 14 18:47:34 apache2.rmohan.com mysqld[19048]: 2016-05-14 18:47:34 140377978452096 [Note] InnoDB: Waiting for purge to start
May 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
May 14 18:47:34 apache2.rmohan.com mysqld[19048]: 2016-05-14 18:47:34 140377978452096 [Note] Plugin ‘FEEDBACK’ is disabled.
May 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
May 14 18:47:34 apache2.rmohan.com mysqld[19048]: 2016-05-14 18:47:34 140377978452096 [Note] Server socket created on IP: ‘::’.
May 14 18:47:34 apache2.rmohan.com mysqld[19048]: 2016-05-14 18:47:34 140377978452096 [Note] /usr/sbin/mysqld: ready for connections.
May 14 18:47:34 apache2.rmohan.com mysqld[19048]: Version: ‘10.1.14-MariaDB’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MariaDB Server
Create a new user to Galera Server, and set the password.
“New User: root”,: and then to the “password test123”.
# mysql -e “grant all privileges on *.* to root@’%’ identified by ‘test123’ with grant option;”
# mysql -e “grant all privileges on *.* to root@localhost identified by ‘test123′ with grant option;”
MariaDB [(none)]> grant all privileges on *.* to root@’%’ identified by ‘test123’ with grant option;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant all privileges on *.* to root@localhost identified by ‘test123′ with grant option;
Query OK, 0 rows affected (0.00 sec)
Create a new user to Galera Server, and set the password.
“New User: root”,: and then to the “password test123″.
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
service mysql start
[root@apache2 ~]# netstat -antp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2605/mysqld
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1085/sshd
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2605/mysqld
Now you would like to make galera2 joined the primary cluster i.e apache1.
Simple set up the apache2 /etc/my.cnf.d/server.cnf file and under [galera] enter the following parameter.
The only difference is the wsrep_node_name, wsrep_node_address and the gcomm value where you need to add the first cluster.
do not forget to adjust the wsrep_node_address and wsrep_node_name variables.
[root@apache1 ~]#
#Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=’gcomm://apache1.rmohan.com,apache2.rmohan.com,apache3.rmohan.com’
wsrep_cluster_name=’galera’
wsrep_node_address=’192.168.1.71′
wsrep_node_name=’apache1.rmohan.com’
wsrep_sst_method=rsync
wsrep_slave_threads=4
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
innodb_locks_unsafe_for_binlog=1
#wsrep_provider_options=”socket.ssl_key=/etc/pki/galera/galera.key; socket.ssl_cert=/etc/pki/galera/galera.crt;”
[root@apache1 ~]#
[root@apache2 ~]#
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=’gcomm://apache1.rmohan.com,apache2.rmohan.com,apache3.rmohan.com’
wsrep_cluster_name=’galera’
wsrep_node_address=’192.168.1.72′
wsrep_node_name=’apache2.rmohan.com’
wsrep_sst_method=rsync
wsrep_slave_threads=4
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
innodb_locks_unsafe_for_binlog=1
#wsrep_provider_options=”socket.ssl_key=/etc/pki/galera/galera.key; socket.ssl_cert=/etc/pki/galera/galera.crt;”
[root@apache2 ~]#
do not forget to adjust the wsrep_node_address and wsrep_node_name variables.
[root@apache3 ~]#
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=’gcomm://apache1.rmohan.com,apache2.rmohan.com,apache3.rmohan.com’
wsrep_cluster_name=’galera’
wsrep_node_address=’192.168.1.74′
wsrep_node_name=’apache3.rmohan.com’
wsrep_sst_method=rsync
wsrep_slave_threads=4
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
innodb_locks_unsafe_for_binlog=1
#wsrep_provider_options=”socket.ssl_key=/etc/pki/galera/galera.key; socket.ssl_cert=/etc/pki/galera/galera.crt;”
[root@apache3 ~]#
do not forget to adjust the wsrep_node_address and wsrep_node_name variables.
[root@apache2 ~]# less /var/log/messages
May 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: wsrep_sst_grab()
May 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: Start replication
May 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
May 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: protonet asio version 0
May 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: Using CRC-32C for message checksums.
May 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: backend: asio
May 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)
May 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: restore pc from disk failed
May 16 22:33:24 apache2 mysqld: 2016-05-16 22:33:24 139713247639680 [Note] WSREP: GMCast version 0
May 16 22:33:25 apache2 mysqld: 2016-05-16 22:33:25 139713247639680 [Warning] WSREP: Failed to resolve tcp://apache3.rmohan.com:4567
May 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
May 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
May 16 22:33:25 apache2 mysqld: 2016-05-16 22:33:25 139713247639680 [Note] WSREP: EVS version 0
May 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:’
May 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
listing
May 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
May 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
May 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
May 16 22:33:27 apache2 mysqld: 2016-05-16 22:33:27 139713247639680 [Note] WSREP: Node 16c80092 state prim
May 16 22:33:27 apache2 mysqld: 2016-05-16 22:33:27 139713247639680 [Note] WSREP: view(view_id(PRIM,16c80092,7) memb {
May 16 22:33:27 apache2 mysqld: 16c80092,0
May 16 22:33:27 apache2 mysqld: 1f8d7692,0
May 16 22:33:27 apache2 mysqld: 258ce90a,0
May 16 22:33:27 apache2 mysqld: } joined {
May 16 22:33:27 apache2 mysqld: } left {
May 16 22:33:27 apache2 mysqld: } partitioned {
May 16 22:33:27 apache2 mysqld: })
May 16 22:33:27 apache2 mysqld: 2016-05-16 22:33:27 139713247639680 [Note] WSREP: save pc into disk
May 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139713247639680 [Note] WSREP: gcomm: connected
May 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
May 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139713247639680 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
May 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139713247639680 [Note] WSREP: Opened channel ‘galera’
May 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139713247639680 [Note] WSREP: Waiting for SST to complete.
May 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
May 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
May 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
May 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)
May 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)
May 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)
May 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: Quorum results:
May 16 22:33:28 apache2 mysqld: version = 3,
May 16 22:33:28 apache2 mysqld: component = PRIMARY,
May 16 22:33:28 apache2 mysqld: conf_id = 6,
May 16 22:33:28 apache2 mysqld: members = 3/3 (joined/total),
May 16 22:33:28 apache2 mysqld: act_id = 0,
May 16 22:33:28 apache2 mysqld: last_appl. = -1,
May 16 22:33:28 apache2 mysqld: protocols = 0/7/3 (gcs/repl/appl),
May 16 22:33:28 apache2 mysqld: group UUID = 60149e36-1b72-11e6-910f-029ebfb18fbb
May 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: Flow-control interval: [28, 28]
May 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: Restored state OPEN -> JOINED (0)
May 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
May 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139713247639680 [Note] WSREP: SST complete, seqno: 0
May 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: Member 2.0 (apache2.rmohan.com) synced with group.
May 16 22:33:28 apache2 mysqld: 2016-05-16 22:33:28 139712949905152 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
May 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.
Initialize the first cluster node
Start MariaDB with the special ‘-wsrep-new-cluster’ option , Do it on node db1 only so the primary node of the cluster is initialized:
[root@apache1 ~]# /etc/init.d/mysql start –wsrep-new-cluster
Starting mysql (via systemctl): [ OK ]
[root@apache1
[root@apache1 ~]# mysql -u root -p -e”show status like ‘wsrep%'”
Enter password:
+——————————+——————————————————-+
| Variable_name | Value |
+——————————+——————————————————-+
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_causal_reads | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_cert_index_size | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_cluster_conf_id | 7 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 60149e36-1b72-11e6-910f-029ebfb18fbb |
| wsrep_cluster_status | Primary |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_connected | ON |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_sent | 0 |
| wsrep_gcomm_uuid | 1f8d7692-1b73-11e6-8496-8af69a3bf125 |
| wsrep_incoming_addresses | 192.168.1.74:3306,192.168.1.71:3306,192.168.1.72:3306 |
| wsrep_last_committed | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_cached_downto | 18446744073709551615 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_commits | 0 |
| wsrep_local_index | 1 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_local_state_uuid | 60149e36-1b72-11e6-910f-029ebfb18fbb |
| wsrep_protocol_version | 7 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.15(r3578) |
| wsrep_ready | ON |
| wsrep_received | 4 |
| wsrep_received_bytes | 856 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_thread_count | 5 |
+——————————+——————————————————-+
[root@apache1 ~]#
Some important information in the output are the following lines:
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
And then restoring it on the node to be connected with
[root@apache1 ~]# mysql -u root -p -e ‘CREATE DATABASE apachetest;’
Enter password:
[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));’
Enter password:
[root@apache1 ~]# mysql -u root -p -e ‘INSERT INTO apachetest.mycluster (name, ipaddress) VALUES (“apache1”, “192.168.1.71”);’
Enter password:
[root@apache1 ~]# mysql -u root -p -e ‘SELECT * FROM apachetest.mycluster;’
Enter password:
+—-+———+————–+
| id | name | ipaddress |
+—-+———+————–+
| 2 | apache1 | 192.168.1.71 |
+—-+———+————–+
[root@apache1 ~]# mysql -u root -p -e ‘INSERT INTO apachetest.mycluster (name, ipaddress) VALUES (“apache2”, “192.168.1.72”);’
Enter password:
[root@apache1 ~]#
Troubleshooting
Name Resolution
If the node trying to connect to an establish cluster is reporting the error:
130307 15:14:52 [Warning] IP address ‘192.168.1.71’ could not be resolved: Name or service not known
And the node it is trying to connect to is displaying the error:
‘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)
The try adding the following option to the nodes configuration files:
skip-name-resolve
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.
Credentials
If the node trying to connect to an establish cluster is reporting the error:
WSREP: gcs/src/gcs_group.c:gcs_group_handle_join_msg():719: Will never receive state. Need to abort.
And the node it is trying to connect to is displaying the error:
[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)
ERROR 1045 (28000): Access denied for user ‘root’@’192.168.1.72’ (using password: YES)
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.
Rsync
If you see the error on the established node of
rsync: failed to connect to 192.168.1.72: No route to host (113)
It means that the default state transfer method is Rsync, not mysqldump. To use mysqldump, add the setting
wsrep_sst_method=mysqldump
Or you can open firewall port 4444.
Rsync Wan
If you see the error
sh: wsrep_sst_rsync_wan: command not found
in the log files of the node trying to connect to a cluster, run
cd /usr/bin
ln -s wsrep_sst_rsync wsrep_sst_rsync_wan
File Access
If you get the error File ‘/var/lib/mysql/aria_log_control’ not found make sure that the file is owned my the user mysql.
I ran into this issue running mysql_install_db as root, which meant that the file was owned by root.
Tips and Tricks
Initial Setup
You can find out how large your SQL database is with the command:
SELECT table_schema,
sum(data_length) / 1024 / 1024 “data”,
sum(index_length) / 1024 / 1024 “index”,
sum( data_length + index_length ) / 1024 / 1024 “total”
FROM information_schema.TABLES
GROUP BY table_schema;
This value includes all the indexes, and represents the amount of data MariaDB will transfer to an empty node when it joins the cluster.
To save yourself some time, initialise the nodes that will join the cluster by dumping the SQL data into a file with the command:
mysqldump -u root -ppassword –all-databases > all-database.sql
And then restoring it on the node to be connected with
mysql -u root -ppassword < all-database.sql
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.
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.
Save Bandwidth
Use the setting
wsrep_sst_method=rsync_wan
To save some bandwidth for state transfers over a WAN connection.
mysql -u root -ppassword < all-database.sql
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.
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.
Save Bandwidth
Use the setting
wsrep_sst_method=rsync_wan
To save some bandwidth for state transfers over a WAN connection.
Recent Comments