How to truncate mysql database table

Sometime swollen table in a databasecan cause disk space problem and if the data inside if expandable then you want/need to empty the table .

First connect to the server with putty, To get the databases on server and there size, execute :

du -h --max-depth=1 /var/lib/mysql/

Enter the mysql prompt :

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 10.3.13-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

Then to get the database size in GB, execute :

SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES GROUP BY table_schema;

Then to get the table size in MB, execute :

SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "testdb" ORDER BY (data_length + index_length) DESC;

To get the table size in GB, execute :

SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 AS TABLE_SIZE_in_MB FROM information_schema.TABLES;

By now you should know your database and tables size ! in order to truncate table, when still connected to the mysql prompt, execute :

USE database_name;

then empty the table (this could take some time if the table is big) :

TRUNCATE TABLE table_name;

Check the table size again to see the changes .

Good Luck