The Codebelay Blog

Tag: mysql

  • How To Back Up WordPress with mysqldump

    Here’s a quick how to:

    1. Tar up the directory:

    tar cvf WordPress.tar wordpress/.
    bzip2 WordPress.tar

    2. Dump the database.

    mysqldump --user user_name --password --host example.com --extended-insert=FALSE database_name > database_name.sql

    I personally use extended-insert set to false because I often have to read the SQL.

    Here is how to restore from backup:


    tar zxvf WordPress.tar.bz
    mysql --user user_name -p -hexample.com database_name < database_name.sql

    Be sure to replace user_name, example.com and database_name with the appropriate values for your system.

    I hope this helps.

  • Sun Breaks MySQL’s Source Install

    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.