[Solution] Cpanel backup wizard gives empty 1KB mysql file for wordpress

Hi all,
I wish to share a tip/technique that I learned after wasting whole afternoon (so that your time is not wasted should you run in similar trouble).

Nature of problem
——————
You’ve a self-hosted wordpress. You goto cpanel=> backup wizard and try to take “partial backup” of mysql file. But it gives you only 1kb zip file and when you try to open the sql file inside it using notepad, it is blank!

Following solution worked in my case:
——————–
The SQL file has grown beyond 50MB size. In my case, I was able to get perfect backups until it was under 50MB. But just this afternoon, when it crossed the 50MB size, the problem started. To reduce the SQL filesize, I did two things

i)(assuming that you use akismet antispam plugin since a long time).
That akismet bloats your commentmeta with lot of redudant entries. Often your commentmeta is larger in size than your posts! So run following SQL commands:

SELECT * FROM wp_commentmeta WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_comments
)

observe that it shows you lot of junk entries. So execute following command:

DELETE FROM wp_commentmeta WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_comments
)

important: in above commands, you’ll need to change prefix “Wp_” to whatever prefix you have in your database. (for example, any security plugin might have changed it to “wp4234_”)
after you do this, “optimize database”, directly from phpmyadmin or via any wordpress plugin and this should drastically bring down your database size without removing any post, comment or setting. don’t worry, it works and it is safe.
Another technique to reduce database size:

ii) (assuming that you use better wordpress security plugin)
goto cpanel> phpmyadmin> your wordpress sQL database, there is a table called “_bwps_log”. Click on “Empty”. That’ll drastically reduce the database.

After that, my sql size reduced from ~55MB to just 28MB and then I was able to use the cpanel > backup wizard > partial backup > mysql and it gave me working backup files and nolong 1kb empty backup files.

By the way, I had also tried following methods but they didnot work:
——————————-
1. I deleted .my.cnf file from the cpanel, as some articles suggested. But it didnot solve the problem.

2. Some articles suggest to use “backwpup” plugin to get backups via wordpress dashboard itself. This didnot work because Bacwpup gave error because some mysql component was not installed.