SSL測試 - 憑證申請

這篇主要說明MySQL基本安裝設定及常用的語法

Install MySQL

$ brew install mysql

Init MySQL

$ cd /usr/local/Cellar/mysql/8.0.15/bin
$ ./mysql_secure_installation
...

Start MySQL

$ mysql.server start

Service start

# Mac
$ brew services start mysql

Shutdown MySQL

$ mysql.server stop

Service shutdown

# Mac
$ brew services stop mysql

關於Service的部分，可以再去翻閱Mac及Linux相關文章

[Mac] Brew Service

Login local MySQL

$ mysql -u root -p

Login remote MySQL

$ mysql -u userName -p -h 192.168.0.1 -P 3306

Query Databases

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

Create Database

mysql> create database anc;
Query OK, 1 row affected (0.01 sec)
    
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| anc                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Enter Datebase

mysql> use anc;
Database changed

Create User

mysql> create user 'alan'@'localhost' identified by '********';
Query OK, 0 rows affected (0.01 sec)

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

cause: this is password too simple

Query password validate policy

mysql> show variables like 'validate_password%';
    
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.02 sec)

MySQL密碼驗證原則對照表：

Policy	Value	Test Performed
0	Low	length;
1	MEDIUM	length; numeric, lowercase/uppercase, and special characters
2	STRONG	length; numeric, lowercase/uppercase, and special characters dictionary file

Login root
Query validate role

mysql> select @@validate_password_length;    
mysql> select @@validate_password_policy;

Setting validate role

mysql> set global validate_password_length=3;
mysql> set global validate_password_policy=0;

Param	Desc
validate_password_dictionary_file	插件用於驗証密碼強度的字典文件路徑。
validate_password_length	密碼最小長度，參數默認為8，它有最小值的限制，最小值為：validate_password_number_count + validate_password_special_char_count + (2 * validate_password_mixed_case_count)
validate_password_mixed_case_count	密碼至少要包含的小寫字母個數和大寫字母個數。
validate_password_number_count	密碼至少要包含的數字個數。
validate_password_policy	密碼驗証原則等級。

Add Grant to new User

mysql> grant all privileges on anc.* to 'alan'@'localhost'; 
Query OK, 0 rows affected (0.01 sec)
    
mysql> exit
Bye

Login new User

$ mysql -u alan -p

---

Show Tables

mysql> show tables;
+--------------------+
| Tables_in_anc      |
+--------------------+
| hibernate_sequence |
| role               |
| user               |
| user_role          |
+--------------------+
4 rows in set (0.00 sec)
```    
``` sql
mysql> select * from user;
+------------+--------+-----------+------------+---------+-----------+
| pk_user_id | active | email     | password   | user_id | user_name |
+------------+--------+-----------+------------+---------+-----------+
|          1 | Y      | alan@alan | -921600614 | Alan    | Alan      |
+------------+--------+-----------+------------+---------+-----------+
1 row in set (0.00 sec)