Get Instant Solutions for Kubernetes, Databases, Docker and more
- 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 table
schema AS `Database`, table
name AS `Table`,
round(((datalength + index
length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (datalength + index
length) 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 your
table WHERE your
date_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 your
table
name 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 'innodb
log
file_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 table
schema, table
name, GROUP
CONCAT(index
name SEPARATOR ', ') AS indexes
FROM information_schema.statistics
WHERE tableschema NOT IN ('information
schema', 'mysql', 'performance_schema', 'sys')
GROUP BY tableschema, table
name, index
type, column
name
HAVING COUNT(*) > 1;
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)
Get Instant Solutions for Kubernetes, Databases, Docker and more
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.
Block quote
Ordered list
Unordered list
Bold text
Emphasis
Superscript
Subscript
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)