MySQL useful scripts

• create database

  CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
  

• create user

  CREATE USER 'root'@'localhost' IDENTIFIED BY '123456';
  CREATE USER 'root'@'%' IDENTIFIED BY '123456';
  

• drop user

  DROP USER 'root'@'localhost';
  

• grant permissions

  GRANT ALL ON *.* TO 'root'@'localhost';
  GRANT ALL ON db1.* TO 'root'@'%';
  

• list users

  USE mysql;
  SELECT user,host FROM user;
  

• useful commands

  show master status;
  show master logs;
  show binlog events in '{binlog filename}';
  show processlist;
  show global variables like 'log_bin';
  show global variables like 'binlog_format';
  set global binlog_format=ROW;