Mysql Basics
Create Database;
MySQLcreate database dbname;
or
MySQLcreate database `dbname` CHARACTER SET utf8 COLLATE utf8_general_ci;
or
MySQLcreate schema some_db default character set utf8mb4;
Create User
MySQLcreate user 'user'@'%' identified by 'some_pwd';
Grant Privileges to User
MySQLgrant all privileges on dbname.* to 'user'@'%';
flush privileges;
Update users password
MySQLALTER USER 'userName'@'%' IDENTIFIED BY 'Newpass';
flush privileges;
Show Users
MySQLselect user,host from mysql.user;
Show Grants
MySQLshow grants for 'some_user'@'%';
Give Admin Rights
MySQLGRANT ALL PRIVILEGES ON *.* TO 'some_user'@'%';
MySQLGRANT ALL PRIVILEGES ON *.* TO 'some_user'@'localhost';
MySQLGRANT PROCESS, SELECT ON *.* TO 'username'@'%';
Drop database
MySQLdrop databse some_db;
Show Process list
max connections
MySQLshow variables like "max_connections";
Check the number of currently open connections
MySQLshow status where `variable_name` = 'Threads_connected';
Increase max connections:
MySQLset global max_connections = 200;
max allowed packets
See max allowed packets value:
MySQLSHOW VARIABLES LIKE 'max_allowed_packet';
Change max allowed packets value:
MySQLSET GLOBAL max_allowed_packet=16777216;