[mysql] cmd

# base
mysql --help
mysql // user: root, host: localhost
mysql -u <user> [-p] [password]
mysql -u <user> [-h] [host]

# mysql shell
## common
status;
select version();
select database(); // show current database
select current_user(); // show current user
show grants; // show current user owning grants
show variables;
source <file-path>;
exit;

## database
create database <database>;
drop database <database>;
show databases;
use <database>;

## table
create table
drop table <table>;
show tables;
desc <table>; // <=> describe <table>;

## encoding
show character set; // Charset, Description, Default collation, Maxlen
show variables like 'character_set%'; // character_set_client, character_set_connection, character_set_database
show variables like 'collation%';
set names <>;
set character set ;

# [note] select user table
use mysql;
select Host, User, Password from user;

## user
select current_user();
create user <user>[@<host>]; // host default: %
create user <user>[@<host>] identified by '<password>';
drop user <user>;
rename user <user> to <new-user>;
set password = password('<password>'); // edit password of the current user
set password for <user> = password('<password>'); // edit password of some user
=> ref: https://dotblogs.com.tw/supershowwei/2016/10/23/231423

## privilege
grant all privileges on <database>[.<table>] to '<user>'@'<host>';
revoke all privileges on <database>[.<table>] from '<user>'@'<host>';
flush privileges;

# [note] problems when access mysql
---
client: 192.168.145.1
server: 192.168.145.153
---
mysql -u root -h 192.168.145.153
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.145.153' (10060 "Unknown error")
=> check: systemctl status mariadb // Active: active (running)
=> check: firewall-cmd --list-all // ports: 3306/tcp

mysql -u root -h 192.168.145.153
ERROR 1130 (HY000): Host '192.168.145.1' is not allowed to connect to this MariaDB server
=> check: select Host, User, Password from mysql.user;

mysql -u root -h 192.168.145.153
ERROR 1045 (28000): Access denied for user 'root'@'192.168.145.1' (using password: NO)
=> check: select Host, User, Password from mysql.user;

mysql -u root -h 192.168.145.153 -p
ERROR 1045 (28000): Access denied for user 'root'@'192.168.145.1' (using password: YES)
=> check: select Host, User, Password from mysql.user;

mysql -u root -h 192.168.145.153 -p
create user hochun836@192.168.145.1 identified by 'test';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
=> check: show grants;
=> check: grant all privileges on *.* to 'root'@'192.168.145.1'; flush privileges;