How to setup database cluster for high availability : Digital ocean with Maria DB

2016-07-27

Vijay Mali

When dealing with applications on the cloud, it is often best to have replications and redundancies in place. For relational databases, replication in a cluster allows data to be transferred between cluster members (server nodes) automatically so that the applications that depend on these databases will still be functional should part of the servers go offline for whatever reason.

In a master-master configuration, each node is able to allow both reads and writes to be distributed throughout the cluster.
Starting from MariaDB version 10.1, the popular MySQL fork comes packaged with Galera Cluster, allowing synchronous master-master replication.
This guide covers how to setup master-master data replication across an arbitrary number of nodes deployed on servers running Ubuntu 16.04 LTS. Once master-master data replication is configured, our applications will be able to connect to MySQL databases on any of our nodes.

Architecture

In this guide, we will be creating droplets based on the Ubuntu 16.04 LTS x64 image. We will also be using DigitalOcean’s snapshots services to reduce the amount of installations we will need to run during our cluster node deployments.

MariaDB 10.1 is the current stable (GA) release of MariaDB. It is built on MariaDB 10.0 with features from MySQL 5.6 & 5.7, as well as new features not found anywhere else. New in MariaDB 10.1, the Galera Cluster packages used for clustering have been combined into the core installation. All the packages required for clustering will be installed by default but will remain dormant until configured.

Galera let’s you scale your database setup both vertically and horizontally. If you feel that your reads are not fast enough or you would like to rearrange your database server-web server assignations, scaling horizontally by adding database servers is recommended. Just make sure to follow the right steps.

Preparing the Server

We will be installing and configuring MariaDB Galera Cluster on one droplet and duplicating it across different regions via the DigitalOcean’s snapshot feature.

follow the following Steps

Login to digitalocean.com & Create a Droplets
In DigitalOcean’s control panel, start a new droplet using the Ubuntu 16.04 x64 image.
Create a Droplets to follow the instructions
Choose a size package
Choose a Data Center Region
Select Additional Options
Add Your Ssh Key (Optional)
Finalize And Create Droplets
type your droplets name
Take a Snap Shot after create your Droplets
Click on droplets to open your droplets panel
click to snapshot & take a snap shot

Once the Server is ready, access the server via SSH.

Using the IP address and password for your cloud server, log in by running the ssh command with username@ipaddress as the argument.
Now open your computer terminal : CTRL+ALT+T and type command.

$ ssh root@192.168.1.1

if You know about ssh-key -------------click here---------------How to use ssh-key
Now you are login on server 1

root@server1:~#

Add a Swap Space to Increase Your Droplets Capacities

Note: While it is tempting to enable swap so that we can get more out of our servers, both MariaDB and DigitalOcean advised against it.

Follow the command to add swap partition

Check the System for Swap Information
```
$ sudo swapon --show
```
If you don't get back any output, this means your system does not have swap space available currently.
You can verify that there is no active swap using the free utility:
```
$ free -h
```
Output:-
                 total        used        free      shared buff/cache   available
Mem:       488M     36M         104M        652K        348M        426M
Swap:        0B          0B          0B

As you can see in the "Swap" row of the output, no swap is active on the system.

Check Available Space on the Hard Drive Partition

$ df -h

Output:-
Filesystem           Size     Used      Avail     Use%   Mounted on   
udev                 238M     0          238M      0%    /dev 
tmpfs                49M      624K       49M       2%    /run
/dev/vda1            20G      1.1G       18G       6%    / 
tmpfs                245M     0          245M      0%    /dev/shm
tmpfs                5.0M     0          5.0M      0%    /run/lock
tmpfs                245M     0          245M      0%    /sys/fs/cgroup
tmpfs                49M      0          49M       0%    /run/user/1001

The device under /dev is our disk in this case. We have plenty of space available in this example (only 4.1G used). Your usage will probably be different

Create a Swap File

Now that we know our available hard drive space, we can go about creating a swap file within our filesystem. We will create a file of the swap size that we want called swapfile in our root (/) directory.

The best way of creating a swap file is with the fallocate program. This command creates a file of a preallocated size instantly.

Since the server in our example has 512MB of RAM, we will create a 4 Gigabyte file in this guide. Adjust this to meet the needs of your own server:
```
$ sudo fallocate -l 4G /swapfile
```
We can verify that the correct amount of space was reserved by typing:
```
$ ls -lh /swapfile
```
Output: -rw-r--r-- 1 root root 4.0G Apr 25 11:14 /swapfile
Our file has been created with the correct amount of space set aside.
Enabling the Swap File
```
$ sudo chmod 600 /swapfile
```
Verify the permissions change by typing:
```
$ ls -lh /swapfile
```
Output:
-rw------- 1 root root 4.0G Apr 25 11:14 /swapfile

As you can see, only the root user has the read and write flags enabled.
We can now mark the file as swap space by typing:
```
$ sudo mkswap /swapfile
```
Output:
Setting up swapspace version 1, size = 4 Gib (1073737728 bytes)
no label, UUID=6e965805-2ab9-450f-aed6-577e74089dbf
After marking the file, we can enable the swap file, allowing our system to start utilizing it:
```
$ sudo swapon /swapfile
```
We can verify that the swap is available by typing:
```
$ sudo swapon --show
```
Output:
NAME TYPE SIZE USED PRIO
/swapfile file 4G 0B -1
We can check the output of the free utility again to corroborate our findings:
```
$ free -h
```
Output:
                     total        used        free      shared buff/cache   available
Mem:            488M        37M        96M        652K        354M        425M
Swap:           4.0G          0B          4.0G

Our swap has been set up successfully and our operating system will begin to use it as necessary.

More Details goto the link

https://www.digitalocean.com/community/tutorials/how-to-add-swap-space-on-ubuntu-16-04

Server Setup & Configuration

Install Apache with php and mysql

Install MariaDB 10.1 on Ubuntu14.04/15.10/16.04
I always like to install software packages from official repository (if there’s one) rather than from my Linux distribution repository. For one thing, I can install the latest stable version. For another, I don’t have to worry about distribution specific modifications. In other words, what I got is a bog-standard package no matter what Linux distribution I use.

This tutorial will guide you through the process of installing the latest stable version of MariaDB and that’s MariaDB 10.1 on Ubuntu 14.04 and 15.10, 16.04. Ubuntu repository has MariaDB 10.0, but no MariaDB 10.1.

The MariaDB and Galera packages are not available in the default Ubuntu repositories. However, the MariaDB project maintains its own repositories for Ubuntu that contain all of the packages that we need.

Step1: Install software-properties-common
```
$ sudo apt-get install software-properties-common
```
Step2: Fetch MariaDB signing key from Ubuntu’s key server.
```
$ apt-key adv --keyserver ha.pool.sks-keyservers.net --recv-keys F1656F24C74CD1D8
```
Step3: add MariaDB repository to your system.
ubuntu 14.04
```
$ sudo add-apt-repository 'deb [arch=amd64,i386] http://sgp1.mirrors.digitalocean.com/mariadb/repo/10.1/ubuntu trusty main'
```
ubuntu 15.10
```
$ sudo add-apt-repository 'deb [arch=amd64,i386] http://sgp1.mirrors.digitalocean.com/mariadb/repo/10.1/ubuntu wily main'
```
Ubuntu 16.04
```
$ sudo add-apt-repository 'deb [arch=amd64,i386] http://sgp1.mirrors.digitalocean.com/mariadb/repo/10.1/ubuntu xenial main'
```
Step4: Update server
```
$ sudo apt-get update
```
Step4: Update server
```
$ sudo apt-get install mariadb-server
```
You will be asked to set a password for the MariaDB root user.

After it’s installed, mysqld process will be automatically started.

Check version
```
root@server1:~# mysql --version
```
```
Output:
mysql Ver 15.1 Distrib 10.1.16-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

root@server1:~# 
```
Finally we should execute the secure installation script to remove anonymous user, disable remote root login and remove test database for security reasons.
```
root@server1:~# mysql_secure_installation
```
You will be asked these questions:
Enter current password for root (enter for none): press enter
Set root password? [Y/n] y
New password: Re-enter new password: Repeat the password
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] <-- y
Reload privilege tables now? [Y/n] y

Test the login to MariaDB with the "mysql command"
```
root@server1:~# mysql -uroot -p
Output: 

Welcome to the MariaDB monitor. Commands end with ; or \g
Your MariaDB connection id is 9
Server version: 10.1.16-MariaDB-1~xenial mariadb.org binary distribution
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
```
To leave the MariaDB shell, enter the command "quit" and press enter.
Install Apache 2.4
Apache 2 is available as an Ubuntu package, therefore we can install it like this:
```
root@server1:~#  sudo apt-get -y install apache2
```
Now direct your browser to http://your_ip_address, and you should see the Apache2 default page (It works!):

The document root of the apache default vhost is /var/www/html on Ubuntu and the main configuration file is /etc/apache2/apache2.conf. The configuration system is fully documented in /usr/share/doc/apache2/README.Debian.gz.
Install PHP 7
We can install PHP 7 and the Apache PHP module as follows:
```
root@server1:~#   sudo apt-get -y install php7.0 libapache2-mod-php7.0
```
Then restart Apache:
```
root@server1:~#  sudo systemctl restart apache2 or sudo service apache2 restart
```
Test PHP and get details about your PHP installation
The document root of the default web site is /var/www/html. We will now create a small PHP file (info.php) in that directory and call it in a browser. The file will display lots of useful details about our PHP installation, such as the installed PHP version.
Now create a info file to get Details of PHP
Now open your computer terminal : CTRL+ALT+T and type command.
```
root@server1:~# nano /var/www/html/info.php


type: CTRL+x , y ,enter
```
Then change the owner of the info.php file to the www-data user and group.
```
$ chown www-data:www-data /var/www/html/info.php
```
Now we call that file in a browser (e.g. http://your_ip_address/info.php):

As you see, PHP 7.0 is working, and it's working through the Apache 2.0 Handler, as shown in the Server API line. If you scroll further down, you will see all modules that are already enabled in PHP5. MySQL is not listed there which means we don't have MySQL / MariaDB support in PHP yet.
Get MySQL / MariaDB support in PHP
To get MySQL support in PHP, we can install the php7.0-mysql package. It's a good idea to install some other PHP modules as well as you might need them for your applications. You can search for available PHP modules like this:
```
$ apt-cache search php7.0
```
Pick the ones you need and install them like this:
```
$ sudo apt-get -y install php7.0-mysql php7.0-curl php7.0-gd php7.0-intl php-pear php-imagick php7.0-imap php7.0-mcrypt php-memcache php7.0-pspell php7.0-recode php7.0-sqlite3 php7.0-tidy php7.0-xmlrpc php7.0-xsl php7.0-mbstring php-gettext
```
Now restart Apache2:
```
$ systemctl restart apache2
```
PHP 7 has now MySQL / MariaDB support as shown in phpinfo() above.

Install the APCu PHP cache to speed up PHP
APCu is a free PHP opcode cacher for caching and optimizing PHP intermediate code. It is strongly recommended to have an Opcache installed to speed up your PHP page.
APCu can be installed as follows:
```
$ sudo apt-get -y install php-apcu
```
Now restart Apache:
```
$ systemctl restart apache2
```
Now reload http://your_ip_address/info.php in your browser and scroll down to the modules section again. You should now find lots of new modules there:

Note : Please don't forget to delete the info.php file when you don't need it anymore as it provides sensitive details of your server. Run the following command to delete the file.
```
$ rm -f /var/www/html/info.php
```

Enable the SSL website in apache

SSL/ TLS is a security layer to encrypt the connection between the web browser and your server. Execute the following commands on your server to enable https:// support. Run:

$ a2enmod ssl

$ a2ensite default-ssl

which enables the ssl module and adds a symlink in the /etc/apache2/sites-enabled folder to the file /etc/apache2/sites-available/default-ssl.conf to include it into the active apache configuration. Then restart apache to enable the new configuration:

$ systemctl restart apache2

Now test the SSL connection by opening https://your_ip_address in a web browser.

You will receive an SSL warning as the SSL certificate of the server is a "self-signed" SSL certificate, this means that the browser does not trust this certificate by default and you have to accept the security warning first. After accepting the warning, you will see the apache default page.

The closed "Green Lock" in front of the URL in the browser shows that the connection is encrypted. To get rid of the SSL warning, replace the self-signed SSL certificate /etc/ssl/certs/ssl-cert-snakeoil.pem with an officially signed SSL certificate from an SSL Authority.

Install phpMyAdmin

phpMyAdmin is a web interface through which you can manage your MySQL databases. It's a good idea to install it:

$ sudo apt-get -y install phpmyadmin

You will see the following questions:
Web server to configure automatically: Select the option: apache2
 Configure database for phpmyadmin with dbconfig-common? Yes
 MySQL application password for phpmyadmin: Press enter, apt will create a random password automatically.

MariaDB enables a plugin called "unix_socket" for the root user by default, this plugin prevents that the root user can log in to PHPMyAdmin and that TCP connections to MySQL are working for the root user. Therefore, I'll deactivate that plugin with the following command:

$ echo "update user set plugin='' where User='root'; flush privileges;" | mysql -u root -p mysql

Enter the MariaDB root password, when requested by the mysql command.
Afterward, you can access phpMyAdmin under http://your_ip_address/phpmyadmin/:

Take a Snap Shot

Click on droplets to open your droplets panel
click to snapshot & take a snap shot

Configure Mariadb Galera Cluster

What we need

In our setup we assume 3 nodes (server1, server2, server3) with one interface each. We assume following IP addresses: 192.168.1.1,192.168.1.2, and 192.168.1.3 .

Configuring Galera

So we have to do some configuration next. There is a MariaDB configuration part and one part to configure Galera (starting with wsrep_).
As we do the most basic and simple installation in this Howto, it is sufficient you just change the IP’s (Remember: 192.168.1.1,192.168.1.2,192.168.1.3) with your IP’s.
In our example, we have set hostnames on each node (server1,server2,server3) so we do not need the IP addresses of the hosts.
This will be needed to define the wsrep_cluster_address Variable (the list of nodes a starting mysqld contacts to join the cluster).

The following configuration file has to be distributed on all nodes. We use a separate configuration file (create a new file) /etc/mysql/conf.d/galera.cnf with the following settings:

Now open your computer terminal : CTRL+ALT+T and type command.

$ nano /etc/mysql/conf.d/galera.cnf

[mysqld]
#mysql settings
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0

#galera settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="test_cluster"
wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3
wsrep_sst_method=rsync

FYI: The shared library for wsrep_provider is provided by the installed galera package.

We could also change the cluster name by changing the value of wserp_cluster_name to fit our style. This setting also works as a shared secret to control the access to the cluster.
With wsrep_cluster_address you see the hostnames of our setup. wsrep_cluster_address could also be gcomm://192.168.1.1,192.168.1.2,192.168.1.3. Multiple IP’s or hostnames have to be comma seperated.
The wsrep_sst_method tells what method to use to synchronise the nodes. While there are also mysqldump and xtrabackup available, I prefer rsync because it is easy to configure (i.e. it does not need any credentials set on the nodes). If you are considering using the xtrabackup method, don’t forget to install xtrabackup.

Now stop mysqld on all nodes:

root@server1:~# systemctl stop mysql

root@server2:~# systemctl stop mysql

root@server3:~# systemctl stop mysql

Starting the Galera Cluster

The configuration file (galera.cnf) is already distributed to all nodes, so we next start the first mysqld on cluster01. This node initializes/starts the cluster (creates a GTID).

Following commands to start Galera Cluster example

galera_new_cluster

service mysql bootstrap

service mysql bootstrap --wsrep-new-cluster

service mysql bootstrap --wsrep-cluster-address="gcomm://"

service mysql start

service mysql start --wsrep-new-cluster

service mysql start --wsrep-cluster-address="gcomm://"

systemctl start mariadb

systemctl start mariadb --wsrep-new-cluster

systemctl start mariadb --wsrep-cluster-address="gcomm://"

root@server1:~# galera_new_cluster

root@server1:~# mysql -u root -p -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
Output:
+--------------+
| cluster size |
+--------------+
| 1            |
+--------------+

If you see the above, great! That’s what we would expect. Now that the Cluster already exists, we let the next nodes just start and join the server.

root@server2:~# systemctl start mysql

Let’s pause here and do a quick check. As we are running a server it is not important if we execute the following on cluster01 or server2.

root@server2:~# mysql -u root -p -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
Output:
+--------------+
| cluster size |
+--------------+
| 2            |
+--------------+

If you see the above, very nice! Now let’s start the third node:

root@server3:~# systemctl start mysql

root@server3:~# mysql -u root -p -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
Output:
+--------------+
| cluster size |
+--------------+
| 3            |
+--------------+

Ok we are finished. We have a running MariaDB Galera Cluster \o/

Restarting the whole Galera Cluster

If the whole galera cluster has to be restarted (due to poweroutage for example) we have to complete the following steps by hand:

Identify the node with the most advanced node state ID.
Start the most advanced node as the first node of the cluster.
Start the rest of the node as usual.

Identify the node with the most advanced node state ID.

$ cat /var/lib/mysql/grastate.dat

The node with the highest seqno is your new first host.

Start the most advanced node as the first node of the cluster.

On the new first host, the one with the highes squence number, bootstrap the galera cluster again:

$ galera_new_cluster

Start the rest of the node as usual.
On each other node start mysqld as usual:

$ systemctl start mysql

Test Master-Master Replication

We've gone through the steps up to this point so that our cluster can perform master-master replication. We need to test this out to see if the replication is working as expected.

On one of our our nodes, we can create a database and table like this:

mysql -u root -pmariadb_admin_password -e 'CREATE DATABASE playground;'
mysql -u root -pmariadb_admin_password -e 'CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));'

This will create a database called playground and a table inside of this called equipment.

We can then insert our first item into this table by executing:

mysql -u root -pmariadb_admin_password -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("slide", 2, "blue")'

We now have one value in our table.

From another node, we can read this data by typing:

mysql -u root -pmariadb_admin_password -e 'SELECT * FROM playground.equipment;'

+----+-------+-------+-------+
| id | type  | quant | color |
+----+-------+-------+-------+
|  1 | slide |     2 | blue  |
+----+-------+-------+-------+

From this same node, we can write data to the cluster:

mysql -u root -pmariadb_admin_password -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("swing", 10, "yellow");'

From our third node, we can read all of this data by querying the again:

mysql -u root -pmariadb_admin_password -e 'SELECT * FROM playground.equipment;'

+----+-------+-------+--------+
| id | type  | quant | color  |
+----+-------+-------+--------+
|  1 | slide |     2 | blue   |
|  2 | swing |    10 | yellow |
+----+-------+-------+--------+

Again, we can add another value from this node:

mysql -u root -pmariadb_admin_password -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("seesaw", 3, "green");'

Back on the first node, we can see that our data is available everywhere:

mysql -u root -pmariadb_admin_password -e 'SELECT * FROM playground.equipment;'

+----+--------+-------+--------+
| id | type   | quant | color  |
+----+--------+-------+--------+
|  1 | slide  |     2 | blue   |
|  2 | swing  |    10 | yellow |
|  3 | seesaw |     3 | green  |
+----+--------+-------+--------+

As you can see, all of our servers can be written to. This means that we have master-master replication functioning correctly.

What to do when node Crash Or add a new node to a Galera Replication Cluster on Ubuntu

On your Crash or new nodes, Repeat the following step will be to

Preparing the Server with create Droplet
access the server via SSH
Add a Swap Space to Increase Your Droplets Capacities
Server Setup & Configuration

Make sure you have done those operation on both new nodes

You will want to verify connectivity with your 3 original nodes and the other new node, so start by pinging them.

root@server3:~#ping 192.168.1.1 (server2)
root@server3:~#ping 192.168.1.2 (server1)
root@server3:~#ping 192.168.1.3 (server3)

Then, try to connect to the MySQL server on the other nodes. By example from server3 to MySQL on server1(192.168.1.1)

root@server3:~# mysql  -H 192.168.1.1 –u root -p

If you get a request to input your password, this means the mysql server on the other side is listening. You can now proceed with configuring MySQL properly.

Configuring the new Galera Node

From one of your original nodes(server1 by example), copy the configuration to the new nodes. In this example, we are copying the file from server1 to server3:

root@server1:~# scp /etc/mysql/my.cnf root@192.168.1.3:/etc/mysql/

You may also want to copy the /etc/mysql/debian.cnf file from one of the original nodes. In this example, we are copying the file from server1 to Server3:

root@server1:~# scp /etc/mysql/debian.cnf root@192.168.1.3:/etc/mysql/

You may also want to copy the /etc/mysql/conf.d/galera.cnf file from one of the original nodes. In this example, we are copying the file from server1 to server3:

root@server1:~# scp /etc/mysql/conf.d/debian.cnf root@192.168.1.3:/etc/mysql/conf.d/

On the Crash or new nodes, create the necessary users. This operation will be needed on server3 :

root@server3:~# mysql –u root –p
 CREATE USER 'wsrep'@'%' IDENTIFIED BY 'password';
 CREATE USER 'root'@'%' IDENTIFIED BY 'password';
 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Finally, make sure to add the All server Nodes to the wsrep_cluster_address line in the mysql configuration file.

wsrep_cluster_address="gcomm://192.168.1.1,192.168.1.2,192.168.1.3"

One last thing before you start up the new components:

Warning! if you’re using rsync or mysqldump for SST transfer, bringing up the new nodes will lock the databases on the donor nodes. This could bring down heavily used setups. It would be strongly recommended to add those nodes when the setup is less solicited. Additionally, you may want to set the load balancer to only redirect requests on 2 nodes instead of 3. You would now be able to set the third node as a donor, which means it would be the only node with its DB getting locked and providing the new nodes with the data.

To set a SST donor, add the following line in the mysql configuration on the two new nodes:

wsrep_sst_donor= donornodename

You can then start the new nodes and they should start replicating the data from the donors.

service mysql restart

Testing It

Just like when setting up your original setup, you can test the replication by adding databases. However, if your cluster is already getting used, you can just watch as new data gets replicated to it by running a test query on the new nodes. Something really simple like listing the databases should work.

mysql -u root -p
show databases;

Alternatively, if you want to make sure your new nodes are in the cluster, use the following :

show status like '%wsrep%';

The line “wsrep_cluster_size” in the output will tell you how many nodes are in the cluster. If that number is equal to how many servers you have setup, then all is well. You have now managed to scale out your cluster.

Conclusion

Your Galera cluster now runs on 5 nodes and is ready for more action. If this guide was helpful to you, kindly share it with others who may also be interested.

Tags :

https://disqus.com/?ref_noscript