Sysbench 0.5 + Ubuntu 14.04 (Trusty) + Percona Server or XtraDB Cluster

I need to perform benchmarks on Percona XtraDB Cluster and Percona Server directly from my Ubuntu 14.04 box. Sysbench 0.5 is the latest stable to date and it supports benchmarking Galera cluster. If you are running on Sysbench 0.4, you would most likely encounter one of following errors if you are running it against a Galera cluster with high value of threads:

ALERT: failed to execute mysql_stmt_execute(): Err1317 Query execution was interrupted
ALERT: failed to execute mysql_stmt_execute(): Err1062 Duplicate entry '2199902' for key 'PRIMARY'

This post presumes that you have already installed standard Percona related packages including the apt-get repository to run a MySQL server (as instructed on Percona’s  documentation page).

 

Installing Sysbench 0.5

1. Install compiler tools and required packages:

$ sudo apt-get install -y gcc autoconf automake make libtool libssl-dev libcrypto++9

2. Install mysql_config (available from Percona repository):

$ sudo apt-get install -y libperconaserverclient18-dev

3. Get Sysbench from Launchpad:

$ bzr branch lp:sysbench

4. Compile and install sysbench:

$ cd sysbench
$ ./autogen.sh
$ ./configure --prefix=/usr --mandir=/usr/share/man
$ make
$ sudo make install

5. Create LUA template directory:

$ sudo mkdir /usr/share/sysbench/tests/db -p
$ sudo cp sysbench/tests/db/* /usr/share/sysbench/tests/db

6. Verify if sysbench is running in the correct version:

$ sysbench --version
sysbench 0.5

Running Sysbench

1. Before starting the benchmark, we need to prepare the test data. Following command will generate a table consists of 20 million rows on a Galera Cluster through HAproxy which running on 127.0.0.1 port 33306:

$ sysbench \
--db-driver=mysql \
--mysql-table-engine=innodb \
--oltp-table-size=20000000 \
--mysql-host=127.0.0.1 \
--mysql-port=33306 \
--mysql-user=sbtest \
--mysql-password=password \
--test=/usr/share/sysbench/tests/db/oltp.lua \
prepare

Or we can generate 8 tables consists of 10M rows in parallel with 8 threads (total of 80M rows of data set):

$ sysbench \
--db-driver=mysql \
--mysql-table-engine=innodb \
--oltp-table-size=10000000 \
--oltp-tables-count=8 \
--num-threads=8 \
--mysql-host=127.0.0.1 \
--mysql-port=33306 \
--mysql-user=sbtest \
--mysql-password=password \
--test=/usr/share/sysbench/tests/db/parallel_prepare.lua \
run

2. Start the benchmark against Galera cluster:

$ sysbench \
--db-driver=mysql \
--num-threads=8 \
--max-requests=5000000 \
--oltp-table-size=20000000 \
--oltp-test-mode=complex \
--test=/usr/share/sysbench/tests/db/oltp.lua \
--mysql-host=127.0.0.1 \
--mysql-port=33306 \
--mysql-user=sbtest \
--mysql-password=password \
run

You can perform other tests based on the LUA templates exist under /usr/share/sysbench/tests/db. Just change the –test parameter to the fullpath of the template file and you are good to go.

Details on this can be found at http://www.percona.com/docs/wiki/benchmark:sysbench:olpt.lua

 

Replacing OpenJDK with Oracle JDK on CentOS 6

I encountered some issues with an application running on Java. I thought it would be fine if running on standard OpenJDK release available at CentOS repository since it is really easy to install with yum, but it wasn’t. The application does required JDK from Oracle release (which is lame).

So here what you should do when you want to replace OpenJDK with Java SE JDK:

1. Check the current OpenJDK version. I am going to retain the same version if possible:

java -version
java version "1.6.0_30"
OpenJDK Runtime Environment (IcedTea6 1.13.3) (rhel-5.1.13.3.el6_5-x86_64)
OpenJDK 64-Bit Server VM (build 23.25-b01, mixed mode)

Check the current RPM package name:

$ rpm -qa | grep openjdk
java-1.6.0-openjdk-1.6.0.0-5.1.13.3.el6_5.x86_64

2. Download Java SE Development Kit 6 update 30, which is equivalent to OpenJDK 1.6.0_30. There is no way (AFAIK) that we can download it directly using curl or wget. You need to have an account with Oracle. Sign up using your web browser and you will get a temporary link similar to below. Copy it from  your web browser and use wget to download it directly to the box:

$ wget -O jdk-6u30-linux-x64-rpm.bin http://download.oracle.com/otn/java/jdk/6u30-b12/jdk-6u30-linux-x64-rpm.bin?AuthParam=1397834288_32d1181063a10cd15522d23abbc5e4f7

**You should do this quick. The link will be expired within minutes.

3. Remove the installed OpenJDK package:

$ yum remove -y java-1.6.0-openjdk

4. Install Oracle JDK:

$ chmod 755 jdk-6u30-linux-x64-rpm.bin
$ ./jdk-6u30-linux-x64-rpm.bin

5. Verify the new Java version:

java -version
java version "1.6.0_30"
Java(TM) SE Runtime Environment (build 1.6.0_30-b12)
Java HotSpot(TM) 64-Bit Server VM (build 20.5-b03, mixed mode)

Now my application works like a charm! The new path to JRE now is /usr/java/jdk1.6.0_30/jre/

Simple Benchmark of your MySQL Server

Installing Sysbench

The easiest way to perform benchmark on a MySQL server is by using sysbench. To install:

Debian/Ubuntu based:

$ apt-get install sysbench

RedHat/CentOS/Fedora based:

$ yum install sysbench

Preparing Sysbench

If we have a MySQL server ready, let’s create a database for sysbench called sbtest.

mysql> CREATE SCHEMA sbtest;

It is better to increase the default MySQL max_connections (which is too low for this test):

mysql> SET GLOBAL max_connections = 1000;

Let’s prepare the sample data for benchmarking with 2 million rows:

$ sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --oltp-table-size=2000000 --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=myR00tP4ssW0rd prepare

Perform the Benchmark

Now let’s the benchmarking begin. We will start an OLTP (online transactions processing) benchmark test with 8 threads and 10000 max-requests then send the output to a file called sysbench.log:

$ sysbench --db-driver=mysql --test=oltp --num-threads=8 --max-requests=10000 --oltp-table-size=2000000 --oltp-test-mode=complex --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=myR00tP4ssW0rd run >> sysbench.log

Repeat the steps for 16, 32, 64, 128, 256 and 512 threads and append the output to sysbench.log:

$ for i in 16 32 64 128 256 512; do sysbench --db-driver=mysql --test=oltp --num-threads=$i --max-requests=10000 --oltp-table-size=2000000 --oltp-test-mode=complex --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=myR00tP4ssW0rd run >> sysbench.log ; done

We will then need to convert the sysbench output to CSV format so we could plot it to a graph using gnuplot.

$ cat sysbench.log | egrep " cat|threads:|transactions:" | tr -d "\n" | sed 's/Number of threads: /\n/g' | sed 's/\[/\n/g' | sed 's/[A-Za-z\/]\{1,\}://g'| sed 's/ \.//g' | awk {'print $1 $3'} | sed 's/(/\t/g' > sysbench.csv

The content of sysbench.csv should be similar as below:

8    274.53
16   397.47
32   442.79
64   516.90
128  503.36
256  414.35
512  445.67

Plotting Graph

I will use GNUplot for this task. To install:

yum install gnuplot #redhat/centos based
apt-get install gnuplot #debian/ubuntu based

Create a file called mygraph, and then paste following lines:

# output as png image
set terminal png
 
# save file to "benchmark.png"
set output "benchmark.png"
 
# graph title
set title "Benchmark for Sysbench"
 
# aspect ratio for image size
set size 1,1
 
# enable grid on y and x axis
set grid y
set grid x
 
# x-axis label
set xlabel "Threads"
 
# y-axis label
set ylabel "Transactions (tps)"
 
# plot data from sysbench.csv
plot "sysbench.csv" using (log($1)):2:xtic(1) with linesp notitle

Run following command to produce the graph based on our template:

$ gnuplot mygraph

And you should see it generates a png image which you can view it similar as below:

benchmark

That’s it. The benchmark results are well-presented in the graph. We can conclude that our MySQL server was performing nicely up until 64 threads then it appears to have some dropping after that.

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!

Debian: Converting Apache + PHP to FastCGI – The Simple Way

I have a server running on Debian 6 64bit installed with Apache and PHP5 using apt-get package manager. By default, it will configure DSO  as the PHP handler (more details here). I need to convert it to serve PHP through FastCGI (mod_fcgid) to save memory usage. It turns to be that converting them is easy and require simple steps.

 

Here is my pre-configured Apache + PHP settings (installed using apt-get install apache2 php5 command):

$ dpkg --get-selections | grep apache
apache2             install
apache2-mpm-prefork install
apache2-utils       install
apache2.2-bin       install
apache2.2-common    install
libapache2-mod-php5 install
 
$ dpkg --get-selections | grep php
libapache2-mod-php5 install
php5                install
php5-cli            install
php5-common         install
php5-suhosin        install

 

1. Update repository:

$ apt-get update

2. Install required packages for fcgid:

$ apt-get install apache2-mpm-worker libapache2-mod-fcgid php5-cgi

3. Enable cgi.fix_pathinfo in /etc/php5/apache2/php.ini:

cgi.fix_pathinfo=1

4. Stop Apache:

$ /etc/init.d/apache2 stop

5. Disable php5, since we will be using php-cgi:

$ a2dismod php5

6. Setup the virtual host for the website under /etc/apache2/sites-available/default:

 <VirtualHost *:80>
        ServerName www.example.com
        ServerAdmin admin@example.com
        DocumentRoot /var/www
 
        <Directory /var/www>
                Options +ExecCGI
                AllowOverride AuthConfig FileInfo Limit
                AddHandler fcgid-script .php
                FCGIWrapper /usr/lib/cgi-bin/php .php
                Order Deny,Allow
                Allow from All
        </Directory>
 
        ErrorLog /var/log/apache2/error.log
        LogLevel warn
 
        CustomLog /var/log/apache2/access.log combined
 
</VirtualHost>

7. Start Apache:

$ /etc/init.d/apache2 start

 

Done! You are now running on FastCGI as the PHP handler. You can verify this with phpinfo:

phpinfo

 

Install grsecurity with Yum

Easiest way to install grsecurity. The good thing about it is only grsecurity provides protection against zero-day and other advanced threats that buys administrators valuable time while vulnerability fixes make their way out to distributions and production testing.

1. Go into repository directory:

$ cd /etc/yum.repos.d/
$ wget http://repos.coredumb.net/grsecurity/grsecurity.repo

2. Install grsecurity kernel and administrator tools:

$ yum clean all
$ yum install kernel gradm

3. Reboot so it could load the grkernel:

$ reboot

 

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!

 

Linux: Follow and Download using wget

Your Linux box is incomplete if you do not have wget installed. It is a simple CLI based application used to download files from network.  It support HTTP, HTTPS and FTP protocols as well as retrieval through HTTP proxies.

 

 

Download latest version of PHPmyAdmin from SourceForge.

 

You can just simply use the latest link of SourceForge software and wget will automatically follow it. You also need to specify the ‘content-disposition’ flag. This option is useful for some file-downloading CGI programs that use “Content-Disposition” header to describe what the name of a downloaded file should be:

$ wget --content-disposition http://sourceforge.net/projects/phpmyadmin/files/latest/download

Download TokuMX  with cookies and redirection from Tokutek download site.

 

To download from this page, I was required to have cookies from download.php=tokumx-1.0-3-linux-x86_64.tgz page and use the same cookies to download from download.php?df=1. You can navigate to this page if you want to try that with a web browser, http://www.tokutek.com/download.php?download_file=tokumx-1.0.3-linux-x86_64.tgz.

$ wget --keep-session-cookies \
--save-cookies cookies.txt \
http://www.tokutek.com/download.php?download_file=tokumx-1.0.3-linux-x86_64.tgz && \
wget --load-cookies cookies.txt \
--content-disposition http://www.tokutek.com/download.php?df=1 && \
rm -f download.php* cookies.txt

 

Download Certain Files from Web Directory

 

You can download certain files (*.gz) under same directory using wget with -r flag. You need to specify –no-directories and –no-host-directories to output the files in the current directory while –level is the depth of the directory it will go:

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

 

I will keep updating this page to share my collection of wget commands.

 

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