Skip to content

Instantly share code, notes, and snippets.

@dkrnl

dkrnl/mysql-optimize-innodb.sh

Created April 15, 2016 02:31
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dkrnl/bfbe7672d47082ab68ce2a593a1556ae to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dkrnl/bfbe7672d47082ab68ce2a593a1556ae to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. 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;