How to change the internal links of all the posts in wordpress

 When you change the url pattern of a Wordpress installation some internal links can still be pointing to the old pattern of urls.

To mitigate this problem you can use a plugin like redirection in order to redirect old pattern urls to the new ones. With this solution you can solve the problem of internal links and of external links too (that you don't have control).

However, it's recommended to change all the internal links inside your content to avoid innecessary redirections.

For this reason we've created a procedure to update the content of all the posts, looking for urls that already are redirected and replacing them for the new ones.

IMPORTANT: This procedure assume that you have already installed and configured the redirection plugin and you have good knowledge of Linux commands and MySQL.

The procedure is as follows:

- Dump the wp_posts table using mysqldump

- Backup this dump just in case

- Launch a query to get all the replacement commands

- Launch replacement commands to replace all the strings in the dump

- Upload the modified dump into mysql

1) Dump the wp_posts table to a file

mysqldump -u <user> -p<password> -h <host> <db> wp_posts > wp_posts.sql

2) Make a backup of the file, just in case...

cp wp_posts.sql wp_posts.bk.sql

3) Then launch this select using your favorite MySQL client or PHPMyAdmin.

The idea of the sql query is to get all the redirection redirects that are enabled in the Wordpress database (table wp_redirection_items) and generate a Linux command (sed) that substitute all the urls in the content from the original ones to the new ones.

select 
concat("sed -i 's/"
,replace(url,'/','\\/'),"/"
,replace(action_data,'/','\\/'),"/g' wp_posts.sql") cmd
from (
	select substr(url,1,length(url)-1) url
	, if(substr(action_data,length(action_data))='/', substr(action_data,1,length(action_data)-1), action_data) action_data
	, action_type, status, action_code
	, url original_url, action_data original_action_data
	from wp_redirection_items
	where url like '%/'
	union all
	select url
	, if(substr(action_data,length(action_data))='/', substr(action_data,1,length(action_data)-1), action_data) action_data
	, action_type, status, action_code
	, url original_url, action_data original_action_data
	from wp_redirection_items
	where url not like '%/'
) t 
where status='enabled'
and action_type='url'
and action_code=301
group by cmd;

A sample line that are returned by the sql query above is:

sed -i 's/\/blog\/2013\/03\/prepare-your-promotions-xxx/\/blog\/prepare-your-promotions-xxx/g' wp_posts.sql

4) Then execute all the lines in your Linux box it will replace all the urls that match /blog/2013/03/prepare-your-promotions-xxx by /blog\/prepare-your-promotions-xxx
because in my case I've changed the url pattern to avoid the date in the url.

Copy the results of all the lines into your Linux box in the same folder where you have generated the backup of the table wp_posts 
(you must have the utility sed installed (available in 99% of Linux installations)).

5) Upload the dump again

mysql -u <user> -p<password> -h <host> <db> < wp_posts.sql

Hope this help you.