Basics

MySQL Cheatsheet

Create Database;

create database dbname;

or

create database `dbname` CHARACTER SET utf8 COLLATE utf8_general_ci;

or

create schema some_db default character set utf8mb4;

Create User

create user 'user'@'%' identified by 'some_pwd';

Grant Privileges to User

grant all privileges on dbname.* to 'user'@'%';
flush privileges;

Update users password

ALTER USER 'userName'@'%' IDENTIFIED BY 'Newpass';
flush privileges;

Show Users

select user,host from mysql.user;

Show Grants

show grants for 'some_user'@'%';

Give Admin Rights

GRANT ALL PRIVILEGES ON *.* TO 'some_user'@'%';

GRANT ALL PRIVILEGES ON *.* TO 'some_user'@'localhost';

Drop database

drop databse some_db;

Show Process list

show processlist;

max connections

show variables like "max_connections";

Increase max connections:

set global max_connections = 200;

max allowed packets

See max allowed packets value:

SHOW VARIABLES LIKE 'max_allowed_packet';

Change max allowed packets value:

SET GLOBAL max_allowed_packet=16777216;