How to bulk edit canonical url's in SQL?

Question

I've been assigned to edit the canonical URL's (rel="canonical" found in the head)of about 600 posts on a client's site. Not all of the posts will use the same canonical url. Nor are these 301's or 302's. I'm told not to use a plugin because I would have to edit each post one at a time. These are the general steps I plan to take:

1. Make an appropriate back up of the mySQL db in phpMyAdmin.
2. Download a csv of the table containing the canonical url's (using phpMyAdmin export).
3. Make a new csv that uses the post id and the new canonical url's (and a bit more magic).
4. Import the new csv (in its corrected format using LibreOffice)through a plugin called Really Simple CSV, which will write the new url's to the database.

This is the tutorial I'm following for these steps.

My questions are: Using phpMyAdmin, what table is used to currently store these url's, and what, exactly, are they called? And, has anyone done this sort of edit before?

The site is currently on WordPress 3.9.x (it's outside of our scope of work to update their site to a newer version.)

Help please!

Answer 1

Down- and uploading CSVs is a tough one that forces you to make a bunch of steps manually which is prone to human error (and typos). My suggestion would be to do something that you can battletest locally in a local copy of your DB. Steps to follow:

1. Make a database dump using the mysqldump tool and use scp to copy it to you locally
2. Pull up a local WP install
3. Install WP CLI, the WP command line runner, locally
4. Run wp post update with the necessary commands locally
5. Test
6. Save your live database by renaming it
7. Upload your local copy of the database to your staging server
8. Test again
9. Make it your adjusted database your live database

Make sure that you have a time where no one fiddles with the database (take your site into maintenance mode!).

Notes:

A) In case you know the IP and port of your MySQL server, you can directly connect to it and pull the dump down to your machine without SSH-ing into remote:

mysqldump -P3306  -h127.0.0.1 -uroot -pYourPassWord databasename > ./dbdumps

Above will dump it in a subfolder of your current directory (pwd) named dbdumps. You should create it up front.

B) Assuming that you have Bash available (command line – sh will still be sufficient enough if you have not), you can use something like the following script. Add it to a file named convert.sh. Also assuming that you got WP CLI installed locally (or your Vagrant machine, Docker container, whatever dev environment you are using locally):

#!/usr/bin/env bash
for id in $(wp post list --field=ID); 
do
    wp post update $id --post_name=foobar
done

As you did not detail that conversion for your posts slug, you will have to find a way to adjust foobar in above example. There are dozens of Stackoverflow answers on the tag:bash topic to help you. As usual sed for the rescue – there's nothing in the world that can not get fixed with awk and sed in a shell script.

You can run above shell script like the following from your terminal:

sh convert.sh

Just give it a try, fail, rinse and repeat.

I've written that mostly out of my head, so you will have to test it. Better make a copy of your local DB so you can trash it start again.