學習筆記-MySQL部署及初始化設定 - CentOS

下載下傳連結

https://dev.mysql.com/downloads/mysql/           

MySQL安裝前置準備

#    檢查是否存在MariaDB
rpm -qa | grep mariadb
#    删除現存MariaDB
yum -y remove mariadb*
#    檢查是否存在MySQL
rpm -qa | grep mysql
#    删除現存MySQL
yum -y remove mysql*
#    删除舊配置檔案
rm -rf /etc/my.cnf
#    删除MySQL相關檔案
rm -rf /var/lib/mysql            

安裝MySQL

#    配置安裝源
sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
sudo rpm -Uvh http://172.16.28.11/mysql80-community/mysql80-community-release-el7-3.noarch.rpm
#    更新MySQL公鑰
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
#    安裝MySQL，以防萬一增加--nogpgcheck參數，跳過公鑰安裝
sudo yum --enablerepo=mysql80-community install -y mysql-community-server --nogpgcheck           

啟動MySQL

#    啟動MySQL
systemctl start mysqld.service
#    檢查運作狀态
systemctl status mysqld.service
#    設定MySQL為開機自啟動
systemctl enable mysqld.service
#    檢視端口運作情況
netstat -nlpt
#    停止MySQL
systemctl stop mysqld
#    重新開機MySQL
systemctl restart mysqld           

MySQL 初始化設定

#    檢視預設密碼
cat /var/log/mysqld.log | grep localhost
cat /var/log/mysql/mysqld.log | grep localhost
#    登入MySQL Shell
mysql -uroot -p
#    修改MySQL密碼 （密碼為P@ssw0rd）
ALTER USER USER() IDENTIFIED BY 'P@ssw0rd';
#    檢查系統使用者通路權限
use mysql;
select user,host,plugin,authentication_string from mysql.user;
select user,host,plugin from mysql.user;
#    開啟 root 遠端登入權限，并重新整理
update mysql.user set host='%' where user='root';
flush privileges;
#    退出MySQL Shell
exit           

使用初始化向導配置MySQL

#    檢視預設密碼
cat /var/log/mysqld.log
#    資料庫初始化向導
sudo mysql_secure_installation
#    確定MySQL伺服器部署的安全。輸入使用者root的密碼：
Securing the MySQL server deployment.
Enter password for user root: 
#    使用者帳戶根的現有密碼已過期。請設定新密碼。
The existing password for the user account root has expired. Please set a new password.
#    新密碼：
New password: 
#    重新輸入新密碼
Re-enter new password: 
#    是否更換root密碼 N
Change the password for root ? ((Press y|Y for Yes, any other key for No) 
#    是否删除匿名使用者 Y
Remove anonymous users? (Press y|Y for Yes, any other key for No) :
#     是否禁止root使用者遠端登入 N
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :
#     是否删除test資料庫 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) :           

測試MySQL

#    進入MySQL Shell
sudo mysql -uroot -pP@ssw0rd
#    測試MySQL，重新整理權限
flush privileges;
#    測試MySQL，列出資料庫
show databases;
#    測試MySQL，檢視使用者
select user,host from mysql.user;
#    退出MySQL Shell
exit           

修改MySQL密碼加密方式

#    修改root使用者密碼加密方式為mysql_native_password 并修改密碼
ALTER USER `root`@`%` IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
ALTER USER `keystone`@`%` IDENTIFIED WITH mysql_native_password;           

取消非root使用者登入MySQL的root使用者限制

# 進入MySQL Shell
sudo mysql -uroot -pP@ssw0rd
# 取消非root使用者登入MySQL的root使用者限制
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
update user set host='%' where user='root';
Grant all privileges on test.* to 'root'@'%';
alter user root identified with mysql_native_password by 'P@ssw0rd';
Grant all privileges on root.* to 'root'@'%';