High Availability: MySQL Cluster with Galera + MySQL Proxy

In this tutorial, I am going to show you on how to achieve higher MySQL uptime with some help from MySQL Proxy, Galera cluster and virtual IP using keepalived.

Actually, the process is similar with my previous post as here, with some added steps to configure MySQL proxy and virtual IP. The architecture can be describe as image below:

Variable that I used:

OS: CentOS 6.0 64bit
MySQL server1: 192.168.0.171
MySQL server2: 192.168.0.172
MySQL proxy server1: 192.168.0.151
MySQL proxy server2: 192.168.0.152
Virtual IP to be shared among MySQL proxies: 192.168.0.170
MySQL root password: [email protected]#
Cluster root username: clusteroot
Cluster root password: [email protected]#
Galera SST user: sst
Galera SST password: sstpass123

Server hostname is important in cluster. Following information has been setup in all servers /etc/hosts file. All configurations shown below are assuming that firewall is turn OFF:

192.168.0.151 myproxy1.cluster.local myproxy1
192.168.0.152 myproxy2.cluster.local myproxy2
192.168.0.171 galera1.cluster.local galera1
192.168.0.172 galera2.cluster.local galera2
127.0.0.1     localhost.localdomain localhost
::1           localhost6 localhost6.localdomain

MySQL Cluster with Galera

1. Following steps are similar with my previous post. But in this tutorial, I am going to rewrite it as refer to this case with latest version of Galera and MySQL. I have no MySQL server installed in this server at the moment. Download the latest Galera library, MySQL with wsrep, MySQL client and MySQL shared from MySQL download page:

$ mkdir -p /usr/local/src/galera
$ cd /usr/local/src/galera
$ wget https://launchpad.net/galera/2.x/23.2.0/+download/galera-23.2.0-1.rhel5.x86_64.rpm
$ wget https://launchpad.net/codership-mysql/5.5/5.5.20-23.4/+download/MySQL-server-5.5.20_wsrep_23.4-1.rhel5.x86_64.rpm
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-5.5.20-1.el6.x86_64.rpm/from/http://ftp.jaist.ac.jp/pub/mysql/
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-5.5.20-1.el6.x86_64.rpm/from/http://ftp.jaist.ac.jp/pub/mysql/

2. Remove unwanted library and install all the packages in following sequence:

$ yum remove mysql-libs -y
$ rpm -Uhv galera-23.2.0-1.rhel5.x86_64.rpm
$ rpm -Uhv MySQL-client-5.5.20-1.el6.x86_64.rpm
$ rpm -Uhv MySQL-shared-5.5.20-1.el6.x86_64.rpm
$ rpm -Uhv MySQL-server-5.5.20_wsrep_23.4-1.rhel5.x86_64.rpm

3. Start the MySQL service and make sure it start on boot:

$ chkconfig mysql on
$ service mysql start

4. Setup the MySQL root password:

$ /usr/bin/mysqladmin -u root password '[email protected]#'

5. Setup the MySQL client for root. Create new text file /root/.my.cnf using text editor and add following line:

[client]
user=root
passowrd='[email protected]#'

6. Change the permission to make sure it is not viewable by others:

$ chmod 600 /root/.my.cnf

7. Login to the MySQL server by executing command “mysql” and execute following line. We will also need to create another root user called clusteroot with password as stated on variables above:

mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT USAGE ON *.* TO [email protected]'%' IDENTIFIED BY '[email protected]#';
mysql> UPDATE mysql.user SET Password=PASSWORD('[email protected]#') WHERE User='root';
mysql> GRANT USAGE ON *.* to [email protected]'%' IDENTIFIED BY 'sstpass123';
mysql> GRANT ALL PRIVILEGES on *.* to [email protected]'%';
mysql> GRANT USAGE on *.* to [email protected]'%' IDENTIFIED BY '[email protected]#';
mysql> GRANT ALL PRIVILEGES on *.* to [email protected]'%' ;
mysql> quit

8. Create the configuration files and directory, copy the example configuration and create mysql exclusion configuration file:

$ mkdir -p /etc/mysql/conf.d/
$ cp /usr/share/mysql/wsrep.cnf /etc/mysql/conf.d/
$ touch /etc/my.cnf
$ echo '!includedir /etc/mysql/conf.d/' >> /etc/my.cnf

9. Configure MySQL wsrep with Galera library. Open /etc/mysql/conf.d/wsrep.cnf using text editor and find and edit following line:

For galera1.cluster.local:

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_sst_auth=sst:sstpass123

For galera2.cluster.local:

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.0.171:4567"
wsrep_sst_auth=sst:sstpass123

10. Restart services in both servers:

$ service mysql restart

11. Check whether Galera replication is running fine:

$ mysql -e "show status like 'wsrep%'"

If the cluster is working, you should see following value in both servers:

wsrep_ready = ON

MySQL proxy servers

1. In both myproxy1 and myproxy2, we will start with installing virtual IP to make sure the MySQL proxy IP is always available. Lets download and install keepalived from here. OpenSSL header and popt library are required, so we will install it first using yum:

$ yum install -y openssl openssl-devel popt*
$ cd /usr/local/src
$ wget http://www.keepalived.org/software/keepalived-1.2.2.tar.gz
$ tar -xzf keepalived-1.2.2.tar.gz
$ cd keepalived-*
$ ./configure
$ make
$ make install

2. Since we have virtual IP which shared between these 2 servers, we need to tell kernel that we have a non-local IP to be bind to mysql proxy service later. Add following line into /etc/sysctl.conf:

net.ipv4.ip_nonlocal_bind = 1

Run following command to apply the changes:

$ sysctl -p

3. By default, keepalived configuration file will be setup under /usr/local/etc/keepalived/keepalived.conf. We will make things easier by symlink it into /etc directory. We will also clear the configuration example inside it:

$ ln -s /usr/local/etc/keepalived/keepalived.conf /etc/keepalived.conf
$ cat /dev/null > /etc/keepalived.conf

4. Download MySQL proxy at http://dev.mysql.com/downloads/mysql-proxy/ .We will setup MySQL proxy under /usr/local directory:

$ cd /usr/local
$ wget http://mysql.oss.eznetsols.org/Downloads/MySQL-Proxy/mysql-proxy-0.8.2-linux-rhel5-x86-64bit.tar.gz
$ tar -xzf mysql-proxy-0.8.2-linux-rhel5-x86-64bit.tar.gz
$ mv mysql-proxy-0.8.2-linux-rhel5-x86-64bit mysql-proxy
$ rm -Rf mysql-proxy-0.8.2-linux-rhel5-x86-64bit.tar.gz

5. This step is different in both servers for keepalived configuration.

For myproxy1, add following line into /etc/keepalived.conf:

vrrp_script chk_mysqlproxy {
        script "killall -0 mysql-proxy" # 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.0.170		# the virtual IP
        }
        track_script {
            chk_mysqlproxy
        }
}

 

For myproxy2, add following line into /etc/keepalived.conf:

vrrp_script chk_mysqlproxy {
        script "killall -0 mysql-proxy" # 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.0.170		# the virtual IP
        }
        track_script {
            chk_mysqlproxy
        }
}

6. Run following command to start MySQL proxy in both servers:

$ /usr/local/mysql-proxy/bin/mysql-proxy -D -P 0.0.0.0:3306 -b 192.168.0.171:3306 -b 192.168.0.172:3306

7. Now lets start keepalived and do some testing whether the IP failover is working. Run following command in both servers:

$ keepalived -f /etc/keepalived.conf

Ping IP 192.168.0.170 from another host. Now in myproxy1, stop the network:

$ service network stop

You will notice that the IP will be down for 2 seconds and then it will up again. It means that myproxy2 has taking over IP 192.168.0.170 from myproxy1. If you start back the network in myproxy1, you will noticed the same thing happen because myproxy1 will taking over back the IP from myproxy2, as what we configure in /etc/keepalived.conf. You can also try to kill mysql-proxy process and you will see the virtual IP will be take over again by myproxy2.

Now your MySQL is running in high availability mode. The MySQL client just need to access 192.168.0.170 as the MySQL database server host.

Linux: Install and Configure PostgreSQL with pgAdmin

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. People will usually comparing PostgreSQL with MySQL, and there are several key factors which let us choose PostgreSQL over MySQL:

  • Single storage engine – no hassle to choose which storage engine like MySQL
  • PostgreSQL aims for SQL standards compliance (the current standard is ANSI-SQL:2008)
  • High level of data integrity demanded by a serious transactional database application, the current generation PostgreSQL performs better than MySQL
  • Open source and run on GPL license – no licensing cost

I will be using standard CentOS 6 with and yum installer. Variable as below:

OS: Centos 6.2 64bit
Server IP: 192.168.0.1
Client IP: 192.168.0.100
Root user: postgres
Password: Bf44Ks#1

1. Install PostgreSQL using yum:

$ yum install -y postgresql*

2. Initialize the server database:

$ service postgresql initdb

3. By default, PostgreSQL will be installed under /var/lib/pgsql/data. Open the configuration file at /var/lib/pgsql/data/postgresql.conf using text editor and enable following line:

listen_addresses = '*'

4. Open client authentication configuration file at /var/lib/pgsql/data/pg_hba.conf to configure how user can access PostgreSQL via network or locally. User postgres is the super-user and I need to allow this user to be access from my Windows 7 laptop using pgAdminIII client. I need to add following line as below:

host    all    postgres    192.168.0.0/24       md5

Explanation: host = access via network, all = can access all database, postgres = user that I want to use to access, 192.168.0.0/24 =  network address which the user used to access this server, md5 = authentication method

5. Start and enable the PostgreSQL service:

$ service postgresql start
$ chkconfig postgresql on

6. Now we need to reset the user postgres password so we can use md5 authentication when connecting through client later. We need to change to user postgres environment by ‘su’ command and then access the PostgreSQL database:

$ su - postgres
-bash-4.1$ psql
psql (8.4.9)
Type "help" for help.
 
postgres=#

Then run following command:

postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'Bf44Ks#1';
postgres=# \q

After quit from psql, reload the server:

$ psql_ctl reload

7. Add following line into /etc/sysconfig/iptables to allow port 5432 for PostgreSQL communication using text editor. Make sure you put the line before any REJECT (-j REJECT) rules:

-A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT

Dont forget to restart IPtables:

$ service iptables restart

8. Now lets download and install pgAdminIII, a client which run on Windows for PostgreSQL database server at http://www.postgresql.org/ftp/pgadmin3/release/v1.8.4/win32/. Once downloaded, unzip the compress file and run the installer. Follow the wizard by accepting default value and complete the installation.

9. Lets connect to the server and run the pgAdminIII client. It usually located under Windows > All Programs > pgAdmin III 1.8 > pgAdmin III. Enter required information as screenshot below:

Done! You may now manage your PostgreSQL server remotely!

CentOS: Increase MySQL Uptime with MySQL Proxy

How can we achieve 100% MySQL uptime? For me, I would answer as follow:

  • Cluster all MySQL servers together
  • Load balance and failover between each of the cluster members
  • Make them run in redundant network line

I have tried to use Galera for active-active multi master replication in  previous post. This setup will surely help on scaling up the MySQL infrastructure that we have without headache. What I need the most is a loadbalancer and auto-failover in front of them. We can use a lot of reverse-proxy provider out there like HAProxy, Heartbeat and others but here I will use MySQL Proxy. Why? Because it require only 3 steps to make it run! I repeat, 3 steps!

Architecture that I will implement as follow:

Variables as below:

OS: CentOS 6 64bit
Server IP: 192.168.0.88

1. Download MySQL Proxy at http://dev.mysql.com/downloads/mysql-proxy/. I will download the “Red Hat & Oracle Linux 5 (x86, 64-bit), Compressed TAR Archive” version. I will run the MySQL proxy under /usr/local directory as below:

$ cd /usr/local
$ wget http://mysql.oss.eznetsols.org/Downloads/MySQL-Proxy/mysql-proxy-0.8.2-linux-rhel5-x86-64bit.tar.gz
$ tar -xzf mysql-proxy-0.8.2-linux-rhel5-x86-64bit.tar.gz
$ mv mysql-proxy-0.8.2-linux-rhel5-x86-64bit mysql-proxy

2. Open MySQL port in iptables:

$ service iptables start
$ iptables -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
$ service iptables save
$ service iptables restart

2. Lets start it! Run following command as root:

$ /usr/local/mysql-proxy/bin/mysql-proxy -P 0.0.0.0:3306 -b 192.168.0.91:3306 -b 192.168.0.92:3306 &

3. We just need to put above command (in step 2) to /etc/rc.local via text editor to make sure it started automatically after reboot.

To stop the mysql-proxy service, just kill the process by run following command:

$ kill -9 `pidof mysql-proxy`

Done! You can change your database information in your web server to the new IP, 192.168.0.88 (the mysql proxy server). Try to reboot MySQL DB1 and let web server make a SQL request and you will notice that your MySQL query is returnable by another server, MySQL DB2.

Create MySQL Database Backup Every Half an Hour

Our company has launched an online contest for our dedicated clients and we are collecting some really important information from them in order join the contest. My boss wants me to create a database backup every half an hour to make sure we reduce the data loss chance to the minimum possible.

I have create a simple BASH script to accomplish this. The script will create a MySQL database backup and specified in a folder based on date. We only stored 3 latest days backup and will remove other backup folder if exist.

1. Create the script in desired directory. In this case, I will use /home/scripts :

$ mkdir /home/scripts
$ touch /home/scripts/mysqlbackup_30min

2. Using your favourite text editor, paste following scripts into the script /home/scripts/mysqlbackup_30min :

#!/bin/bash
# Scripts to create mysql backup every half and hour
 
# Confiration value
mysql_host=""
mysql_database=""
mysql_username=""
mysql_password=''
backup_path=/backup/mysql
expired=3			#how many days before the backup directory will be removed
 
today=`date +%Y-%m-%d`
 
if [ ! -d $backup_path/$today ]
then
        mkdir -p $backup_path/$today
else
        /usr/bin/mysqldump -h $mysql_host -u $mysql_username -p$mysql_password $mysql_database > $backup_path/$today/$mysql_database-`date +%H%M`.sql
fi
 
# Remove folder which more than 3 days
find $backup_path -type d -mtime +$expired | xargs rm -Rf

3. Change configuration value to suit your environment. In this case I will information as below:

mysql_host="localhost"
mysql_database="contest_db"
mysql_username="contest_userw"
mysql_password='Okf8#$el'

4. Create a cron job to execute this task every half and hour. Open /var/spool/cron/root and add following line:

*/30 * * * * /bin/sh /home/scripts/mysqlbackup_30min

5. Restart cron daemon:

$ service crond restart

Done. You should see something like below after one day:

$ tree /backup/mysql
.
|-- 2011-11-14
|   |-- contest_db-1930.sql
|   |-- contest_db-2000.sql
|   |-- contest_db-2030.sql
|   |-- contest_db-2100.sql
|   |-- contest_db-2130.sql
|   |-- contest_db-2200.sql
|   |-- contest_db-2230.sql
|   |-- contest_db-2300.sql
|   `-- contest_db-2330.sql
`-- 2011-11-15
    |-- contest_db-0030.sql
    |-- contest_db-0100.sql
    |-- contest_db-0130.sql
    |-- contest_db-0200.sql
    |-- contest_db-0230.sql
    |-- contest_db-0300.sql
    |-- contest_db-0330.sql
    |-- contest_db-0400.sql
    |-- contest_db-0430.sql
    |-- contest_db-0500.sql
    |-- contest_db-0530.sql
    |-- contest_db-0600.sql
    |-- contest_db-0630.sql
    |-- contest_db-0700.sql
    |-- contest_db-0730.sql
    |-- contest_db-0800.sql
    |-- contest_db-0830.sql
    |-- contest_db-0900.sql
    |-- contest_db-0930.sql
    |-- contest_db-1000.sql
    |-- contest_db-1030.sql
    |-- contest_db-1100.sql
    |-- contest_db-1130.sql
    `-- contest_db-1200.sql

Resync MySQL Master/Slave Replication

I have encounter following error from the MySQL replication monitoring alert:

Error 'Duplicate entry '72264-4-10-2011' for key 1' on query. Default database: 'grad_data'. Query: 'INSERT INTO tblusr_log
                                          ( ID,UserType,Stats,Month,Year )
                                          VALUES
                                          ( '72264','4',1,MONTH(NOW()),YEAR(NOW()))'

This situation stopped the data replication process, which cause database in slave server fall behind and not syncing with the database at master server. There are several way to solve this issue:

Skip the error once and for all

1. Stop slave:

mysql> STOP SLAVE;

2. Add following line into /etc/my.cnf under [mysqld] directive and restart slave replication:

slave-skip-errors = 1062

3. Start slave:

mysql> START SLAVE;

Skip the error one by one

1. Run following command in mysql console to skip once:

mysql> STOP SLAVE; SET GLOBAL sql_slave_skip_counter = 1; START SLAVE;

2. Check again the mysql slave status:

mysql> SHOW SLAVE STATUS\G

If the error appear again, repeat step 1 and step 2 accordingly until the ‘Slave_SQL_Running‘ turn to ‘Yes’. You also can use following command to monitor the replication status via console or SSH:

$ watch -n1 'mysql -e "SHOW SLAVE STATUS\G"'

Re-sync back the database and re-start replication

Here is the recommended way. It safest and reliable enough to make sure all data are replicated correctly. This steps takes longer time but it worth to do. We will need to dump the database in master server, restore it in the new server and re-start the replication. Variable as below:

Master server IP: 192.168.11.2
Slave server IP: 192.168.11.3
Database name: grad_data
MySQL database user: grad_user
MySQL database password: [email protected]
MySQL slave user: slaver
MySQL slave password:  slaverp4ss

1. Dump the database in master server:

$ mysqldump -u grad_user -p"[email protected]" --master-data --single-transaction grad_data > /root/grad_data.master.sql

2. Copy it over to the slave server using scp:

$ scp /root/grad_data.master.sql root@192.168.11.3:/root

3. Recreate the database in slave server:

mysql> DROP DATABASE grad_data;
mysql> CREATE DATABASE grad_data;

4. Restore back the database in slave server:

$ mysql -u grad_user -p"[email protected]" grad_data < /root/grad_data.master.sql

5. Review the binlog file and position in the sqldump file. Open /root/grad_data.master.sql using text editor and find line similar to below:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=1017207641;

You need to take note the log_file name and log_pos number.

6. Restart the slave replication (make sure the MASTER_LOG_FILE and MASTER_LOG_POS in command below same as value in step 5):

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.11.2', MASTER_PORT=3306, MASTER_USER='slaver', MASTER_PASSWORD='slaverp4ss', MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=1017207641;
mysql> START SLAVE;

Verify slave status

Check the slave replication status:

 mysql> SHOW SLAVE STATUS\G

You should see something like below to indicate that your MySQL replication is running fine:

*************************** 1. row ***************************
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes

Linux: Email Alert on MySQL Replication Failure

In my environment, MySQL replication is really important because we are splitting different web servers with different database server to balance the load between MySQL servers. It quite traditional ways because this is kind of old database servers which sustain until today.

There is simple way to setup a monitoring script to alert us via email on any replication error. This script need to be run on slave server of MySQL replication.

Variable as below:

Database server: MySQL 5.0.77
Hostname: mysql.mydomain.org
Database host: localhost
Database name: mymarket_data
Database user: root
Database password: [email protected]

1. Create directory and file for the script:

$ mkdir -p /root/scripts
$ touch /root/scripts/check_slave.php

2. Copy and paste following PHP scripts and put it into a file called check_slave.php:

<?php
/**
 * Description: This script checks the slave status on the configured host
 *              printing "BAD <description>" or "OK <description>" for failure
 *              or success respectively.
 *              The possible failures could include:
 *              1) connection failure
 *              2) query failure (permissions, network, etc.)
 *              3) fetch failure (???)
 *              4) slave or io thread is not running
 *              5) Unknown master state (seconds_behind_master is null)
 *              6) seconds_behind_master has exceeded the configured threshold
 *
 *              If none of these condition occur, we asssume success and return
 *              an "OK" response, otherwise we include the error we can find
 *              (mysqli_connect_error() or $mysqli->error, or problem
 *               description).  A monitoring system need only check for:
 *              /^BAD/ (alert) or /^OK/ (everybody happy)
 */
 
/* **************************
 * Change related value below
 * **************************
 */
    $host = "";
    $user = "";
    $pass = "";
    $mailto = ""; // Your email address
    $mailsubject = "";
    $mailfrom = "";
 
/* ******************************************
 * No need to change anything below this line
 * ******************************************
 */ $mailmessage = "BAD: ".$err_msg."\n";
    $mailheaders = "From:" . $mailfrom;
    error_reporting(E_ALL);
    header("Content-Type: text/plain"); # Not HTML
    $sql = "SHOW SLAVE STATUS";
    $skip_file = 'skip_alerts';
    $link = mysql_connect($host, $user, $pass, null);
 
    if($link)
        $result = mysql_query($sql, $link);
    else {
        printf("BAD: Connection Failed %s", mysql_error());
        mysql_close($link);
        return;
    }
 
    if($result)
        $status = mysql_fetch_assoc($result);
    else {
        printf("BAD: Query failed - %s\n", mysql_error($link));
        mysql_close($link);
        return;
    }
 
    mysql_close($link);
 
    $slave_lag_threshold = 120;
 
    $tests = array(
        'test_slave_io_thread' => array('Slave_IO_Running', "\$var === 'Yes'",
                                        'Slave IO Thread is not running'),
        'test_slave_sql_thread' => array('Slave_SQL_Running', "\$var === 'Yes'",
                                        'Slave SQL Thread is not running'),
        'test_last_err' => array('Last_Errno', "\$var == 0",
                                 "Error encountered during replication - "
                                 .$status['Last_Error']),
        'test_master_status' => array('Seconds_Behind_Master', "isset(\$var)",
                                        'Unknown master status (Seconds_Behind_Master IS NULL)'),
        'test_slave_lag' => array('Seconds_Behind_Master',
                                  "\$var < \$slave_lag_threshold",
                                  "Slave is ${status['Seconds_Behind_Master']}s behind master (threshold=$slave_lag_threshold)")
    );
 
    $epic_fail = false;
    if(is_file($skip_file))
        $epic_fail = false;
    else
    {
        foreach($tests as $test_name => $data) {
            list($field, $expr, $err_msg) = $data;
            $var = $status[$field];
            $val = eval("return $expr;");
            if(!$val) {
                mail($mailto,$mailsubject,$mailmessage,$mailheaders);
                $epic_fail = true;
            }
        }
    }
 
    if(!$epic_fail) {
        print "OK: Checks all completed successfully\n";
    }
?>

3. Change related value on following line:

/* **************************
 * Change related value below
 * **************************
 */
    $host = "localhost";
    $user = "root";
    $pass = "[email protected]";
    $mailto = "[email protected]"; // Your email address
    $mailsubject = "mysql.mydomain.org Replication Alert";
    $mailfrom = "[email protected]";

4. Setup cron to execute the script every 15 minutes:

*/15 * * * * /usr/bin/php -q /root/scripts/check_slave.php

5. Restart cron service:

$ service crond restart

Done. You should receive notification email if the master/slave replication failed!

 

Update

Following script is being modified by Joel Brock as stated in comment section below:

<!--?php 
/**
* Description: This script checks the slave status on the configured host
* printing "BAD ” or “OK ” for failure
* or success respectively.
* The possible failures could include:
* 1) connection failure
* 2) query failure (permissions, network, etc.)
* 3) fetch failure (???)
* 4) slave or io thread is not running
* 5) Unknown master state (seconds_behind_master is null)
* 6) seconds_behind_master has exceeded the configured threshold
*
* If none of these condition occur, we asssume success and return
* an “OK” response, otherwise we include the error we can find
* (mysqli_connect_error() or $mysqli--->error, or problem
* description). A monitoring system need only check for:
* /^BAD/ (alert) or /^OK/ (everybody happy)
*/
 
/* **************************
* Change related value below
* **************************
*/
$host = array(
// “cr-1″ => “192.168.0.81″,
// “cr-2″ => “192.168.0.82″,
// “cr-3″ => “192.168.0.83″,
// “cr-4″ => “192.168.0.84″,
// “cr-5″ => “192.168.0.85″,
// “jp-1″ => “192.168.100.81″,
// “jp-2″ => “192.168.100.82″,
// “jp-3″ => “192.168.100.83″,
// “jp-4″ => “192.168.100.84″,
// “jp-5″ => “192.168.100.85″,
“cr-test” => “192.168.0.87″
);
$user = “”;
$pass = “”;
$mailto = “”;
$mailfrom = “”;
 
/* ******************************************
* No need to change anything below this line
* ******************************************
*/
 
error_reporting(E_ALL);
header(“Content-Type: text/plain”); # Not HTML
foreach ($host as $key => $value) {
 
$mailsubject =[".$key."] SLAVE REPLICATION ALERT”;
$mailheaders = “From:. $mailfrom;
$sql = “SHOW SLAVE STATUS”;
$skip_file = ‘skip_alerts’;
$link = mysql_connect($value, $user, $pass, null);
 
if($link)
    $result = mysql_query($sql, $link);
else {
    printf(“BAD: Connection Failed %s”, mysql_error());
    mysql_close($link);
    return;
}
 
if($result)
    $status = mysql_fetch_assoc($result);
else {
    printf(“BAD: Query failed – %s\n”, mysql_error($link));
    mysql_close($link);
    return;
}
 
mysql_close($link);
 
$slave_lag_threshold = 120;
 
$tests = array(
    ‘test_slave_io_thread’ => array(‘Slave_IO_Running’, “\$var === ‘Yes’”,
    ‘Slave IO Thread is not running’),
    ‘test_slave_sql_thread’ => array(‘Slave_SQL_Running’, “\$var === ‘Yes’”,
    ‘Slave SQL Thread is not running’),
    ‘test_last_err’ => array(‘Last_Errno’, “\$var == 0,
    “Error encountered during replication – ”
    .$status['Last_Error']),
    ‘test_master_status’ => array(‘Seconds_Behind_Master’,isset(\$var),
    ‘Unknown master status (Seconds_Behind_Master IS NULL)),
    ‘test_slave_lag’ => array(‘Seconds_Behind_Master’,
    “\$var $data) {
        list($field, $expr, $err_msg) = $data;
        $var = $status[$field];
        $val = eval(return $expr;);
        $val1 = (!$val) ? $val1 + 1 : $val11;
        $mailmessage .= “BAD:. $key . ” replication failed. Reason:. $err_msg . “\n”;
        }
    if ($val1 > 0) {
        mail($mailto,$mailsubject,$mailmessage,$mailheaders);
        print $mailmessage . “\n”;
        $epic_fail = true;
        }
    }
 
    if(!$epic_fail) {
        print “OK: Checks all completed successfully on [".$key."]\n”;
    }
}
?>

Manage Multiple MySQL Servers using PHPmyAdmin

In my environment, I have 5 different MySQL database servers running separately under different geographical location. Since it run standalone and not in cluster mode, I need to have one platform to manage these database servers altogether.

PHPmyAdmin is able to do this, with some changes on the configuration files. You just need to allow the MySQL user and host on every database server to be connected to. The setup that I am going to do will be as below:

Variables that I used as below:

Web Server: Apache 2.2.19
PHP: 5.3.2
Web Server IP: 212.77.103.146
PHPmyAdmin directory: /var/www/html/pma
User: pmaroot
Password: passdb432^^

1.  Download PHPmyAdmin PHP source at http://www.phpmyadmin.net/home_page/downloads.php . In this case, I will download this version phpMyAdmin-3.4.7-english.tar.gz. I assume you download the installer under /usr/local/src directory. We will need to rename the folder and paste it into directory that has been setup inside Apache to put PHPmyAdmin files:

$ cd /usr/local/src
$ tar -xzf phpMyAdmin-3.4.7-english.tar.gz
$ mv phpMyAdmin-3.4.7-english /var/www/html/pma

2.  Now lets create another root user just to manage databases using PHPmyAdmin. Execute following command in all MySQL database servers:

mysql> CREATE USER 'pmaroot'@'%' IDENTIFIED BY 'passdb432^^';
mysql> GRANT ALL PRIVILEGES ON *.* TO [email protected]'%';

3. Make sure all database servers are listening to all IP which accessible from outside. To simplify this, just remove or comment if you find following lines in your my.cnf file (usually located under /etc) :

#bind-address=127.0.0.1
#bind-address=localhost

4. To differentiate our MySQL servers easily, better we add the servers’ hostname into PHPmyAdmin server /etc/hosts file. Based on diagram above, I will add following line into the web server /etc/hosts:

china.mysql		118.144.76.16
usa.mysql		209.85.227.26
spain.mysql		84.236.148.11
singapore.mysql		202.156.14.10
colombia.mysql		190.0.39.34

5. So now we need to create PHPmyAdmin configuration files to include all databases server as above. Copy the configuration example as below to the active configuration file:

$ cd /var/www/html/pma
$ cp config.sample.inc.php config.inc.php

6. Open config.inc.php using text editor and add following value (actually you can put anything for the blowfish secret):

$cfg['blowfish_secret'] = 'youcanputanyphraseinsidethisquote'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

7. Inside this file you will also see following line:

/*
 * First server
 */
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

Copy those whole line for another 4 times and change the appropriate host value. Example as below:

/*
 * First server
 */
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'china.mysql';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = false;
 
/*
 * Second server
 */
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'usa.mysql';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = false;
 
/*
 * Third server
 */
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'spain.mysql';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = false;
 
/*
 * Fourth server
 */
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'singapore.mysql';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = false;
 
/*
 * Fifth server
 */
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'colombia.mysql';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

Done. Now I should be able to open the PHPmyAdmin via web browser at http://212.77.103.146/pma . I can select my MySQL servers I want to connect and access it using pmaroot user as created above. Screenshot as below:

Export MySQL Query Result to CSV

One of my developer team wants me to export some SQL query result and export it to CSV so they can import it to the local MySQL server. Since the database is quite big, around 2 million rows, dumping the whole table can really impact the database performance because he request this during peak hours.

After browse around in the Internet, I found the solution in StackOverflow forum. I share it here for the knowledge base. Variables as below:

Database server: MySQL 5.0
Database name: product_system
Table: tblproduct
Fields to be exported: ProductID and Name
Export to: tblproduct.csv
Username: root
Password:  dbserverpass123$

Following command should be run in SSH environment:

$ mysql -u root -p"dbserverpass123$"  product_system -B -e "select ProductID,Name from tblproduct;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > tblproduct.csv

After that, I copied over the csv into public_html folder so it can be downloaded by my developer team.

$ cp tblproduct.csv /home/user1/public_html

Done. Simple and easy!

MySQL: Recommended my.cnf Settings for InnoDB

Starting MySQL 5.5, InnoDB becomes the default storage engine replacing MyISAM. There are many performance improvements in this release. In particular, crash recovery, the automatic process that makes all data consistent when the database is restarted, is fast and reliable. (Now much much faster than long-time InnoDB users are used to.) The bigger the database, the more dramatic the speedup.

Tweaking and optimizing your MySQL database server is quite subjective, with a lot of conditions and variables need to consider. Following settings might help you improve and delivering the great InnoDB database by putting the specific value in my.cnf of your server:
Continue reading “MySQL: Recommended my.cnf Settings for InnoDB” »

PHP Driver: MySQL vs MySQLi vs PDO MySQL

In order for PHP to communicate well with database provider which is MySQL, we need to decide on which PHP “driver” that we should use. The term “driver” is not so correct but its fair enough to describe the MySQL connecter from PHP point-of-view.

mysql extension

  • Mysql extension is the original extension designed to allow you to develop PHP applications that interact with a MySQL database.
  • The mysql extension provides a procedural interface and is intended for use only with MySQL versions older than 4.1.3.
  • This extension can be used with versions of MySQL 4.1.3 or newer, but not all of the latest MySQL server features will be available.
  • Develop using PHP extension framework.
  • Mysql extension does not support Charsets, Prepared Statements and Stored Procedures.

mysqli extension

  • MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later.
  • Mysqli was faster perform faster queries result than PDO-mysql and mysql extension.
  • Developed using PHP extension framework.
  • Mysqli does support Charsets, Prepared Statements and Stored Procedures.
  • Server-side prepared statements which can increases the number of round-trips or memory usage.
  • If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.

PDO-mysql extension

  • PDO (PHP Data Objects) offer great security than other without much hassle, but for transition i would suggest you to move to mysqli since it faster, easier than PDO, and most api/syntax are quite same with the old mysql extension.
  • PDO allows you to use the same API calls for various databases. You can scale your application to use other databases with just a few code changes.
  • The PDO library has much of the security built in.
  • PDO will need to have different queries to return number of rows.
  • Cannot use /* */ commenting.
  • Server and client-side prepared statements.
  • Complete database abstraction layer.

Comparison

Features MySQL Improved Extension – ext/mysqli PDO Driver for MySQL – PDO_MYSQLND Classical MySQL Extension – ext/mysql
PHP version introduced 5.0 5.0 Before 3.0
Comes with PHP 5.x yes yes Yes
Comes with PHP 6.0 yes yes Yes
MySQL activities active development active development as of PHP 5.3 maintenance only
MySQL recommended for new projects Yes Yes No
API supports Charsets Yes Yes No
API supports server-side Prepared Statement Yes Yes No
API supports client-side Prepared Statement No Yes No
API supports Stored Procedures Yes Yes No
API supports Multiple Statement Yes Most No
Supports all of MySQL 4.1+ Yes Most No

Notes

The mysql extension, the mysqli extension and the PDO-mysql driver can each be individually configured to use either libmysql or mysqlnd. As mysqlnd is designed specifically to be utilised in the PHP system it has numerous memory and speed enhancements over libmysql.

So, when writing an application that is 100% guaranteed to always use MySQL, using mysql or mysqli extension will work better than PDO. But when we need the flexibility of a database abstraction layer, PDO will make your code much more solid and portable.

PDO offer great security than other without much hassle, it is highly recommened to use mysqli since it faster and easier than PDO, and most syntax are similar with the old mysql extension.

For list of supported connector in MySQL, you can refer this page:
http://www.mysql.com/products/connector/

Move MySQL Directory to Another Location

Lets say your MySQL data has fully utilized the disk space and you have another new hard disk specifically mounted for this purpose, you will need to migrate the data over to this new location. By default, MySQL will occupied following locations in the server:

Data -/var/lib/mysql
Config – /etc
Log -/var/log

We will not migrate data in /etc or /log because it usually not required much space. I will use following variables:

Server OS: CentOS 5.6 64bit
Old MySQL data: /var/lib/mysql
New MySQL data: /mnt/disk1/mysql

Warning: You MUST able to afford downtime on MySQL service to do this!

1. First of all, we need to make sure the service is stopped (if you are running cPanel, please disable MySQL monitoring under WHM > Service Manager > Untick ‘monitor’ for MySQL):

[[email protected] ~]# service mysql stop

2. Make sure there is no MySQL services running on the server:

[[email protected] ~]# ps aux | grep mysql | egrep -v 'grep'

3. Lets copy over the data directory. We are using rsync command so we can see the copy progress with estimate time remaining (this is really good for monitoring), and also preserve the permission and ownership of the MySQL directory:

[[email protected] ~]# rsync -avpP /var/lib/mysql /mnt/disk1/mysql

4. Now we need to tell MySQL to look at new data directory. Open /etc/my.cnf via text editor and change following server options:

[mysqld]
......
datadir=/mnt/disk1/mysql
socket=/mnt/disk1/mysql/mysql.sock
......

5. Now lets start back the MySQL with this new settings:

[[email protected] ~]# service mysql start

If everything are running back as expected, you can then delete the old /var/lib/mysql directory so you can save more free space.

MySQL General Security Guidelines

1. Do not ever give anyone (except MySQL root accounts) access to the user table in the mysql database! This is critical!

2. Learn the MySQL access privilege system. The GRANT and REVOKE statements are used for controlling access to MySQL. Do not grant more privileges than necessary. Never grant privileges to all hosts.
Checklist:

  • Try mysql -u root. If you are able to connect successfully to the server without being asked for a password, anyone can connect to your MySQL server as the MySQL root user with full privileges! Review the MySQL installation instructions, paying particular attention to the information about setting a root password.
  • Use the SHOW GRANTS statement to check which accounts have access to what. Then use the REVOKE statement to remove those privileges that are not necessary.
  • Do not store any plain-text passwords in your database. If your computer becomes compromised, the intruder can take the full list of passwords and use them. Instead, use MD5(), SHA1(), or some other one-way hashing function and store the hash value.
  • Do not choose passwords from dictionaries. Special programs exist to break passwords. Even passwords like “xfish98” are very bad. Much better is “duag98” which contains the same word “fish” but typed one key to the left on a standard QWERTY keyboard.
  • Another method is to use a password that is taken from the first characters of each word in a sentence (for example, “Mary had a little lamb” results in a password of “Mhall”). The password is easy to remember and type, but difficult to guess for someone who does not know the sentence.

3. Invest in a firewall. This protects you from at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in a demilitarized zone (DMZ).
Checklist:

  1. Try to scan your ports from the Internet using a tool such as nmap. MySQL uses port 3306 by default. This port should not be accessible from untrusted hosts. Another simple way to check whether or not your MySQL port is open is to try the following command from some remote machine, where server_host is the host name or IP address of the host on which your MySQL server runs:
    shell> telnet server_host 3306
  2. If you get a connection and some garbage characters, the port is open, and should be closed on your firewall or router, unless you really have a good reason to keep it open. If telnet hangs or the connection is refused, the port is blocked, which is how you want it to be.
  3. Do not trust any data entered by users of your applications. They can try to trick your code by entering special or escaped character sequences in Web forms, URLs, or whatever application you have built. Be sure that your application remains secure if a user enters something like
    "; DROP DATABASE mysql;"

    This is an extreme example, but large security leaks and data loss might occur as a result of hackers using similar techniques, if you do not prepare for them. A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as:

     SELECT * FROM table WHERE ID=234

    when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query:

    SELECT * FROM table WHERE ID=234 OR 1=1

    As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotation marks around the numeric constants:

    SELECT * FROM table WHERE ID='234'

    If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing non-numeric characters from it.

Continue reading “MySQL General Security Guidelines” »