Tag Archives: mysql

WordPress notes for pomeroy.us

Posted on by

Production site is www.pomeroy.us
Development site is dev.pomeroy.us

Assumptions:
– webserver root directory is /var/web
– production node is called prod
– development node is called dev
– WordPress database is called wpdb

Procedure to copy production WordPress instance to the development node:
1. Copy webserver www root dir via a tarball
tar czf prod-20110808.tgz /var/web

2. Dump the WordPress database to a MySQL dmp file:
mysqldump -u$mysqluser -p$mysqlpass wpdb | \
 gzip -c > prod-20110808.dmp.gz

3. Copy these two backup files to the dev node:
scp prod-20110808* user@dev:.

On the development node:
4. Unpack the webserver tarball:
mv /var/web /var/web.previous
cd /
tar xzvf prod-20110808.tgz

5. Drop the WordPress database and restore the new version:
mysql> drop database wpdb;
mysql> create database wpdp;
$ gunzip prod-20110808.dmp.gz
$ mysql -u$mysqluser -p wpdb < prod-20110808.dmp

6. Update the WordPress 'siteurl' and 'home' options to point to the development node:
update wp_options set option_value='http://dev.pomeroy.us' where option_name='siteurl';
update wp_options set option_value='http://dev.pomeroy.us' where option_name='home';

Should be all done!

MySQL Notes

Posted on by

MySQL Command Line and Configuration Notes

Drop tables with wildcard:

There are multiple ways to specify MySQL credentials, this is not the best, but simply an example of how to drop tables using a wildcard pattern. In this case, command line history such as .bash_history will store your MySQL username and password plaintext, and an extended process listing will also reveal both username and password. When run from the command line like this, the SQL commands and the credentials are not stored in the MySQL history file (.mysql_history).  On closed (private) systems, the risk is low, especially if you clean up after these maintenance activities by purging the command histories.

mysql -u user -p password database -e "show tables" | grep "table_pattern_to_drop_" | awk '{print "drop table " $1 ";"}' | mysql -u user -p password database

Update WordPress home URL

Posted on by

There are times when moving or copying WordPress blogs from one server to another, the owner may want to update the URL associated with the specific site.

A simple MySQL update can match the WordPress blog to a new site URL:

mysql> select option_value from wp_options where option_name = 'siteurl';

+--------------------------------+
| option_value                   |
+--------------------------------+
| http://www.example.com |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select option_value from wp_options where option_name = 'home';

+--------------------------------+
| option_value                   |
+--------------------------------+
| http://www.example.com |
+--------------------------------+
1 row in set (0.00 sec)

mysql> update wp_options set option_value='http://server.newsite.com' where option_name='siteurl';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update wp_options set option_value='http://server.newsite.com' where option_name='home';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0