Migrating WordPress with Large wp_posts Table

I’m working on a freelance project involving the migration of a WordPress blog. Part of the process requires moving the MySQL database to a new server without having SSH command line access on the server. (Commands below were executed on my local Linux PC.)

After exporting the database dump using adminer, I had to split the file into separate tables and then split the wp_posts file into more separate files. These additional steps were required because the wp_posts table was too large to import through adminer due to PHP post_max_size errors.

To split the dumped MySQL file I used the following script:

cat dump.sql | awk 'BEGIN {
 output = "comments"; } $data ~ /^CREATE TABLE/ {
 close(output);
 output = substr($3,2,length($3)-2); }
 { print $data >> output }'

Via http://www.mindraven.com/blog/mysql/splitting-a-very-large-mysql-dump-file/

Then I ran the following command on the wp_posts file that was created by the above process:

awk '/INSERT INTO/{n++}{print >"split-" n "-wp-posts" }' wp_posts

Via http://nixcraft.com/shell-scripting/8464-split-files-specifying-string-bash-shell.html

Leave a Reply