Install MySQL Replication and Cluster using Galera

INFO: Updated version of Galera cluster installation with high-availability MySQL setup can be refer here

What is Galera? Its generic synchronous multi-master replication plugin for transactional applications.

I will not telling you about this application cause you can read and understand by go to http://www.codership.com/content/using-galera-cluster or refer to the image for Galera overview. I just show you on how to install Galera/MySQL with 1 master and 1 slave (joined node) architecture.

Please find variables that I used as below:

OS: CentOS 5.6 64bit
Server1: 192.168.1.1 (master)
Server2: 192.168.1.2 (joined node)
MySQL root password: rootpass
SST MySQL user: sst
SST MySQL password: sstpass123

Configuration in both servers are quite similar. So I will show you on how to install Galera MySQL in Server1 and then same procedure in Server2 with some minor changes. Lets configure Server1 first:

1. Install gcc44 and gcc44-c++ packages and set CC and CXX variables accordingly:

[root@centos ~] yum install gcc44 gcc44-c++ -y
export CC=gcc44
export CXX=g++44

2. Lets check what kind of MySQL applications installed in the box:

[[email protected] ~] rpm -qa | grep mysql
mysql-5.0.77-4.el5_6.6
mod_auth_mysql-3.0.0-3.2.el5_3
mysql-bench-5.0.77-4.el5_6.6
mysql-connector-odbc-3.51.26r1127-1.el5
mysql-server-5.0.77-4.el5_6.6
mysql-test-5.0.77-4.el5_6.6
libdbi-dbd-mysql-0.8.1a-1.2.2
mysql-devel-5.0.77-4.el5_6.6
mysql-devel-5.0.77-4.el5_6.6
php53-mysql-5.3.3-1.el5_6.1
mysql-5.0.77-4.el5_6.6

3. Lets download all needed applications. Download following applications in respective site:

URL: https://launchpad.net/codership-mysql/+download
Download MySQL-server-wsrep-5.1.53-0.8.0b-x86_64.rpm = the MySQL server
URL: https://launchpad.net/galera/0.8/0.8.0
Download galera-0.8.0-x86_64.rpm = Galera library
URL: http://dev.mysql.com/downloads/mysql/5.1.html
Download the client-community and shared-community for 64bit

4. Following files should exist if you download the correct version:

[[email protected] galera] ls -1
galera-0.8.0-x86_64.rpm
MySQL-client-community-5.1.57-1.rhel5.x86_64.rpm
MySQL-server-wsrep-5.1.53-0.8.0b-x86_64.rpm
MySQL-shared-community-5.1.57-1.rhel5.x86_64.rpm

5. Lets remove currently installed mysql. Dont worry about this, because we are actually upgrading our MySQL to version 5.1:

[[email protected] galera] rpm -e --nodeps mysql.i386 mysql.x86_64 mysql-devel.i386 mysql-devel.x86_64 mysql-server mysql-bench

6. Now, we need to install all files based on following sequence:

[[email protected] galera] rpm -Uhv galera-0.8.0-x86_64.rpm
[[email protected] galera] rpm -Uhv MySQL-client-community-5.1.57-1.rhel5.x86_64.rpm
[[email protected] galera] rpm -Uhv MySQL-shared-community-5.1.57-1.rhel5.x86_64.rpm
[[email protected] galera] rpm -Uhv MySQL-server-wsrep-5.1.53-0.8.0b-x86_64.rpm

7. Lets reset/create MySQL root password. In this example we will use simple root password as above, but I recommend you to use strong password:

[[email protected] ~] mysqladmin -u root password 'rootpass'

8. To ease up MySQL administration, we will need to create /root/.my.cnf files and put following contents. This will automate your login as client:

[client]
user=root
password='rootpass'

9. Change the permission to 600 to make sure no one can open it other than root:

[[email protected] ~] chmod 0600 /root/.my.cnf

10. Check whether MySQL we installed is running correctly. You should see the MySQL version with ‘wsrep_version’:

[[email protected] galera] mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.53 wsrep_0.8.0
 
Copyright (c) 2000, 2010, Oracle and/or....

11. Run following commands inside MySQL. First command will delete empty username, 2nd is to create root@% user, 3rd is update root password for all hosts, 4th is create SST user which we will use to communicate between nodes and last command will grant SST user to dump databases:

mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT USAGE ON *.* TO root@'%' IDENTIFIED BY 'rootpass';
mysql> UPDATE mysql.user SET Password=PASSWORD('rootpass') WHERE User='root';
mysql> GRANT USAGE ON *.* to sst@'%' IDENTIFIED BY 'sstpass123';
mysql> GRANT ALL PRIVILEGES on *.* to sst@'%';

12. Since we have update the MySQL version from 5.0 to 5.1, we need to upgrade the mysql table first by run following command:

[[email protected] ~] mysql_upgrade

13. Lets locate libgalera and take note of the full path. We will use this as the communication provider for MySQL to communicate:

[[email protected] galera] updatedb
[[email protected] galera] locate libgalera
/usr/lib64/galera/libgalera_smm.so

14. Lets configure MySQL so it will know where and how to communicate with other nodes. Open /etc/mysql/conf.d/wsrep.conf (create the directory if it is not exist) with text editor and change value for following variables:

[[email protected] galera] mkdir -p /etc/mysql/conf.d/
[[email protected] galera] vi /etc/mysql/conf.d/wsrep.cnf
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_sst_auth=sst:sstpass123

15. Make sure your IPTables has allowed the required port. If not, use following command to open the port:

[[email protected] ~] iptables -A INPUT -i eth0 -p tcp -m tcp --source 192.168.1.1/24 --dport 3306 -j ACCEPT
[[email protected] ~] iptables -A INPUT -i eth0 -p tcp -m tcp --source 192.168.1.1/24 --dport 4567 -j ACCEPT

16. Make sure in your /etc/my.cnf (create this file it is not exist) to add following line:

!includedir /etc/mysql/conf.d/

17. Restart the MySQL so it will run the latest configuration:

[[email protected] galera] service mysql restart

18. Make sure MySQL is run on port 3306 and wsrep is run on port 4567

[[email protected] galera] netstat -tulpn | grep -e 4567 -e 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      8977/mysqld
tcp        0      0 0.0.0.0:4567                0.0.0.0:*                   LISTEN      8977/mysqld

19. The setup and configuration for Server1 is done. For Server2, just login into the box and repeat step 1 to 18, but with some minor changes as below:

On step 14 (we will tell Server2 to connect to Server1 port on 4567 for cluster replication):

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.1:4567"
wsrep_sst_auth=sst:sstpass123

On step 15 (take note on the source IP is change for Server2):

[[email protected] ~] iptables -A INPUT -i eth0 -p tcp -m tcp --source 192.168.1.2/24 --dport 3306 -j ACCEPT
[[email protected] ~] iptables -A INPUT -i eth0 -p tcp -m tcp --source 192.168.1.2/24 --dport 4567 -j ACCEPT

To check whether Galera is installed correctly and running properly, login into mysql and execute following command:

mysql> show status like 'wsrep%';

You will see similar output as below in both servers:

| wsrep_local_state_comment  | Synced (6)                           |
| wsrep_cluster_conf_id      | 2                                    |
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_state_uuid   | 2d13bbfc-a2c3-11e0-0800-823f3f6853c0 |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_index          | 1                                    |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+

Now your MySQL servers are clustered with Galera replication. You can add another slave by refferring installation steps 18 and it will replicate accordingly.

INFO: Updated version of Galera cluster installation with high-availability MySQL setup can be refer here

Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *