系統(tǒng)配置

# 禁用防火墻與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

# 磁盤分區(qū)(數(shù)據(jù)庫數(shù)據(jù)盤)
mkfs.xfs /dev/sdb
mkdir /data
mount /dev/sdb /data

# 設(shè)置自動掛載
# 獲取磁盤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
# 啟動服務(wù)&設(shè)置開機(jī)自啟動
systemctl start mysqld && systemctl enable mysqld && systemctl status mysqld初始化數(shù)據(jù)庫

4.初始化數(shù)據(jù)庫

# 獲取數(shù)據(jù)庫臨時密碼
grep 'A temporary password' /var/log/mysqld.log | tail -1
# 初始化數(shù)據(jù)庫
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賬號遠(yuǎn)程登錄
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.

# 重新加載特權(quán)表，生效配置
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

修改數(shù)據(jù)庫存放位置

數(shù)據(jù)庫存放在系統(tǒng)盤始終是不安全的，所以一般我們會配置一個數(shù)據(jù)盤，用于單獨(dú)存放數(shù)據(jù)庫文件，在前面的【系統(tǒng)通用配置】中，我們已經(jīng)掛載了磁盤/dev/sdb至/data，這個就是專用用于數(shù)據(jù)庫數(shù)據(jù)存儲的，詳細(xì)MySQL修改方法如下：

# 創(chuàng)建數(shù)據(jù)庫存放目錄
mkdir /data/mysql
# 配置權(quán)限
chown -vR mysql:mysql /data/mysql/
chmod -vR 700 /data/mysql/
# 停止數(shù)據(jù)庫服務(wù)
systemctl stop mysqld
# 復(fù)制原數(shù)據(jù)庫至新目錄
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

# 啟動數(shù)據(jù)庫服務(wù)
systemctl start mysqld && systemctl status mysqld

# 確保數(shù)據(jù)庫啟動成功以后，我們就可以刪除不再需要的原數(shù)據(jù)庫文件了
rm -rf /var/lib/mysql

設(shè)置允許遠(yuǎn)程連接

遠(yuǎn)程連接可以設(shè)置root賬號，也可以單獨(dú)創(chuàng)建一個賬號，專門用于遠(yuǎn)程管理，畢竟root賬號的權(quán)限過大，一旦密碼泄漏嚴(yán)重影響數(shù)據(jù)庫安全。

# 登錄MySQL
[root@localhost ~]# mysql -u root -p
# 進(jìn)入mysql庫
mysql> use mysql;
# 更新域?qū)傩裕?#39;%'表示允許任何主機(jī)訪問
mysql> update user set host="%" where user ="root";
# 全局特權(quán)所有數(shù)據(jù)庫
mysql> grant all privileges on *.* to 'root'@'%' with grant option;
# 生效配置
mysql> FLUSH PRIVILEGES;

MySQL基礎(chǔ)操作

這里簡單說一下單獨(dú)創(chuàng)建用戶，并允許遠(yuǎn)程連接，以及添加、刪除用戶&數(shù)據(jù)庫的操作。

# 登錄MySQL
[root@localhost ~]# mysql -u root -p

# 創(chuàng)建數(shù)據(jù)庫
mysql> CREATE DATABASE test;

# 創(chuàng)建用戶db_test并允許遠(yuǎn)程連接
mysql> CREATE USER 'db_test'@'%' IDENTIFIED BY 'password';

# 授權(quán)允許遠(yuǎn)程訪問test數(shù)據(jù)庫的所有表
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)

# 查詢數(shù)據(jù)庫
mysql> show databases;
+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| mysql                 |
| performance_schema    |
| sys                   |
| test                  |
+-----------------------+
6 rows in set (0.00 sec)

# 刪除數(shù)據(jù)庫
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)

# 退出數(shù)據(jù)庫
mysql> exit
Bye