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 theinnodb_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
MyISAMorARCHIVEtable, or making many changes to aMyISAMorARCHIVEtable with variable-length rows (tables that haveVARCHAR,VARBINARY,BLOB, orTEXTcolumns). Deleted rows are maintained in a linked list and subsequentINSERToperations reuse old row positions. You can useOPTIMIZE 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 SELECT and 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
voila!
