[MySQL] 使用RPM在Linux中離線安裝MySQL

此文章同步刊登於我的部落格

安裝流程

一、下載安裝包(RPM)

1. 下載安裝包按照需求到這裡下載相對應版本的RPM包
   

二、安裝MySQL

1. 將tar檔上傳到server上
2. 解壓縮tar檔

   mkdir mysql
   tar xvf mysql-5.7.35-1.el7.x86_64.rpm-bundle.tar -C ./mysql
   cd mysql
   ls -alh

3. 安裝rpm包

   rpm -ivh mysql-community-client* --nodeps --force
   rpm -ivh mysql-community-common* --nodeps --force
   rpm -ivh mysql-community-libs* --nodeps --force
   rpm -ivh mysql-community-server* --nodeps --force

4. 確認安裝包

   rpm -qa | grep mysql

5. 啟動MySQL，並確認啟動成功

   systemctl start mysqld
   systemctl status mysqld

6. 查看root密碼

預設的root密碼會在/var/log/mysqld.log裡

grep "A temporary password" /var/log/mysqld.log

7. 初始化資料庫

   # mysql_secure_installation
   
   New password: NEW_PASSWORD
   Re-enter new password: NEW_PASSWORD
   Change the password for root ? N
   Do you wish to continue with the password provided? Y
   Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
   Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
   Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
   Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y

8. 修改root密碼

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD';
   FLUSH PRIVILEGES;

三、設定防火牆

參考資料

1. 查看防火牆狀態

   systemctl status firewalld

2. 查詢是否有mysql服務名稱

   firewall-cmd --get-services | grep mysql

3. 將mysql服務永久設定到public中

• 有服務名稱：

  firewall-cmd --zone=public --permanent --add-service=mysql

• 沒有服務名稱：

  firewall-cmd --zone=public --permanent --add-port=3306/tcp

4. 查看public的永久設定值

   firewall-cmd --zone=public --permanent --list-services

   

資料庫操作

一、建立使用者

1. 從外部sql檔執行語法

   mysql -uroot -p --default-character-set=utf8 <SQL_FILE

2. 建立使用者

   CREATE USER 'user'@'CLIENT_IP' IDENTIFIED BY 'NEW_PASSWORD';
   FLUSH PRIVILEGES;

3. 設定密碼不過期

   ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
   FLUSH PRIVILEGES;

4. 給予全部權限

   GRANT ALL PRIVILEGES ON `DB_NAME`.* TO 'user'@'CLIENT_IP' IDENTIFIED BY 'NEW_PASSWORD';
   FLUSH PRIVILEGES;

5. 查看使用者清單

   SELECT user, host FROM mysql.user;

6. 查看授權

   SHOW GRANTS FOR 'user'@'CLIENT_IP';

7. 限縮權限先移除所有權限，再重新賦予

   revoke all privileges on *.* from 'user'@'CLIENT_IP';
   GRANT SELECT,UPDATE,INSERT,DELETE ON `DB_NAME`.* TO 'usernamep'@'localhost' IDENTIFIED BY PASSWORD 'password';
   FLUSH PRIVILEGES;

8. 刪除使用者

   DELETE FROM mysql.user WHERE user='USERNAME' AND host='HOST';
   FLUSH PRIVILEGES;

環境

• Red Hat Enterprise Linux 7
• MySQL 5.7.35

參考資料

• https://blog.gtwang.org/linux/centos-7-firewalld-command-setup-tutorial/2/