Start a MySQL instance to run Debian package build-time tests

I'm the maintainer of MySQL Connector/Python Debian package, and it's shipped with several unittests, but they require a MySQL instance to be run against, so up to know I wasn't able to run them at build-time.

But I want to change that, so I wanted to find a way to spawn a MySQL instance just for testing purposes. I've searched a lot, and then found a package solving the same problem: php5.

The package has a setup-mysql.sh script that takes care of starting/stopping the instances, and debian/rules file sets up instance parameters and then it starts the instance before the tests and then stops it after them.

It's a very interesting solution, so I thought of sharing + taking notes to self, just in case I need it in other situations.

MySQL master/slave chain

Have you ever needed to create a MySQL databases replication chain like A->B->C where B is slave of A and master of C? Me neither, until yesterday.

Since it took us about an afternoon to make it works (along with our DBAs, so we're not alone ;)) let's share some knowledge.

A very brief recap of how MySQL replication works:

1. slave I/O thread connects to the master, gets the new information from the binlog files and stores them in the relay log;
2. slave SQL thread reads the relay log and applies the changes to the slave database, without changing the slave binlog files.

That said, B replicates correctly from A, but C is unable to replicate from B because B doesn't change its binlog files with updates coming from A, because there's no changes done directly on B.

In order to make the chain works, you need to add the parameter log-slave-updates on B configuration: that will reply the changes from relay log to binlog, and so C will see the changes it needs to correctly replicate.

PS: mysqldump --master-data (executed on the slave server against the master) would help you set up the correct information for replication.

Compress MySQL Tables

I got this long running task that (fsck!) died almost at the end of the job, and it logs its operation in a MySQL table.

Since I want to keep those "partial" results for further inspection (even after I'll fix and let the tool run again until the end) I don't want to waste disk space (it's ~650mb), so why don't compress that table?

With MySQL (and MyISAM tables like mine), it's pretty easy: go into the directory of the db schema:

# cd /var/lib/mysql/tabs_compare/
# ls -l
total 659500
-rw-rw---- 1 mysql mysql        65 2008-09-21 00:35 db.opt
-rw-rw---- 1 mysql mysql     12840 2008-12-25 00:44 log_1230162297.frm
-rw-rw---- 1 mysql mysql 675299244 2009-01-01 20:34 log_1230162297.MYD
-rw-rw---- 1 mysql mysql      1024 2009-01-01 20:37 log_1230162297.MYI

Pretty big :) Now, check the table:

# myisamchk log_1230162297.MYI
Checking MyISAM file: log_1230162297.MYI
Data records: 4293747   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check record links

Hopefully there is no error, so we can compress that table:

# myisampack log_1230162297.MYI
Compressing log_1230162297.MYD: (4293747 records)
- Calculating statistics
- Compressing file
43.05%

And the result it's really nice:

# ls -l
total 375572
-rw-rw---- 1 mysql mysql        65 2008-09-21 00:35 db.opt
-rw-rw---- 1 mysql mysql     12840 2008-12-25 00:44 log_1230162297.frm
-rw-rw---- 1 mysql mysql 384558282 2009-01-01 20:34 log_1230162297.MYD
-rw-rw---- 1 mysql mysql      1024 2009-01-01 20:49 log_1230162297.MYI

The compress command didn't even take a full core on my machine and almost took 20secs to complete.

Now rebuild indexes (if any) and recheck the table to be error free:

# myisamchk -r log_1230162297.MYI
...
# myisamchk log_1230162297.MYI
...

now tell MySQL to reload those table with:

mysql> flush tables <schema>;

Keep always in mind that compressed tables are read-only, so no DML commands, while DDL are allowed (so you can DROP it, for example).