Several specific methods of Mysql space cleaning

[root@mysqlhost01 /]# df -lh

[root@mysqlhost01 /]# du -sh /usr
5.5G /usr

mysql>show variables like '%expire_logs_days%'; --mysql 5.7
mysql> show variables like '%binlog_expire_logs_seconds%' --mysql8.0

mysql8.0
MySQL 8 starts with expire_logs_days. Disable binlog_expire_logs_seconds. Set binlog to automatically clear logs. Save time in seconds. Default: 2592000 for 30 days, 14400 for 4 hours, 86400 for 1 day, and 259200 for 3 days.
mysql> set global binlog_expire_logs_seconds=86400;

mysql5.7
The default value is 0, which means that logs do not expire. You can set the global parameters to make it temporarily effective:
mysql>set global expire_logs_days=10;

Step 1: Log in to MySQL and use show binary logs; to view the log file.
mysql>show binary logs;
Step 2: View the log file in use: show master status;
mysql>show master status;
The log file currently in use is mysqlhost01-bin.000010, so this file should be excluded when deleting the log file.
The command to delete the log file: purge binary logs to 'mysqlhost01-bin.000010';
mysql>purge binary logs to 'mysqlhost01-bin.000010';
Delete log files other than mysqlhost01-bin.000010. You can also specify another file name, such as mysql-bin.000003.
Deleting it will free up most of the space.

Step 1: Check the slow log mode mysql>show variables like 'log_output%';
Step 2 Check the Slow log file location show variables like '%slow%';
Step 3 Clear the Slow log [root@mysqlhost01 /]# cd /usr/local/mysql57/mysql5730/data
[root@mysqlhost01 data]# echo "">mysqlhost01-slow.log

Step 1 Check the error log locationmysql>show variables like 'log_error';
Step 2 Check the error log size [root@mysqlhost01 data]# ll -h log.err
Step 3 Clear the error log echo "">/usr/local/mysql57/mysql5730/data/log.err

select table_schema,table_name,
concat(round((data_length+index_length)/1024/1024/1024,2),'G') as tablesize_gb,
table_rows from information_schema.tables
order by tablesize_gb desc limit 5;

table_schema: database name table_name: table name tablesize_gb: table size, in G units table_rows: number of rows 

Conventional tables refer to those that do not meet the standards of large tables.
Delete
Syntax: Delete from table_name [ where condition]
Delete only deletes the data that meets the conditions and will not reduce the space occupied by the table.
After deleting a large amount of data, there will be fragments, which need to be sorted and reclaimed. Optimize table table.name
Or alter table table.name engine='innodb' (will lock the table, please be careful to execute during the business off-peak period)


Truncate
Syntax: Truncate table table_name
Truncate deletes all table data and reclaims the occupied table space.

Drop
Syntax: Drop table table_name
Drop deletes all table data and table structure and reclaims the occupied table space.