[wp-hackers] change siteurl at DB level

[Tom Hall]

I've been trying to do something like this to copy a production site to a
development version. I've run into an issue with the plugin settings that's
puzzling me. I'd appreciate it if anyone can give me some high-level ideas
about why I lose the plugin settings.

After copying all the files to the dev site and modifying the database, it
basically seems to work alright, but many of the plugin settings get lost.

When I look at the production and dev databases with phpMyAdmin, I can see
that the settings get copied OK, but as soon as I access the dev site with a
browser, at least some of the settings get set to default values.

One difference in what I'm doing is that, instead of modifying the database
dump file, I proceed to import the database dump file into a new database,
then modify the copied database afterward with this script:

 http://interconnectit.com/124/search-and-replace-for-wordpress-databases/

(This script also takes care of "serialized data")

- Tom

On Tue, Aug 24, 2010 at 05:21:32PM -0500, Potkanski, Jason wrote:
> Here is a small bash script I use to move from prod back to dev on my local machine. You can run this from the server or the test box depending on how your network is configured. As the others have said, the only way to guarantee the change is a DB dump.
> 
> #!/bin/bash
> mysqldump -v --host=proddbserver --user=produser --password=prodpw --lock-tables=false --databases wp_prod > dump.sql
> sed 's/blogs.prod.com/dev.prod.com/g' dump.sql > newdump.sql
> mysql --force -u devuser --password=devpassword -h 127.0.0.1 wp_prod < newdump.sql
> rm dump.sql
> rm newdump.sql
> 
> You can add --default-character-set=UTF8 to the mysqldump and mysql lines if you have MySQL default character set mismatches.
> 
> Jason Potkanski
> Tribune Technology
> 
> -----Original Message-----
> From: wp-hackers-bounces at lists.automattic.com [mailto:wp-hackers-bounces at lists.automattic.com] On Behalf Of Andrew Gray
> Sent: Tuesday, August 24, 2010 3:19 PM
> To: wp-hackers at lists.automattic.com
> Subject: [wp-hackers] change siteurl at DB level
> 
> I have been trying to move a site from  development.domain.com  to domain.com all day and got fed up and wrote some SQL to make the change on the DB level.
> I know that you can download the DB dump and fix it, but it causes some problems for me with UTF characters.  Also some of my sites are pretty big now, with all the revisions, custom post types and what not.
> 
> If you run this command, you can pretty much change all of the stock tables.
> 
> This will not change all your plugins values, but saved me a ton of time. Took about 1 sec to run
> 
> 
> #Change Domain in in Wordpress DB
> #before you run this command in SQL (phpmyadmin)
> # 1. Backup DB
> # 2. Find and replace the text below in your text editor
> #replace $old$ with the old domain name
> #replace  $new$ with the new domain name
> 
> update wp_postmeta set meta_value = replace(meta_value, '$old$' , '$new$') ;
> update wp_posts set post_content = replace(post_content, '$old$' , '$new$') ;
> update wp_options set option_value = replace(option_value, '$old$' , '$new$') ;
> 
> #end sql command
> 
> I would love it if there was an easy way to force wordpress to use a place holder for the domain and then replace in the correct one when it reads from the DB.
> If I could find a filter that would filter every value from read and write to the db, I could do this very easy, but there is no filter for every value coming from the DB.
> I just do not know why the SITEURL should be stored in the DB.  Is there a pair of filters that I can use to replace a value in every read and write
> 
> Andrew
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>