Skip to content

Instantly share code, notes, and snippets.

@clevertonh

clevertonh/mysql-optimize-innodb.sh

Forked from dkrnl/mysql-optimize-innodb.sh
Created June 22, 2022 14:03
Show Gist options
  • Save clevertonh/514c915d7a84417ba014937312622e0a to your computer and use it in GitHub Desktop.
Save clevertonh/514c915d7a84417ba014937312622e0a to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @dkrnl dkrnl created this gist Apr 15, 2016.
    11 changes: 11 additions & 0 deletions mysql-optimize-innodb.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,11 @@
    #!/bin/sh

    set -x

    for i in `mysql --batch --execute 'SELECT CONCAT("\`", REPLACE(table_schema, "\`", "\`\`"), "\`.\`", REPLACE(table_name, "\`", "\`\`"), "\`") as \`mysql.db\` FROM information_schema.tables where ENGINE="MyISAM" and table_schema!="information_schema"'`; do mysql --execute "OPTIMIZE NO_WRITE_TO_BINLOG table $i;"; done

    for i in `mysql --batch --execute 'SELECT CONCAT("\`", REPLACE(table_schema, "\`", "\`\`"), "\`.\`", REPLACE(table_name, "\`", "\`\`"), "\`") as \`mysql.db\` FROM information_schema.tables where ENGINE="InnoDB" and table_schema!="information_schema"'`; do mysql --execute "ALTER TABLE $i ENGINE=InnoDB; ANALYZE NO_WRITE_TO_BINLOG TABLE $i;"; done

    mysql --execute 'PURGE BINARY LOGS BEFORE NOW();'
    mysql --execute 'FLUSH TABLES;'
    mysql --execute 'FLUSH QUERY CACHE;