- Identify and delete any unnecessary files or logs in the server to quickly free up space.cd /var/log/mysql
sudo rm -rf your-unneeded-log-files.log
- Use this query to find large tables that might be candidates for cleanup or pruning.SELECT tableschema AS `Database`, tablename AS `Table`,
round(((datalength + indexlength) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (datalength + indexlength) DESC;
- If binary logging is enabled and you don't need the old logs, you can purge them.PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';
- Based on the previous step, decide if you can delete old or unnecessary data from large tables.DELETE FROM yourtable WHERE yourdate_column < 'YYYY-MM-DD';
- After cleanup, optimize the table to reclaim space.OPTIMIZE TABLE your_table;
- For InnoDB tables, consider enabling compression for large tables.ALTER TABLE yourtablename ROW_FORMAT=COMPRESSED;
- If possible, add more disk space to the server. This might involve contacting your hosting provider or using cloud management tools depending on your environment.
- If the InnoDB log files are consuming too much space, consider resizing them. Note: This requires restarting MySQL.
- Check the current size:SHOW VARIABLES LIKE 'innodblogfile_size';
- To change the size, you'll need to edit the `my.cnf` or `my.ini` file and restart MySQL.
- If you have an additional storage device with more space, move the MySQL data directory to this location.
- Stop MySQL service.sudo systemctl stop mysql
- Copy the data directory to the new location.sudo cp -R -p /var/lib/mysql /new/path
- Update the `datadir` in `my.cnf` or `my.ini` with the new path and restart MySQL.
- If there are tables with historical data that are not frequently accessed, consider archiving this data to a separate database or storage.
- Duplicate indexes can consume unnecessary space.SELECT tableschema, tablename, GROUPCONCAT(indexname SEPARATOR ', ') AS indexes
FROM information_schema.statistics
WHERE tableschema NOT IN ('informationschema', 'mysql', 'performance_schema', 'sys')
GROUP BY tableschema, tablename, indextype, columnname
HAVING COUNT(*) > 1;
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)
