Solved

wordpress - mysql - update path in all posts

Posted on 2016-08-16
13
29 Views
Last Modified: 2016-09-26
Hi,

We have moved a wordpress site from a temporary server to the new server.

The mysql database is littered with the path details for anything from images to posts with the following in long lines in the database

http://185.123.97.162/~username/mydomain.com/etc etc etc

Is there a command i can use to search for any reference for the above to change it to

http://mydomain.com/etc etc etc


Or is there another way to do this?

I have tried changing the permalink to default the back again but no joy there
Thanks
0
Comment
Question by:David
13 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points (awarded by participants)
ID: 41757795
I know how to technically do this in mysql, given the table and column name:

update yourtable set yourcolumn = replace(yourcolumn , 'http://185.123.97.162/', 'http://mydomain.com/')
where yourcolumn  like '%http://185.123.97.162/%'

Open in new window


what I don't know is the relevant table/column name(s) in wordpress
0
 

Author Comment

by:David
ID: 41757812
performing a search in all tables in the database in phpMyAdmin i get many results in many tables, please see attached image

phpMyAdmin
So i want any reference to http://185.123.97.162/~username/mydomain.com/ to be changed to just http://mydomain.com
0
 

Author Comment

by:David
ID: 41757818
for example one post in the MySQL is

css=".vc_custom_1470043286616{padding-bottom: 40px !important;background: #e7ebf0 url(http://185.123.97.162/~username/mydomain.com/ wp-content/uploads/2016/05/rion-Key-Drivers.jpg?id=970) !important;background-position: center !important;background-repeat: no-repeat !important;background-size: cover !important;}" el_class="related"][vc_column][vc_column_text disable_pattern="false" align="center"]
0
 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 200 total points (awarded by participants)
ID: 41757819
There are plugins you can use for migrating it one such tool is this one

https://wordpress.org/support/plugin/all-in-one-wp-migration

If you cannot use that then you have an interesting time ahead of you.

You can export your database to an SQL file and then do a search and replace and then import the modified script - however this is not always a failsafe way of doing this because sometimes URL's are stored inside serialized arrays which means that when you change the URL - if the length changes you also have to change the length specifier for the url field. If you are not familiar with PHP serialize then this might become quite technical - it also means it can take a long time.

You can recognise a serialised array when it looks something like this

a:11:{i:1;O:8:"stdClass":5:{s:17:"http://mysite.com"}}

Open in new window


Try the migration plugin first.
0
 

Author Comment

by:David
ID: 41758105
unfortunately the plugin copied over the path

I think we can do this by some kind of query on each table but advice on this would be appreciated
0
 

Author Comment

by:David
ID: 41758126
for example in the wp0m_posts table there are 2368 items int eh post_content"  that need to be found and replaced
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 51

Accepted Solution

by:
Julian Hansen earned 200 total points (awarded by participants)
ID: 41758131
If you are going to do the DB method then I recommend the second method I described in my earlier post.

1. Dump your DB to an SQL file
2. Open the file in a text editor
3. Do a search and replace operation using the old URL as the search and the new URL as the replace.
4. Save the file back to the SQL file
5. Restore the target DB from the modified SQL file

This will have the same effect as running a query on tables but will be (in my view) quicker and easier.

Just bear in mind the note I made about plugins that save paths in serialized strings - you might not have any of these but just in case you do.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41758134
addendum - the advantage of the above method is you don't need to know the structure / use of the tables. This reduces to a search and replace operation. Once the DB is restored paths throughout the DB should be changed.
0
 

Author Comment

by:David
ID: 41758185
That was a great tip and updated all the links!!

The problem i have now is the site has a few issues now with image sizes and the logo has reverted back to the template default. Do you know why this migth be?
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41758222
If you search the old db for the logo image and then find the corresponding in the new - what is the difference?

If you just replaced the domain names and paths for the URL's it should not affect the image sizes.
0
 
LVL 3

Assisted Solution

by:Donna
Donna earned 100 total points (awarded by participants)
ID: 41758621
Use the Velvet Blues plugin ...and search and read before you decide about the GUID paths...if your site was already active, you may want to leave those alone. That's the best way.. and you can deactivate or uninstall once you are done with it.

If you are moving a site, I like the WP-Migrate plugin. (in fact, I like it for plain old DB backups too!)
0
 
LVL 13

Assisted Solution

by:Abhijeet Rananaware
Abhijeet Rananaware earned 100 total points (awarded by participants)
ID: 41758665
Use search and Replace plugin for Wordpress. Its Good and works like a charm.

Their is option to do Dry Run first.

In Search for field put "http://185.123.97.162/~username/mydomain.com"
and replace with field put "http://mydomain.com" and then select ALL tables check box . Run it.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…
The viewer will learn how to count occurrences of each item in an array.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now