MySQL Cluster (MariaDB Galera)

How to setup MySQL Cluster (master/master) with MariaDB Galera Cluster

We will use two Servers for MySQL Cluster
cluster1 with IP 10.90.90.101
cluster2 with IP 10.90.90.102
..this can be public or private IP's depending on your needs.

Make sure that SeLinux is disabled and IP's are whitelisted in the firewall.

Create a mariadb repository: nano /etc/yum.repos.d/mariadb.repo

CentOS 6 64bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

CentOS 6 32bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-x86
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Installation on all cluster servers

yum -y install http://dl.fedoraproject.org/pub/epel/6/x86_64/socat-1.7.2.3-1.el6.x86_64.rpm
yum -y install MariaDB-server MariaDB-client rsync galera
service mysql start
/usr/bin/mysql_secure_installation

You can enter a DATABASE-PASSWORD and accepted all defaults (if you don't know what to do).

Now we need to add MariaDB Galera Cluster user

mysql -u root -p

** For password just hit enter

mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'DATABASE-PASSWORD';
mysql> GRANT USAGE ON *.* to sst_user@'%' IDENTIFIED BY 'DATABASE-PASSWORD';
mysql> GRANT ALL PRIVILEGES on *.* to sst_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> quit

You can replace "%" with hostname or IP address from which that users can access the database.
"%" means that you can access databases from any server so this is decreasing your security level.

Now lets configure MariaDB Galera Cluster

service mysql stop

Under [galera] in file /etc/my.cnf.d/server.cnf add cluster configuration.
DON'T FORGET TO EDIT wsrep_node_address and wsrep_node_name for each server.

wsrep_on=ON
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.90.90.101,10.90.90.102"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='10.90.90.101'
wsrep_node_name='cluster1'
wsrep_sst_method=rsync
wsrep_sst_auth=sst_user:dbpass

Now let's start our clusters
On cluster1 ONLY run MySQL with additonal option "--wsrep-new-cluster"

/etc/init.d/mysql start --wsrep-new-cluster

On every other cluster server like cluster2 in our example you need to start mysql as usual

service mysql start

And that is it, you cluster should be working now :)

Test which MySQL servers are running in cluster
You should see IP address of each server in cluster and number of servers in cluster

mysql -u root -p -e "show status like 'wsrep%'"
| wsrep_local_state_comment | Synced                                 |
| wsrep_incoming_addresses  | 10.90.90.101:3306,10.90.90.102:3306    |
| wsrep_cluster_size        | 3                                      |
| wsrep_connected           | ON                                     |
| wsrep_ready               | ON                                     |

Final improvements
Avoid password requests from command line for MySQL root user:
Create file /root/.my.cnf and add in it

[client]
host=MYSQL-IP
port=3306
password="DATABASE-PASSWORD"  
user=root

Secure this file to be readable for root user only

chmod 400 /root/.my.cnf

Avoid DNS
To avoid DNS communication between cluster servers we need to define cluster servers in /etc/hosts file on each server.
We can do that by adding IP's and server name for each cluster at end of /etc/hosts file.

10.90.90.101 cluster1
10.90.90.102 cluster2

You can also change configuration for server by using example from this files /usr/share/mysql/:

my-huge.cnf (1-2GB RAM)        
my-innodb-heavy-4G.cnf (4GB RAM,InnoDB only,ACID,heavy queries)
my-large.cnf (512MB RAM)        
my-medium.cnf (128MB RAM)    
my-small.cnf (<=64MB RAM)

Don't forget that cluster works with innodb tables, you can check the database table type with this command.

mysql -e "SHOW TABLE STATUS FROM \`DATABASE-NAME\`;"|awk {'print $2'}|sort|uniq -c

*** Replace DATABASE-NAME with the name of your database.

For assistance you can contact our support
http://centos-webpanel.com/contact

Tagged: