Installing MySQL 5.7 (Oracle) using APT repository

I got a task to test and deploy the latest MySQL 5.7 from Oracle on Ubuntu and Debian servers. It turns out with MySQL’s APT repository, this is very trivial. Here is how to get MySQL 5.7 installed on Debian-based systems:

1) Download mysql-apt-config from MySQL download page. Just click on the “Download” button then “No thanks, just start my download”. Copy the link and use wget:

$ wget https://dev.mysql.com/get/mysql-apt-config_0.7.2-1_all.deb

2) Install the package:

$ sudo dpkg -i mysql-apt-config_0.7.2-1_all.deb

* It will prompt out a wizard to configure APT repository. Choose the first option:

Screen Shot 2016-04-14 at 5.31.39 PM

Then, choose mysql-5.7:Screen Shot 2016-04-14 at 5.31.51 PM

Then, choose OK on the menu:Screen Shot 2016-04-14 at 5.32.09 PM

3) Update repository package list:

$ sudo apt-get update

4) Install MySQL community server:

$ sudo apt-get install mysql-community-server

*This will install all required packages as well.

MySQL 5.7 is now installed! That’s it. Easy peasy.

Install and Configure HAProxy for MySQL Galera Cluster

In this post, I’m going to cover on how to add load balancer for the MariaDB Galera Cluster. The major steps are:

  1. Ensure all Galera nodes are running as a single cluster (all nodes are primary and synced).
  2. Install HAproxy (you can install it on separate node or on the application server).
  3. Configure clustercheck script. This script performs health check on each backend server.
  4. Configure HAproxy statistic page.
  5. Point the application to load balancer.

Our architecture looks like below:

galera_haproxy_secaserver

Configure clustercheck script

* Steps described in this section should be performed on all DB nodes unless specified otherwise.

1. Firstly, we need to configure the backend health check reporting. We will use Percona’s clustercheck script available here. Get the script and put it under /usr/local/bin by running following commands:

$ git clone https://github.com/olafz/percona-clustercheck
$ cp percona-clustercheck/clustercheck /usr/local/bin

2. The clustercheck script performs regular check on the Galera node by monitoring several MySQL variables/status. It yields a simple HTML output with corresponding HTTP return code (either 503 – Service Unavailable or 200 – OK). To make things easier for HAproxy to trigger the script and get the latest status of the backend, we have to make it listens to a port. We can use xinetd to turn the script into a service daemon and make it listen to a custom port, in this case, I’m going to use 9200.

Create a new file called /etc/xinet.d/mysqlchk, and add following lines:

# default: on
# description: mysqlchk
service mysqlchk
{
  disable = no
  flags = REUSE
  socket_type = stream
  port = 9200
  wait = no
  user = nobody
  server = /usr/local/bin/clustercheck
  log_on_failure += USERID
  only_from = 0.0.0.0/0
  per_source = UNLIMITED
}

3. Then, we need to add the mysqlchk service into xinetd:

$ echo 'mysqlchk      9200/tcp    # MySQL check' >> /etc/services

4. By default, the script will use a MySQL user called “clustercheckuser” with password “clustercheckpassword!”. We need to ensure this MySQL user is exist with the corresponding password before the script would be able to perform health checks. Run following DDL statements on one of the DB node (Galera should replicate the statement to the other nodes):

mysql> GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';
mysql> FLUSH PRIVILEGES;

You can change this value inside the clustercheck script in line 32,33. In this post, I’m going to use the default username and password.

5. Verify if the script returns a correct value:

$ /usr/local/bin/clustercheck > /dev/null
$ echo $?
0

If the DB node is in synced, you should get 0. Otherwise 1 should be the output. The backend health check is configured.

Install HAproxy

* Steps described in this section should be performed on HAproxy or application server.

1. The easy way is using package manager (yum/apt). However, it’s highly recommended to use the latest version available on HAproxy site. Either way, I’ll show the the installation steps here.

a) If you choose to install HAproxy via package manager:

$ yum install haproxy # Redhat/CentOS
$ sudo apt-get install haproxy # Debian/Ubuntu

b) Via source from HAproxy download site:

$ yum install php-curl gcc make # Redhat/CentOS
$ apt-get install php5-curl gcc make # Debian/Ubuntu
$ wget http://www.haproxy.org/download/1.5/src/
$ tar xvzfz
$ cd
$ make TARGET=linux26
$ cp -f haproxy /usr/sbin/haproxy

Installing from source (option b) comes with no init script. So you have to create it manually or simply provision the process via command line (which is non-standard way). I’m not going to cover this unconventional way in this post.

 

Configure HAproxy

Now we have HAproxy installed. We need to configure it to listen on port 3307 for MySQL service and perform back-end health checks. On /etc/haproxy/haproxy.cfg, ensure following lines exist:

global
        pidfile /var/run/haproxy.pid
        daemon
        user haproxy
        group haproxy
        stats socket /var/run/haproxy.socket user haproxy group haproxy mode 600 level admin
 
        maxconn 8192
        spread-checks 3
        quiet
defaults
        mode    tcp
        option  dontlognull
        option tcp-smart-accept
        option tcp-smart-connect
        retries 3
        option redispatch
        maxconn 8192
        timeout check   3500ms
        timeout queue   3500ms
        timeout connect 3500ms
        timeout client  10800s
        timeout server  10800s
 
userlist STATSUSERS
        group admin users admin
        user admin insecure-password admin
        user stats insecure-password yourpassword
 
listen admin_page 0.0.0.0:9600
        mode http
        stats enable
        stats refresh 60s
        stats uri /
        acl AuthOkay_ReadOnly http_auth(STATSUSERS)
        acl AuthOkay_Admin http_auth_group(STATSUSERS) admin
        stats http-request auth realm admin_page unless AuthOkay_ReadOnly
 
listen  mysql_3307
        bind *:3307
        mode tcp
        timeout client  10800s
        timeout server  10800s
        balance leastconn
        option httpchk
        option allbackups
        default-server port 9200 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100
        server db1 10.0.0.187:3306 check
        server db2 10.0.0.188:3306 check
        server db3 10.0.0.189:3306 check

Now enable the service on boot and fire it up:

RHEL/CentOS 6:

$ chkconfig haproxy on # RHEL6
$ service haproxy start # RHEL6

Ubuntu 14.04 and lower, Debian 7 and lower:

$ update-rc.d haproxy defaults
$ sudo service haproxy start

RHEL/CentOS 7, Debian 8, Ubuntu 15.04:

$ systemctl enable haproxy
$ systemctl start haproxy

Verify if HAproxy is listening to the correct ports:

$ sudo netstat -tulpn | grep haproxy
tcp        0      0 0.0.0.0:9600            0.0.0.0:*               LISTEN      370/haproxy
tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN      370/haproxy

3307 is the MySQL load-balanced port, while 9600 is the HAproxy statistic page. You can login to check the status by going to http://haproxy_ip_address:9600/ and login with username ‘admin’ and password ‘yourpassword’ as configured inside haproxy.cfg. You should see something like below:

Screen Shot 2015-09-17 at 3.00.03 PM

Now, you can redirect the application or MySQL client to HAproxy host on port 3307, for a load balanaced  MySQL connections with auto failover.

CentOS/Redhat: How to Install MySQL Sandbox

When you have started to work heavily with MySQL servers, and you need to create, install, configure and destroy MySQL instances, MySQL Sandbox is a great tool to automate those tasks. MySQL Sandbox allows us to deploy a MySQL test environment within seconds with simple commands.

So following is what I would do to install MySQL Sandbox on my CentOS box and create 3 mysql instances for my test environment:

1. Install Perl CPAN package:

$ yum install perl-CPAN

2. Connect to Perl MCPAN shell :

$ perl -MCPAN -e shell

**Answer ‘yes’ for the prompt. Perl will auto-configure most of the stuff for you

3. Install required Perl modules:

install Test::More
install MySQL::Sandbox

4. MySQL Sandbox is recommended to run as a non-root user. Create a user called sandbox and switch to the user’s environment:

$ useradd sandbox
$ su - sandbox

5. Download the MySQL Sandox package from Launchpad:

$ wget https://launchpadlibrarian.net/187745286/MySQL-Sandbox-3.0.47.tar.gz

6. Add MySQL sandbox into environment path of user sandbox’s bashrc:

$ vim ~/.bashrc

And append following line:

export PATH=$PATH:/home/sandbox/MySQL-Sandbox-3.0.47/bin

7. Re-login as the sandbox user to activate environment:

$ exit
$ su - sandbox

8. Download MySQL generic TAR file from MySQL Server archive page:

$ wget http://downloads.mysql.com/archives/get/file/mysql-5.6.20-linux-glibc2.5-x86_64.tar.gz

** I used MySQL 5.6.20 for this installation

Installation completed. To create a single MySQL instances, use following command:

$ make_sandbox mysql-5.6.20-linux-glibc2.5-x86_64.tar.gz

I use following command to create 3 MySQL instances for my test environment:

$ make_multiple_sandbox mysql-5.6.20-linux-glibc2.5-x86_64.tar.gz

Your instances should be ready and running within minutes. They will be created under /home/sandbox/sandboxes.

Restoring xbstream Backup Created by Percona Xtrabackup

 

Backing up

Once Percona Xtrabackup is installed, you can create a full backup with parallel compression as below:

$ innobackupex --stream=xbstream --parallel=4 --compress --compress-threads=2 /var/lib/mysql > /root/backup/backup.xbstream

Once completed, you should see a backup file has been created with .xbstream extension:

du -sh /root/backup/backup.xbstream
106M backup.xbstream

 

Restoration

1. Extract the file using xbstream -x command as below:

$ xbstream -x < /root/backup/backup.xbstream

2. If you look into the current folder recursively, you should see there are a lot of files ended with .qp extension. We need to extract these files using qpress which is available in Percona repository. Install qpress as below:

CentOS/Redhat:

$ yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
$ yum install qpress

Debian/Ubuntu:

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
echo 'deb http://repo.percona.com/apt VERSION main' >> /etc/apt/sources.list
echo 'deb-src http://repo.percona.com/apt VERSION main' >> /etc/apt/sources.list
$ apt-get update
$ apt-get install qpress

* Replace VERSION with your Debian/Ubuntu release name

3. Extract each file ended with .qp and in their respective directory, then remove .qp file once its extracted:

$ for i in $(find -name "*.qp"); do qpress -vd $i $(dirname ${i}) && rm -f $i; done

4. You can now use the –apply-log command to prepare the MySQL data:

$ innobackupex --apply-log ./

5. Stop MySQL server:

$ service mysql stop

5. Move or remove the current data directory and use –copy-back command to let xtrabackup copy the prepared data to MySQL data directory:

$ rm -Rf /var/lib/mysql/*
$ innobackupex --copy-back ./

6. Assign correct ownership to MySQL data directory:

$ chown -Rf mysql.mysql /var/lib/mysql

7. Start the MySQL server:

$ service mysql start

 

Restoration completed!