Transfer of the MySQL data of a WordPress Site to a different domain

The internal links of a WordPress site are stored by their full URL into the database, and therefore a straight copy of a WordPress database would work only if the other WordPress site got exactly the same URL.

The solution is to run a mysqldump(1) on the master site and then transfer the generated .sql-file to the other machine:

mysqldump --user=root --password=uLtRaSeCrEt12345 --default-character-set=utf8 --set-gtid-purged=OFF wpdbname > wpdump.sql

Since the MySQL dump is simply a text file of MySQL commands and the contents are in plain text, I may utilize sed(1) for substituting in the SQL dump the URL of the master site - in my case - by the URL of my local development site I wrote a shell script for doing this which executes the mysqldump on the remote machine via ssh(1):

ssh mysqldump --user=root --password=uLtRaSeCrEt12345 --default-character-set=utf8 --set-gtid-purged=OFF wpdbname 1> /tmp/wpdump.sql 2>/dev/null

sed -e 's|||g' -i "" /tmp/wpdump.sql
sed -e 's|||g' -i "" /tmp/wpdump.sql
echo "drop database wpdbname; create database wpdbname;" | mysql --user=root --password=uLtRaSeCrEt12345 --host=localhost --default-character-set=utf8
mysql --user=root --password=uLtRaSeCrEt12345 --host=localhost --default-character-set=utf8 wpdbname < /tmp/wpdump.sql
rm -f /tmp/wpdump.sql
  • Of course I would need to adapt the script for different URL’s respectively.
  • Of course I would need to set up a working MySQL/WordPress installation on a new machine.
  • Of course I would need to transfer image and media data as well by using for example scp(1).

Keywords: MySQL-Script, WordPress

Copyright © Dr. Rolf Jansen - 2018-02-27 21:00:08

Discussion on Twitter: 1082812756963803136