Tag: MariaDB

MariaDB-10.1 Galera Cluster on CentOS 7

Some times back, i posted two articles on MariaDB Master-Master replication and MariaDB Master-Slave replication. Well, after several requests from friends, i was asked to blog on MariaDB Galera Cluster. MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is available on Linux only, and only supports the XtraDB/InnoDB storage engines (although there is experimental support for MyISAM. – Mariadb.com. You can easily download Galera Cluster using the Repository Configuration tool. There is also a slight difference between oldest version of MariaDB compared to that of the 10.1 series. Recently, a question was asked on ServerFault to which i clarified same. Check it out for more information. Lets now have some funs with MariaDB Galera Clustering.

Photo credits:Mariadb.org
Photo credits:Mariadb.org

I have created 3 CentOS7 virtual machines on Virtual Box. Lets called each Server as Galera1 [192.168.1.9], Galera2 [192.168.1.10] and Galera3[192.168.1.11]. You need to edit the /etc/hosts file to allow each machine to ping each others via hostname. For testing purposes port 3306 which is by default listening on MySQL should be open. I have also disable SELinux and Firewalld as same are for testing purposes. Lets now install MariaDB Galera Cluster.

1.Edit the repository by using configuration as mentioned on the official MariaDB website.

# MariaDB 10.1 CentOS repository list - created 2015-11-08 17:34 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

2. Lets install some Pre-requisites and other interesting tools.

yum install rsync nmap lsof perl-DBI nc

3. Install MariaDB-server and start the Mariadb service

yum install MariaDB-server MariaDB-client MariaDB-compat galera socat jemalloc
service mysql start

4. 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.

5. After the normal procedure to set up the first database on galera1, you can create a database and dump it, then import the database on galera2 and galera3. 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 galera1, edit the /etc/my.cnf.d/server.cnf file on galera1 and configure it as follows. The server galera1 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 galera1. Add the following parameter under the [galera] option.

[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='192.168.1.9'
wsrep_node_name='galera1'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

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

8. You would noticed that the ports 3306 and 4567 should be listening

Screenshot from 2016-03-27 11-23-59

9. Now you would like to make galera2 joined the primary cluster i.e galera1. Simple set up the galera2 /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.

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://192.168.1.9'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.1.10'
wsrep_node_name='galera2'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

10. Once mysql is started with the command service mysql start, you would noticed that galera2 is now on replicating from cluster1. You should noticed something similar to this on your log.

Screenshot from 2016-03-27 12-33-56

11. Repeat the same procedure for galera3 by tweaking the gcomm value and modifying the wsrep_node_address etc..

If you interested in having some more ideas about the MariaDB improved security features, do check out the following link where I attended a conference on MariaDB organized by the MSCC.

Tips:

  • If  you are using the 10.0, you would need MariaDB-Galera-server package compared to 10.1 where you just need MariaDB-server.
  • There are no longer separate MariaDB Galera Cluster releases for MariaDB 10.1 and above. Simply download MariaDB (10.1 or above) and configure your cluster as normal. MariaDB-10.1 is just ‘Galera ready’.
  • If you are migrating from MariaDB-server to Galera make sure you have clean up all the residues belonging to the old versions. See http://serverfault.com/questions/646973/installing-mariadb-galera-via-yum-fails-with-no-package-mariadb-galera-server.
  • The galera package will provide you the /usr/lib64/galera/libgalera_smm.so  and several binaries for the cluster configuration. There are several cluster configuration under ws_rep see the documentation.
  • The socat package establishes two birectional byte streams and transfers data between them.
  • I would also recommend to disable SELinux on the machines.
  • When you start the mysql service, you can tail the log at /var/log/messages otherwise you can set up mysql log.
  • Use this command show status like ‘wsrep_%’; on your mysql prompt to understand the replication.
  • To know how much node in the cluster use the following command show status like ‘wsrep_cluster_size’;

MariaDB and improved security features presentation

If you have been following the MSCC – Mauritius Software Craftsmanship Community some weeks back, you would have noticed a forthcoming meetup on MariaDB and improved security features spoken by Joffrey Michaie from OceanDBA and Codarren Velvindron from the cyberstorm Mauritius. Thanks to Jochen Kirstätter (joki) founder of the MSCC who proudly sponsored the event.

mariadb

12784250_10153937032372365_1053507839_n

Joffrey at the MariaDB meet up

1915707_190175371353387_934013232802437990_n

Some craftsmans at the Meet up

1935103_190175328020058_3787078129675559662_n

Codarren explaining Glibc

12801299_190175304686727_3455294414280043217_n

Logan and me from hackers Mauritius

10399406_190175258020065_1829899531834394151_n

Codarren and me from hackers Mauritius

12718230_190175168020074_27057020749688372_n

Can you spot where am i ?

Screenshot from 2016-02-28 00-06-47

Jochen. founder of MSCC

Loading image... Loading image... Loading image... Loading image... Loading image... Loading image... Loading image...

The first part of the presentation started with Joffrey who gave a brief introduction of MariaDB and the importance of its security features. He also laid heavy emphasis on the backup concepts that DBA need to go through. What are most interesting are that there seem two additional services that are coming on from OceanDBA – Backup as a Service and DB as a service.

Other points raised up concerning the importance of backups are to start a clustering solutions, to perform analysis and several tests on the Pre-production or staging servers. Database backups also need to be tested as there can be corrupted zip files. Another interesting issue raised up is about the locking table mechanisms during backups. Other backup strategy and concept were also explained such as:

  • Cold backups – The downtime issues were raised up which according to me does not look practicable unless there is really a specific reason
  • Hot backups – Usually carried out by the MySQLDUMP utility by everyone.
  • Logical backups – Data that are usually backup as tables, views, indexes etc.. and they are mostly human readable statements. logical backups can be performed at the level of database and table.
  • A tool that is completely new to me is the mydumper which can be used to backup terabytes of data. Some interesting arguments raised up are –lock-all-tables –skip-lock-tables and –master-data
  • Binary backups – The binary backup which is the copy of the actual database structure and requires a file system or disk subsystem access. It is one the fastest method to backup and very compatible for mixed MYISAM and INNODB tables.
  • HA (High availability) as the backup – Usually used in clusters and in Galera replication. However, to ensure that there is no data loss, a SAN replication was also recommended for data centers.
  • Time delay replication – This was explained by taking an example, say a one hour delay backup based on the risk management that has been carried out.
  • The Percona tools which can be used alongside MariaDB for backup Analysis.

On the second part of the presentation, Codarren lays emphasis on the security aspects concerning MariaDB in the context of whether to use Glibc or MUSL. Glibc libraries are used on mail servers, SQL servers, forms etc.. Back to the Glibc Ghost vulnerability, an explanation was given by taking a web-based form application where a particular field when filled with malicious information can be used to make calls to Glibc library with the intention to return a specific value. To re-mediate at that situation, same was patched using the function getaddressinfo() This patch lead to another vulnerability. Since today, we can deduce that though Glibc has gone through the various patch, yet, there are more bugs that have been discovered.

A solution was thus proposed to adopt the MUSL library infrastructure. We can see that the MUSL has a clean code policy compared to Glibc. Coddarren laid emphasis on the Alpine Linux operating system which is naturally based on MUSL. The size of Alpine compared to CentOS, Ubuntu, Debian are much smaller. Other issues raised are on the Grsecurity aspect which though is not widely spread are a very important aspect to take into consideration. MUSL looks to be very promising compared to GLIBC. Another analogy is taken from the Docker technology where companies are adapting Alpine Linux in the production environment to escape Glibc.

ice_logo-5dcea9e47b780ff52f75c3c3304d54827f56211e

The third part of the presentation was continued on by Joffrey on the Galera clustering solution. An explanation is given using a schema how replication is being done at the cluster level. Several particular Database schemas were taken for example where a node with a cluster which is slow in terms of network or infrastructure issue where the other servers will have to wait for the request to reach its destinations. Other points mentioned are:

  • Split brain in Galera where human interactions are needed especially where the ratio of the number of nodes have different data from other nodes within the same cluster. 
  • The importance of having applications built-in with retrying logic.
  • Galera conflict diagnostic. For example cert.log which is used to log and monitor conflict transactions.
  • Features such as auto-commit mode.
  • Galera load balancing using Haproxy – custom monitoring on cluster size.
  • MariaDB Maxscale which operate at layer 7 persistent connection.
  • Maxadmin command line utility to list servers that are in the cluster

Jochen has also laid emphasis on future meetups and the proposal for members if they could find other suitable environments to carry out more interesting meetups in days to come. No one could deny that they have not learned anything. Indeed, the meet up was really interesting and fruitful. Some stickers were shared having the MariaDB logo which I have already pasted at the back of my Laptop 🙂

Simple Master-Master replication on MariaDB

Lets set up a simple Master-Master database replication on MariaDB. I have already posted an article on the setting up of Master-Slave database replication. You can test it on Virtual Box or Vmware labs for your own analysis and benchmark. Lets called both MariaDB Master servers as Master1 and Master2.


ice_logo-5dcea9e47b780ff52f75c3c3304d54827f56211e
Photo credits – Mariadb.org

1. Create the 2 Labs (test it on Centos7) and edit your /etc/hosts so that each server can ping each other via the hostname. On Master1, edit your /etc/my.cnf file and on the top enter the following parameter:


[mysqld]
server-id=1
log-bin=mysql-bin

2. On master1 SQL prompt, create a user for replication slave followed by flush privileges.

MariaDB [(none)]> grant replication slave on *.* to [email protected]'%' identified by 'replipassword';
MariaDB [(none)]> flush privileges;

3. Flush the tables for read-only access.


MariaDB [(none)]> flush tables with read lock;

4. At this point, if you do a show master status, you need to have a file with the mysql-bin incrementing number as well a  position number.

MariaDB [(none)]> show master status\G
File: mysql-bin.000001
Position: 612
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

5. Now, move on to the Master2 server, I assume you have already start MariaDB, setting up mysql_secure_installation, and edit the /etc/hosts file etc..

6. On Master2 edit your /etc/my.cnf file and on top enter the following parameter, after which you can restart the MariaDB service.


[mysqld]
server-id=2

7. Now on the Master1 server, do a dump of the database MySQL and import it to Master2, so that we make sure it starts and synchronize with the same conf. So, I did a dump of the MySQL database on master1 and SCP it to master2

[[email protected] yum.repos.d]# mysqldump -u root -p --database mysql > mysql.mysql
[[email protected] home]# scp mysql.mysql [email protected]:/home

8. On Master2 import the MySQL database


[[email protected] home]# mysql mysql -u root -p <  mysql.mysql 

9. Now, we will temporarily run Master2 as the slave.

MariaDB [(none)]> change master to master_host='master1', master_user='repli', master_password='replipassword', master_log_file='mysql-bin.000001', master_log_pos=612;
MariaDB [(none)]> start slave;

10. At this point, you would notice that the Slave is running correctly by verifying with a show slave status\G. Do check the Slave_IO_Running, Master_User, Log_Pos and the Slave_SQL_Running parameters.

11. Now that Master1 is the master and Master2 is the slave, we will perform the reverse order to reach our goal. At this level, you can unlock the tables which we did at step3. On master1 fire this command.

unlock table

12. On Master2 (which is actually a slave), re-edit my.cnf file and under the [mysqld] and server-id add the following parameter. It should look like this


[mysqld]
server-id=2
log-bin=mysql-bin

13. On Master2 restart MariaDB with the command systemctl restart mariadb and logging to Mysql and do a show master status. The result should be similar to this.

MariaDB [(none)]> show master status\G
File: mysql-bin.000001
Position: 313
Binlog_Do_DB:
Binlog_Ignore_DB: 

14. On Master1, a show slave status will prompt you an empty set. Now since we have dumped the MySQL database to Master2, we can now just run Master1 as the slave, i.e to repeat the process as in step 9. Launch this command on Master1 to create it as a slave. The File and Position should be taken from the Master2.

MariaDB [(none)]> change master to master_host='master2', master_user='repli', master_password='replipassword', master_log_file='mysql-bin.000001', master_log_pos=313;
MariaDB [(none)]> start slave;

15.  Test by creating database and tables on both servers, it will replicate on each other.

Tips:

    • Each time you edit my.cnf file, you need to restart MariaDB so that the conf take effect.
    • After having created the Master-Slave replication at step 10, you can check on both Master1/2 with a netstat -ntpla | egrep -i established.*mysql. You would notice the established connection and the port it’s listening.
    • The command host with the IP established will also confirm its from the specific host.
    • Further testing on Master1 can also be done with command as mysql -u root -p -e “show processlist” which will give you an indication of the state of the server.
  • The value of File and Position of Master1 when using the command show master status should correspond with the values of Master_Log_File and Read_Master_Log_Pos of Master2 with the command show slave status and vice versa.

Simple Master-Slave replication on MariaDB

This article will explain all the necessary configurations to set up a simple Master-Slave replication on MariaDB. I have tested it on CentOS7. More articles on MariaDB optimization, Master-Master replication, and Galera cluster are coming up soon. Well, I was introduced to MariaDB in the year 2014 by Joffrey Michaie of SkySQL at Flying Dodo video conference room, Bagatelle, Mauritius during a LUGM [Linux User Group of Mauritius] meet up. You can still view out the video on the LUGM Youtube Channel.


What is MariaDB? “MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. Being a fork of a leading open source software system, it is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle. ” – Wikipedia. 


ice_logo-5dcea9e47b780ff52f75c3c3304d54827f56211e
Photo Credits – Mariadb.com

I have set up 2 Centos 7 Labs; one as Master and the other as Slave.

You will need to install MariaDB on both servers to start with.

Make sure port 3306 is opened to enable replication.


Since the test is based on 2 Virtual Box labs, I have temporarily disabled IPtables.

At the time I am writing this article, the latest version is 10.1.8-stable.

You can easily download MariaDB through the repository configuration tool

1. After having installed a fresh MariaDB on both virtual machines – Master / Slave, you will need to configure the root access to MySQL by using the following command. It will prompt you to enter a password which is a blank password by default. Then, you will have to enter a password for root which will prompt you to remove anonymous users, disallow root login, remove the test database and access to it and reloading privilege tables. Just press ‘Y’ by following the command below:


mysql_secure_installation

2. After setting up the root user of MySQL, you can test it with a fake or no password to ensure the root password has been set up effectively. Next step, is to create a database with some tables in it or simply import a database. I created a table in the database called ’employees’ on the Master.

create database employees;
use employees;
create table profile (name char(30), age int(2), address varchar(40)) ;

3.  So, a database has been created on the Master. We will now edit the /etc/my.cnf on the master and under the [mysqld] enter the following:


server-id=1
log-bin = mysql-bin

4. Next step, is to create a user for replication purpose to the slave server (The command need to be launched on the master server). The IP 192.168.1.8 is the slave server. Once the grant replication is launched the user repliuser will be created automatically. Afterward, launch the command flush privileges so that the server noticed these changes to load the grant tables into memory.

MariaDB [(none)]>

grant replication slave on *.* to [email protected]'192.168.1.8' identified by 'replipassword';

MariaDB [(none)]>

flush privileges;

5. At this point, if you launch a show master status; on the master server you will have a prompt “Empty set“. To enable the master, we need to restart MariaDB. As we want to replicate this master database on the slave server, we need to stop mysql to continue writing on the table.

MariaDB [(none)]>

flush tables with read lock;
systemctl restart mariadb

6. Connect back to MariaDB and launched a show master status\G You will need to find a result similar to this screenshot.

Screenshot from 2015-11-03 20:18:21

7. We now have a file name and a position. These two values will be used to set up the slave database. Do a MySQL dump on the master database and import it to the slave server (I copied it from master to slave on the /home folder).

On the Master server:

[[email protected] ~]#


mysqldump -u root -p employees  > employees.sql

[[email protected] ~]#

scp employees.sql [email protected]:/home

8. Now, we log in to the slave server. Create database employees just as in the master server and import the database employees to the slave database server.

[[email protected] home]#

mysql -u root -p employees < employees.sql

9. Edit the /etc/my.cnf in the slave server and enter the following. Note the value of the server-id should be greater than that of the master server, otherwise, it may not work. Then restart MariaDB on the slave server.

server-id = 2
systemctl restart mariadb

10. Right now, the slave server cannot identify the master-slave to synchronize the database for replication.

On the master server:

unlock tables;

On the slave server:

MariaDB [(none)]>


change master to master_host='master', master_user='repliuser', master_password='replipassword', master_log_file='mysql-bin.000001', master_log_pos=313;

11. You can now start the slave replication. Afterward, you can type a show slave status\G;

start slave;
Screenshot from 2015-11-03 21:04:07
show slave status\G indicating replication on the slave server

Tips:

    • To find the list of services for which specific ports can be opened, use the command firewall-cmd –get-services. Hence, you can add the MySQL service with the command firewall-cmd –permanent –add-service-mysql. After adding the MySQL service you will need to reload the firewall service with the command firewall-cmd reload. To verify if certain rules have been loaded in the firewall use the command firewall-cmd –list-all
    • To check which database you have, you can fire the command show schemas;


  • At step 9, If you want to replicate a specific database on the slave server, you can use the following parameter in the /etc/my.cnf in the slave under [mysqld] use replicate-wild-do-table=employees.% where the .% means all tables under the database employees.
  • Since you will be on Virtual Box or Vmware, you may need to edit your /etc/hosts so that each server recognized each other as the Master_User and Slave_User respectively. In this case, in the /etc/hosts for the master enter the IP of the slave server followed by the hostname of the slave and vice versa. Test it with a ping. In my case, if I ping master from the slave, it will answer promptly.

Hello Tunnelers

Hello, Tunnelers across the globe. I made this blog to share my experience and knowledge as a System and Application Administrator. Most articles are based on real-life experience in the field of Linux, FreeBSD and Open source technologies. However, additional tests are usually made to support my blog posts and I welcome constructive comments from you to enlighten me if needed.

Fellow Tunnelers, the Tunnelix is a concept that has inspired me to bridge Linux and Unix Operating systems tunneling through the hacking world. Do follow me on Twitter and join the adventure throughout the Tunnel.

linux-bsd-840x420

My website has been made using technologies like Nginx, HHVM, WordPress, CentOS, PHP, JQuery, MariaDB and others. I made some penetration testing using Kali Linux tools, Apache Benchmark and other online testing tools such as GTmetrix. You can follow my tweets to keep in touch with me. Your comments are welcome and I am also reachable on Facebook. Most blog posts will be based on the technical aspects of IT though sometimes I will blog about my own IT Management skills that I have encountered. Sharing is the key to success. Technology always keeps on evolving and just as other blogs, old posts are sometimes void. I will try my best to keep all my blog posts up-to-date.