Archive for October 2nd, 2011

How to find a keyword in your WordPress posts using SQL

If you have a lot of WordPress posts and might want to find all posts containing a certain keyword for any reason, you can start by using the following SQL code, which was taken from this post. I used this in phpMyAdmin for a MySQL database. Make sure you are in the correct database first!

You can substitute any keyword for ‘needle’ below, but you must have the single-quotes and percent signs around it.

SELECT ID FROM wp_posts WHERE post_content LIKE '%needle%';

Example: Let’s say you use the NG Gallery plugin, which has you add a tag to all your posts to include said gallery. Now you find to find all posts which have that NG Gallery tag in them. The following query would work:

SELECT ID FROM wp_posts WHERE post_content LIKE '%nggallery%';

This can also be built upon for find-and-replace operations.

Keep in mind, you can really muck things up using SQL. Make a backup first if you don’t know what you’re doing!

Questions, comments, and feedback are always welcome. Thanks!

, , , , ,

Leave a comment

Basic Ubuntu VPS server backup via FTP or SSH SFTP

In my quest for the perfect “in my dreams” backup solution for my Ubuntu VPS, I created this very simple script which can be run as a cron job and can be easily modified to backup any amount of data to any remote FTP or SFTP server.

You could very easily include a database backup by running mysqldump beforehand, but I’m not including it in this script.

This required yafc to be installed, but Ubuntu installations can easily install it by running

sudo apt-get install yafc

And now, for the script:

# format of the open command is proto://username:[email protected]/
# proto is either ftp or ssh
# special characters in the username or password are not well tolerated
# anything in the EOF tags are direct commands to yafc. Test if unsure
DIR=`date +%F`
yafc <<EOF
open ftp://username:[email protected]/
cd backup-dir
mkdir $DIR
cd $DIR
put -p -r *

Enjoy! Questions, comments, and feedback are welcome and appreciated. Thank you!

, , , , , , , , ,

Leave a comment