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:
- Make a database dump using the
mysqldump tool and use
scp to copy it to you locally
- Pull up a local WP install
- Install WP CLI, the WP command line runner, locally
wp post update with the necessary commands locally
- Save your live database by renaming it
- Upload your local copy of the database to your staging server
- Test again
- 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!).
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 (
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):
for id in $(wp post list --field=ID);
wp post update $id --post_name=foobar
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
sed in a shell script.
You can run above shell script like the following from your terminal:
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.