Login to MySQL

1	`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

1 2	`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;

1 2	`-- 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