一、MYSQL設(shè)置賬號密碼復(fù)雜度
1、設(shè)置密碼復(fù)雜度
[root@localhost tmp]# mysql -uroot -p
2、測試修改密碼
mysql>alter user 'root'@'localhost' identified by 'cnbi123';
3、查看一下當(dāng)前修改后的密碼策略,如果沒有需要啟動安全插件
mysql> SHOW VARIABLES LIKE "%password%";
image.png
4、啟動安全插件
# find / -name validate_password.so
/usr/lib64/mysql/plugin/validate_password.so
首先打開/etc/my.cnf，然后在[mysqld]的下方加入如下代碼：
plugin-load-add=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
重啟mysqld服務(wù)
# systemctl restart mysqld.service
5、登錄
# mysql -uroot -p
# mysql -hlocalhost -P3309 -uroot -ppass123
6、查看一下當(dāng)前修改后的密碼策略
mysql> SHOW VARIABLES LIKE "%password%";
image.png
7、設(shè)置強密碼策略，啟動插件默認(rèn)是強密碼，下面是說明參數(shù)不用設(shè)置
參數(shù)說明
validate_password_policy值
image.png
默認(rèn)是1，即MEDIUM，所以剛開始設(shè)置的密碼必須符合長度，且必須含有數(shù)字，小寫或大寫字母，特殊字符。
如果不想設(shè)置那么復(fù)雜，比如指向設(shè)置root密碼為1234，設(shè)置方式：
首先，修改validate_password_policy參數(shù)的值
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec）
這樣，判斷密碼的標(biāo)準(zhǔn)就基于密碼的長度了。這個由validate_password_length參數(shù)來決定。
mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
| 8 |
+----------------------------+
1 row in set (0.00 sec)
validate_password_length參數(shù)默認(rèn)為8，它有最小值的限制，最小值為：
validate_password_number_count+ validate_password_special_char_count+ (2 * validate_password_mixed_case_count)
其中:
validate_password_number_count #指定了密碼中數(shù)字的長度，
validate_password_special_char_count #指定了密碼中特殊字符的長度，
validate_password_mixed_case_count #指定了密碼中大小字母的長度。
這些參數(shù)，默認(rèn)值均為1，所以validate_password_length最小值為4，如果你顯性指定validate_password_length的值小于4，盡管不會報錯，但validate_password_length的值將設(shè)為4。
如下演示：
mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
| 8 |
+----------------------------+
1 row in set (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
| 4 |
+----------------------------+
1 row in set (0.00 sec)
如果修改了validate_password_number_count，validate_password_special_char_count，validate_password_mixed_case_count中任何一個值，則validate_password_length將進(jìn)行動態(tài)修改。
mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
| 4 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select @@validate_password_mixed_case_count;
+--------------------------------------+
| @@validate_password_mixed_case_count |
+--------------------------------------+
| 1 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> set global validate_password_mixed_case_count=2;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@validate_password_mixed_case_count;
+--------------------------------------+
| @@validate_password_mixed_case_count |
+--------------------------------------+
| 2 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
| 6 |
+----------------------------+
1 row in set (0.00 sec)
二、設(shè)置密碼90天過期
設(shè)置全局密碼過期時間default_password_lifetime，單位為天
1、查看當(dāng)前的密碼過期時間：
mysql> SHOW VARIABLES LIKE "%password%";
image.png
2、首先打開/etc/my.cnf，然后在[mysqld]的下方加入如下代碼：
default_password_lifetime=90
3、保存退出后，重啟mysqld服務(wù)
[root@anolis8 ~]# systemctl restart mysqld.service
4、登錄mysql查看
[root@anolis8 ]# mysql -hlocalhost -uroot -ppass123
mysql> SHOW VARIABLES LIKE "%password%";
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user 'root'@'localhost' identified by 'Pass123#';
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW VARIABLES LIKE "%password%";
+----------------------------------------------+-----------------+
| Variable_name | Value |
+----------------------------------------------+-----------------+
| caching_sha2_password_auto_generate_rsa_keys | ON |
| caching_sha2_password_digest_rounds | 5000 |
| caching_sha2_password_private_key_path | private_key.pem |
| caching_sha2_password_public_key_path | public_key.pem |
| default_password_lifetime | 90 |
| disconnect_on_expired_password | ON |
| generated_random_password_length | 20 |
| mysql_native_password_proxy_users | OFF |
| password_history | 0 |
| password_require_current | OFF |
| password_reuse_interval | 0 |
| report_password | |
| sha256_password_auto_generate_rsa_keys | ON |
| sha256_password_private_key_path | private_key.pem |
| sha256_password_proxy_users | OFF |
| sha256_password_public_key_path | public_key.pem |
| 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 |
+----------------------------------------------+-----------------+
23 rows in set (0.01 sec)
default_password_lifetime=0 時默認(rèn)密碼永不過期。
除全局配置外，也可以創(chuàng)建用戶時指定密碼過期時間
1、創(chuàng)建用戶test_passwd并設(shè)置密碼過期時間為90天
mysql> CREATE USER 'test_passwd'@'localhost' identified by 'Atest_passwd123' PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.10 sec)
mysql> select user,host,password_last_changed,password_lifetime from mysql.user where user='test_passwd';
+-------------+-----------+-----------------------+-------------------+
| user | host | password_last_changed | password_lifetime |
+-------------+-----------+-----------------------+-------------------+
| test_passwd | localhost | 2024-08-22 15:32:34 | 90 |
+-------------+-----------+-----------------------+-------------------+
1 row in set (0.00 sec)
2、創(chuàng)建用戶test_passwd_never并設(shè)置密碼永不過期
mysql> CREATE USER 'test_passwd2'@'localhost' identified by 'Atest_passwd123' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password_last_changed,password_lifetime from mysql.user where user='test_passwd2';
+--------------+-----------+-----------------------+-------------------+
| user | host | password_last_changed | password_lifetime |
+--------------+-----------+-----------------------+-------------------+
| test_passwd2 | localhost | 2024-08-22 15:34:08 | 0 |
+--------------+-----------+-----------------------+-------------------+
1 row in set (0.00 sec)
3、創(chuàng)建用戶test_passwd_default并設(shè)置密碼過期時間遵循系統(tǒng)默認(rèn)值
mysql> CREATE USER 'test_passwd_default'@'localhost' identified by 'Atest_passwd123' PASSWORD EXPIRE DEFAULT;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password_last_changed,password_lifetime from mysql.user where user='test_passwd_default';
+---------------------+-----------+-----------------------+-------------------+
| user | host | password_last_changed | password_lifetime |
+---------------------+-----------+-----------------------+-------------------+
| test_passwd_default | localhost | 2024-08-22 15:35:25 | NULL |
+---------------------+-----------+-----------------------+-------------------+
1 row in set (0.00 sec)
三、設(shè)置登錄失敗處理功能，失敗登錄5次鎖定5分鐘
1、登錄
[root@localhost tmp]# mysql -uroot -p
2、安裝插件
mysql> install plugin CONNECTION_CONTROL soname 'connection_control.so';
Query OK, 0 rows affected (0.16 sec)
mysql> install plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS soname 'connection_control.so';
Query OK, 0 rows affected (0.00 sec)
查看：
mysql> show variables like '%connection_control%';
+-------------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 3 |
| connection_control_max_connection_delay | 2147483647 |
| connection_control_min_connection_delay | 1000 |
+-------------------------------------------------+------------+
3 rows in set (0.01 sec)
3、修改my.cnf文件
vi /etc/my.cnf
在文件中，我們增加如下兩行
connection-control-failed-connections-threshold=5 #登陸失敗次數(shù)限制
connection-control-min-connection-delay=300000 #限制重試時間，此處為毫秒，注意按需求換算，此處為5分鐘
4、重啟mysqld服務(wù)
[root@anolis8 ~]# systemctl restart mysqld.service
5、重新登錄數(shù)據(jù)庫，查看是否生效
mysql> show variables like '%connection_control%';
+-------------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 5 |
| connection_control_max_connection_delay | 2147483647 |
| connection_control_min_connection_delay | 300000 |
+-------------------------------------------------+------------+
3 rows in set (0.01 sec)
四、mysql數(shù)據(jù)庫設(shè)置超時斷開連接
mysql數(shù)據(jù)庫長時間未操作自動斷開連接由參數(shù)：
interactive_timeout和wait_timeout控制，默認(rèn)都是8小時（28800分鐘）
mysql> show variables like '%timeout%';
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| mysqlx_connect_timeout | 30 |
| mysqlx_idle_worker_thread_timeout | 60 |
| mysqlx_interactive_timeout | 28800 |
| mysqlx_port_open_timeout | 0 |
| mysqlx_read_timeout | 30 |
| mysqlx_wait_timeout | 28800 |
| mysqlx_write_timeout | 60 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| replica_net_timeout | 60 |
| rpl_stop_replica_timeout | 31536000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------------+----------+
22 rows in set (0.01 sec)
修改interactive_timeout和wait_timeout即可設(shè)置長時間未操作自動斷開連接。
修改interactive_timeout和wait_timeout為15分鐘（將參數(shù)添加到my.cnf參數(shù)文件）。
步驟：
1.修改配置文件my.cnf添加以下參數(shù)
vim /etc/my.cnf
[mysqld]
...
interactive_timeout=15
wait_timeout=15
2.保存退出后，重啟數(shù)據(jù)庫
[root@anolis8 ~]# systemctl restart mysqld.service
3.查看修改后的參數(shù)
mysql> show variables like '%timeout%';
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 15 |
| lock_wait_timeout | 31536000 |
| mysqlx_connect_timeout | 30 |
| mysqlx_idle_worker_thread_timeout | 60 |
| mysqlx_interactive_timeout | 28800 |
| mysqlx_port_open_timeout | 0 |
| mysqlx_read_timeout | 30 |
| mysqlx_wait_timeout | 28800 |
| mysqlx_write_timeout | 60 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| replica_net_timeout | 60 |
| rpl_stop_replica_timeout | 31536000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 15 |
+-----------------------------------+----------+
22 rows in set (0.01 sec)
五、 mysql 開啟 general_log 日志
在做等保評測時，會要求 mysql 開啟 general_log 日志，該日志會記錄所有的數(shù)據(jù)庫動作，增長幅度非常大，因此適合于在出現(xiàn)問題時臨時開啟一段時間，待問題排查解決后再進(jìn)行關(guān)閉，否則日志文件的增長速度會超出你的想象。
mysql> show variables like '%general_log%';
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/anolis8.log |
+------------------+----------------------------+
2 rows in set (0.01 sec)
可以發(fā)現(xiàn)，當(dāng)前 general_log 參數(shù)為關(guān)閉狀態(tài)（OFF）
日志輸出位置是：/home/www/general_log/gen.log
注意：這個輸出位置不支持目錄，必須設(shè)置成文件。
2、開啟 general_log：
[root@anolis8 ~]# ll /var/lib/mysql/anolis8.log
ls: 無法訪問'/var/lib/mysql/anolis8.log': 沒有那個文件或目錄
[root@anolis8 ~]# touch /var/lib/mysql/anolis8.log
[root@anolis8 ~]# chown -R mysql:mysql /var/lib/mysql/anolis8.log
[root@anolis8 ~]# ll /var/lib/mysql/anolis8.log
-rw-r--r-- 1 mysql mysql 0 8月 22 16:22 /var/lib/mysql/anolis8.log
mysql> set global general_log_file='/var/lib/mysql/anolis8.log';
Query OK, 0 rows affected (0.00 sec)
mysql> set global general_log=1;
Query OK, 0 rows affected (0.05 sec)
mysql> show variables like '%general_log%';
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| general_log | ON |
| general_log_file | /var/lib/mysql/anolis8.log |
+------------------+----------------------------+
2 rows in set (0.00 sec)
3、查看日志文件
到設(shè)置的輸出位置就可以看到對應(yīng)的輸出文件大小正在發(fā)生變化。
下一步就可以利用 logrotate 對輸出文件進(jìn)行滾動存儲了。
[root@anolis8 ~]# ll /var/lib/mysql/anolis8.log
-rw-r--r-- 1 mysql mysql 0 8月 22 16:22 /var/lib/mysql/anolis8.log
[root@anolis8 ~]# ll /var/lib/mysql/anolis8.log
-rw-r--r-- 1 mysql mysql 249 8月 22 16:23 /var/lib/mysql/anolis8.log
參考文檔
https://www.cnblogs.com/lewisat/p/17696410.html
https://www.cnblogs.com/orcl-2018/p/14103451.html
https://www.cnblogs.com/netWild/p/18062917
