mysql

On my last server to upgrade to Hardy, MySQL failed to start. Not sure why it only happened to this server, but trying to start MySQL resulted in:

/etc/init.d/mysql: ERROR: Using expire_logs_days without log_bin crashes the server. See README.Debian.gz

Sure enough, looking in /etc/mysql/my.cnf showed that log_bin was commented out, and expire_logs_days was enabled.

Uncommenting log_bin = /var/log/mysql/mysql-bin.log resolved the issue.

I've just finished building what is soon to become my new primary MySQL server. It's a dual CPU, dual-core AMD64 running at 2 GHz each. I've put 8 GB of RAM in it right now, but the board will support up to 64 GB. It's running three Raptor 10,000 RPM SATA drives in a Linux RAID 0 (striping) for performance. No, there's not a mirror or checksum because not only will it have daily off-machine backups but will also be replicating to a backup database server when it's up and running.

I've configured network bonding, so it effectively has a 2 Gbps connection to the network. Right now I'm doing some serious load testing on the Raptor's since I've had issues in the past with them on another chipset motherboard. So far so good!

Wow, talk about running me in circles!

For replication to happen, you need consistent access to the replication log files. Those files should be there after a restart. Turns out, by default, the build of MySQL in Ubuntu 7.10 places these files under /var/run/

Every reboot, the files in /var/run were deleted. Boom, there went my replication. I just had to manually reconfigure MySQL to store these somewhere else and resync my servers. What a pain! After the fix, now my replication state continues after a reboot.

To fix, add the following line in your my.cnf under [mysqld]:

relay-log = '/var/replication/mysql-relay'

And, create a directory owned by mysql:mysql called /var/replication

Or put it somewhere else, that's just what I used. Make sure you do this BEFORE you set up replication, or else you'll hate yourself trying to move it later.

Okay, so like an idiot I thought 'Hey, I have two new servers, worth $3000 each, but I'm only going to have them here for testing for a few days before they get shipped out. I know, let's NOT use a UPS!'

What the heck was I thinking? Today it's been extremely windy, and at 6:40 AM we started getting minor power flickers.

Minor enough it screwed with things without damaging hardware.Net result: after rebooting the two new servers, MySQL replication wouldn't start on the slave. After shaking the Google tree, this little apple fell out.

For some reason my mysqld-relay-bin.index file was reporting that we were on log file 00009, but I only had 00001 and 00002 on the hard drive.

Following the advice of the above site, I was able to restart slave replication pretty easily. A simple text edit of the index file and then a 'start slave' and we were instantly back up to sync.

I have a database that I need to replicate to several slaves. It includes one innodb table, and the rest are all MyISAM.

It's really not that tough once you get all the details right. I made the decision to replicate the innodb table to a MyISAM table on the slave since I'm only using it because of write locking on the master. Because of this I used mysqldump to create a SQL file for import into the slave, and modified the table type on it before importing. Other than that detail, I pretty much followed these directions.

While trying to figure out a problem with PHP and stored procedures, I clicked a link to the mysql.com bug database. Here's the result I got:

It seems even the people who WRITE MySQL can't get it to work sometimes! Note the error I highlighted in yellow.