I liked Shlomi Noach post on “IS TRUE and IS FALSE“.  This kind of logic create bugs.

What will this return?

select if((1 = true) and ( 1 is TRUE) and (NULL is not TRUE), TRUE, FALSE) as answer;

You should know,

NULL is never TRUE or FALSE but

NULL is always NOT TRUE or NOT FALSE.

NULL is a little like a vacuum.  Ancient Greek philosophers did not like to admit the existence of a vacuum, asking themselves “how can ‘nothing’ be something?”.    Does GOD exist in NULL?

If  select “” is NOT NULL; is TRUE what is “”?

select "" is NOT NULL;
+----------------+
| "" is NOT NULL |
+----------------+
|              1 |
+----------------+

Is the lack of an answer, an answer? Is the lack of datum, DATA?  Or should it be “”.  Should we record the blank spaces?

Given:

When should this happen?

for x = 1 to X;
INSERT INTO `test`(`data`) VALUES ( NULL);
next x;

Null happens.

In last weeks OurSQL postcast (episode 65)  Sheeri,  Sarah and Jerry talked about making MySQL safe with SSL.  Encryption always seems to be such a confusing subject. I think every database should be using SSL by default.  So, I was wondering just how easily SSL could be setup.

Most existing examples I found  setup SSL authentication and encryption.  If you are handling  PCI DSS or HIPAA data you must encrypt the data on the wire, but passwords are all you need to authenticate the application to the data source.

 # mkdir /etc/mysql/certs
 # cd /etc/mysql/certs

This looks complicated, it’s not.  JUST PRESS ENTER when openssl ask you a question.  This will not make you any less secure.  Your data will be encrypted.   Note these keys will expire in three years (1095 days).

 # openssl genrsa 2048 > ca-key.pem
 # openssl req -new -x509 -nodes -days 1095 -key ca-key.pem -out ca-cert.pem
 # openssl req -newkey rsa:2048 -days 1095 -nodes -keyout server-key.pem -out server-req.pem
 # openssl rsa -in server-key.pem -out server-key.pem
 # openssl x509 -req -in server-req.pem -days 1095 -CA ca-cert.pem -CAkey ca-key.pem \
 -set_serial 01 -out server-cert.pem

With the keys generated you need to tell MySQL to use them. Add these lines to your my.cnf.

 # vi /etc/my.cnf

 ssl
 ssl-cipher=DHE-RSA-AES256-SHA
 ssl-ca=/etc/mysql/certs/ca-cert.pem
 ssl-cert=/etc/mysql/certs/server-cert.pem
 ssl-key=/etc/mysql/certs/server-key.pem

Now restart mysql.

 # service mysql restart

You should see SSL is enabled and mysql sees the keys.

 mysql> show variables like '%ssl%';
 +---------------+----------------------------------+
 | Variable_name | Value                            |
 +---------------+----------------------------------+
 | have_openssl  | YES  |
 | have_ssl      | YES  |
 | ssl_ca        | /etc/mysql/certs/ca-cert.pem     |
 | ssl_capath    |                                  |
 | ssl_cert      | /etc/mysql/certs/server-cert.pem |
 | ssl_cipher    | DHE-RSA-AES256-SHA               |
 | ssl_key       | /etc/mysql/certs/server-key.pem  |
 +---------------+----------------------------------+
 7 rows in set (0.00 sec)

Duplicate these keys and the configuration segment to all your servers.

Client

If the client system is not a server, copy of the MySQL SSL keys to it was well.  Then, you need to tell the client to use SSL.  Edit your user’s .my.cnf file and give it the keys too.

 # vi ~/.my.cnf

[client]
 ssl
 ssl-cipher=DHE-RSA-AES256-SHA
 ssl-ca=/etc/mysql/certs/ca-cert.pem

Thats it.  It should be working.  Look for ‘Cipher in use’.

 # mysql -e "\s"

mysql Ver 14.14 Distrib 5.1.59, for unknown-linux-gnu (x86_64) using readline 5.1

 Connection id: 7
 Current database:
 Current user: root@localhost
 SSL: Cipher in use is DHE-RSA-AES256-SHA
 ......
 Threads: 1 Questions: 22 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 2.0

Replication

On the master, you need to tell the replication user to require SSL connections. Replace the rep_user with your replication user’s ID.

 mysql> GRANT USAGE ON *.* TO 'rep_user'@'%'  REQUIRE SSL;
 mysql> flush privileges;

and on the slave tell it to connect to the master with SSL.  Then make sure you are still connecting. Change the IP and user name, show here,  to your settings.

 mysql> stop slave;
 mysql> CHANGE MASTER TO master_host='192.168.1.12', master_user='rep_user', \
MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/certs/ca-cert.pem' ;
 mysql> start slave;
 mysql> show slave status;

Applications

Your application could be written in lots of different languages and I can’t go over each of them but here are some links to setup up SSL connection to MySQL in several of the most popular.  If you know where there are some better examples, please leave me a comment.

Python

Perl

JDBC

So you have inherited a MySQL server and no one know what applications are use what databases.   Space is every tight and every night transactions logs almost fill your hard disk.  The server was once used as a replication server and it seems every database the company uses is duplicated here.

You could do a full dump, drop everything and wait for the requests to restore. (This might be OK on a test database.)

We could ask MySQL for all the databases that have tables that have been updated in the last 30 days.

$ mysql information_schema -e  \
"select DISTINCT TABLE_SCHEMA as NAME from TABLES
  WHERE UPDATE_TIME IS NOT NULL and UPDATE_TIME > NOW() - INTERVAL 30 DAY"  \
> Databases

But this only gives a list of databases that have changed.  What about tables that are only read from?

Bin-Logs don’t help because they too only have updates.  What we really need is a long snapshot of the general log. But, starting the general log will just fill your disk space even more.

As long as all queries are remote we can use tcpdump.

Filter all the MySQL connections for  ‘FROM tablenames’.

$ tcpdump -i eth1 -s 0 -l -w - dst port 3306 | strings | \
 sed -n 's/\(.*\)\(from.*\)/\2/p' | awk '{print $2}' > TableList
 $ cat TableList | sort | uniq > ShortList

Some of the queries may reference databases.tablename so we can filter out the database names and add them to your Database list.

$ cat TableList | sed -n 's/\./ /p' | awk '{print $1}' | sort | uniq >> Databases

Now for the queries that only use table names. We need to find the database each belongs to.  This isn’t completely accurate because the table name may be in more then one databases.

$  for x in `cat TableList | sort | uniq` ;
 do
 mysql information_schema -e "select TABLE_SCHEMA from TABLES where TABLE_NAME='$x'\G"  \
 | grep TABLE | awk '{print $2}' ;
 done | sort | uniq >> Databases

To create the final list of database that should not be dropped.

$ sort Databases | uniq > KEEPTHESE ; cat KEEPTHESE

Now you can do the deed with some confidence your not removing anything being used.

P.S.  Did I tell you, Never do anything you can’t reverse.   Make a BACKUP FIRST!

Installing DRDB in CentOS 5.6.

In Part 1 I when through the process of preparing a number of CentOS 5.6 servers. Now make the services they’ll preform more stable.

High Availability (HA)

I’ll be presenting two ways to provide redundant data and high available services. First, Pacemaker – with DRDB will duplicate your data at the disk partition level and watch for failures. Should the hardware failure, Pacemaker will take all the needed steps to start MySQL on the Hot Stand By (HSB). This is not perfect. Should someone run ‘rm *’ or drop a database DRDB will duplicate the loss on the HSB.

In another part, I’ll use Tungsten replicator. It offers a set of features that surpass the built-in MySQL replicator. The community version of Tungsten has global transaction IDs so even with many slaves, global transactions IDs make turning a slave into the master easy. Tungsten replicator is not a HA service. You have to manually fail to a new master. Tungsten Enterprise (if you have money) solves all the issues. With the tools the enterprise version supplies you can easily migrate the “master” server to any slave.

HA with Pacemaker:

Neither RedHat or CentOS supply PaceMaker packages. Redhat support their own propitiatory clustering suite. CentOS is suck trying to maintain compatibility with Redhat while still giving you a high availability system. CentOS does supply heartbeat and openais but not pacemaker. Thankful Redhat helps out by supporting the Fedora project and in turn Fedora provide an EPEL repository for Redhat 5.

The Pacemaker packages in Fedora’s EPEL directories are built against some additional packages that don’t exist on vanilla RHEL/CentOS installs. For more information on EPEL, see http://fedoraproject.org/wiki/EPEL/FAQ So before installing Pacemaker, you will first need to tell the machine how to find the EPEL packages Pacemaker depends on. To do this, download and install the EPEL package that matches your RHEL/CentOS version.

LINBIT is the primary maintainer of DRBD and offers a product and service portfolio for exactly what we are building here. They have produced a video that takes you through this same process using the the DRDB Console Manager. I’m going to take you through the same process by hand. I hope this way you will better understand the management touch points.

DRBD Installation:

I build two computer alike in every way (clones) and use DRBD to sync the data partitions on each. CentOS provides the packages needed.

 # wget http://www.clusterlabs.org/rpm/epel-5/clusterlabs.repo  
 # mv clusterlabs.repo /etc/yum.repos.d  
 # rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-4.noarch.rpm  
 # yum -y install pacemaker.x86_64 heartbeat.x86_64 drbd83.x86_64 kmod-drbd83.x86_64
 # /sbin/chkconfig --add heartbeat  
 # depmod  
 # modprobe drbd

DRBD Configuration:

On both machines (db1 and db2), edit the DRBD configuration file. The host names must be the save as returned by ‘hostname’ command. You may also need to edit the host name in /etc/sysconfig/network. I have highlighted parts you will need to edit in red.

To replace the shared-secret, select at least half of the characters from the “63 random alpha-numeric characters” from Gibson Research Ultra High Security Passwords.

 # vi /etc/drbd.conf

 include "drbd.d/global.conf";
 include "drbd.d/*.res";

# vi /etc/drbd.d/global.conf

global { usage-count yes; }
common { startup { degr-wfc-timeout 0; }
net { cram-hmac-alg sha1; shared-secret R4x2alEkxtIg2kzbXqUL6l4uoTI7Ab7Qt; }
disk { on-io-error detach; } }

# hostname
 db1.grennan.com

# vi /etc/drbd.d/db.res

resource db { protocol C; syncer { rate 10M; }
on db1.grennan.com { device /dev/drbd0; disk /dev/md3; address 192.168.4.1:7788; flexible-meta-disk internal; }
on db2.grennan.com { device /dev/drbd0; disk /dev/md3; address 192.168.4.2:7788; flexible-meta-disk internal; } }

 # scp -r /etc/drbd.d db2:/etc  # scp -r /etc/drbd.conf db2:/etc

Manage DRDB processes:

If the disk was formatted during the OS install you may need to erase the ext3 file system info on both DB1 and DB2.

 # umount /data  # dd if=/dev/zero of=/dev/md3 count=2048

Write the DRBD meta data on both DB1 and DB2.

# drbdadm create-md db

On >>> DB1 only <<<

  # drbdadm adjust db 
  # drbdsetup /dev/drbd0 primary -o
  # service drbd start

On DB2

  # service drbd start

Did you miss the >>> DB1 only <<< above?   If you did you’ll need to re-run ‘drbdsetup /dev/drbd0 primary -o’ on DB1.

WAIT until the sync process completes.

Back on DB1 create the file system and mount it.

  # mkdir /data 
  # mkfs -j /dev/drbd0
  # tune2fs -c -1 -i 0 /dev/drbd0
  # mkdir /data
  # mount -o rw /dev/drbd0 /data

Don’t forget to make the /data directory on DB2.

  # mkdir /data/mysql
  # cat /proc/drbd

version: 8.3.8 (api:88/proto:86-94)
 GIT-hash: d78846e52224fd00562f7c225bcc25b2d422321d build by mockbuild@builder10.centos.org, 2010-06-04 08:04:09

1: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r----
 ns:2433920 nr:0 dw:0 dr:2433920 al:0 bm:148 lo:0 pe:0 ua:0 ap:0 ep:1 wo:b oos:28530088
 [==>.................] sync'ed: 21.9% (27860/30236)M delay_probe: 757
 finish: 4:57:11 speed: 1,248 (6,404) K/sec

In the next post I’ll go through installing MySQL in preparation for configuring Pacemaker. Then I’ll show you how to test fail over.

In the next part I will go over setting up MySQL.

PART 1 – This will be a multi part post.

THIS DOCUMENT IS BEING UPDATED – PLEASE WATCH FOR CHANGES!

After years of supporting MySQL, for many different companies, I’ve seen this story played out again and again.
The company:

• chooses a Database Management System (MySQL)
• installs the DBMS on a computer with other processes
• writes many programs to access the data (Without concern on how the queries are written.)
• moves DBMS to a computer of its own and writes more programs
• buy bigger computer to run the DBMS and writes more programs
• tires of DBMS response times and outages caused by developers working on production systems and hires a Database Administrator to fix the mess

This is a step by step description of  how I build a highly available, production MySQL servers. Like most things it life, these problems can be avoided with a little extra work at the start.

My first goal is to create a MySQL DBMS that with 99.999% up time. MySQL can distribute read requests but not writes. This make write service a single point of failure. To fix this you can either turn a slave server into the master or provide a hot standby to become the master.

My second goal is scalability. This is done by creating more read only slave servers. More slaves complicate turning one of them into the master should the master fail. The enterprise version Continuent’s Tungsten replicator fixes this. But, my goal is to do this on the cheep for start-ups while providing flexibility for the future. With DRBD and Pacemaker I can create a hot standby for the master server.

I have chosen these programs and utilities because they are free. Enterprise support is available for each.

Here is what I’m using:

• Scientific Linux 6.1
• DRBD disk replication for a hot standby
• MySQL 5.1.xx (Percona) or MeriaDB
• Heartbeat
• Pacemaker for failure recover to the hot standby
• Tunsten data replication
• MaaKit a toolkit for users, developers, and administrators of open-source databases
• OpenArk Kit – a set of utilities for MySQL
• xtrabackup for database backups
• mydumper for quick dumps
• MySQL Sandbox for testing
• mytop and innotop
• mysqlsniffer

I’ve worked hard to make this just cut and past. I enjoyed the work. I hope you do to.

Continue Reading »

I love short and consist install instructions. I know this is a MySQL blog but our good friend PostGreSQL has a great GIS library. This is what I learned upgrading our PostGIS system to GIS 1.5. Much thanks to Jeremy Tunnell for give this document it’s start.

Start with CentOS 5.6 x86_64 basic install.

Add the PostgreSQL Yum repository to your system.

 $ wget http://yum.pgrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm
 $ rpm -i pgdg-centos-9.0-2.noarch.rpm

Another location for these is DAG. I have to tried these so your results may very.

You will need to exclude the packages CentOS provide by added two lines to the BASE and UPDATE sections of /etc/yum.repos.d/CentOS-Base.repo. They are:

exclude=postgresql*
exclude=geos*

You you are ready to install the needed packages. This includes proj version 4 and geos version 3.

 $ yum install postgresql90-contrib.x86_64
 $ yum install postgis90.x86_64
 $ yum install postgresql90-server
 $ yum install proj
 $ yum install geos
 $ yum install php-pear
 $ yum install php-devel

GEOS

There may be some dependencies you will have to work through. If you are using PDO and haven’t installed the pgsql PDO drivers, you can do it now:

 $ pecl install pdo_pgsql

PL/pgSQL

You you are ready to initialize the database files and start postgresql.

 $ service postgresql-9.0 initdb
 $ service postgresql-9.0 start

Now you can change to the postgres user and begin installing the functions for GIS. You have to start with defining the language.

 $ su – postgres
 $ psql
 # create language plpgsql ;
 # \q

Now you can create your database and add the GIS functions calls to it.

 $ createdb geos
 $ psql -d geos -f /usr/pgsql-9.0/share/contrib/postgis-1.5/postgis.sql
 $ psql -d geos -f /usr/pgsql-9.0/share/contrib/postgis-1.5/spatial_ref_sys.sql

You you can verify the install.

 $ psql geos
 # select postgis_full_version();
                                              postgis_full_version
——————————————————————————————————–
 POSTGIS=”1.5.2″ GEOS=”3.2.2-CAPI-1.6.2″ PROJ=”Rel. 4.7.1, 23 September 2009″ LIBXML=”2.6.26″ USE_STATS
(1 row)

For more on using PostGIS check out Jeremy’s “PostGIS part 2”.

.

I spent my day doing updates to the automysqlbackup script.  Here is some of what I’ve added over the last year.

The bug number fixes are from SourceForge.  https://sourceforge.net/tracker/?atid=628964&group_id=101066&func=browse

# 2.5.5 MTG – (2011-07-21)
#    – Bug – Typo Ureadable Unreadable config file line 424 – ID: 3316825
#    – Bug – Change “#!/bin/bash” to “#!/usr/bin/env bash” – ID: 3292873
#    – Bug – problem with excludes – ID: 3169562
#    – Bug – Total disk space on symbolic links – ID: 3064547
#    – Added DEBUG option to only print the commands that will be executed.
#    – Bug – WHICH command didn’t work if there was a WHICH alias.
# VER 2.5.4: MTG – (2011-01-28)
#    – fixed bug in rsync process.
#    – Added the ability to backup only a single table by naming the table like
#      database.table
# VER 2.5.2-02:  MTG – (2010-12-29)
#    – Added file promission settings (chmod) to directory and file creation points.
# VER 2.5.2-01:  MTG – (2010-11-06)
#    – Added ‘-R’ to the mysqldump options to include stored procedures in the backup
#      by default. (suggested by Zack Evans)
# VER 2.5.2:  MTG – (2010-11-04)
#    – Added option to archive (rsync) the local backup files to a remote locations
#      using the COPYDIR varaible.
#    – Added option to copy files into a directory based on the hostname using the
#      variable HOSTNAME.  This allows the script to be run from a shared storage directory
#      ( SBM, NFS, NetApp) the data to be kept seperate places.
#    – Added option to backup all database schemas only using variable FULLSCHEMA.
#    – Added option to backup MySQL configuration file, my.cnf and remove files older then seven
#      days from the BACKUPDIR directory.
#    – Added –master-data=2 and –single-transaction to include a comment with the master server’s
#      the binary log coordinates. If used the CHANGE_MASTER_TO line must be uncommented.

The project is at https://sourceforge.net/projects/automysqlbackup/

You can download my version at http://www.mysqlfanboy.com/Files/automysqlbackup.sh

Email me or leave a comment if you have any trouble.