Blog

I was supposed to go to Medjool to meet up with the Zappos crew, but instead I ended up helping a pal with a MySQL database upgrade problem with version 5.1.24-rc.

The upgrade was a cluster fuck!

These steps for upgrading MySQL have served me well for the past 9 years.

tar cvf backup_data.tar /usr/local/mysql/var/*
gzip backup_data.tar
cd /usr/local/src/mysql-5.1.24-rc
./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql --with-ssl
make
make install
scripts/mysql_install_db

Then I’d just run ‘/usr/local/libexec/mysqld –user=mysql &’ and I’d be on my merry way to happily computing on the web.

But now wiith version 5.1.24-rc of MySQL, I’d have to ask what the heck are they doing at Sun to MySQL?

When I started the server, I noticed that the state files were running in /var . Big fail there, since the prefix is defined as /usr/local/mysql .

Also, I noticed that when I ran scripts/mysql_install_db there were more path errors:

FATAL ERROR: Could not find /fill_help_tables.sql

Then the biggest source of fail occured when I ran this:

/usr/local/libexec/mysqld –print-defaults

/usr/local/mysql/libexec/mysqld would have been started with the following arguments:

--port=3306 --socket=/tmp/mysql.sock --skip-locking --key_buffer=16M 
--max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K 
--net_buffer_length=8K --read_buffer_size=256K 
--read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M 
--log-bin=mysql-bin --server-id=1 --user=mysql 
--pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock
--port=3306 --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp
--language=/usr/share/mysql/english --skip-external-locking --bind-address=127.0.0.1
--key_buffer=16M --max_allowed_packet=16M --thread_stack=128K --thread_cache_size=8
--query_cache_limit=1M --query_cache_size=16M --expire_logs_days=10 --max_binlog_size=100M
--skip-bdb 

Once again, path errors, and –skip-bdb is an option that doesn’t even exist for mysqld!!!!!!

Here was my fix for the scripts’ install:

./scripts/mysql_install_db --no-defaults --port=3306 --socket=/tmp/mysql.sock \ 
--skip-locking --key_buffer=16M --max_allowed_packet=1M --table_cache=64 \
--sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K  \
--read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --log-bin=mysql-bin \ 
--server-id=1 --user=mysql --pid-file=/var/run/mysqld/mysqld.pid \
--socket=/var/run/mysqld/mysqld.sock \
 --port=3306 --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var \
--tmpdir=/tmp --language=/usr/local/mysql/share/mysql/english --skip-external-locking \
--bind-address=127.0.0.1 --key_buffer=16M --max_allowed_packet=16M \
--thread_stack=128K --thread_cache_size=8 --query_cache_limit=1M \
--query_cache_size=16M --expire_logs_days=10 --max_binlog_size=100M

Here’s my fix for how the server must start from now on:

/usr/local/mysql/bin/mysqld_safe --no-defaults --port=3306  \
--socket=/tmp/mysql.sock --skip-locking --key_buffer=16M  \
--max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K  \
--net_buffer_length=8K --read_buffer_size=256K  \
--read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M  \
--log-bin=mysql-bin --server-id=1 --user=mysql  \
--pid-file=/var/run/mysqld/mysqld.pid  \
--socket=/var/run/mysqld/mysqld.sock --port=3306  \
--basedir=/usr/local/mysql --datadir=/usr/local/mysql/var  \
--tmpdir=/tmp --language=/usr/local/mysql/share/mysql/english  \
--skip-external-locking --bind-address=127.0.0.1 --key_buffer=16M  \
--max_allowed_packet=16M --thread_stack=128K --thread_cache_size=8  \
--query_cache_limit=1M --query_cache_size=16M  \
--expire_logs_days=10 --max_binlog_size=100M &

All that just to start a server, so now, I’m totally telling my friends to use PostgreSQL instead. Sure it’s slower and doesn’t scale as much by a factor of 4 compared to MySQL, but hey, maybe it’s time for something new.