Login to MySQL

mysql -u [username] -p

MySQL User Management


DESC mysql.user:
SELECT host, user FROM mysql.user;

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
-- Hostname % or Localhost

DROP USER 'username'@'hostname';

ERROR 1819 (HY000)
Your password does not satisfy the current policy requirements

SHOW VARIABLES LIKE 'validate_%';
SET GLOBAL validate_password_policy=LOW;

Database Management

-- List all DATABASE 
SHOW DATABASES;

-- List database with DEFAULT_CHARACTER_SET_NAME and DEFAULT_COLLATION_NAME
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

-- Create DATABASE and set CHARACTER_SET and COLLATION_NAME
CREATE DATABASE db_name [DEFAULT] CHARACTER SET utf8 [DEFAULT] COLLATE utf8_general_ci;

-- Change CHARACTER_SET and COLLATION_NAME
ALTER DATABASE db_name [DEFAULT] CHARACTER SET utf8 [DEFAULT] COLLATE utf8_general_ci;

-- Grant user to database
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';
GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'host';
GRANT ALL|SELECT|INSERT|UPDATE|DELETE ON db_name.* TO 'username'@'host';
FLUSH PRIVILEGES;

-- Show user grant;
SHOW GRANTS FOR 'username'@'hostname';

-- Revoke
REVOKE ALL PRIVILEGES ON *.* TO 'username'@'host';
REVOKE ALL PRIVILEGES ON db_name.* TO 'username'@'host';
REVOKE ALL|SELECT|INSERT|UPDATE|DELETE ON db_name.* TO 'username'@'host';

Table Management

-- Create table and set CHARACTER_SET and COLLATION_NAME
CREATE TABLE tbl_name (column_list) [DEFAULT] CHARACTER SET charset_name COLLATE collation_name;

-- Change table and set CHARACTER_SET and COLLATION_NAME
ALTER TABLE tbl_name (column_list) [DEFAULT] CHARACTER SET charset_name COLLATE collation_name;

-- Temp
SHOW VARIABLES LIKE 'collation%';

Quit

QUIT;

https://dev.mysql.com/doc/refman/8.0/en/show-grants.html
https://dev.mysql.com/doc/refman/8.0/en/show-collation.html
https://dev.mysql.com/doc/refman/8.0/en/charset-database.html
https://dev.mysql.com/doc/refman/8.0/en/charset-table.html
https://stackoverflow.com/questions/5906585/how-to-change-the-character-set-and-collation-throughout-a-database

วันอาทิตย์ที่ 14 มิถุนายน พ.ศ. 2563

[MySQL] Command Line Basic