Wordpress database migration

I have a script that mysqldumps a copy of my production Wordpress DB, restores it over my test Wordpress install & then corrects all the "production" settings & urls in the test DB.

Both my production & test databases live on the same server, but you could change the mysqldump settings to dump from a remote mysql server & restore to a local server quite easily.

Here are my scripts:

overwrite_test.coach_db_with_coache_db.sh

#!/bin/bash dbUser="co*******"dbPassword="*****"dbSource="coach_production"dbDest="coach_test"tmpDumpFile="/tmp/$dbSource.sql"mysqldump --add-drop-table --extended-insert --user=$dbUser --password=$dbPassword --routines --result-file=$tmpDumpFile $dbSourcemysql --user=$dbUser --password=$dbPassword $dbDest < $tmpDumpFilemysql --user=$dbUser --password=$dbPassword $dbDest < /AdminScripts/change_coach_to_test.coach.sql

change_coach_to_test.coach.sql

-- Change all db references from @oldDomain to @newDomainSET @oldDomain = 'coach.co.za';SET @newDomain = 'test.coach.co.za';SET @testUsersPassword = 'password';UPDATE `wp_1_options` SET `option_value` = REPLACE(`option_value`,@oldDomain,@newDomain) WHERE `option_name` IN ('siteurl','home','fileupload_url');UPDATE `wp_1_posts` SET `post_content` = REPLACE(`post_content`,@oldDomain,@newDomain);UPDATE `wp_1_posts` SET `guid` = REPLACE(`guid`,@oldDomain,@newDomain);UPDATE `wp_blogs` SET `domain` = @newDomain WHERE `domain` = @oldDomain;UPDATE `wp_users` SET `user_pass` = MD5( @testUsersPassword );-- Only valid for main wpmu siteUPDATE `wp_site` SET `domain` = @newDomain WHERE `domain` = @oldDomain;

Perhaps you are just looking for the wrong thing. Wouldn't a backup plugin handle this with ease? I know they exist for all the big CMS packages...

The two methods would be using the export/import feature under tools or copying the database. I email myself a copy of my production database weekly using the WordPress Database Backup plugin.

The import feature can be problematic for moving a wordpress blog as you have to configure your php.ini file often because the default value of files you can upload on a hosted php implementation tends to be too small by default.