系統配置
# 禁用防火墻與SElinux
systemctl stop firewalld && systemctl disable firewalld
setenforce 0
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
# 開啟防火墻啟用MySQL 3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
# 重載配置
firewall-cmd --reload
# 查看端口號是否開啟(返回yes開啟)
firewall-cmd --query-port=3306/tcp
# 獲取防火墻列表
firewall-cmd --list-all
# 磁盤分區(數據庫數據盤)
mkfs.xfs /dev/sdb
mkdir /data
mount /dev/sdb /data
# 設置自動掛載
# 獲取磁盤UUID
[root@mysql8 ~]# blkid | grep sdb
/dev/sdb: UUID="dc71382a-b53a-40ff-a0fe-2c0422105ddd" TYPE="xfs"
# 追加至/etc/fstab
echo "UUID=dc71382a-b53a-40ff-a0fe-2c0422105ddd /data xfs defaults 0 0" >> /etc/fstab
2.配置安裝源
yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
3.安裝mysql5.7
yum install mysql-community-server
# 啟動服務&設置開機自啟動
systemctl start mysqld && systemctl enable mysqld && systemctl status mysqld初始化數據庫
4.初始化數據庫
# 獲取數據庫臨時密碼
grep 'A temporary password' /var/log/mysqld.log | tail -1
# 初始化數據庫
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
# 修改root管理員密碼
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
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.
# 刪除測試庫
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.
# 重新加載特權表，生效配置
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
修改數據庫存放位置
數據庫存放在系統盤始終是不安全的，所以一般我們會配置一個數據盤，用于單獨存放數據庫文件，在前面的【系統通用配置】中，我們已經掛載了磁盤/dev/sdb至/data，這個就是專用用于數據庫數據存儲的，詳細MySQL修改方法如下：
# 創建數據庫存放目錄
mkdir /data/mysql
# 配置權限
chown -vR mysql:mysql /data/mysql/
chmod -vR 700 /data/mysql/
# 停止數據庫服務
systemctl stop mysqld
# 復制原數據庫至新目錄
cp -av /var/lib/mysql/* /data/mysql/
# 修改/etc/my.cnf配置，如下所示：
[root@localhost ~]# cat /etc/my.cnf | grep -v "^#" | grep -v "^$"
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
port=3306
socket=/data/mysql/mysql.sock
[mysqld_safe]
socket=/data/mysql/mysql.sock
nice=0
# 啟動數據庫服務
systemctl start mysqld && systemctl status mysqld
# 確保數據庫啟動成功以后，我們就可以刪除不再需要的原數據庫文件了
rm -rf /var/lib/mysql
設置允許遠程連接
遠程連接可以設置root賬號，也可以單獨創建一個賬號，專門用于遠程管理，畢竟root賬號的權限過大，一旦密碼泄漏嚴重影響數據庫安全。
# 登錄MySQL
[root@localhost ~]# mysql -u root -p
# 進入mysql庫
mysql> use mysql;
# 更新域屬性，'%'表示允許任何主機訪問
mysql> update user set host="%" where user ="root";
# 全局特權所有數據庫
mysql> grant all privileges on *.* to 'root'@'%' with grant option;
# 生效配置
mysql> FLUSH PRIVILEGES;
MySQL基礎操作
這里簡單說一下單獨創建用戶，并允許遠程連接，以及添加、刪除用戶&數據庫的操作。
# 登錄MySQL
[root@localhost ~]# mysql -u root -p
# 創建數據庫
mysql> CREATE DATABASE test;
# 創建用戶db_test并允許遠程連接
mysql> CREATE USER 'db_test'@'%' IDENTIFIED BY 'password';
# 授權允許遠程訪問test數據庫的所有表
mysql> GRANT ALL ON test.* TO 'db_test'@'%';
# 查詢所有用戶
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+------------------------------------+
| query |
+------------------------------------+
| User: 'db_test'@'%'; |
| User: 'mysql.session'@'localhost'; |
| User: 'mysql.sys'@'localhost'; |
| User: 'root'@'localhost'; |
+------------------------------------+
6 rows in set (0.01 sec)
# 查詢數據庫
mysql> show databases;
+-----------------------+
| Database |
+-----------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+-----------------------+
6 rows in set (0.00 sec)
# 刪除數據庫
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
# 更新指定用戶密碼
mysql> UPDATE user SET authentication_string=PASSWORD('密碼') WHERE User='db_test' and Host='%';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
# 刪除用戶
mysql> drop user 'db_test'@'%';
Query OK, 0 rows affected (0.00 sec)
# 生效配置
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 退出數據庫
mysql> exit
Bye
