Databases How-To MySQL WebApps wordpress

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 --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
mysql --user user_name -p database_name < database_name.sql

Be sure to replace user_name, 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 install

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/ --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=
--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

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/ \
--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= --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/  \
--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= --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.