Once in a while an enterprise class product mutates to become even more efficient at what it delivers. This has been the case with mySQL which has seen quite a few developments in the past few years.
One pain point for MySQL has been to achieve enterprise class high availability. Conventional approaches have been to create replication clusters or to use Linux clustering with tools such as pacemaker and DRBD. These approaches provide either fail over or they provide read scalability or a combination of both. A separate approach requires using a different storage engine (ndb) which is packaged in a separate product (MySQL cluster). However the lack of support for foreign keys is by far the strongest deterrent. Enterprises do not want to compromise on data quality. And they do not want to handle that sort of logic in the middle layer.
This has changed with the announcement of Galera from Codership at version 1. Galera allows creating full active active clusters with dynamic node addition. This means we are now approaching five nines on the innodb storage engines. Along with this high availability it also brings on to the table scale out architectures and load balancing. Combine Galera with a software load balancer such as haproxy and you have an enterprise class cluster.
I will show you how to set up a test cluster on the centos platform using rpm based installation.
Let's begin with our Galera installation on CentOS 6 in a set of virtual machines running under Virtual Box. In this example I am using the 64 bit versions of software. From the CentOS repositories first download the CentOS minimal iso and create a virtual machine. Use the following steps.
During Linux installation I used the entire 8 GB drive as it's just to test drive Galera. Real life implementations would of course be different. After installation and reboot, configure networking. You will need to do two things.
Edit the file /etc/sysconfig/network and add a valid host name and domain name. In this example I use node1.galera.
NETWORKING=yes
HOSTNAME=node1.galera
Edit the file /etc/sysconfig/network-scripts/ifcfg-eth0 to give a static IP address from the host only network. The file should look like this:
DEVICE="eth0"
HWADDR="08:00:27:DB:DD:FE"
ONBOOT="yes"
BOOTPROTO="static"
IPADDR=192.168.56.21
GATEWAY=192.168.56.1
To find out what network range does the host only network consist of, if you're on a Windows machine you will see something like this:
Ethernet adapter VirtualBox Host-Only Network:
Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::458:ff8a:49a2:3354%26
IPv4 Address. . . . . . . . . . . : 192.168.56.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :
Prepare the MySQL server from the security perspective. For this demonstration, we will disable SELinux and also IPtables firewall. We will also need to ensure that the changes persist through a server reboot by editing the /etc/selinux/config file and changing the value from 'enforcing' to 'disabled'.
[root@localhost ~]# setenforce 0
[root@localhost ~]# service iptables stop
iptables: Flushing firewall rules: [ OK ]
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Unloading modules: [ OK ]
[root@localhost ~]# chkconfig iptables off
Download the following rpm's from the Galera website.
MySQL-client-community-5.1.58-1.rhel5.x86_64
MySQL-server-wsrep-5.1.58-21.1.1.rhel5.x86_64
MySQL-shared-compat-5.1.58-1.rhel5.x86_64
galera-21.1.0-1.rhel5.x86_64
You will need to transfer this to your virtual machine. In this example I have used a host only network. So in order to get this across, I use a very useful piece of software called WinSCP.
At the first attempt you might get these missing dependencies.
error: Failed dependencies:
/usr/bin/perl is needed by MySQL-client-community-5.1.58-1.rhel5.x86_64
Why do we get this. Because Perl is no longer de facto in Red Hat/CentOS distributions, although Python is. And, the MySQL client still contains code written in Perl. This dependency can be met by directly using a 'yum install perl' command if you have a direct connection to the internet. If you are behind a firewall then you'll need to download the full DVD ISO and mount it at /media/cdrom. Then issue the command:
yum --disablerepo=\* --enablerepo=c6-media install perl
After this install the MySQL client which should be successful.
[root@node1 ~]# rpm -Uvh MySQL-client-community-5.1.58-1.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:MySQL-client-community ########################################### [100%]
Next install the remaining RPM's.
[root@localhost ~]# rpm -Uvh MySQL-shared-compat-5.1.58-1.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:MySQL-shared-compat ########################################### [100%]
[root@node1 ~]# rpm -Uvh MySQL-server-wsrep-5.1.58-21.1.1.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:MySQL-server-wsrep ########################################### [100%]
111130 8:40:17 [Note] WSREP: wsrep_load(): loading provider library 'none'
111130 8:40:18 [Note] WSREP: Service disconnected.
111130 8:40:19 [Note] WSREP: Some threads may fail to exit.
111130 8:40:19 [Note] WSREP: wsrep_load(): loading provider library 'none'
111130 8:40:19 [Note] WSREP: Service disconnected.
111130 8:40:20 [Note] WSREP: Some threads may fail to exit.
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h node1.galera password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
Starting MySQL.... SUCCESS!
Now that the MySQL server is installed, lets go ahead with the galera installation.
[root@node1 ~]# rpm -Uvh galera-21.1.0-1.rhel5.x86_64.rpm
error: Failed dependencies:
libcrypto.so.6()(64bit) is needed by galera-21.1.0-1.rhel5.x86_64
libssl.so.6()(64bit) is needed by galera-21.1.0-1.rhel5.x86_64
Hmmm, it seems galera too has some unmet dependencies. So what's going to provide these dependencies. Easy way to find out.
[root@node1 ~]# yum --disablerepo=\* --enablerepo=c6-media resolvedep libcrypto.so.6
Loaded plugins: fastestmirror, presto
Loading mirror speeds from cached hostfile
* c6-media:
0:openssl098e-0.9.8e-17.el6.centos.i686
That's a funny sounding rpm name. Well, no time to get into the details right now, as the focus is on getting the Galera cluster ready.
[root@node1 ~]# yum --disablerepo=\* --enablerepo=c6-media install openssl098e
Both the dependencies are provided by this rpm package. So, now we should be ready to install Galera!
[root@node1 ~]# rpm -Uvh galera-21.1.0-1.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:galera ########################################### [100%]
That's very exciting! On the way to set up the first node of my Galera cluster. But wait a minute. There's some more preparation to be done. First create a directory in /etc named mysql.
Copy the wsrep.cnf file at /usr/share/mysql to this directory. You will also need to copy the sample my.cnf files as per your server foot print to the /etc directory. The reason I created a sub directory in /etc is that the indcludedir tag has a default limit on the amount of nesting permitted. And it also is elegant to have the configuration files in one place. I could have put it in /etc but the tag would have ended up reading (probably) all the cnf files.
[root@node1 mysql]# cp wsrep.cnf /etc/mysql/
[root@node1 mysql]# cp my-small.cnf /etc/my.cnf
Now we need to make the following changes in wsrep.cnf. Be very careful in ensuring that you have the changes in place. Galera is immensely sensitive to configuration parameters, and you might just get a failed cluster initially because of small changes that remain to be implemented.
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_sst_auth=root:galera2011
# Group communication system handle
wsrep_cluster_address="gcomm://"
wsrep_cluster_name="mygalera"
A bit of an explanation on what's being done at the top. The first directive is to give the path of the shared library. This file is picked up when the wsrep service starts. If this is not provided then the MySQL instance behaves as a non cluster instance.
Optionally you can also set this:
wsrep_node_name=node1
The second directive is to provide the MySQL root user and its password. This is required when we use the mysqldump utility to synchronize new cluster member node additions. Another protocol that can be used is rsync which does not require the MySQL root user. We can of course set up another user with all the required permissions. However for this exercise root is sufficient for our needs.
Next we need to make a few changes in my.cnf. In my.cnf, you need to provide an includedir tag so that the wsrep.cnf is read at startup.
!includedir /etc/mysql/
We also need to prepare the MySQL database from the user permissions perspective so that node synchronization can be done using mysqldump. Start MySQL normally using 'service mysql start'.
[root@node1 mysql]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.58 wsrep_21.1.r3138
Next, let's connect and see the existing users and permissions. As you can see from the report below, it's not exactly ready for use. We will need to modify the user list and also provide a password.
mysql> select user, host, password from mysql.user;
+------+--------------+----------+
| user | host | password |
+------+--------------+----------+
| root | localhost | |
| root | node1.galera | |
| root | 127.0.0.1 | |
| | localhost | |
| | node1.galera | |
+------+--------------+----------+
5 rows in set (0.00 sec)
Issue the following SQL statements, which will prepare the database for Galera.
DELETE FROM mysql.user WHERE user='';
DROP USER 'root'@'127.0.0.1';
UPDATE mysql.user SET Password=PASSWORD('galera2011') WHERE User='root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'galera2011';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Now let's see the status of our users. If you see something like this, you're ready to go. Note that this preparation step is required for each node you add in the Galera cluster. It's good to have the root password the same for all nodes.
mysql> select user, host, password from mysql.user;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | *8012BB80B616A3DD5A8B7D382DCFAB30F86DDB1F |
| root | node1.galera | *8012BB80B616A3DD5A8B7D382DCFAB30F86DDB1F |
| root | % | *8012BB80B616A3DD5A8B7D382DCFAB30F86DDB1F |
+------+--------------+-------------------------------------------+
3 rows in set (0.00 sec)
Let's see what the status looks like for wsrep before using Galera. The service is shown to be off and the cluster status is disconnected.
mysql> show status like 'wsrep%';
+--------------------------+----------------------+
| Variable_name | Value |
+--------------------------+----------------------+
| wsrep_cluster_conf_id | 18446744073709551615 |
| wsrep_cluster_size | 0 |
| wsrep_cluster_state_uuid | |
| wsrep_cluster_status | Disconnected |
| wsrep_connected | OFF |
| wsrep_local_index | 18446744073709551615 |
| wsrep_provider_name | |
| wsrep_provider_vendor | |
| wsrep_provider_version | |
| wsrep_ready | ON |
+--------------------------+----------------------+
10 rows in set (0.00 sec)
Now let's restart the MySQL service and see what happens. Log out of the MySQL client and issue the command 'service mysql restart'.
[root@node1 mysql]# service mysql restart
Shutting down MySQL...... SUCCESS!
Starting MySQL. SUCCESS!
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid | 111fc28b-1b05-11e1-0800-e00ec5a7c930 |
| wsrep_protocol_version | 1 |
| wsrep_last_committed | 0 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 134 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced (6) |
| wsrep_cert_index_size | 0 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 111fc28b-1b05-11e1-0800-e00ec5a7c930 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy |
| wsrep_provider_version | 21.1.0(r86) |
| wsrep_ready | ON |
+----------------------------+--------------------------------------+
38 rows in set (0.01 sec)
We have now got our first node ready. Now it's time to get this working on a new node.
Follow the above steps for my.cnf changes, and do the following changes in wsrep.cnf.
# Group communication system handle
wsrep_cluster_address="gcomm://192.168.56.21"
Here, we're telling the second node what the IP address of the primary (first) node is, so that it can become a member of the cluster. You will also need to prepare the database with the set of SQL statements described earlier. The other changes are:
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_sst_auth=root:galera2011
wsrep_cluster_name="mygalera"
After logging in to the second node and showing the wsrep status using the MySQL client you should see two nodes for the parameter wsrep_cluster_size.
Happy Clustering! In the next post we will look at using HA Proxy as a TCP load balancer in front of the MySQL cluster.
One pain point for MySQL has been to achieve enterprise class high availability. Conventional approaches have been to create replication clusters or to use Linux clustering with tools such as pacemaker and DRBD. These approaches provide either fail over or they provide read scalability or a combination of both. A separate approach requires using a different storage engine (ndb) which is packaged in a separate product (MySQL cluster). However the lack of support for foreign keys is by far the strongest deterrent. Enterprises do not want to compromise on data quality. And they do not want to handle that sort of logic in the middle layer.
This has changed with the announcement of Galera from Codership at version 1. Galera allows creating full active active clusters with dynamic node addition. This means we are now approaching five nines on the innodb storage engines. Along with this high availability it also brings on to the table scale out architectures and load balancing. Combine Galera with a software load balancer such as haproxy and you have an enterprise class cluster.
I will show you how to set up a test cluster on the centos platform using rpm based installation.
Let's begin with our Galera installation on CentOS 6 in a set of virtual machines running under Virtual Box. In this example I am using the 64 bit versions of software. From the CentOS repositories first download the CentOS minimal iso and create a virtual machine. Use the following steps.
- In Virtualbox, click on the New button and select Red Hat 64 Bit. Make sure that in your BIOS you have Virtualization enabled. Else, it will also work with 32 bit machines. Select Red Hat for 32 bit machines.
- Give a suitable name for the machine. I provided 512 MB RAM and disabled the audio and usb drivers.
- After the machine is created and you see an entry in the navigation pane in Virtualbox, right click and go to settings. In the Network option, select 'host only'.
- Start the virtual machine. At first start it will request you to either provide the path to the CD/DVD drive or ask for an ISO file. Select browse and select the downloaded CentOS minimal ISO file
During Linux installation I used the entire 8 GB drive as it's just to test drive Galera. Real life implementations would of course be different. After installation and reboot, configure networking. You will need to do two things.
Edit the file /etc/sysconfig/network and add a valid host name and domain name. In this example I use node1.galera.
NETWORKING=yes
HOSTNAME=node1.galera
Edit the file /etc/sysconfig/network-scripts/ifcfg-eth0 to give a static IP address from the host only network. The file should look like this:
DEVICE="eth0"
HWADDR="08:00:27:DB:DD:FE"
ONBOOT="yes"
BOOTPROTO="static"
IPADDR=192.168.56.21
GATEWAY=192.168.56.1
To find out what network range does the host only network consist of, if you're on a Windows machine you will see something like this:
Ethernet adapter VirtualBox Host-Only Network:
Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::458:ff8a:49a2:3354%26
IPv4 Address. . . . . . . . . . . : 192.168.56.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :
Prepare the MySQL server from the security perspective. For this demonstration, we will disable SELinux and also IPtables firewall. We will also need to ensure that the changes persist through a server reboot by editing the /etc/selinux/config file and changing the value from 'enforcing' to 'disabled'.
[root@localhost ~]# setenforce 0
[root@localhost ~]# service iptables stop
iptables: Flushing firewall rules: [ OK ]
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Unloading modules: [ OK ]
[root@localhost ~]# chkconfig iptables off
Download the following rpm's from the Galera website.
MySQL-client-community-5.1.58-1.rhel5.x86_64
MySQL-server-wsrep-5.1.58-21.1.1.rhel5.x86_64
MySQL-shared-compat-5.1.58-1.rhel5.x86_64
galera-21.1.0-1.rhel5.x86_64
You will need to transfer this to your virtual machine. In this example I have used a host only network. So in order to get this across, I use a very useful piece of software called WinSCP.
At the first attempt you might get these missing dependencies.
error: Failed dependencies:
/usr/bin/perl is needed by MySQL-client-community-5.1.58-1.rhel5.x86_64
Why do we get this. Because Perl is no longer de facto in Red Hat/CentOS distributions, although Python is. And, the MySQL client still contains code written in Perl. This dependency can be met by directly using a 'yum install perl' command if you have a direct connection to the internet. If you are behind a firewall then you'll need to download the full DVD ISO and mount it at /media/cdrom. Then issue the command:
yum --disablerepo=\* --enablerepo=c6-media install perl
After this install the MySQL client which should be successful.
[root@node1 ~]# rpm -Uvh MySQL-client-community-5.1.58-1.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:MySQL-client-community ########################################### [100%]
Next install the remaining RPM's.
[root@localhost ~]# rpm -Uvh MySQL-shared-compat-5.1.58-1.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:MySQL-shared-compat ########################################### [100%]
[root@node1 ~]# rpm -Uvh MySQL-server-wsrep-5.1.58-21.1.1.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:MySQL-server-wsrep ########################################### [100%]
111130 8:40:17 [Note] WSREP: wsrep_load(): loading provider library 'none'
111130 8:40:18 [Note] WSREP: Service disconnected.
111130 8:40:19 [Note] WSREP: Some threads may fail to exit.
111130 8:40:19 [Note] WSREP: wsrep_load(): loading provider library 'none'
111130 8:40:19 [Note] WSREP: Service disconnected.
111130 8:40:20 [Note] WSREP: Some threads may fail to exit.
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h node1.galera password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
Starting MySQL.... SUCCESS!
Now that the MySQL server is installed, lets go ahead with the galera installation.
[root@node1 ~]# rpm -Uvh galera-21.1.0-1.rhel5.x86_64.rpm
error: Failed dependencies:
libcrypto.so.6()(64bit) is needed by galera-21.1.0-1.rhel5.x86_64
libssl.so.6()(64bit) is needed by galera-21.1.0-1.rhel5.x86_64
Hmmm, it seems galera too has some unmet dependencies. So what's going to provide these dependencies. Easy way to find out.
[root@node1 ~]# yum --disablerepo=\* --enablerepo=c6-media resolvedep libcrypto.so.6
Loaded plugins: fastestmirror, presto
Loading mirror speeds from cached hostfile
* c6-media:
0:openssl098e-0.9.8e-17.el6.centos.i686
That's a funny sounding rpm name. Well, no time to get into the details right now, as the focus is on getting the Galera cluster ready.
[root@node1 ~]# yum --disablerepo=\* --enablerepo=c6-media install openssl098e
Both the dependencies are provided by this rpm package. So, now we should be ready to install Galera!
[root@node1 ~]# rpm -Uvh galera-21.1.0-1.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:galera ########################################### [100%]
That's very exciting! On the way to set up the first node of my Galera cluster. But wait a minute. There's some more preparation to be done. First create a directory in /etc named mysql.
Copy the wsrep.cnf file at /usr/share/mysql to this directory. You will also need to copy the sample my.cnf files as per your server foot print to the /etc directory. The reason I created a sub directory in /etc is that the indcludedir tag has a default limit on the amount of nesting permitted. And it also is elegant to have the configuration files in one place. I could have put it in /etc but the tag would have ended up reading (probably) all the cnf files.
[root@node1 mysql]# cp wsrep.cnf /etc/mysql/
[root@node1 mysql]# cp my-small.cnf /etc/my.cnf
Now we need to make the following changes in wsrep.cnf. Be very careful in ensuring that you have the changes in place. Galera is immensely sensitive to configuration parameters, and you might just get a failed cluster initially because of small changes that remain to be implemented.
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_sst_auth=root:galera2011
# Group communication system handle
wsrep_cluster_address="gcomm://"
wsrep_cluster_name="mygalera"
A bit of an explanation on what's being done at the top. The first directive is to give the path of the shared library. This file is picked up when the wsrep service starts. If this is not provided then the MySQL instance behaves as a non cluster instance.
Optionally you can also set this:
wsrep_node_name=node1
The second directive is to provide the MySQL root user and its password. This is required when we use the mysqldump utility to synchronize new cluster member node additions. Another protocol that can be used is rsync which does not require the MySQL root user. We can of course set up another user with all the required permissions. However for this exercise root is sufficient for our needs.
Next we need to make a few changes in my.cnf. In my.cnf, you need to provide an includedir tag so that the wsrep.cnf is read at startup.
!includedir /etc/mysql/
We also need to prepare the MySQL database from the user permissions perspective so that node synchronization can be done using mysqldump. Start MySQL normally using 'service mysql start'.
[root@node1 mysql]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.58 wsrep_21.1.r3138
Next, let's connect and see the existing users and permissions. As you can see from the report below, it's not exactly ready for use. We will need to modify the user list and also provide a password.
mysql> select user, host, password from mysql.user;
+------+--------------+----------+
| user | host | password |
+------+--------------+----------+
| root | localhost | |
| root | node1.galera | |
| root | 127.0.0.1 | |
| | localhost | |
| | node1.galera | |
+------+--------------+----------+
5 rows in set (0.00 sec)
Issue the following SQL statements, which will prepare the database for Galera.
DELETE FROM mysql.user WHERE user='';
DROP USER 'root'@'127.0.0.1';
UPDATE mysql.user SET Password=PASSWORD('galera2011') WHERE User='root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'galera2011';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Now let's see the status of our users. If you see something like this, you're ready to go. Note that this preparation step is required for each node you add in the Galera cluster. It's good to have the root password the same for all nodes.
mysql> select user, host, password from mysql.user;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | *8012BB80B616A3DD5A8B7D382DCFAB30F86DDB1F |
| root | node1.galera | *8012BB80B616A3DD5A8B7D382DCFAB30F86DDB1F |
| root | % | *8012BB80B616A3DD5A8B7D382DCFAB30F86DDB1F |
+------+--------------+-------------------------------------------+
3 rows in set (0.00 sec)
Let's see what the status looks like for wsrep before using Galera. The service is shown to be off and the cluster status is disconnected.
mysql> show status like 'wsrep%';
+--------------------------+----------------------+
| Variable_name | Value |
+--------------------------+----------------------+
| wsrep_cluster_conf_id | 18446744073709551615 |
| wsrep_cluster_size | 0 |
| wsrep_cluster_state_uuid | |
| wsrep_cluster_status | Disconnected |
| wsrep_connected | OFF |
| wsrep_local_index | 18446744073709551615 |
| wsrep_provider_name | |
| wsrep_provider_vendor | |
| wsrep_provider_version | |
| wsrep_ready | ON |
+--------------------------+----------------------+
10 rows in set (0.00 sec)
Now let's restart the MySQL service and see what happens. Log out of the MySQL client and issue the command 'service mysql restart'.
[root@node1 mysql]# service mysql restart
Shutting down MySQL...... SUCCESS!
Starting MySQL. SUCCESS!
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid | 111fc28b-1b05-11e1-0800-e00ec5a7c930 |
| wsrep_protocol_version | 1 |
| wsrep_last_committed | 0 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 134 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced (6) |
| wsrep_cert_index_size | 0 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 111fc28b-1b05-11e1-0800-e00ec5a7c930 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy
| wsrep_provider_version | 21.1.0(r86) |
| wsrep_ready | ON |
+----------------------------+--------------------------------------+
38 rows in set (0.01 sec)
We have now got our first node ready. Now it's time to get this working on a new node.
Follow the above steps for my.cnf changes, and do the following changes in wsrep.cnf.
# Group communication system handle
wsrep_cluster_address="gcomm://192.168.56.21"
Here, we're telling the second node what the IP address of the primary (first) node is, so that it can become a member of the cluster. You will also need to prepare the database with the set of SQL statements described earlier. The other changes are:
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_sst_auth=root:galera2011
wsrep_cluster_name="mygalera"
After logging in to the second node and showing the wsrep status using the MySQL client you should see two nodes for the parameter wsrep_cluster_size.
Happy Clustering! In the next post we will look at using HA Proxy as a TCP load balancer in front of the MySQL cluster.
No comments:
Post a Comment