Quite often I inherit WordPress websites that have been neglected over the years and require some routine maintenance to get them up-to-date
Normally the best way to do this WordPress maintenance is to grab a copy of the live site and get it running on a VVV Vagrant box. A recent WordPress website redesign job needed just this cleaning up as it had had nearly 50,000 comments in the spam comments queue. Clearing them out wasn’t an issue with the Batch Spam Comment Delete plugin, what was an issue was importing the 46MB sql file into vagrant via phpMyAdmin which would then timeout.
There are various ways you can increase these timeout value in the php-fpm & nginx conf files, but by far the easiest way to manage this large sql file was to import it using mysql on the command line. Simply log into your running vagrant box via ssh using the command below:
vagrant ssh
Then browse to the sql file location on the vagrant box, mine usually end up in the projects www folder under /svr/www. You’ll need to go back a few directories to get there using the following command as ssh drops you into the home directory on the box
cd ../
Once into your sql folder, simply run the command below to import the file, changing ‘database_name’ and ‘name_of_your.sql’ respectively. You’ll get prompted for the mysql password which on vagrant is the same as the username – root.
mysql -u root -p database_name < name_of_your.sql
Some hosts are quite specific about the format of the above (20i)
mysql -h hostname -u username -p -D databasename < name_of_your.sql
Other times it’s useful to import a gzipped file:
gunzip < database.sql.gz | mysql -u user -p database
Sometimes I have had instances where an import wold fail, because there are errors in the sql file. One example was with a legacy site which didn’t have a primary index on the options table. To get around this use the -f option to ignore errors when importing.
mysql --force -u user -p database < name_of_your.sql
It’s really quick and much easier than faffing about in phpMyAdmin.
