Lots of issues cropping up on one of my CPanel servers, most of which are pointing to issues with MySQL.

While trying to test I have copied a database for a WordPress website from the live site to a dev site, or at least I attempted to do so.

MySQL 8.0 Incorrect format parameter error message in phpMyAdmin

Which led me down a rabbit-hole of database optimisation tools, plugins, and SQL configuration.

Having run some WordPress plugins to remove rubbish, I exported again to find that all that effort only removed 2MB of data and was essentially a waste of time. Obviously the system is full of cruft, but identifying the cruft takes more effort than WP Database Cleaner plugins can do. I’ll come back to this later.

In any case, the export .sql file was too big, so I cut it into slices, finding that even with grabbing a stack of lines, there was a bulky section of data in one area and I had to mess around with the file slice sizes to get the largest file down to under the 128M and even then it was still 129M on disk, but probably under the 128MiB vs 128MB.

After looking at the wrong end of the puzzle for an hour, what I eventually found was that I should have checked the diskspace consumed by the tables and / or the number of rows in the tables. In this case only one table was accounting for 130MB of the total 190MB of the database. It was a log record and defaulted to 30 day retention which was consuming a large chunk of space. Reset to have 14 days retention, truncated the table removing some of the older records and tried again.

This time, with 40MB reduced file size, exporting the Structure first and then data in two chunks, that large log table in one, and everything else in the other. Importing 3 files to the new database went smoothly.

Lesson learnt, before exporting SQL, review what is being exported, and make an assessment of expected output file sizes. It should save a lot of effort.