此文章同步刊登於我的部落格
安裝流程
一、下載安裝包(RPM)
- 下載安裝包按照需求到這裡下載相對應版本的RPM包
二、安裝MySQL
- 將tar檔上傳到server上
- 解壓縮tar檔
mkdir mysql
tar xvf mysql-5.7.35-1.el7.x86_64.rpm-bundle.tar -C ./mysql
cd mysql
ls -alh
- 安裝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 - 確認安裝包
rpm -qa | grep mysql
- 啟動MySQL，並確認啟動成功
systemctl start mysqld
systemctl status mysqld - 查看root密碼
預設的root密碼會在/var/log/mysqld.log裡
grep "A temporary password" /var/log/mysqld.log
- 初始化資料庫
# 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
- 修改root密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD';
FLUSH PRIVILEGES;
三、設定防火牆
- 查看防火牆狀態
systemctl status firewalld - 查詢是否有mysql服務名稱
firewall-cmd --get-services | grep mysql
- 將mysql服務永久設定到public中
- 有服務名稱：
firewall-cmd --zone=public --permanent --add-service=mysql
- 沒有服務名稱：
firewall-cmd --zone=public --permanent --add-port=3306/tcp
- 查看public的永久設定值
firewall-cmd --zone=public --permanent --list-services
資料庫操作
一、建立使用者
- 從外部sql檔執行語法
mysql -uroot -p --default-character-set=utf8 <SQL_FILE
- 建立使用者
CREATE USER 'user'@'CLIENT_IP' IDENTIFIED BY 'NEW_PASSWORD';
FLUSH PRIVILEGES; - 設定密碼不過期
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
FLUSH PRIVILEGES; - 給予全部權限
GRANT ALL PRIVILEGES ON `DB_NAME`.* TO 'user'@'CLIENT_IP' IDENTIFIED BY 'NEW_PASSWORD';
FLUSH PRIVILEGES; - 查看使用者清單
SELECT user, host FROM mysql.user;
- 查看授權
SHOW GRANTS FOR 'user'@'CLIENT_IP';
- 限縮權限先移除所有權限，再重新賦予
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; - 刪除使用者
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/
