• List all users in the current MySQL server: 

    SELECT User, Host FROM mysql.user;

  • Remove a user from the server: 

    DROP USER '<User>'@'<Host>';

  • Remove all binary logs: 

    RESET MASTER;

  • Delete all binary logs but keep a week worth of logs: 

    1$ mysql --verbose --execute="PURGE BINARY LOGS BEFORE '`date +"%Y-%m-%d" -d last-week`';"

    And if you put this in a cron-tab, don’t forget to escape percents: 

    1$ mysql --verbose --execute="PURGE BINARY LOGS BEFORE '`date +\%Y-\%m-\%d -d last-week`';"

  • Check, auto-repair and optimize all databases: 

    1$ mysqlcheck --auto-repair --optimize --all-databases

  • Export a database: 

    1$ mysqldump -u my_user "my-database" > data.sql

  • Here is a cron -able command to restart a MySQL service if no process found active: 

    1$ [ `ps axu | grep -v "grep" | grep --count "mysql"` -le 0 ] && /etc/init.d/mysql restart

  • Monitor the queries being run ( source  ): 

    1$ watch -n 1 mysqladmin --user=XXXXX --password=XXXXX processlist

  • Get the list of default configuration parameters the server will use regardless of the values set in config files ( source  ): 

    1$ mysqld --no-defaults --verbose --help

  • Migrate all tables of all databases from MyISAM to InnoDB: 

    1$ mysql --skip-column-names --silent --raw --execute="SELECT CONCAT(table_schema , '.', table_name) FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE' AND engine='MyISAM';" | xargs -I '{}' mysql --verbose --execute="ALTER TABLE {} ENGINE=InnoDB;"