MySQL cheatsheet

Commands I use often, with short explanations.

• Create a database: CREATE DATABASE `my_db` CHARACTER SET utf8 COLLATE utf8_general_ci;

• Show a database’s charset and collation:

  USE db_name;
  SELECT @@character_set_database, @@collation_database;

• Create a user: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

• Grant permissions on a database: GRANT ALL PRIVILEGES ON `DB_name`.* TO 'monty'@'localhost';

• Change a user’s password: ALTER USER some_user@'%' IDENTIFIED BY 'new_password';

• Show the process list (who’s connected and what they’re running):

  SELECT * FROM information_schema.processlist \G
  SHOW PROCESSLIST;

• Enable query logging:

  SET GLOBAL log_output='table';
  SET GLOBAL general_log=1;

• Enable the slow query log:

  SET GLOBAL long_query_time=2;
  SET GLOBAL slow_query_log=1;

• Enable profiling:

  SET profiling=1;
  -- run your query
  SHOW PROFILES;
  SHOW PROFILE FOR QUERY qry_number;
  SET profiling=0;

• Add a column to a table: ALTER TABLE table_name ADD column_name type;

• Change a column’s type: ALTER TABLE table_name MODIFY column_name type;

• Enable read-only mode: SET GLOBAL read_only = ON; or add read_only=1 to /etc/mysql/my.cnf

• Set up replication:

  GRANT REPLICATION SLAVE ON *.* TO "repl_user"@"%" IDENTIFIED BY "password";
  CHANGE MASTER TO MASTER_HOST='master_host_fqdn', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='binlog_file_name', MASTER_LOG_POS=pos_number;

• Skip one statement and restart replication:

  STOP SLAVE;
  SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
  START SLAVE;
  SHOW SLAVE STATUS \G

• Skip a specific replication error – add to my.cnf: slave_skip_errors = error_number

• Faster InnoDB writes (e.g. on a replica) – add to my.cnf:

  # default = 1
  innodb_flush_log_at_trx_commit=2
  innodb_flush_method=O_DIRECT

• Compare databases between servers (or on the same server): mysqldbcompare --server1=root@localhost:3306 --server2=root:pwd@mysql_url:3306 --changes-for=server2 --changes-for=server2 shows the changes needed to make server2 match server1.