Cleanup WordPress database

Handy cleanup SQL for WordPress database.

Advertisements

SQL to get WooCommerce product categories (terms)

For an import script I needed to get WooCommerce product categories so I ended up looking the generated query in /var/log/mysql/query.log for get_the_terms($postID, ‘taxonomy_name’)

I ended up using the following SQL query to get product categories:

SELECT t.*, tt.*
FROM wp_terms AS t 
INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id 
INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN ('product_cat') AND tr.object_id IN (11448)

Purging mysql-bin files, a.k.a. saving GBs of SSD space

My SSD was filling up and while searching for the cause I ended at MySQL binary log files. They used almost 40GB :-O while I don’t use the logs so often and especially not so backdated

$ sudo ls -lSh /var/lib/mysql |head 
total 7.8G
-rw-rw---- 1 mysql mysql 1.1G May 18 13:34 mysql-bin.000521
-rw-rw---- 1 mysql mysql 796M May 14 20:36 mysql-bin.000517
-rw-rw---- 1 mysql mysql 528M May 21 20:18 mysql-bin.000525
-rw-rw---- 1 mysql mysql 370M May 22 20:18 mysql-bin.000526
-rw-rw---- 1 mysql mysql 367M May 17 19:44 mysql-bin.000520
-rw-rw---- 1 mysql mysql 354M May 13 20:38 mysql-bin.000516
-rw-rw---- 1 mysql mysql 348M May 2 20:25 mysql-bin.000503
-rw-rw---- 1 mysql mysql 297M May 18 20:13 mysql-bin.000522
-rw-rw---- 1 mysql mysql 285M May 16 20:29 mysql-bin.000519

After some googling I found how to purge the files.

PURGE BINARY LOGS BEFORE '2017-05-01 00:00:00';

Because I don’t want to be bothered with this same issue a set expire_log_days to the 60 days in my.cnf, because the default is 0, so no automatic removal.