筆記:MySQL 5.7/8.0 安裝

2021031123:14

CentOS 7 安裝 MySQL 5.7
$ sudo yum install yum-utils -y

$ sudo yum -y localinstall https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
參考:
  https://dev.mysql.com/doc/mysql-repo-excerpt/5.7/en/linux-installation-yum-repo.html
  https://dev.mysql.com/downloads/repo/yum/ 

$ sudo yum-config-manager --disable mysql80-community 
$ sudo yum-config-manager --enable mysql57-community

$ sudo yum -y install mysql-community-server
 
# 啟動 mysqld
$ sudo service mysqld start

# 讓 mysqld 能在重開機時 自動啟動
$ sudo systemctl enable mysqld

$ sudo service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2021-07-05 14:48:48 CST; 14min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 5690 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 5666 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 5693 (mysqld)
    Tasks: 28
   CGroup: /system.slice/mysqld.service
           └─5693 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jul 05 14:48:46 centos-vb.nidbox.cc systemd[1]: Starting MySQL Server...
Jul 05 14:48:48 centos-vb.nidbox.cc systemd[1]: Started MySQL Server.
 

更改密碼

安裝完 mysqld 會產生一組 root 臨時密碼 (這裡的 root 是指資料庫的 root 帳號 ,不是 Linux 作業系統的 root 帳號)
密碼寫在記錄檔: /var/log/mysqld.log
要設定 mysql root 的新密碼後,才能以 root 身份登入 mysql
$ sudo grep 'temporary password' /var/log/mysqld.log
2021-07-05T01:35:18.780057Z 1 [Note] A temporary password is generated for root@localhost: J?onoUo8xCua

設定新密碼:
$ sudo mysql_secure_installation 

sudo mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

密碼需>=8字元,必需含大寫+小寫+數字+符號
New password:   
Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

#是否要移除匿名帳號?
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

#是否要禁止 root 帳號從遠端登入?
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

#是否刪除 test 資料庫
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

#是否馬上讓新密碼生效? (也就是  flush privileges; ) 
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!


mysql 要求的密碼規則有點複雜,在測試機上希望使用比較簡單的密碼時
可以重設規則:
(mysql 5.6.6 新增的密碼強度驗證)
# 以 root 登入 mysql
# 顯示目前的密碼規則
mysql>  show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |密碼最小長度 (default 8)
| validate_password_mixed_case_count   | 1      |密碼必需含有幾個大寫跟小寫字母(1表示需有大小寫各一個)
| validate_password_number_count       | 1      |密碼必需含有幾個數字
| validate_password_policy             | MEDIUM |密碼強度 (default MEDIUM)
| validate_password_special_char_count | 1      |密碼必需含有幾個符號
+--------------------------------------+--------+
7 rows in set (0.00 sec)

# validate_password_policy  
# 0 LOW     只檢查密碼長度
# 1 MEDIUM  檢查密碼長度+數字+大寫+小寫+符號
# 2 STRONG  檢查密碼長度+數字+大寫+小寫+符號字典檔

#設定為 LOW
mysql 7.x
mysql> set global validate_password_policy=0;
mysql> set global validate_password_mixed_case_count=0;
mysql> set global validate_password_number_count=0;
mysql> set global validate_password_special_char_count=0;
mysql> set global validate_password_length=3;

mysql 8.x以後
mysql> set global validate_password.policy=0;
mysql> set global validate_password.mixed_case_count=0;
mysql> set global validate_password.number_count=0;
mysql> set global validate_password.special_char_count=0;
mysql> set global validate_password.length=3;




改密碼:

#5.7.6以後版本
mysql> ALTER USER 'user_name'@'localhost' IDENTIFIED BY 'PASSWORD'; 
mysql> flush privileges;  

#5.7.5(或更舊)
mysql> UPDATE user SET Password=PASSWORD('NEW_PASSWORD') WHERE user='user_name' AND host='localhost';
or
mysql> SET PASSWORD FOR 'user_name'@'localhost' = PASSWORD('NEW_PASSWORD'); 


           

另外在 /etc/my.cnf 加入這行、重啟 mysqld
[mysqld]
validate_password=OFF
或者
validate_password_policy=0  #LOW 只檢查長度