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.