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!

Tweet

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.

Tweet

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:

• CentOS (Redhat) 5.6
• 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.

NOTE:  Type the GREEN stuff, cut and past the BLUE stuff and edit the RED stuff to fit you needs.

Getting started:

Every good system starts with good hardware. The two thinks database servers hunger for are disk space and memory. You should supply your self with as much as you can afford.  Most of my production system run on 32 gig of memory and RAID-10 systems.  Two network ports or more is recommended.  One network port will be used with a crossover cable for the heartbeat function.

To test this installation I’m building on a VMware server.  If you’d like to know more about this hardware read my “Building a Home VMware server” post.

Building the Operating System – DB1

The operating system I’m using is CentOS 5.6 64 bit.  You might choose Redhat 6.0. I’m being conservative and I’m trying to use free (I have no budget for this project) version of commercial products with enterprise support.

To help you understand the following instructions, for this example, I’m building a virtual machines (VM) with four (4) virtual SAS hard disks. I have split the four 15G virtual disks into four partitions.  The /boot and /tmp partitions are RAID-1 and the / (root) and /data partitions are RAID-5.  The sizes of these partitions depend you your needs but your /boot needs to be about 1G and the / needs to be about 12G.  I leave /data unassigned and un-formatted.  My layout looks like this.

Md0 - /boot    Md1 - /    Md2 – /tmp    Md3 - /data
Disk 0 - 15G    256M    4G    512M    11G
Disk 1 - 15G    256M    4G    512M    11G
Disk 2 - 15G    256M    4G    512M    11G
Disk 3 - 15G    256M    Checksum    512M    Checksum
Total    1G    12G    2G    33G

Install the OS:

Start with a “minimum installation” with as few applications installed as possible.   No desktop or server applications are needed.
The first step after the install is to update the installed packages.

# yum -y update

Even with a minimum install there is a little clean up.  I remove a few un-needed services like bluetooth, printing and there are a couple of packages we will need latter that where not installed.  It is better to install them now and avoid some dependency issues.  I remove supplied MySQL.

# rpm -e bluez-utils
# rpm -e smartmontools
# yum -y install ruby
# yum -y install perl-DBD-MySQL.x86_64
# yum -y install libdbi-dbd-mysql.x86_64
# rpm -e mysql --nodeps
# chkconfig iptables off
# chkconfig ip6tables off

After the OS is install and updated the disk looks like this.

# df

Filesystem 1K-blocks  Used Available Use% Mounted on

/dev/md2   11903664    1394068 9895160  13% / /dev/md3   29995056    176200  28270608 1%  /data /dev/md1   505508      10547   468862   3%  /tmp /dev/md0   256586      22969   220369   10% /boot tmpfs      2037380     0       2037380  0%  /dev/shm

Disable Security:

Because we move the MySQL data directy you will need to disable SELinux or update it.

To disable it, edit /etc/selinux/config and change the SELINUX line to SELINUX=disabled:

# vi /etc/selinux/config

SELINUX=disabled
# echo 0 >/selinux/enforce
# service iptables stop
# chkconfig iptables off
# service ip6tables stop
# chkconfig ip6tables off

If you don’t want to disable To update SELinux for the new data directory you will need to have the the selinux tools installed.

# yum -y install policycoreutils
# semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"
# restorecon -Rv /data/mysql

Network configuration:

# vi /etc/hosts

  192.168.2.22 db.grennan.com db
  192.168.2.23 db1.grennan.com db1
  192.168.2.24 db2.grennan.com db2
  192.168.2.25 db3.grennan.com db3

Syncing Time:

Time Singularization is very important to maintaining data. You may want to edit the /etc/ntp.conf file to point to your primary NTP time server. CentOS and Redhat provide time servers for your use. I recommend using pool.ntp.org.

# yum install ntp
# chkconfig ntpd on
# ntpdate 0.pool.ntp.org
# service ntpd start

Building DB2/3

DB2 will become the hot standby server (drbd / heartbeat) and DB3 will  become the MySQL slave server (Tungsten).

If, during the OS install, you created and formatted the /data partition you will need to remove it from /etc/fstab.  On both DB1 and DB2,  edit /etc/fstab and remove the /data file system.

# vi /etc/fstab

/dev/md2                /                       ext3    defaults        1 1
/dev/md3                /data                   ext3    defaults        1 2
/dev/md1                /tmp                    ext3    defaults        1 2
/dev/md0                /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP-sdd3         swap                    swap    defaults        0 0
LABEL=SWAP-sdc3         swap                    swap    defaults        0 0
LABEL=SWAP-sdb3         swap                    swap    defaults        0 0
LABEL=SWAP-sda3         swap                    swap    defaults        0 0

Setup SSH:

# ssh-keygen -t dsa -f ~/.ssh/id_dsa -N ""
# cp ~/.ssh/id_dsa.pub ~/.ssh/authorized_keys
# scp -r ~/.ssh db2:

root@db2's password:

  id_dsa.pub               100%  610     0.6KB/s   00:00  id_dsa                   100%  668     0.7KB/s   00:00  authorized_keys       100%  610     0.6KB/s   00:00  known_hosts              100%  398     0.4KB/s   00:00

Visualization

I can’t stand the color choices made for BASH so I set my own.

# vi ~/.bash_profile

export LS_COLORS='no=00:fi=00:di=00;33:ln=00;36:pi=40;33:so=00;35:bd=40;33; \
 01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00; \
 32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00; \
 31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00; \
 31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00; \
 35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:'

Tweet

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”.

.

Tweet

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.

Tweet

This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them.

When I was working for Dell doing Linux support my first words to a customer where “DO YOU HAVE COMPLETE AND VERIFIED BACKUP?” Make one now before you think about doing anything I suggest here.

You should always try to manage your data through a MySQL client.  If things have gone very bad this may not be possible. MySQL may not start. If your file system get corrupt you may have missing files. Sometimes people create other files in the MySQL directory (BAD).  This should help you understand what is safe to remove.

Before you try to work with one of these files make sure you have the file permissions set correctly.

This may not be a complete list of files used my MySQL.  It most certainly doesn’t describe everything each table is used for. If you know of ways to replace a missing file or what happens to MySQL when a file is missing that I haven’t described here, please leave me a comment or email me.  I’ll update this document and give your a reference.

my.cnf

This file alters the default configuration settings. MySQL looks in the /etc directory for my.cnf. You should review this file to insure you are looking in the right place for all other MySQL files.  MySQL WILL run without it.  If you have trouble getting MySQL to start, read the error log then try moving are renaming this file.

There are other places MySQL looks for configuration files.

• /etc/my.cnf

• /etc/mysql/my.cnf

• ~/my.cnf

• <$MYSQLHOME>/my.cnf

• ~/.my.cnf

MySQL will use only one configuration file if you use DEFAULTS-FILE = /etc/foo.bar.

mysql <directory>

This directory contains all your data.  This may not be its name.  On Linux servers the default location for MySQL files is /var/lib/mysql.  This directory is controlled by the “datadir” variable in your my.cnf file.

Do I need to say, deleting this directory deletes everything?

mysql/mysql<directory>

This directory is MySQL’s database and is inside the directory containing all your data (see above).  All the files in this directory are MyISAM tables for MySQL information like table and column privilages, users, functions and more.  MySQL will not run without this this database.

If you delete this directory, and all the files/tables within, you can recreate a blank set with the MySQL install utility.

mysql_install_db --user=mysql --datadir=/var/lib/mysql

After running this MySQL will rediscover any database that still exist (directory in the MySQL directory).  You will however have to recreate all users, permissions, functions, events, processes, and maybe more.

ibdata1

If your remove this file your InnoDB DATA IS GONE and MySQL will recreate an empty file.

If you are not using the innodb_file_per_table option (default), this file holds almost ALL of your data in InnoDB tables.  This file is all but useless without its corresponding  ‘.frm’ file for each table in the right database directory. If all you have is the .frm files you can recreate the structure of your tables. (See below.)

Idbdata1 can get really big. The default size is 10MB. MySQL will automatically extended it by the default size as needed. If MySQL crashed, some of your InnoDB data may be in your transaction logs (ib_logfile.*).

By design the InnoDB file does not shrink. The safest way to shrink this file is to take a complete backup, stop MySQL, remove the ib* files, start MySQL and restore all your data.  REALLY.

DatabaseName <directory in mysql>

Each MySQL database has a directory named after it.  Each directory holds the meta data for the database. Your MyISAM data is in this directory. InnoDB tables may be here if the innodb_file_per_table variable is used. By default InnoDB tables are in the ibdata1 file (see above).

If you delete the directory your data may be gone. MyISAM data WILL be lost.  InnoDB tables may still be in the ibdata1.  If so, you will need to recreate the meta data files to recover your data.

Creating a directory is almost equivalent to ‘create database’.  If a directory exists MySQL will show you have a database by that name. The create database command may also creates a db.opt file.

<TableName>.frm

This file is key to both InnoDB and MyISAM databases. It is the meta data to the location of your data. It contains the table column definitions.

If you remove this file MySQL will tell you your DATA doesn’t exist.  It does. Your data is still in the ibdata (.ibd) file or the ibdata1 file. You need to recreate the table to recreate this file. If you don’t know the exact structure of this table your out of luck.

Stop MySQL and move the .MYD and .MYI files to another directory.  (You might also make a backup copy.) Start MySQL and recreate this table. Stop MySQL and copy the .MYD and .MYI files back to the database directory and restart MySQL.

<TableName>.MYD

THIS IS YOUR MyISAM DATA. If this is all you have, and you know the data structure of the the table, all is not lost.  (See .frm Above.)  You may also need to recreate the .MYI index file.

<TableName>.MYI

This file contains the indexs for your table.  If it becomes corrupt or is deleted you can recreate it using the ‘REPAIR TABLE table_name USE_FRM;’ command.

/tmp/#<TableName>.[ frm MYD MYI ]

MySQL creates temporary files in the /tmp director (C:\windows\temp) when sorting (ORDER BY or GROUP BY).  It also creates temp files on a replication server when  the LOAD DATA LOCAL INFILE… command is used. Replication will fail if these files are removed.

<TableName>.ibd

THIS IS YOUR InnoDB DATA. If this is all you have, and you know the data structure of the the table, all is not lost.  (See .frm Above.) Unlike MyISAM tables the indexes are contained in this file with your data.

MySQL doesn’t create this file unless you are using the innodb_file_per_table option.

If you have a “Clean” .ibd file you can import it into a database with the command “ALTER TABLE tbl_name IMPORT TABLESPACE;”

<TableName>.CSV

THIS IS YOUR CSV DATA.  This file contains comma separated text data. These file do not have indexes.

<TableName>.CSM

This file contains meta data for CSV and archive tables.  I have not found what is stored here. I do know it tells MySQL if you are logging to the general logs.

ib_logfile*

This file contains your un-committed transactions data. MySQL uses it to recover from a crash.

If you shut down InnoDB cleanly, you can remove them. MySQL will recreate them.

If you change the size of innodb_log_file_size, you will need to recreate these files by stopping MySQL cleanly and deleting them.

mysql-bin.*

This “Bin Log” files contain any change made to any database. Each transaction is assigned a MASTER_LOG_POS(ion).  These files are not created by default. They are used for replication and point-in-time recovery.

You can stop the server and remove these files IF you remove the mysql-bin.index file as well.  MySQL creates a new bin log file each time it starts or the logs are flushed.  Deleting these files will the server is running will break replication.

mysql-bin.index

This file is used by MySQL to keep a Bin Log list.  It is a simple text file like;

./mysql-bin.000001

./mysql-bin.000002

./mysql-bin.000003

If you remove this file, MySQL will recreate it with only the newest bin log name. If you need to remove old bin logs use the command “purge binary logs [to mysql-bin.######] [before “yyyy-mm-dd”]”.

You can control the number of bin logs using the expire_logs_days variable.

mysqld.log

This is MySQL’s primary administration log. MySQL reports starts and stops as well as some warning and errors in this file.  If MySQL crashes, mysqld_safe will restart it. This log will report this.

You can delete this file if needed.

slow.log

The slow query log consists of all SQL statements that took more than long_query_time seconds to execute and (as of MySQL 5.1.21) required at least min_examined_row_limit rows to be examined.

You can delete this file if needed.

db.opt

Database characteristics, like the CHARACTER SET clause are stored in the db.opt file. You may have strange query results if this file is missing. MySQL will use it’s default. You can recreate this file my altering the table with the correct settings.

mysql.pid

The PID file hold the process ID number for the running server. MySQL creates this file and scripts that start and stop MySQL use it to control MySQL.

MySQL will remove this file when is stops. You should not delete it if MySQL is running. If mysql is NOT running and the file exists MySQL may have crashed and you should delete this file.

master.info
relay-log.info

These are found only on the slave servers. Slaves read the commands from those files in order to replicate what the master has done. Simularly to the bin log, it will create files with numbered extensions as needed.

Master.info is a simple text file. This is a store of the master command is used by mysql when it restarts to make the slave connection.

15
mysql-bin.006004
1006945586
192.168.1.2
repl_user
P@sSw0Rd
3306
60
0

Removing these files will breat replication and / or through your slave out of sync.

*relay-bin.index

The “*” is either the hostname or the same as the .PID file.  This file is the index for the relay log used in replication.  Relay bin files are found only on slave servers.

<TableName>.TRG

Contains the list of triggers belonging to a given table

<TrigerName>.TRN

Contains some metadata on the trigger

<TableName>.ARZ

It’s the file containing the data for an ARCHIVE table.

<TableName>.par

Contains the definition of a partition

tablename#P#partitionname.{MYD,ARZ,ibd}
contains the data related to a given partition

References:

FULL DISK
http://dev.mysql.com/doc/refman/5.1/en/full-disk.html

MySQL Database Backup .MYI and .MYD
http://www.aeonity.com/frost/mysql-database-backup-myi-myd

Recovering from Crashes
http://dev.mysql.com/tech-resources/articles/recovering-from-crashes.html

Using Per-Table Tablespaces

http://dev.mysql.com/doc/refman/5.0/en/innodb-multiple-tablespaces.html

Troubleshooting InnoDB Data Dictionary Operations
http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html

Giuseppe Maxia – who writes a great blog about mysql at http://datacharmer.blogspot.com/ contributed several files I missed.

Tweet

I have updated AutoMySQLBackup again. This time I have included the ability to make backups at the table level by including the table name, with the database name, in the list of databases to be backed up.  Adding a ‘.’ between the database name and the table name (data.table) will backup only that table in the database.  (IE DBNAME=”mysql test database.table”)

You can download my version here.  automysqlbackup.sh.

Some of my improvements include:

• copy (rsync) the local backup files to a remote locations using the COPYDIR variable.
• option to copy files into a directory based on the host name using the variable HOSTNAME.  This allows the script to be run from a shared storage directory ( SBM, NFS, NetApp) the data to be kept separate.
• option to backup the full schema only using variable FULLSCHEMA.
• option to backup MySQL configuration file, my.cnf
• remove files older then seven days from the BACKUPDIR directory.
• Added –master-data=2 and –single-transaction to include a replication information as a comment
• The ability to backup a single table in a database.

Tweet

This post started with a simple question: “Does the function NOW() get executed every time a row is examined?” According to the manual,  “Functions that return the current date or time each are evaluated only once per query …. multiple references to a function such as NOW() … produce the same result. …. (However,) as of MySQL 5.0.12, SYSDATE() returns the time (the row is) executes. “

• CURDATE() returns the current date.
• CURTIME() returns the current time.
• UTC_DATE() returns the current UTC date.
• UTC_TIME() returns the current UTC time.
• NOW() return the current date and time.
• UTC_TIMESTAMP() returns the current UTC date and time.
• SYSDATE() returns the date and time at which the function executes.
• LOCALTIME(), LOCALTIME(), LOCALTIMESTAMP(), LOCALTIMESTAMP() are synonyms for NOW().

All these functions return some value of a date / time. For example, WEEK() returns the week number for date.

Here is an example of a common query.

SELECT  stn, temp, lat, lon WHERE valid_time > NOW() – INTERVAL 1 HOUR;

This is the right way to make this query because NOW() is only executed once.  If SYSDATE() was used, each row selected would call SYSDATE().

So, if NOW() is only executed once and SYSDATE() is executed every time, we should see this difference if we BENCHMARK these functions.

mysql> SELECT BENCHMARK( 10000000, NOW() );
+------------------------------+
| BENCHMARK( 10000000, NOW() ) |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.07 sec)

+----------------------------------+
| BENCHMARK( 10000000, SYSDATE() ) |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (5.49 sec)

And it seems to be true. But what about real SQL statements. Will this hold up? Here is a simple table.

CREATE TABLE `TableName1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1

I used this SQL statement to put records in the table. It adds a new records with a new date/time with each query. Like:

INSERT INTO `stage`.`TableName1`(`id`,`dtime`) VALUES ( NULL,NOW())

What if we then query the table every second. Will NOW() be processed as a part of the results?  I used the SLEEP() function to delay each record.

SELECT id, dtime, (SELECT NOW()) FROM TableName1 WHERE NOT SLEEP(1);

This query took ten seconds to run for a table with ten rows and returns these results.

id	dtime	                (SELECT NOW())
 1	2010-08-06 15:55:57	2010-08-06 16:05:06
 2	2010-08-06 15:56:12	2010-08-06 16:05:06
 3	2010-08-06 15:56:16	2010-08-06 16:05:06
 4	2010-08-06 15:56:20	2010-08-06 16:05:06
 5	2010-08-06 15:56:30	2010-08-06 16:05:06
 6	2010-08-06 16:48:23	2010-08-06 16:05:06
 7	2010-08-06 16:48:26	2010-08-06 16:05:06
 8	2010-08-06 16:48:27	2010-08-06 16:05:06
 9	2010-08-06 16:48:28	2010-08-06 16:05:06
 10	2010-08-06 16:48:29	2010-08-06 16:05:06

As the documentation states, the NOW() is NOT processes every time.

What if  NOW() is a part of a JOIN?

SELECT t.id, t.dtime, d.n  FROM TableName1 AS t, (SELECT 1, NOW() AS n) AS d  WHERE NOT SLEEP(1);

Again, this SELECT takes ten seconds to run for a table with ten rows.

id	dtime                 	n
 1	2010-08-06 15:55:57	2010-08-06 17:09:00
 2	2010-08-06 15:56:12	2010-08-06 17:09:00
 3	2010-08-06 15:56:16	2010-08-06 17:09:00
 4	2010-08-06 15:56:20	2010-08-06 17:09:00
 5	2010-08-06 15:56:30	2010-08-06 17:09:00
 6	2010-08-06 16:48:23	2010-08-06 17:09:00
 7	2010-08-06 16:48:26	2010-08-06 17:09:00
 8	2010-08-06 16:48:27	2010-08-06 17:09:00
 9	2010-08-06 16:48:28	2010-08-06 17:09:00
 10	2010-08-06 16:48:29	2010-08-06 17:09:00

And again the NOW() function is only executed once.

The WHERE clause is harder to test. I inserted a row in the table only seconds apart. I then quickly ran the SELECT again to see if the NOW() matched the times in the table.

SELECT t.id, t.dtime FROM TableName1 AS t WHERE NOT SLEEP(1) AND dtime = (SELECT NOW() - INTERVAL 15 SECOND)  ;

Here is the data table.

id	dtime
 1	2010-08-06 17:35:00
 2	2010-08-06 17:35:00
 3	2010-08-06 17:35:01
 4	2010-08-06 17:35:03
 5	2010-08-06 17:35:04
 6	2010-08-06 17:35:04
 7	2010-08-06 17:35:04
 8	2010-08-06 17:35:05
 9	2010-08-06 17:35:05
 10	2010-08-06 17:35:05
 11	2010-08-06 17:35:06
 12	2010-08-06 17:35:06
 13	2010-08-06 17:35:06
 14	2010-08-06 17:35:07
 15	2010-08-06 17:35:07
 16	2010-08-06 17:35:07
 17	2010-08-06 17:35:08
 18	2010-08-06 17:35:08
 19	2010-08-06 17:35:08
 20	2010-08-06 17:35:09
 21	2010-08-06 17:35:09
 22	2010-08-06 17:35:09
 23	2010-08-06 17:35:09
 24	2010-08-06 17:35:10
 25	2010-08-06 17:35:10

The select statement above only took three seconds to run and returned three rows all the same.

 id	dtime
 11	2010-08-06 17:35:06
 12	2010-08-06 17:35:06
 13	2010-08-06 17:35:06

MySQL is evaluating dtime = (SELECT NOW() – INTERVAL 15 SECOND) before the SLEEP(1).  MySQL only ran the SLEEP(1) for each record that matched.  I reversed the two parts of the AND and  the order of the didn’t matter.

Next I tried these functions with SYSDATE().  I found it is executed every time it is evaluated.  This SELECT took 17 seconds and return a different time for each.

SELECT id, dtime, (SELECT SYSDATE()) FROM TableName1 WHERE NOT SLEEP(1);

id    dtime                 (SELECT SYSDATE())
1    2010-08-06 18:28:42    2010-08-06 19:00:57
2    2010-08-06 18:28:42    2010-08-06 19:00:58
3    2010-08-06 18:28:42    2010-08-06 19:00:59
4    2010-08-06 18:28:43    2010-08-06 19:01:00
5    2010-08-06 18:28:43    2010-08-06 19:01:01
6    2010-08-06 18:28:43    2010-08-06 19:01:02
7    2010-08-06 18:28:43    2010-08-06 19:01:03
8    2010-08-06 18:28:44    2010-08-06 19:01:04
9    2010-08-06 18:28:44    2010-08-06 19:01:05
10    2010-08-06 18:28:44    2010-08-06 19:01:06
11    2010-08-06 18:28:44    2010-08-06 19:01:07
12    2010-08-06 18:28:45    2010-08-06 19:01:08
13    2010-08-06 18:28:45    2010-08-06 19:01:09
14    2010-08-06 18:28:45    2010-08-06 19:01:10
15    2010-08-06 18:28:45    2010-08-06 19:01:11
16    2010-08-06 18:28:46    2010-08-06 19:01:12
17    2010-08-06 18:28:46    2010-08-06 19:01:13

The SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE().

CACHING

MySQL doesn’t cache nondeterministic functions like NOW() and SYSDATE().  If your application processes the same data base queries within the same second or if you need the two queries to return the same results, you will need to turn NOW() into a string.

SELECT stn, temp, lat, lon WHERE valid_time > ‘2010-05-06 23:20:00’ – INTERVAL 1 HOUR;

Even better, do the math in your program.  Here are some benchmarks show this outcome.

SELECT stn, temp, lat, lon WHERE valid_time > ‘2010-05-06 22:20:00’;

+------------------------------------------------------------------------+
| BENCHMARK( 10000000, NOW() -  INTERVAL 1 HOUR = '2011-01-27 19:00:00') |
+------------------------------------------------------------------------+
|                                                                      0 |
+------------------------------------------------------------------------+
1 row in set (0.36 sec)

mysql> SELECT BENCHMARK( 10000000, SYSDATE() -  INTERVAL 1 HOUR = '2011-01-27 19:00:00');
+----------------------------------------------------------------------------+
| BENCHMARK( 10000000, SYSDATE() -  INTERVAL 1 HOUR = '2011-01-27 19:00:00') |
+----------------------------------------------------------------------------+
|                                                                          0 |
+----------------------------------------------------------------------------+
1 row in set (2.42 sec)

mysql> SELECT BENCHMARK( 10000000, '2010-05-06 23:20:00' -  INTERVAL 1 HOUR = '2011-01-27 19:00:00');
+----------------------------------------------------------------------------------------+
| BENCHMARK( 10000000, '2010-05-06 23:20:00' -  INTERVAL 1 HOUR = '2011-01-27 19:00:00') |
+----------------------------------------------------------------------------------------+
|                                                                                      0 |
+----------------------------------------------------------------------------------------+
1 row in set (3.98 sec)

mysql> SELECT BENCHMARK( 10000000, '2010-05-06 23:20:00' = '2011-01-27 19:00:00');
+---------------------------------------------------------------------+
| BENCHMARK( 10000000, '2010-05-06 23:20:00' = '2011-01-27 19:00:00') |
+---------------------------------------------------------------------+
|                                                                   0 |
+---------------------------------------------------------------------+
1 row in set (0.22 sec)

REFERENCE:

http://dev.mysql.com/doc/refman/5.0/en/functions.html

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Tweet

Justin Swanhart points out HandlerSocket currently lacks atomic operations . Since HandlerSocket uses different connections for reading and writing, you can’t increment/decrement a value without creating a race condition.

Still, the idea of skipping SQL interpretation and just reading the data you know you want is a great one.  Writing data might even be better. But being able to use both SQL and NoSQL could be really wonderful.  What if we could use complex queries to update complex tables and pluck values out as needed.  For example, queries to analyze current weather conditions and produce forecasts that we could then retrieve via a location key? What about updating current condition data via NoSQL before running the analysis?  Will this work?

NoSQL writes (insert/update/delete) and SQL reads

Using the PHP test program from my last post, I’ve tried inserting and updating data via NoSQL and reading it back via SQL.  What I found was not good.  NoSQL is consistent.  If I write data via NoSQL I always get back my data via NoSQL.  However, if I try to write data via NoSQL and read it with a  simple ‘Select * from user;’  I get inconsistent results.

There is what the record looked like.

MariaDB [test]> select * from user where user_id = 1;
+---------+-----------+-----------------------+---------------------+
| user_id | user_name | user_email            | created             |
+---------+-----------+-----------------------+---------------------+
|       1 | mark      |                       | 0000-00-00 00:00:00 |
+---------+-----------+-----------------------+---------------------+

Here is the code segment I used to update  the table. Review my previous posts for more details.

$hs2 = new HandlerSocket($host, $wport);
if (!($hs2->openIndex(1, $dbname, $table, '', 'user_id,user_name,user_email,created')))
{
 echo $hs2->getError(), PHP_EOL;
 die();
}

if (!($hs2->executeUpdate(1, '=', array('1'), array('1',"Mark",'mark@www.mysqlfanboy.com'), 1, 0)))
{
 echo $hs2->getError(), PHP_EOL;
 die();
}

A quick query via NoSQL shows the data has been updated. The ‘mark’ is now ‘Mark’ and the email address is there.

# php mytest.php
data > Mark
array(1) {
 [0]=>
 array(4) {
 [0]=>
 string(1) "1"
 [1]=>
 string(4) "Mark"
 [2]=>
 string(20) "mark@www.mysqlfanboy.com"
 [3]=>
 string(19) "0000-00-00 00:00:00"
 }
}

I found MySQL SQL queries did not return my changes.  I tried many times to write new and updated records without SQL show the results. I thought this was because of query caching so I tried flushing the query cache without effect.  I was worried the date was buffered somewhere but not written to disk.  I restarted MySQL and the data was corrected.

# mysql test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 26457
Server version: 5.1.51-MariaDB-mariadb87-log (MariaDB - http://mariadb.com/)

This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> select * from user;
+---------+-----------+-----------------------+---------------------+
| user_id | user_name | user_email            | created             |
+---------+-----------+-----------------------+---------------------+
|       1 | mark      |                       | 0000-00-00 00:00:00 |
|       2 | linda     | linda@www.mysqlfanboy.com | 0000-00-00 00:00:00 |
|       3 | mark      | test@www.mysqlfanboy.com  | NULL                |
|       4 | test      | test@www.mysqlfanboy.com  | 0000-00-00 00:00:00 |
|       5 | foo       | foo@www.mysqlfanboy.com   | 0000-00-00 00:00:00 |
+---------+-----------+-----------------------+---------------------+
5 rows in set (0.00 sec)

MariaDB [test]> FLUSH QUERY CACHE;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> select * from user;
+---------+-----------+-----------------------+---------------------+
| user_id | user_name | user_email            | created             |
+---------+-----------+-----------------------+---------------------+
|       1 | mark      |                       | 0000-00-00 00:00:00 |
|       2 | linda     | linda@www.mysqlfanboy.com | 0000-00-00 00:00:00 |
|       3 | mark      | test@www.mysqlfanboy.com  | NULL                |
|       4 | test      | test@www.mysqlfanboy.com  | 0000-00-00 00:00:00 |
|       5 | foo       | foo@www.mysqlfanboy.com   | 0000-00-00 00:00:00 |
+---------+-----------+-----------------------+---------------------+
5 rows in set (0.00 sec)

MariaDB [test]> quit
Bye
# service mysql stop
Shutting down MySQL.                                       [  OK  ]
# service mysql start
Starting MySQL.                                            [  OK  ]
# mysql test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 5.1.51-MariaDB-mariadb87-log (MariaDB - http://mariadb.com/)

This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> select * from user;
+---------+-----------+-----------------------+---------------------+
| user_id | user_name | user_email            | created             |
+---------+-----------+-----------------------+---------------------+
|       1 | Mark      | mark@www.mysqlfanboy.com  | 0000-00-00 00:00:00 |
|       2 | linda     | linda@www.mysqlfanboy.com | 0000-00-00 00:00:00 |
|       3 | mark      | test@www.mysqlfanboy.com  | NULL                |
|       4 | test      | test@www.mysqlfanboy.com  | 0000-00-00 00:00:00 |
|       5 | foo       | foo@www.mysqlfanboy.com   | 0000-00-00 00:00:00 |
+---------+-----------+-----------------------+---------------------+
5 rows in set (0.00 sec)

MySQL writes and NoSQL reads

Writing data via MySQL and reading via NoSQL works.  I found no issues with this process.  This leads me to believe the issue is related to write buffering and not query caching.

Security

What security.  HandleSocket gives you direct access to EVERY table.  With HandleSocket you can update anything including MySQL security tables.  You would be crazy to use HandleSocket in a public facing system.  Even if you using it in back office process you should use some sort of filewall technology like iptables to limit commectivity.

I’ll say this again: HANDLESOCKET HAS NO SECURITY!

Tweet

In my last two posts I installed the HandlerSocket plugin into MariaDB and showed how to use it with Perl.  That’s good, but if you are thinking of using HandlerSocket  I’m guessing you have a very high traffic website and it’s written in PHP.  In this post I’m going to connect HandlerSocket with PHP.  In the next post I’ll discuss using HandlerSocket on a production system.

There are a couple of HandlerSocket php modules projects.  I tried each of them and I found PHP-HandlerSocket was the best.  Both of them are still rough and neither of them have documentation beyond their source code.  Maybe this will move things forward.

Here are the applications you need to have installed that where not installed in my last two posts.  Run this to check your system.

 yum install php-devel re2c php php-mysql

Start in the a Downloads directory and wget the newest tarball.

 cd ~
 mkdir Downloads
 cd Downloads
 wget https://php-handlersocket.googlecode.com/files/php-handlersocket-0.3.0.tar.gz
 tar zxf  php-handlersocket-0.2.0.tar.gz

Compile and install the module.  You may need to install php and php-devel .

 cd php-handlersocket
 phpize
 ./configure
 make
 make test
 make install

In the ‘make test’ step you are looking for Tests passed    :    1 (100.0%) (100.0%)

With everything in place, you need to teach PHP to load the module my editing the PHP configuration file  and add the extension.

 vi /etc/php.ini

Search for the Dynamic Extensions section and add this line.

 extension=handlersocket.so

Run php to test that the module is loading correctly.  If nothing happens, your good.

I had some trouble at the start.   This is bad:

 # php
 PHP Warning:  PHP Startup: Unable to load dynamic library  '/usr/lib64/php/modules/hadlersocket.so'
 -  /usr/lib64/php/modules/hadlersocket.so: cannot open shared object file:
  No such file or directory in Unknown on line 0

To do more testing we need a database and some data.  Here is what I created. I’ve colored the sections of code as they correspond to the output.

CREATE TABLE `user` (
 `user_id` INT (10) UNSIGNED NOT NULL,
 `user_name` VARCHAR (50),
 `user_email` VARCHAR (255),
 `created` DATETIME DEFAULT NULL,
 PRIMARY KEY (`user_id`),
 KEY `NAME` (`user_name`)
 ) ENGINE = INNODB ;

INSERT INTO `user`  VALUES
 ( 1, 'mark', 'mark@www.mysqlfanboy.com', '0000-00-00 00:00:00' ),
 ( 2, 'linda', 'linda@www.mysqlfanboy.com', '0000-00-00 00:00:00' ),
 ( 3, 'mark', 'test@www.mysqlfanboy.com', NULL ) ;

I created this program to read this data:  (mytest.php)  HandlerSocket can write data to the database.  I’ve left writing as an exercise to the reader. (For now.)

<?php
 $host = 'db';
 $port = 9998;
 $dbname = 'test';
 $table = 'user';
 $index = 'NAME';
 $columns = 'user_id,user_name,user_email,created';

 $hs = new HandlerSocket($host, $port);
 if (!($hs->openIndex(0, $dbname, $table, $index, $columns)))
 {
 echo $hs->getError(), PHP_EOL;
 die();
 }

 $retval = $hs->executeSingle(0, '=', array('mark'), 10, 0);
 echo "data > ",$retval[0][1], " \n";
 var_dump($retval);

 $retval = $hs->executeMulti(
 array(array(0, '=', array('mark'), 1, 0),
 array(0, '=', array('linda'), 1, 0)));
 var_dump($retval);

 unset($hs);

 ?>

Here is the output of running mytest.php.

# php mytest.php
data > mark 
array(2) { [0]=> array(4) { [0]=> string(1) "1" [1]=> string(4) "mark" [2]=> string(20) "mark@www.mysqlfanboy.com" [3]=> string(19) "0000-00-00 00:00:00" } [1]=> array(4) { [0]=> string(1) "3" [1]=> string(4) "mark" [2]=> string(20) "test@www.mysqlfanboy.com" [3]=> NULL } }
array(2) { [0]=> array(1) { [0]=> array(4) { [0]=> string(1) "1" [1]=> string(4) "mark" [2]=> string(20) "mark@www.mysqlfanboy.com" [3]=> string(19) "0000-00-00 00:00:00" } } [1]=> array(1) { [0]=> array(4) { [0]=> string(1) "2" [1]=> string(5) "linda" [2]=> string(21) "linda@www.mysqlfanboy.com" [3]=> string(19) "0000-00-00 00:00:00" } } }  

So it works, but do you really want to use it?  There are a lot more to questions to investigate.   Is it writing data any faster then SQL? Is writing faster then a bulk file load?  What if you mix SQL and HandlerSocket calls?  What does buffering do to these processes?

You you have any questions about about what I’ve written or that you would like be to investigate, email me – mark @ Grennan.com.

Tweet