Convert CSV to JSON using BASH

I have been assigned a task to generate random data in JSON format. I do have a big data set ready in CSV (comma separated values) and would love to convert it to JSON just using BASH. You can copy following codes and save it as a executable script file.

#!/bin/bash
# CSV to JSON converter using BASH
# Usage ./csv2json input.csv > output.json
 
input=$1
 
[ -z $1 ] && echo "No CSV input file specified" && exit 1
[ ! -e $input ] && echo "Unable to locate $1" && exit 1
 
read first_line < $input
a=0
headings=`echo $first_line | awk -F, {'print NF'}`
lines=`cat $input | wc -l`
while [ $a -lt $headings ]
do
        head_array[$a]=$(echo $first_line | awk -v x=$(($a + 1)) -F"," '{print $x}')
        a=$(($a+1))
done
 
c=0
echo "{"
while [ $c -lt $lines ]
do
        read each_line
        if [ $c -ne 0 ]; then
                d=0
                echo -n "{"
                while [ $d -lt $headings ]
                do
                        each_element=$(echo $each_line | awk -v y=$(($d + 1)) -F"," '{print $y}')
                        if [ $d -ne $(($headings-1)) ]; then
                                echo -n ${head_array[$d]}":"$each_element","
                        else
                                echo -n ${head_array[$d]}":"$each_element
                        fi
                        d=$(($d+1))
                done
                if [ $c -eq $(($lines-1)) ]; then
                        echo "}"
                else
                        echo "},"
                fi
        fi
        c=$(($c+1))
done < $input
echo "}"

To perform the conversion, run the script with first argument is the CSV file that you want to convert to and redirect the output to an output file. Make sure the CSV file contains field names as the header, similar to example below:

name,modified,social_security
"Farrah Walters","208-72-8449","1386670785"
"Shay Warner","539-53-2690","1386644172"
"Maxine Norton","231-61-5065","1386658663"

Hope this could help others out there! You can download the script here.

Importing Big mysqldump with Progress Bar

I have been facing hard time to import a big dump file (25 GB in size to be exact). It could take hours to load data in so it could be worth to watch the progress of this import. Fortunately, Linux has always had a convenient way to do this. I just need to install pv,  which can monitor the progress of piping data.

Enough with talking, let’s start installing!

I am using CentOS 6.4 box, so it requires me to install EPEL repo at the first place:

$ rpm -Uhv http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

Install pv using yum:

$ yum install -y pv

Installation done. Let the importing begin!

$ pv /home/user/my_big_dump.sql | mysql -uroot -p
928MB 0:07:41 [ 2.3MB/s] [==>                                                      ]  4% ETA 2:35:52

 

So now I can sit back and relax while watching up the import progress. If no error occurred, the import could finish within two and a half hours. Cheers!

Converting Magento to Work Well on Galera Cluster

I have a Magento data set which run on MySQL-wsrep with Galera. Galera has its known limitations, and one of it is:

DELETE operation is unsupported on tables without primary key. Also rows in tables without primary key may appear in different order on different nodes. Don’t use tables without primary key.

Basically, if you want to have your DB serve by Galera cluster, please use InnoDB storage engine and define a primary key for each table. That’s all. Since Magento dataset is unaware of this limitation, you could see that there are many tables do not meet the criteria.

You can use following query to identify unsupported stuffs in Galera (Thanks to Giuseppe Maxia for this):

SELECT DISTINCT Concat(t.table_schema, '.', t.table_name)     AS tbl,
                t.engine,
                IF(Isnull(c.constraint_name), 'NOPK', '')     AS nopk,
                IF(s.index_type = 'FULLTEXT', 'FULLTEXT', '') AS ftidx,
                IF(s.index_type = 'SPATIAL', 'SPATIAL', '')   AS gisidx
FROM   information_schema.tables AS t
       LEFT JOIN information_schema.key_column_usage AS c
              ON ( t.table_schema = c.constraint_schema
                   AND t.table_name = c.table_name
                   AND c.constraint_name = 'PRIMARY' )
       LEFT JOIN information_schema.statistics AS s
              ON ( t.table_schema = s.table_schema
                   AND t.table_name = s.table_name
                   AND s.index_type IN ( 'FULLTEXT', 'SPATIAL' ) )
WHERE  t.table_schema NOT IN ( 'information_schema', 'performance_schema','mysql' )
       AND t.table_type = 'BASE TABLE'
       AND ( t.engine <> 'InnoDB'
              OR c.constraint_name IS NULL
              OR s.index_type IN ( 'FULLTEXT', 'SPATIAL' ) )
ORDER  BY t.table_schema,
          t.table_name;

Example:

mysql> SELECT DISTINCT
    ->        CONCAT(t.table_schema,'.',t.table_name) as tbl,
    ->        t.engine,
    ->        IF(ISNULL(c.constraint_name),'NOPK','') AS nopk,
    ->        IF(s.index_type = 'FULLTEXT','FULLTEXT','') as ftidx,
    ->        IF(s.index_type = 'SPATIAL','SPATIAL','') as gisidx
    ->   FROM information_schema.tables AS t
    ->   LEFT JOIN information_schema.key_column_usage AS c
    ->     ON (t.table_schema = c.constraint_schema AND t.table_name = c.table_name
    ->         AND c.constraint_name = 'PRIMARY')
    ->   LEFT JOIN information_schema.statistics AS s
    ->     ON (t.table_schema = s.table_schema AND t.table_name = s.table_name
    ->         AND s.index_type IN ('FULLTEXT','SPATIAL'))
    ->   WHERE t.table_schema NOT IN ('information_schema','performance_schema','mysql')
    ->     AND t.table_type = 'BASE TABLE'
    ->     AND (t.engine <> 'InnoDB' OR c.constraint_name IS NULL OR s.index_type IN ('FULLTEXT','SPATIAL'))
    ->   ORDER BY t.table_schema,t.table_name;
+-------------------------------------------------+--------+------+----------+--------+
| tbl                                             | engine | nopk | ftidx    | gisidx |
+-------------------------------------------------+--------+------+----------+--------+
| magento.api2_acl_user                           | InnoDB | NOPK |          |        |
| magento.api_session                             | InnoDB | NOPK |          |        |
| magento.catalogsearch_fulltext                  | MyISAM |      | FULLTEXT |        |
| magento.catalog_category_anc_categs_index_idx   | InnoDB | NOPK |          |        |
| magento.catalog_category_anc_categs_index_tmp   | InnoDB | NOPK |          |        |
| magento.catalog_category_anc_products_index_idx | InnoDB | NOPK |          |        |
| magento.catalog_category_anc_products_index_tmp | InnoDB | NOPK |          |        |
| magento.catalog_category_product_index_enbl_idx | InnoDB | NOPK |          |        |
| magento.catalog_category_product_index_enbl_tmp | InnoDB | NOPK |          |        |
| magento.catalog_category_product_index_idx      | InnoDB | NOPK |          |        |
| magento.catalog_category_product_index_tmp      | InnoDB | NOPK |          |        |
| magento.catalog_product_index_price_downlod_tmp | MEMORY |      |          |        |
| magento.oauth_nonce                             | MyISAM | NOPK |          |        |
| magento.weee_discount                           | InnoDB | NOPK |          |        |
| magento.widget_instance_page_layout             | InnoDB | NOPK |          |        |
| magento.xmlconnect_config_data                  | InnoDB | NOPK |          |        |
+-------------------------------------------------+--------+------+----------+--------+

 

I do not know much about Magento data set and structure. So I am assuming that the output above can simply bring future problems according to Galera limitation, so it might be good to comply with that and alter whatever necessary on those tables.

So I start by adding a simple auto increment primary key into tables labeled as NOPK:

mysql> ALTER TABLE magento.api2_acl_user ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;
mysql> ALTER TABLE magento.api_session ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;
mysql> ALTER TABLE magento.weee_discount ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;
mysql> ALTER TABLE magento.widget_instance_page_layout ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;
mysql> ALTER TABLE magento.xmlconnect_config_data ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;

Next, add primary key and convert the storage as engine to InnoDB:

mysql> ALTER TABLE magento.oauth_nonce ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST, ENGINE='InnoDB';

Then, remove the full-text indexing and convert the storage engine to InnoDB:

mysql> ALTER TABLE magento.catalogsearch_fulltext DROP INDEX FTI_CATALOGSEARCH_FULLTEXT_DATA_INDEX;
mysql> ALTER TABLE magento.catalogsearch_fulltext ENGINE='InnoDB';

I am quite sure the above drop indexes statement would likely to have some performance hit. Maybe Magento does not really fit in Galera multi-master environment, but it is worth to give it a try. I will keep updating this post to share about this.

Am going to sleep now. Cheers!

Importing IMDb Sample Data Set to MySQL

Here in this post, I am going to show you on how to import IMDb plain text data files available at this page into your MySQL database server. I was using this data set to perform various benchmark tests around a moderately-large database size. If you want to have a small MySQL database sample data, you can try to use Sakila or World database available at MySQL Documentation Page.

I will be using CentOS 6.3 64bit as the OS platform and presume that MySQL has already installed and running. Make sure that you are having at least 4 GB of free space in the partition that MySQL @@datadir reside to allow this long-running process (3 to 5 hours – depending on your hardware specs + server workload) to successfully complete.

We will be using a Python-based package called IMDbPy. This package requires you to have Python with some development libraries installed as well as SQLObject. Then, we will need to download all data files from IMDb mirror site: ftp://ftp.fu-berlin.de/pub/misc/movies/database/, create the database and start the import process.

 

Installing IMDbPy and SQLObject

1. Install required packages using yum:

$ yum install -y gcc python python-devel openssl-devel libxml2-devel libxslt-devel zlib-devel MySQL-python python-setuptools python-pip

2. Install SQLObject using Python EasyInstall:

$ easy_install -U SQLObject

3. Download IMDbPy from this page into the MySQL server, extract it and start the installation process:

$ wget http://prdownloads.sourceforge.net/imdbpy/IMDbPY-4.9.tar.gz
$ tar -xzf IMDbPY-4.9.tar.gz
$ cd IMDbPY-*
$ python setup.py install

 

Importing Data

1. Create a directory to dump all the data files that we will download:

$ mkdir /root/data
$ cd /root/data

2. Download only .gz file from the IMDb mirror site to /root/data :

$ wget -r --accept="*.gz" --no-directories --no-host-directories --level 1 ftp://ftp.fu-berlin.de/pub/misc/movies/database/

3. Create a database in MySQL called ‘imdb’, with user ‘imdb’ and password ‘imdb’. We will then GRANT the user to the designated database:

mysql> CREATE DATABASE imdb;
mysql> GRANT ALL PRIVILEGES ON imdb.* TO 'imdb'@'localhost' IDENTIFIED BY 'imdb';
mysql> FLUSH PRIVILEGES;

3. Start the import process with -u and -d flag:

$ imdbpy2sql.py -d /root/data/ -u 'mysql://imdb:[email protected]/imdb'

Take note that -d is the directory of the .gz dump files are located and -u is the connection string for our MySQL database server. You can change the connection string to any of SQLObject’s supported database such as PostgreSQL, SQLite, Firebird and MAX DB. Please refer to this documentation for details.

You will see similar output as below which indicates the importing process has started:

SCANNING movies: Last Sunset (2006) (movieID: 2130001)
SCANNING movies: Legend of Hell (2012) (movieID: 2140001)
SCANNING movies: Lifestyles of Squirrels (2011) (movieID: 2150001)
SCANNING movies: Los signos del tiempo (1983) (movieID: 2160001)
SCANNING movies: Madame T (2012) (movieID: 2170001)
SCANNING movies: Marijji ringu (2007) (movieID: 2180001)
SCANNING movies: Menculik miyabi (2010) (movieID: 2190001)
* FLUSHING MoviesCache...

Wait up until it finish and you will have large sample data to play around in your MySQL server!

 

Ubuntu: Error Installing MySQL Server

I encountered following error when trying to upgrade MySQL server in Ubuntu 12.04:

dpkg: error processing mysql-server-5.5 (--configure):
 subprocess installed post-installation script returned error exit status 1
No apport report written because MaxReports is reached already
                                                              Setting up mysql-client (5.5.22-0ubuntu1) ...
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-server-5.5; however:
  Package mysql-server-5.5 is not configured yet.
dpkg: error processing mysql-server (--configure):
 dependency problems - leaving unconfigured
No apport report written because MaxReports is reached already
                                                              Processing triggers for libc-bin ...
ldconfig deferred processing now taking place
Errors were encountered while processing:
 mysql-server-5.5
 mysql-server
E: Sub-process /usr/bin/dpkg returned an error code (1)
A package failed to install.  Trying to recover:
Setting up mysql-server-5.5 (5.5.22-0ubuntu1) ...
start: Job failed to start
invoke-rc.d: initscript mysql, action "start" failed.
dpkg: error processing mysql-server-5.5 (--configure):
 subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-server-5.5; however:
  Package mysql-server-5.5 is not configured yet.
dpkg: error processing mysql-server (--configure):
 dependency problems - leaving unconfigured
Errors were encountered while processing:
 mysql-server-5.5
 mysql-server

The easiest way to overcome this is by removing all mysql related packages in the server:

$ sudo apt-get purge mysql*

Then, try again to reinstall the mysql-server package as follow:

$ sudo apt-get install -y  mysql-client mysql-server

Make sure to backup your data files before performing the upgrade! Hope this workaround will help others!

Install MariaDB Galera Cluster in Ubuntu

I am going to show you on how to install MariaDB Cluster (with Galera) in Ubuntu Precise (12.04) LTS 64bit. You need at least 3 hosts running together to form a reliable cluster.

Hosts list:

maria1 – 192.168.10.101
maria2 – 192.168.10.102
maria3 – 192.168.10.103

 

Prepare Hosts

In all hosts, this is my /etc/hosts definition:

192.168.10.101        maria1 maria1.mycluster.com
192.168.10.102        maria2 maria2.mycluster.com
192.168.10.103        maria3 maria3.mycluster.com

 

Install MariaDB Cluster

1. Install the GPG key required by apt:

$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 1BB943DB

2. Add the apt repository:

$ sudo add-apt-repository 'deb http://mirror.stshosting.co.uk/mariadb/repo/5.5/ubuntu precise main'

3. Get the latest repo:

$ sudo apt-get update

4. Install MariaDB Cluster and related packages:

$ sudo apt-get install -y galera  mariadb-galera-server-5.5 mariadb-client-5.5 libmariadbclient18 mariadb-client-core-5.5 rsync netcat-openbsd

** Make sure you put the same MySQL root password on all hosts

 

Install Percona Xtrabackup

1. We are going to use Xtrabackup to perform State Snapshot Transfer (SST). Install GPG key for Percona:

$ sudo apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

2. Add the apt repository for Percona:

$ sudo add-apt-repository 'deb http://repo.percona.com/apt precise  main'

3. Update and install Percona Xtrabackup:

$ sudo apt-get update && sudo apt-get -y install percona-toolkit percona-xtrabackup

 

Configure MariaDB Cluster

1. Once the installation completed, you need to comment following lines in /etc/mysql/my.cnf:

#bind-address
#default_storage_engine
#query_cache_limit
#query_cache_size

2. Add following lines for wsrep configuration options in /etc/mysql/my.cnf under [mysqld] directive:

binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1

3. Add following lines into specific node under [mysqld] directive:

maria1:

# wsrep provider configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="maria1"
wsrep_node_name="maria1"
wsrep_sst_method=xtrabackup
wsrep_sst_auth="root:MyR00tPasswd"
wsrep_node_incoming_address=192.168.10.101
wsrep_sst_receive_address=192.168.10.101
wsrep_slave_threads=16

maria2:

# wsrep provider configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://maria1
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="maria2"
wsrep_node_name="maria2"
wsrep_sst_method=xtrabackup
wsrep_sst_auth="root:MyR00tPasswd"
wsrep_node_incoming_address=192.168.10.102
wsrep_sst_receive_address=192.168.10.102
wsrep_slave_threads=16

maria3:

# wsrep provider configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://maria1
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="maria3"
wsrep_node_name="maria3"
wsrep_sst_method=xtrabackup
wsrep_sst_auth="root:MyR00tPasswd"
wsrep_node_incoming_address=192.168.10.103
wsrep_sst_receive_address=192.168.10.103
wsrep_slave_threads=16

 

Initialize the Cluster

1. Login to maria1 to initialize the cluster by restarting the service:

$ sudo service mysql restart

2. Login to maria2 and maria3 and restart MySQL. This will allow maria2 and maria3 to perform SST from maria1:

$ sudo service mysql restart

3. Login to MariaDB console and check for following values:

MariaDB [(none)]> show status like 'wsrep_%';

And you should see following output:

| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_incoming_addresses | 192.168.10.101:3306,192.168.10.102:3306,192.168.10.103:3306 |
| wsrep_cluster_size | 3 |
| wsrep_ready | ON |

You could now point your web server or application server to one of MariaDB cluster node to query.

 

Notes

Once the cluster successfully initialized and connected, you should change the wsrep_cluster_address to other nodes in case if the server rebooted. So, login to respective server and change the wsrep_cluster_address to following value:

wsrep_cluster_address=gcomm://maria1,maria2,maria3

Install MySQL Cluster in Debian

MySQL Cluster is different compare to normal MySQL server. It has 3 roles:

  • management
  • data
  • SQL or API

Data node will required a lot of memory utilization. It is recommended for these nodes to not share any workload with SQL or management nodes as it would end up with resources exhaustion. So we will setup SQL node together with management node to reduce number of servers used to 4 (instead of 6 – 3 roles x 2 servers).

To have the best minimum setup, we will setup 2 servers as 2 data nodes, and another 2 servers will be SQL nodes co-located with management nodes:

  • sql1 (192.168.10.101) – sql node #1 + management node #1
  • sql2 (192.168.10.102) – sql node #2 + management node #2
  • data1 (192.168.10.103) – data node #1
  • data2 (192.168.10.104) – data node #2

I am using Debian 6.0.7 Squeeze 64bit.

 

All Nodes

1. Install libaio-dev which required by MySQL Cluster:

$ apt-get update && apt-get install libaio-dev -y

2. Download the package from MySQL Downloads page here and extract it under /usr/local directory:

$ cd /usr/localwget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/mysql-cluster-gpl-7.2.12-linux2.6-x86_64.tar.gz/from/http://cdn.mysql.com/
$ tar -xzf mysql-cluster-gpl-7.2.12-linux2.6-x86_64.tar.gz

3. Rename the extracted directory to a shorter name which is mysql:

$ mv mysql-cluster-gpl-7.2.12-linux2.6-x86_64 mysql

4. Create MySQL configuration directory:

$ mkdir /etc/mysql

5. Export MySQL bin path into user environment:

$ export PATH="$PATH:/usr/local/mysql/bin"

6. Create mysql user and group and assign the correct permission to MySQL base directory:

$ useradd mysql
$ chown -R mysql:mysql /usr/local/mysql

Once completed, make sure that MySQL path is /usr/local/mysql with correct ownership of user mysql.

Data Nodes

1. Login to data1 and data2 create MySQL configuration file at /etc/mysql/my.cnf and add following lines:

[mysqld]
ndbcluster
ndb-connectstring=sql1,sql2
 
[mysql_cluster]
ndb-connectstring=sql1,sql2

2. Create the MySQL cluster data directory:

$ mkdir /usr/local/mysql/mysql-cluster

SQL Nodes + Management Nodes

1. Login to sql1 and sql2 and configure SQL nodes by adding following lines into /etc/mysql/my.cnf:

[mysqld]
ndbcluster
ndb-connectstring=sql1,sql2
port=3306
default_storage_engine=ndbcluster
 
[mysql_cluster]
ndb-connectstring=sql1,sql2

2. Create data directory for MySQL cluster:

$ mkdir -p /usr/local/mysql/mysql-cluster

3. Create MySQL Cluster configuration file at /etc/mysql/config.ini:

$ vim /etc/mysql/config.ini

And add following lines:

[ndb_mgmd default]
DataDir=/usr/local/mysql/mysql-cluster
 
[ndb_mgmd]
NodeId=1
HostName=sql1
 
[ndb_mgmd]
NodeId=2
HostName=sql2
 
[ndbd default]
NoOfReplicas=2
DataMemory=256M
IndexMemory=128M
DataDir=/usr/local/mysql/mysql-cluster
 
[ndbd]
NodeId=3
HostName=data1
 
[ndbd]
NodeId=4
HostName=data2
 
[mysqld]
[mysqld]

4. Copy the mysql.server init script from support-files directory into /etc/init.d directory and setup auto-start on boot:

$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
$ update-rc.d mysql defaults

5. Install MySQL system tables:

$ cd /usr/local/mysql
$ scripts/mysql_install_db --user=mysql

Starting the Cluster

1. Start MySQL cluster management service in sql1 and sql2:

For sql1:

$ ndb_mgmd -f /etc/mysql/config.ini --ndb-nodeid=1

For sql2:

$ ndb_mgmd -f /etc/mysql/config.ini --ndb-nodeid=2

2. Start MySQL cluster storage service:

For data1:

$ ndbd --ndb-nodeid=3

For data2:

$ ndbd --ndb-nodeid=4

3. Start the MySQL API service at sql1 and sql2:

$ service mysql start

4. Check the MySQL cluster status in sql1 or sql2:

$ ndb_mgm -e show
Connected to Management Server at: ndb1:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @192.168.197.10.103(mysql-5.5.30 ndb-7.2.12, Nodegroup: 0, Master)
id=4 @192.168.197.10.104 (mysql-5.5.30 ndb-7.2.12, Nodegroup: 0)
 
[ndb_mgmd(MGM)] 2 node(s)
id=1 @192.168.197.10.101 (mysql-5.5.30 ndb-7.2.12)
id=2 @192.168.197.10.102 (mysql-5.5.30 ndb-7.2.12)
 
[mysqld(API)] 2 node(s)
id=5 @192.168.197.10.101 (mysql-5.5.30 ndb-7.2.12)
id=6 @192.168.197.10.102 (mysql-5.5.30 ndb-7.2.12)

5. Since MySQL user table do not run on ndb storage enginer, we need to create MySQL root password in both nodes (sql1 and sql2):

$ mysqladmin -u root password 'r00tP4ssword'

Cluster ready! Now you can start to query the MySQL cluster by connecting to sql1 or sql2. For best performance in load balancing and failover, you can setup HAproxy in front of sql1 or sql2.

Customize and Disable PHPmyAdmin ‘Export’ Menu

In my development environment, we have 2 levels of PHPmyAdmin user, the superuser (root) and developer user. Superuser is able to access all features available in PHPmyAdmin and developer user is the database user for database planet_shop which have limitation as stated in MySQL user privilege table.

The current problem is that developer user, which use PHPmyAdmin to access and manage the database, is also able to export the database using PHPmyAdmin export menu as screenshot below:

pma_export

My boss want this menu to be hide and disabled to developer to prevent them dump the MySQL data which is strictly confidential. This feature should only accessible for superuser only. To do this, I need to do some changes to PHPmyAdmin coding which is located under /var/www/html/phpmyadmin directory in my web server. I am using following variables:

OS: CentOS 6 64bit
PHPmyAdmin web directory: /var/www/html/phpmyadmin
PHPmyAdmin version: 3.4.3.2 (inside README)

1. We need to hide Export menu to be viewed from 2 places, in libraries/server_links.inc.php and libraries/db_links.inc.php. Open /var/www/html/phpmyadmin/libraries/server_links.inc.php using text editor and find following line (line 67):

$tabs['export']['icon'] = 'b_export.png';
$tabs['export']['link'] = 'server_export.php';
$tabs['export']['text'] = __('Export');

and change it to:

if ($is_superuser) {
    $tabs['export']['icon'] = 'b_export.png';
    $tabs['export']['link'] = 'server_export.php';
    $tabs['export']['text'] = __('Export');
}

2. Then, we need to hide Export menu from database page. Open /var/www/html/phpmyadmin/libraries/db_links.inc.php using text editor and find following line (line 107):

$tabs = array();
$tabs[] =& $tab_structure;
$tabs[] =& $tab_sql;
$tabs[] =& $tab_search;
$tabs[] =& $tab_qbe;
$tabs[] =& $tab_export;

and change it to:

$tabs = array();
$tabs[] =& $tab_structure;
$tabs[] =& $tab_sql;
$tabs[] =& $tab_search;
$tabs[] =& $tab_qbe;
if ($is_superuser) {
    $tabs[] =& $tab_export;
}

3. The first 2 steps were only hiding the Export tab from PHPmyAdmin for non superuser. Now we need to disable it as well in database page. Open /var/www/html/phpmyadmin/db_export.php using text editor and find following line:

// $sub_part is also used in db_info.inc.php to see if we are coming from
// db_export.php, in which case we don't obey $cfg['MaxTableList']
$sub_part = '_export';
require_once './libraries/db_common.inc.php';
$url_query .= '&goto=db_export.php';
require_once './libraries/db_info.inc.php';

And add following line after that:

if (!$is_superuser) {
    require './libraries/server_links.inc.php';
    echo '<h2>' . "\n"
       . PMA_getIcon('b_usrlist.png')
       . __('Privileges') . "\n"
       . '</h2>' . "\n";
    PMA_Message::error(__('No Privileges'))->display();
    require './libraries/footer.inc.php';
}

4. We also need to disable this in server page. Open /var/www/html/phpmyadmin/server_export.php using text editor and find following line:

/**
* Does the common work
*/
require_once './libraries/common.inc.php';
 
$GLOBALS['js_include'][] = 'export.js';

And add following line after that:

if (!$is_superuser) {
    require './libraries/server_links.inc.php';
    echo '<h2>' . "\n"
       . PMA_getIcon('b_usrlist.png')
       . __('Privileges') . "\n"
       . '</h2>' . "\n";
    PMA_Message::error(__('No Privileges'))->display();
    require './libraries/footer.inc.php';
}

 

Done. Now we can verify in PHPmyAdmin by login as the developer and you will notice that Export menu has been hide:

pma_hide

 

If user still access the Export page using direct URL, for example: http://192.168.0.100/phpmyadmin/server_export.php , they will see following error:

pma_nopriv

 

High Availability: cPanel with MySQL Cluster, Keepalived and HAProxy

I have successfully installed and integrate MySQL Cluster with HAproxy and Keepalived to provide scalable MySQL service with cPanel server run on CentOS 6.3 64bit. As you guys know that cPanel has a function called “Setup Remote MySQL server” which we can use to remotely access and control MySQL server from cPanel.

This will bring a big advantage, because the cPanel server load will be reduced tremendously due to mysqld service and resource will be serve from a cluster of servers. Following picture shows my architecture:

cpanel_cluster

I will be using following variables:

OS: CentOS 6.3 64bit
WHM/cPanel version: 11.34.0 (build 11)
MySQL root password: MhGGs4wYs

The Tricks

  • We will need to use same MySQL root password in all servers including the cPanel server
  • cPanel server’s SSH key need to be installed in all database servers to allow passwordless SSH login
  • All servers must have same /etc/hosts value
  • At least 4 servers for MySQL Cluster ( 2 SQL/Management/LB and 2 Data nodes)
  • mysql1 (active) and mysql2 (passive) will share a virtual IP which run on Keepalived
  • mysql1 and mysql2 will be the load balancer as well, which redirect MySQL traffic from cPanel server to mysql1 and mysql2
  • MySQL Cluster only support ndbcluster storage engine. Databases will be created in ndbcluster by default
  • For mysql1 and mysql2, MySQL will serve using port 3307 because 3306 will be used by HAProxy for load balancing

All Servers

1. In this post, I am going to turn off firewall and SELINUX for all servers:

$ service iptables stop
$ chkconfig iptables off
$ setenforce 0sed -i.bak 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config

2. Install ntp using yum to make sure all servers’ time is in sync:

$ yum install ntp -y
$ ntpdate -u my.pool.ntp.org

Prepare the cPanel Server

1. Lets start declaring all hosts naming in /etc/hosts:

192.168.10.100     cpanel      cpanel.mydomain.com
192.168.10.101     mysql1      mysql1.mydomain.com
192.168.10.102     mysql2      mysql2.mydomain.com
192.168.10.103     mysql-data1 mysql-data1.mydomain.com
192.168.10.104     mysql-data2 mysql-data2.mydomain.com
192.168.10.110     mysql       mysql.mydomain.com    #Virtual IP for mysql service

2. Copy /etc/hosts file to other servers:

$ scp /etc/hosts mysql1:/etc
$ scp /etc/hosts mysql2:/etc
$ scp /etc/hosts mysql-data1:/etc
$ scp /etc/hosts mysql-data2:/etc

3. Setup SSH key. This will allow passwordless SSH between cPanel server and MySQL servers:

$ ssh-keygen -t dsa

Just press ‘Enter’ for all prompts.

4. Copy the SSH key to other servers:

$ ssh-copy-id -i ~/.ssh/id_dsa root@mysql1
$ ssh-copy-id -i ~/.ssh/id_dsa root@mysql2
$ ssh-copy-id -i ~/.ssh/id_dsa root@mysql-data1
$ ssh-copy-id -i ~/.ssh/id_dsa root@mysql-data2

5. Setup MySQL root password in WHM. Login to WHM > SQL Services > MySQL Root Password. Enter the MySQL root password and click “Change Password”.

6. Add additional host in WHM > SQL Services > Additional MySQL Access Hosts and add required host to be allowed to access the MySQL cluster as below:

add_host

 

 

Data Nodes (mysql-data1 and mysql-data2)

1. Download and install MySQL storage package from this page:

$ cd /usr/local/src
$ wget http://mirror.services.wisc.edu/mysql/Downloads/MySQL-Cluster-7.1/MySQL-Cluster-gpl-storage-7.1.25-1.el6.x86_64.rpm
$ rpm -Uhv MySQL-Cluster-gpl-storage-7.1.25-1.el6.x86_64.rpm

2. Create a mysql configuration file at /etc/my.cnf and add following line. This configuration will tell the storage to communicate with mysql1 and mysql2 as the management nodes:

[mysqld]
ndbcluster
ndb-connectstring=mysql1,mysql2
 
[mysql_cluster]
ndb-connectstring=mysql1,mysql2

 

SQL Nodes (mysql1 and mysql2)

1. Install required package using yum:

$ yum install perl libaio* pcre* popt* openssl openssl-devel gcc make -y

2. Download all required packages for Keepalived, HAProxy and MySQL Cluster package from this site (management, tools, shared, client, server):

$ cd /usr/local/src
$ wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
$ wget http://haproxy.1wt.eu/download/1.4/src/haproxy-1.4.22.tar.gz
$ wget http://mirror.services.wisc.edu/mysql/Downloads/MySQL-Cluster-7.1/MySQL-Cluster-gpl-management-7.1.25-1.el6.x86_64.rpm
$ wget http://mirror.services.wisc.edu/mysql/Downloads/MySQL-Cluster-7.1/MySQL-Cluster-gpl-tools-7.1.25-1.el6.x86_64.rpm
$ wget http://mirror.services.wisc.edu/mysql/Downloads/MySQL-Cluster-7.1/MySQL-Cluster-gpl-shared-7.1.25-1.el6.x86_64.rpm
$ wget http://mirror.services.wisc.edu/mysql/Downloads/MySQL-Cluster-7.1/MySQL-Cluster-gpl-client-7.1.25-1.el6.x86_64.rpm
$ wget http://mirror.services.wisc.edu/mysql/Downloads/MySQL-Cluster-7.1/MySQL-Cluster-gpl-server-7.1.25-1.el6.x86_64.rpm

3. Extract and compile Keepalived:

$ tar -xzf keepalived-1.2.7.tar.gz
$ cd keepalived-*
$ ./configure
$ make
$ make install

4. Extract and compile HAProxy:

$ tar -xzf haproxy-1.4.22.tar.gz
$ cd haproxy-*
$ make TARGET=linux26 ARCH=x86_64 USE_PCRE=1
$ make install

5. Install mysql packages with following order (management > tools > shared > client > server):

$ cd /usr/local/src
$ rpm -Uhv MySQL-Cluster-gpl-management-7.1.25-1.el6.x86_64.rpm
$ rpm -Uhv MySQL-Cluster-gpl-tools-7.1.25-1.el6.x86_64.rpm
$ rpm -Uhv MySQL-Cluster-gpl-shared-7.1.25-1.el6.x86_64.rpm
$ rpm -Uhv MySQL-Cluster-gpl-client-7.1.25-1.el6.x86_64.rpm
$ rpm -Uhv MySQL-Cluster-gpl-server-7.1.25-1.el6.x86_64.rpm

6. Create new directory for MySQL cluster. We also need to create cluster configuration file config.ini underneath it:

$ mkdir /var/lib/mysql-cluster
$ vim /var/lib/mysql-cluster/config.ini

And add following line:

[ndb_mgmd default]
DataDir=/var/lib/mysql-cluster
 
[ndb_mgmd]
NodeId=1
HostName=mysql1
 
[ndb_mgmd]
NodeId=2
HostName=mysql2
 
[ndbd default]
NoOfReplicas=2
DataMemory=256M
IndexMemory=128M
DataDir=/var/lib/mysql-cluster
 
[ndbd]
NodeId=3
HostName=mysql-data1
 
[ndbd]
NodeId=4
HostName=mysql-data2
 
[mysqld]
NodeId=5
HostName=mysql1
 
[mysqld]
NodeId=6
HostName=mysql2

7. Create the mysql configuration file at /etc/my.cnf and add following line:

[mysqld]
ndbcluster
port=3307
ndb-connectstring=mysql1,mysql2
default_storage_engine=ndbcluster
 
[mysql_cluster]
ndb-connectstring=mysql1,mysql2

Starting the Cluster

1. Start mysql cluster management service:

For mysql1:

$ ndb_mgmd -f /var/lib/mysql-cluster/config.ini --ndb-nodeid=1

For mysql2:

$ ndb_mgmd -f /var/lib/mysql-cluster/config.ini --ndb-nodeid=2

2. Start the mysql cluster storage service in both data nodes (mysql-data1 & mysql-data2):

$ ndbd

3. Start the mysql service (mysql1 & mysql2):

$ service mysql start

4. Login to mysql console and run following command  (mysql1 & mysql2):

mysql> use mysql;
mysql> alter table user engine=ndbcluster;
mysql> alter table db engine=ndbcluster;

5. Check the output of table db and user in mysql database and make sure it should appear as below:

mysql> SELECT table_name,engine FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=DATABASE();
+---------------------------+------------+
| table_name                | engine     |
+---------------------------+------------+
| user                      | ndbcluster |
| columns_priv              | MyISAM     |
| db                        | ndbcluster |
| event                     | MyISAM     |
| func                      | MyISAM     |
| general_log               | CSV        |
| help_category             | MyISAM     |
| help_keyword              | MyISAM     |
| help_relation             | MyISAM     |
| help_topic                | MyISAM     |
| host                      | MyISAM     |
| ndb_apply_status          | ndbcluster |
| ndb_binlog_index          | MyISAM     |
| plugin                    | MyISAM     |
| proc                      | MyISAM     |
| procs_priv                | MyISAM     |
| servers                   | MyISAM     |
| slow_log                  | CSV        |
| tables_priv               | MyISAM     |
| time_zone                 | MyISAM     |
| time_zone_leap_second     | MyISAM     |
| time_zone_name            | MyISAM     |
| time_zone_transition      | MyISAM     |
| time_zone_transition_type | MyISAM     |
+---------------------------+------------+
24 rows in set (0.00 sec)

6. Check the management status in mysql1. You should see output similar to below:

$ ndb_mgm -e show
Connected to Management Server at: mysql1:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @192.168.10.103 (mysql-5.1.66 ndb-7.1.25, Nodegroup: 0, Master)
id=4 @192.168.10.104 (mysql-5.1.66 ndb-7.1.25, Nodegroup: 0)
 
[ndb_mgmd(MGM)] 2 node(s)
id=1 @192.168.10.101 (mysql-5.1.66 ndb-7.1.25)
id=2 @192.168.10.102 (mysql-5.1.66 ndb-7.1.25)
 
[mysqld(API)] 2 node(s)
id=5 @192.168.10.101 (mysql-5.1.66 ndb-7.1.25)
id=6 @192.168.10.102 (mysql-5.1.66 ndb-7.1.25)

7. Change MySQL root password to follow the MySQL root password in cPanel server (mysql1):

$ mysqladmin -u root password 'MhGGs4wYs'

8. Add MySQL root password into root environment so we do not need to specify password to access mysql console (mysql1 & mysql2):

$ vim /root/.my.cnf

And add following line:

[client]
user="root"
password="MhGGs4wYs"

9. Add haproxy user without password to be used by HAProxy to check the availability of real server (mysql1):

mysql> GRANT USAGE ON *.* TO [email protected]'%';

10. Add root user from any host so cPanel servers can access and control the MySQL cluster (mysql1):

mysql> GRANT USAGE ON *.* TO [email protected]'%' IDENTIFIED BY 'MhGGs4wYs';
mysql> GRANT USAGE ON *.* TO [email protected]'mysql1' IDENTIFIED BY 'MhGGs4wYs';
mysql> GRANT USAGE ON *.* TO [email protected]'mysql2' IDENTIFIED BY 'MhGGs4wYs';
mysql> GRANT ALL PRIVILEGES ON *.* TO [email protected]'%';
mysql> GRANT ALL PRIVILEGES ON *.* TO [email protected]'mysql1';
mysql> GRANT ALL PRIVILEGES ON *.* TO [email protected]'mysql2';

11. The last step, we need to allow GRANT privileges to [email protected]’%’ by running following command in mysql console (mysql1):

mysql> UPDATE mysql.user SET `Grant_priv` = 'Y' WHERE `User` = 'root';

 

Configuring Virtual IP and Load Balancer (mysql1 & mysql2)

1. Configure HAProxy by creating a configuration /etc/haproxy.cfg:

$ vim /etc/haproxy.cfg

And add following line:

defaults
    log global
    mode http
    retries 2
    option redispatch
    maxconn 4096
    contimeout 50000
    clitimeout 50000
    srvtimeout 50000
 
listen mysql_proxy 0.0.0.0:3306
    mode tcp
    balance roundrobin
    option tcpka
    option httpchk
    option mysql-check user haproxy
    server mysql1 192.168.10.101:3307 weight 1
    server mysql2 192.168.10.102:3307 weight 1

2. Next we need to configure virtual IP. Open /etc/sysctl.conf and add following line to allow non-local IP to bind:

net.ipv4.ip_nonlocal_bind = 1

And run following command to apply the changes:

$ sysctl -p

3. Create Keepalived configuration file at /etc/keepalived.conf and add following line:

For mysql1:

vrrp_script chk_haproxy {
      script "killall -0 haproxy"    # verify the pid is exist or not
      interval 2                     # check every 2 seconds
      weight 2                       # add 2 points of prio if OK
}
 
vrrp_instance VI_1 {
      interface eth0                 # interface to monitor
      state MASTER
      virtual_router_id 51           # Assign one ID for this route
      priority 101                   # 101 on master, 100 on backup
      virtual_ipaddress {
            192.168.10.110           # the virtual IP
      }
      track_script {
            chk_haproxy
      }
}

For mysql2:

vrrp_script chk_haproxy {
      script "killall -0 haproxy"    # verify the pid is exist or not
      interval 2                     # check every 2 seconds
      weight 2                       # add 2 points of prio if OK
}
 
vrrp_instance VI_1 {
      interface eth0                 # interface to monitor
      state MASTER
      virtual_router_id 51           # Assign one ID for this route
      priority 100                   # 101 on master, 100 on backup
      virtual_ipaddress {
            192.168.10.110 # the virtual IP
      }
      track_script {
            chk_haproxy
      }
}

4. Start HAProxy:

$ haproxy -D -f /etc/haproxy.cfg

5. Start Keepalived:

$ keepalived -f /etc/keepalived.conf

6. Add following line into /etc/rc.local to make sure Keepalived and HAProxy start on boot:

$ vim /etc/rc.local

And add following line:

/usr/local/sbin/haproxy -D -f /etc/haproxy.cfg
/usr/local/sbin/keepalived -f /etc/keepalived.conf

7. Check the virtual IP should be up in mysql1:

$ ip a | grep eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
inet 192.168.10.101/24 brd 192.168.10.255 scope global eth0
inet 192.168.10.110/32 scope global eth0

8. Verify in mysql2 whether Keepalived is running in backup mode:

$ tail /var/log/messages
Dec 14 12:08:56 mysql2 Keepalived_vrrp[3707]: VRRP_Instance(VI_1) Entering BACKUP STATE

9. Check that HAProxy is run on port 3306 and mysqld is run on port 3307:

$ netstat -tulpn | grep -e mysql -e haproxy
tcp   0   0   0.0.0.0:3306     0.0.0.0:*       LISTEN      3587/haproxy
tcp   0   0   0.0.0.0:3307     0.0.0.0:*       LISTEN      3215/mysqld

 

Setup Remote MySQL Server in cPanel Server

1. Go to WHM > SQL Services > Setup Remote MySQL server and enter following details. Make sure the Remote server address is the virtual IP address setup in Keepalived in mysql1:

Remote server address (IP address or FQDN): mysql.mydomain.org
Remote SSH Port                           : 22
Select authentication method              : Public Key (default)
Select an installed SSH Key               : id_dsa

2. Wait for a while and you will see following output:

remote_success

3. Now MySQL Cluster is integrated within WHM/cPanel. You may verify this by accessing into PHPmyAdmin in WHM at WHM > SQL Services > PHPmyAdmin and you should see that you are connected into the MySQL Cluster as screenshot below:

my_cluster

Testing

We can test our MySQL high availability architecture by turning off the power completely for mysql1 or mysql2 and mysql-data1 or mysql-data2 in the same time. You will notice that the MySQL service will still available in cPanel point-of-view.

Here is my PHPmyAdmin for my test blog running on WordPress. You can notice that the database created is under ndbcluster engine:

blog_cluster

I never test this architecture in any production server yet and I cannot assure that all WHM/cPanel SQL functionalities are working as expected. Following features in cPanel has been tried and working well:

  • PHPMyAdmin
  • cPanel MySQL features (MySQL Database and MySQL Database Wizard)

Monitor MySQL Galera Cluster from Split-Brain

I have another set of MySQL Galera Cluster running on Percona XtraDB Cluster which having 2 nodes with 1 arbitrator. In total, I do have 3 votes in the quorum. The expected problem when we have 2 nodes run in cluster is the possibility for this cluster to be split-brain if the arbitrator is down, followed by network switch down in the same time. This will surely bring a great impact to your database consistency and cluster availability.

To avoid this thing to happen, we need to closely monitor the number of nodes being seen by the cluster. If 3 nodes, it is normal and do nothing. If 2 nodes, we should send a warning which notify one server is down and if 1 node, shutdown the MySQL server so it will prevent for the split-brain to happen.

To check what is the number of node being seen by the cluster in MySQL Galera, we can use this command:

$ mysql -e "show status like 'wsrep_cluster_size'";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

We should create a BASH script which monitor and evaluate this value to the respective action.

1. First of all, install sendmail and mailx. We need this in order to send the alert via email:

$ yum install sendmail mailx -y

2. Make sure sendmail will be started on boot and we need to start the service as well:

$ chkconfig sendmail on
$ service sendmail start

3. Create the BASH script for this monitoring using text editor in /root/scripts directory named galera_monitor:

$ mkdir -p /root/scripts
$ vim /root/scripts/galera_monitor

And paste following line:

#!/bin/bash
## Monitor galera cluster size
 
## Where the alert should be sent to
EMAIL="[email protected]"
 
cluster_size=`mysql -e "show status like 'wsrep_cluster_size'" | tail -1 | awk {'print $2'}`
hostname=`hostname`
error=`tail -100 /var/lib/mysql/mysql-error.log`
 
SUBJECT1="ERROR: [$hostname] Galera Cluster Size"
SUBJECT2="WARNING: [$hostname] Galera Cluster Size"
EMAILMESSAGE="/tmp/emailmessage.txt"
 
echo "Cluster size result: $cluster_size" > $EMAILMESSAGE
echo "Latest error: $error" >> $EMAILMESSAGE
 
if [ $cluster_size -eq 1 ]; then
    /bin/mail -s "$SUBJECT1" "$EMAIL" < $EMAILMESSAGE
    /etc/init.d/mysql stop                    # stop the mysql server to prevent split-brain
elif [ $cluster_size -eq 2 ]; then
    /bin/mail -s "$SUBJECT2" "$EMAIL" < $EMAILMESSAGE
fi

4. Add the root login credentials into /root/.my.cnf so it can auto-login into mysql console:

[client]
user=root
password=MyR00tP4ss

5. Change the permission of /root/.my.cnf so it only accessible by root:

$ chmod 400 /root/.my.cnf

6. Change the permission of the script so it is executable:

 $ chmod 755 /root/scripts/galera_monitor

7. Add the scripts into cron:

$ echo "* * * * * /bin/sh /root/scripts/galera_monitor" >> /var/spool/cron/root

8. Reload cron daemon to apply changes:

$ service crond reload

Done. You should received an email every minutes if your cluster size has reduced to 2 and you should do something about it to bring the 3 nodes up. If cluster size is 1, then it will stop the mysql server from running.

Notes: You should NOT enable the cron if you re-initialize your Galera cluster, as it will keep MySQL stopping. This script is only suitable for monitoring production cluster.

CentOS 6: Install MySQL Cluster – The Simple Way

In this post I am going to install a MySQL cluster as refer to the architecture as below:

 

 

MySQL cluster is consists of 3 types of node:

  • Data node (mysql-data1 & mysql-data2)
  • Sql daemon node (mysql-mysqld1 & mysql-mysqld2)
  • Management node (mysql-management)

Data node will hold the database and it will replicated automatically to all data nodes. Sql daemon node is the interface between database and client. It will serve the query they got from data nodes, similar like a “gateway”. Management node is required in order to monitor and manage the whole cluster. Recommended minimum setup for high availability and scalability will be 5 servers as what I have highlights in the picture above. I will be  using CentOS 6.3 64bit for all servers.

All Servers

1. SELINUX must be disabled on all server. Change the SELINUX configuration file at /etc/sysconfig/selinux:

SELINUX=disabled

2. Firewall is disabled on all servers:

$ service iptables stop
$ chkconfig iptables off
$ setenforce 0

3. Entries under /etc/hosts for all servers should be as below:

web-server         192.168.1.21
mysql-mysqld1      192.168.1.51 
mysql-mysqld2      192.168.1.52
mysql-management   192.168.1.53
mysql-data1        192.168.1.54
mysql-data2        192.168.1.55

Management Node

1. Download and install MySQL Cluster (management & tools) package from here:

$ cd /usr/local/src
$ wget http://download.softagency.net/MySQL/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-management-7.0.35-1.rhel5.x86_64.rpm
$ wget http://download.softagency.net/MySQL/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-tools-7.0.34-1.rhel5.x86_64.rpm
$ rpm -Uhv MySQL-Cluster-gpl-management-7.0.35-1.rhel5.x86_64.rpm
$ rpm -Uhv MySQL-Cluster-gpl-tools-7.0.34-1.rhel5.x86_64.rpm

2. Create the mysql-cluster directory and configuration file config.ini:

$ mkdir -p /var/lib/mysql-cluster
$ vim /var/lib/mysql-cluster/config.ini

And add following line:

[ndb_mgmd default]
DataDir=/var/lib/mysql-cluster
 
[ndb_mgmd]
HostName=mysql-management
 
[ndbd default]
NoOfReplicas=2
DataMemory=256M
IndexMemory=128M
DataDir=/var/lib/mysql-cluster
 
[ndbd]
HostName=mysql-data1
 
[ndbd]
HostName=mysql-data2
 
[mysqld]
HostName=mysql-mysqld1
 
[mysqld]
HostName=mysql-mysqld2

Data Nodes

1. Following steps should be executed on both data nodes (mysql-data1 and mysql-data2). Download and install the MySQL storage package from here:

$ cd /usr/local/src
$ wget http://download.softagency.net/MySQL/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-storage-7.0.35-1.rhel5.x86_64.rpm 
$ rpm -Uhv MySQL-Cluster-gpl-storage-7.0.35-1.rhel5.x86_64.rpm

2. Add following line under /etc/my.cnf:

[mysqld]
ndbcluster
ndb-connectstring=mysql-management
 
[mysql_cluster]
ndb-connectstring=mysql-management

SQL Nodes

1. Following steps should be executed on both SQL nodes (mysql-mysqld1 and mysql-mysqld2). Remove mysql-libs using yum:

$ yum remove mysql-libs -y

2. Install required package using yum:

$ yum install libaio -y

3. Download the MySQL client, shared and server package from MySQL download site here:

$ cd /usr/local/src
$ wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/MySQL-Cluster-client-gpl-7.2.8-1.el6.x86_64.rpm/from/http://cdn.mysql.com/
$ wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/MySQL-Cluster-shared-gpl-7.2.8-1.el6.x86_64.rpm/from/http://cdn.mysql.com/wget http://www.mysql.com/get/Downloads/MySQL-Cluster-7.2/MySQL-Cluster-server-gpl-7.2.8-1.el6.x86_64.rpm/from/http://cdn.mysql.com/

4. Install all packages:

$ rpm -Uhv MySQL-Cluster-*

5. Add following line into /etc/my.cnf:

[mysqld]
ndbcluster
ndb-connectstring=mysql-management
default_storage_engine=ndbcluster
 
[mysql_cluster]
ndb-connectstring=mysql-management

Start the Cluster

1. To start the cluster, we must follow this order:

Management Node > Data Node > SQL Node

2. So, login to management node (mysql-management) and execute following command:

$ ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.1.63 ndb-7.0.35
2012-11-22 07:36:55 [MgmtSrvr] INFO -- The default config directory '/usr/mysql-cluster' does not exist. Trying to create it...
2012-11-22 07:36:55 [MgmtSrvr] INFO -- Sucessfully created config directory

3.  Next is start the ndbd service in Data Node (mysql-data1):

$ ndbd
2012-11-22 07:37:24 [ndbd] INFO -- Angel connected to 'mysql-management:1186'
2012-11-22 07:37:24 [ndbd] INFO -- Angel allocated nodeid: 2

4. Next is start the ndbd service in Data Node (mysql-data2):

$ ndbd
2012-11-22 07:37:24 [ndbd] INFO -- Angel connected to 'mysql-management:1186'
2012-11-22 07:37:24 [ndbd] INFO -- Angel allocated nodeid: 3

5. Next is start the mysql service in SQL node (mysql-mysqld1):

$ service mysql start

6. Next is start the mysql service in SQL node (mysql-mysqld2):

$ service mysql start

Monitor the Cluster

Monitoring the cluster will required you to login into management server. To check overall status of cluster:

$ ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.1.54 (mysql-5.1.63 ndb-7.0.35, Nodegroup: 0, Master)
id=3 @192.168.1.55 (mysql-5.1.63 ndb-7.0.35, Nodegroup: 0)
 
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.53 (mysql-5.1.63 ndb-7.0.35)
 
[mysqld(API)] 2 node(s)
id=4 @192.168.1.51 (mysql-5.5.27 ndb-7.2.8)
id=5 @192.168.1.52 (mysql-5.5.27 ndb-7.2.8)

To check the Data nodes status:

$ ndb_mgm -e "all status"
Connected to Management Server at: localhost:1186
Node 2: started (mysql-5.1.63 ndb-7.0.35)
Node 3: started (mysql-5.1.63 ndb-7.0.35)

To check the memory usage of data nodes:

$ ndb_mgm -e "all report memory"
Connected to Management Server at: localhost:1186
Node 2: Data usage is 0%(23 32K pages of total 8192)
Node 2: Index usage is 0%(20 8K pages of total 16416)
Node 3: Data usage is 0%(23 32K pages of total 8192)
Node 3: Index usage is 0%(20 8K pages of total 16416)

Stopping the Cluster

1. To stop the cluster, we must follow this order:

SQL Node > Management Node / Data Node

2. Login to SQL node (mysql-mysqld1mysql-mysqld2) and run following command:

$ service mysql stop

3. Login to management node (mysql-management) and run following command:

$ ndb_mgm -e shutdown

 

Done! You should now able to create or import database on one of the SQL node. You can put a load balancer in front of the SQL node to take advantage on the performance and high availability.

Notes

You may notice that the distribution version I installed is “rhel5”. You can get the “el6” distribution package at this page: http://mirror.services.wisc.edu/mysql/Downloads/MySQL-Cluster-7.2/ and search for any “el6” package name.

High Availability: Configure Piranha for HTTP, HTTPS and MySQL

Piranha is a simple yet powerful tool to manage virtual IP and service with its web-based GUI.

As refer to my previous post on how to install and configure Piranha for HTTP service: http://blog.secaserver.com/2012/07/centos-configure-piranha-load-balancer-direct-routing-method/, in this post we will complete over the Piranha configuration with HTTP and HTTPS load balancing using direct-routing with firewall marks and MySQL load balancing using direct-routing only.

HTTP/HTTPS will need to be accessed by users via virtual public IP 130.44.50.120 while MySQL service will be accessed by web servers using virtual private IP 192.168.100.30. Kindly refer to picture below for the full architecture:

 

All Servers

SELINUX must be turned off on all servers. Change the SELINUX configuration file at /etc/sysconfig/selinux:

SELINUX=disabled

Load Balancers

1. All steps should be done in both servers unless specified. We will install Piranha and other required packages using yum:

$ yum install piranha ipvsadm mysql -y

2. Open firewall ports as below:

$ iptables -A INPUT -m tcp -p tcp --dport 3636 -j ACCEPT
$ iptables -A INPUT -m tcp -p tcp --dport 80 -j ACCEPT
$ iptables -A INPUT -m tcp -p tcp --dport 443 -j ACCEPT
$ iptables -A INPUT -m tcp -p tcp --dport 539 -j ACCEPT
$ iptables -A INPUT -m udp -p udp --dport 161 -j ACCEPT

3. Start all required services and make sure they will auto start if server reboot:

$ service piranha-gui start
$ chkconfig piranha-gui on
$ chkconfig pulse on

4. Run following command to set password for user piranha. This will be used when accessing the web-based configuration tools:

$ piranha-passwd

5. Turn on IP forwarding. Open /etc/sysctl.conf and make sure following line has value 1:

net.ipv4.ip_forward = 1

And run following command to activate it:

$ sysctl -p

6. Check whether iptables is loaded properly as the kernel module:

$ lsmod | grep ip_tables
ip_tables 17733 3 iptable_filter,iptable_mangle,iptable_nat

7. Since we will need to serve HTTP and HTTPS from the same server, we need to group the traffic to be forwarded to the same destination. To achieve this, we need to mark the packet using iptables and so it being recognized correctly on the destination server. Set the iptables rules to mark all packets which destined for the same server as “80”:

$ iptables -t mangle -A PREROUTING -p tcp -d 130.44.50.120/32 --dport 80 -j MARK --set-mark 80
$ iptables -t mangle -A PREROUTING -p tcp -d 130.44.50.120/32 --dport 443 -j MARK --set-mark 80

Load Balancer #1

1. Check the IP address is correctly setup:

$ ip a | grep inet
inet 130.44.50.121/28 brd 110.74.131.15 scope global eth0
inet 192.168.100.41/24 brd 192.168.10.255 scope global eth1

2. Login into Piranha at http://130.44.50.121:3636/. Login as user piranha and password which has been setup in step #4 of Load Balancers section.

3. Enable redundancy. Go to Piranha > Redundancy > Enable.

4. Enter the IP information as below:

Redundant server public IP     : 130.44.50.122
Monitor NIC links for failures : Enabled
Use sync daemon                : Enabled

Click ‘Accept’.

5. Go to Piranha > Virtual Servers > Add > Edit. Add information as below and click ‘Accept’:

 

6. Next, go to Real Server. This we will put the IP address of all real servers that serve HTTP. Fill up all required information as below:

7. Now we need to do the similar setup to HTTPS. Just change the port number for ‘Application port’ to 443. For Real Server, change the real server’s destination port to 443.

8. For MySQL virtual server, enter information as below:

 

9. For MySQL real servers, enter information as below:

 

10. Configure monitoring script for MySQL virtual server. Click on ‘Monitoring Script’ and configure as below:

 

11. Setup the monitoring script for mysql:

$ vim /root/mysql_mon.sh

And add following line:

#!/bin/sh
USER=monitor
PASS=M0Npass5521
####################################################################
CMD=/usr/bin/mysqladmin
 
IS_ALIVE=`$CMD -h $1 -u $USER -p$PASS ping | grep -c "alive"`
 
if [ "$IS_ALIVE" = "1" ]; then
    echo "UP"
else
    echo "DOWN"
fi

12. Change the script permission to executable:

$ chmod 755 /root/mysql_mon.sh

13. Now copy over the script and Piranha configuration file to load balancer #2:

$ scp /etc/sysconfig/ha/lvs.cf lb2:/etc/sysconfig/ha/lvs.cf
$ scp /root/mysql_mon.sh lb2:/root/

14. Restart Pulse to activate the Piranha configuration in LB#1:

$ service pulse restart

Load Balancer #2

In this server, we just need to restart pulse service as below:

$ chkconfig pulse on
$ service pulse restart

Database Cluster

1. We need to allow the MySQL monitoring user from nanny (load balancer) in the MySQL cluster. Login into MySQL console and enter following SQL command in one of the server:

mysql> GRANT USAGE ON *.* TO [email protected]'%' IDENTIFIED BY 'M0Npass5521';

2. Add the virtual IP manually using iproute:

$ /sbin/ip addr add 192.168.100.30 dev eth1

3. Add following entry into /etc/rc.local to make sure the virtual IP is up after boot:

$ echo '/sbin/ip addr add 192.168.100.30 dev eth1' >> /etc/rc.local

Attention: If you restart the interface that hold virtual IP in this server, you need to execute step #2 to bring up the virtual IP manually. VIPs can not be configured to start on boot.

4. Check the IPs in the server. Example below was taken from server Mysql1:

$ ip a | grep inet
inet 130.44.50.127/24 brd 130.44.50.255 scope global eth0
inet 192.168.100.33/24 brd 192.168.100.255 scope global eth1
inet 192.168.100.30/32 scope global eth1

Web Cluster

1. On each and every server, we need to install a package called arptables_jf from yum. We will used this to manage our ARP tables entries and rules:

$ yum install arptables_jf -y

2. Add following rules respectively for every server:

Web1:

arptables -A IN -d 130.44.50.120 -j DROP
arptables -A OUT -d 130.44.50.120 -j mangle --mangle-ip-s 130.44.50.123

Web 2:

arptables -A IN -d 130.44.50.120 -j DROP
arptables -A OUT -d 130.44.50.120 -j mangle --mangle-ip-s 130.44.50.124

Web 3:

arptables -A IN -d 130.44.50.120 -j DROP
arptables -A OUT -d 130.44.50.120 -j mangle --mangle-ip-s 130.44.50.125

3. Enable arptables_jf to start on boot, save the rules and restart the service:

$ service arptables_jf save
$ chkconfig arptables_jf on
$ service arptables_jf restart

4. Add the virtual IP manually into the server using iproute command as below:

$ /sbin/ip addr add 130.44.50.120 dev eth0

5. Add following entry into /etc/rc.local to make sure the virtual IP is up after boot:

$ echo '/sbin/ip addr add 130.44.50.120 dev eth0' >> /etc/rc.local

Attention: If you restart the interface that hold virtual IP in this server, you need to execute step #4 to bring up the virtual IP manually. VIPs can not be configured to start on boot.

6. Check the IPs in the server. Example below was taken from server Web1:

$ ip a | grep inet
inet 130.44.50.123/28 brd 110.74.131.15 scope global eth0
inet 130.44.50.120/32 scope global eth0
inet 192.168.100.21/24 brd 192.168.100.255 scope global eth1

You are now having a complete high availability MySQL and HTTP/HTTPS service with auto failover and load balance features by Piranha using direct routing method.

In this tutorial, I am not focusing on HTTPS because in this test environment I do not have SSL setup correctly and do not have much time to do that. By the way, you may use following BASH script to monitor HTTPS from Piranha (nanny):

#!/bin/bash
 
if [ $# -eq 0 ]; then
        echo "host not specified"
        exit 1
fi
 
curl -s --insecure \
	--cert /etc/crt/hostcert.pem \
	--key /etc/crt/hostkey.pem \
	https://${1}:443 | grep "" \
	&> /dev/null
 
if [ $? -eq 0 ]; then
        echo "UP"
else
        echo "DOWN"
fi

I hope this tutorial could be useful for some guys out there!