Daniel Dvorkin

My take on WordPress and related geekery

Tag: Tools

holding the key

Take ownership of a WordPress database

One of the gigs that used to put food on my table was as WPML‘s support manager. Customers that needed help usually ended up sending me a DB dump so I could implement a local copy to reproduce the problem and test whatever their issue might be.

Tired of doing the repetitive task of updating the site url in wp_options table, changing all the urls in the wp_posts table, and changing the password for the admin user, I came up with this little script that will do that for me.

It needs to be said: Be careful of not uploading this script to a live server.


// Bad, bad things WILL happen if you forget to delete this file when you upload the site to a public server
// Did I say bad already?

// Load WordPress environment
require_once( dirname(__FILE__) . '/wp-load.php' );

// First I want to get access. Changing all administrator passwords to "admin". Go read the first comment of this code!
$wp_user_search = new WP_User_Query( array( 'role' => 'administrator' ) );
$admins = $wp_user_search->get_results();

foreach ($admins as $admin){

// Now lets fix the Site URL and Home URL
// But first let's save the current siteurl, for later use.
$old_siteurl = get_option("siteurl");

$pageURL = 'http';
if (isset($_SERVER["HTTPS"]) && $_SERVER["HTTPS"] == "on") {$pageURL .= "s";}
$pageURL .= "://";
if ($_SERVER["SERVER_PORT"] != "80") {
} else {
$new_siteurl = substr($pageURL,0,strrpos($pageURL,"/"));

update_option("siteurl", $new_siteurl);
update_option("home", $new_siteurl);

//And now those pesky hardcoded permalinks

 "UPDATE $wpdb->posts SET guid = replace(guid, '{$old_siteurl}','{$new_siteurl}'); "

// Last but not least, those hardcoded urls (links, images, etc) in the posts contents

 "UPDATE $wpdb->posts SET post_content = replace(post_content, '{$old_siteurl}','{$new_siteurl}');"


Just drop this file in any WordPress installation and execute it on the browser.


Bash script to backup all your MySQL databases

Backing up all your MySQL databases one by one is a pain. Here is a small bash script I made to dump and compress all my databases to my Dropbox folder.

  • The script will skip any database whose name starts with an underscore, so I can have test or junk databases that I don’t want to back up.
  • Every time it runs, it’ll delete all the previous backups (I don’t care, but if you do then just comment out line 7)
  • You need to change lines 3, 4 and 5 to reflect your MySQL user, password and folder where you want to put the dumps.


rm "$OUTPUT/*gz" > /dev/null 2>&1

databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/`date +%Y%m%d`.$db.sql
        gzip $OUTPUT/`date +%Y%m%d`.$db.sql

Now you just need to make it executable:

chmod 700 backup.sh

And then add it to the crontab so it’ll run automagically:

crontab -e
00 20 * * * /path/to/backup.sh

In this case it’ll run every day at 8 PM. You can learn more about crontab here.

Powered by WordPress & Theme by Anders Norén