Repair & Optimize all Mysql Databases one liner
Mysql tuner telling you to optimize your tables? or you just happen to be bored? Mysql optimize Reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table. This statement does not work with views. You should use
OPTIMIZE TABLE in these cases, depending on the type of table:
- After doing substantial insert, update, or delete operations on an
InnoDBtable that has its own .ibd file because it was created with the
innodb_file_per_tableoption enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system.
- After deleting a large part of a
ARCHIVEtable, or making many changes to a
ARCHIVEtable with variable-length rows (tables that have
TEXTcolumns). Deleted rows are maintained in a linked list and subsequent
INSERToperations reuse old row positions. You can use
OPTIMIZE TABLEto reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.
This statement requires
INSERT privileges for the table.
well use this one liner to repair/optimize all your DBs in one go.
mysqlcheck --auto-repair --optimize --all-databases