Code in the hole (Entries tagged as mysql)

How to sync a MySQL table between two remote databases.

nospam@example.com (David Winterbottom) — Fri, 03 Sep 2010 21:04:58 +0000

Definitely tricker than you might think.

Seems like it should be trivial using "SELECT ... INTO OUTFILE" and "LOAD DATA INFILE ..." to make the transfer via dumping the table into a temporary file. However, the "SELECT ... INTO OUTFILE" creates a file on the remote server, rather than locally. This prevents the use of LOAD DATA INFILE for the second step as the file being loaded has to be local, or on the destination server.

Following the guidance in the docs, you can create local dump of a table by using the "--execute" option to output the results of a "SELECT ..." statement into a local file.

mysql -D database_name -e "SELECT ... " > /path/to/file.txt

This works but has two downsides:

First, running a shell command forces you to step outside the MySQL adapter of your progamming language which means it is a new place where the database credentials need to be passed. Shelling out commands always feels like you've failed.
Further, as far as I can tell, you can't control the field separator or line endings using this technique (in the same way as you can with "SELECT ... INTO OUTFILE ...") and so the file includes an unwanted line with the field names and tab-separates the fields.

It's worth noting the mysqldump isn't much help here, as the "--tab" option that allows CSV output to be generated only works with a local database connection.

Now that we've got our data locally, we load it into the remote database using "LOAD DATA INFILE" and make use of the "LOCAL" keyword which lets us use a local data file:

mysql -h x.x.x.x -u user -D database_name --password=... -e \
    "LOAD DATA LOCAL INFILE '/path/to/file.txt' \
     REPLACE INTO TABLE table_name \
     IGNORE 1 LINES"

Of course, you may want to truncate the table first if you want a clean sync. As this operations locks the destination table, it often makes sense to load the data into a temporary copy of the table, and then perform a "RENAME TABLE" operation to swap in the new table.

Here's a quick and dirty PHP implementation:

$tableName = 'some_table';
$sql =
   "SELECT * 
    FROM $tableName";
$pathToCsv = '/tmp/some-file.csv';
$command = sprintf("mysql -h %s -u %s  --password=%s -D %s -e '%s' > %s",
    '10.0.0.2', 
    'db-user', 
    'db-password', 
    'database_name', 
    $sql, 
    $pathToCsv);
exec($command);

$sql =
   "LOAD DATA LOCAL INFILE '$pathToCsv'
    REPLACE INTO TABLE `$tableName`
    CHARACTER SET 'utf8'
    IGNORE 1 LINES";
$db->execute($sql); // Using your favourite database adapter

Monitoring MySQL with Ganglia and gmetric

nospam@example.com (David Winterbottom) — Sun, 14 Dec 2008 21:34:00 +0000

Following some server monitoring advice from the excellent 'Building Scalable Web Sites' by Cal Henderson, I've recently been experimenting with Ganglia on a cluster of servers at Tangent Labs. It has proved to be deeply impressive and has given us a great tool for keeping an eye on how our servers are performing, as well as providing an invaluable diagnostic tool should things go wrong.

In essence, Ganglia is a distributed monitoring application that allows statistics on a cluster of servers to be aggregated in a single place. Moreover, an excellent web front-end is also provided to view charts of the statistics being collected. You can view a live demo of the Ganglia monitoring application for the UC Berkeley Millennium Project which shows exactly what Ganglia can provide.

Out of the box, Ganglia collects basic server stats on load, memory, CPU, network and disk. However, what makes it great is that it can be easily extended to collect statistics on anything that you can measure from the command line. There is a repository of user-contributed scripts although it's not that extensive. In this post, I detail a script that can used to feed statistics about MySQL into Ganglia for monitoring - something important to any LAMP-stack application.

A few technical details first: Ganglia utilises three utilities to run each with a distinct responsibility:

gmond: A daemon responsible for collecting monitoring stats and sharing them using XML over TCP. The gmond daemon runs on all nodes that you want to monitor.
gmetad: A daemon responsible for collecting and storing data from other gmetad and gmomd services. This only needs to be running on a single, central node.
gmetric: A command-line application used for using Ganglia to monitor custom metrics.

All very simple really - see the docs for the nitty gritty. Ok, enough intro - here's my script for MySQL:

» gmetric-mysql.sh (3.1kb)

It's written in Bash and uses a temporary file to read the MySQL stats into before parsing and submitting to gmetric. This is done as many of the MySQL stats of interest are cumulative counts rather than rates (eg SELECTs per second) which are generally of more interest. Hence, the script estimates the rate of change by dividing the value delta by the timestamp delta.

To use this script, simply copy it into a convenient location, ensure it is executable and add the following line to your crontab.

* * * * * /path/to/gmetric-mysql.sh > /dev/null 2>&1

As soon as this is done, your Ganglia web front-end should start plotting the new metrics.

Checking all MySQL tables

nospam@example.com (David Winterbottom) — Sun, 23 Nov 2008 11:30:40 +0000

It's well known that MyISAM tables are prone to corruption and need to be regularly checked and repaired. Moreover, in a production environment, it can be beneficial to run a daily check of all tables and mail news of any errors to an appropriate developer/DBA.

There are two options for checking MySQL tables. The most effective method is to run the myisamchk utility directly on the index files (.MYI) of the tables in question (some simple shell expansion makes this easy):

myisamchk --silent --fast /path/to/datadir/*/*.MYI

However, this proses a problem in that you must ensure that no other programs are accessing the tables while they are being checked. Hence they must be locked, or better still, the MySQL daemon stopped before running any checks. Perversely, if this is not done, the act of checking the tables can corrupt them.

Another option is to use the CHECK TABLE syntax in SQL (which does not pose a risk of corruption). There are various scripts (written in PHP and bash) posted on the CHECK TABLE manual page but this operation can be done easily through a single line:

mysql -p<password> -D<database> -B -e "SHOW TABLES" \
| awk '{print "CHECK TABLE "$1";"}' \
| mysql -p<password> -D<database>

This dynamically creates a list of "CHECK TABLE ..." commands which is piped into MySQL for execution.

For checking a selection of tables rather than all, use the LIKE operator when selecting the tables to check:

mysql -p<password> -D<database> -B -e "SHOW TABLES LIKE 'User%'" \
| awk 'NR != 1 {print "CHECK TABLE "$1";"}' \
| mysql -p<password> -D<database>

This only checks the tables that start 'User'. Note that the awk program has an extra clause to ensure that the first line of MySQL output is skipped.

Monitoring MySQL processes

nospam@example.com (David Winterbottom) — Sun, 26 Oct 2008 22:06:00 +0000

Just a quick tip on monitoring the queries that mysql is handling on a production site. You can use the mysqladmin tool to return a list of the processes currently being handled. Combining this with the UNIX watch command allows a real-time monitoring of what's going on.

watch -n 1 mysqladmin processlist

The "-n 1" specifies that mysqladmin executes every second. Depending on your set-up, you may need to specify a mysql user and password:

watch -n 1 mysqladmin --user=<user> --password=<password> processlist