這篇文章給大家分享的是有關(guān)MySQL如何配置安全性、易用性的內(nèi)容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過(guò)來(lái)看看吧。
創(chuàng)新互聯(lián)是專(zhuān)業(yè)的通江網(wǎng)站建設(shè)公司，通江接單;提供網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站,網(wǎng)頁(yè)設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專(zhuān)業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行通江網(wǎng)站開(kāi)發(fā)網(wǎng)頁(yè)制作和功能擴(kuò)展;專(zhuān)業(yè)做搜索引擎喜愛(ài)的網(wǎng)站,專(zhuān)業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來(lái)合作!
一、設(shè)定管理員用戶(hù)和密碼
清除不安全的用戶(hù)信息，設(shè)定管理員用戶(hù)為system，密碼為mysql。
具體操作步驟如下：
[mysql@JY-DB ~]$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.30-log JSS for mysqltest
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(root@localhost)[(none)]>
(root@localhost)[(none)]> select user, host from mysql.user;
+------+----------------+
| user | host |
+------+----------------+
| root | 127.0.0.1 |
| root | ::1 |
| | jy-db |
| root | jy-db |
| | localhost |
| root | localhost |
+------+----------------+
6 rows in set (0.04 sec)
(root@localhost)[(none)]> delete from mysql.user where (user,host) not in (select 'root', 'localhost');
Query OK, 5 rows affected (0.05 sec)
(root@localhost)[(none)]> update mysql.user set user='system', password=password('mysql');
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(root@localhost)[(none)]> flush privileges;
Query OK, 0 rows affected (0.03 sec)
(root@localhost)[(none)]> \q
Bye
上面修改完成并刷新權(quán)限后，再次測(cè)試MySQL數(shù)據(jù)庫(kù)連接，就必須需要指定用戶(hù)名和密碼登錄了。具體操作步驟如下：
[mysql@JY-DB ~]$ mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[mysql@JY-DB ~]$ mysql -usystem -pmysql
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.30-log JSS for mysqltest
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(system@localhost)[(none)]>
二、處理test庫(kù)權(quán)限隱患
查看當(dāng)前mysql.db信息：
(system@localhost)[(none)]> select * from mysql.db \G
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_%
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
2 rows in set (0.00 sec)
(system@localhost)[(none)]>
處理test庫(kù)權(quán)限安全隱患：
(system@localhost)[(none)]> truncate table mysql.db;
Query OK, 0 rows affected (0.04 sec)
(system@localhost)[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
(system@localhost)[(none)]> select * from mysql.db \G
Empty set (0.00 sec)
(system@localhost)[(none)]>
三、自定義腳本提升易用性
3.1中間定義文件
創(chuàng)建中間定義文件，提高腳本的復(fù)用性。
vi /data/mysqldata/scripts/mysql_env.ini
# set env
MYSQL_USER=system
MYSQL_PASS='mysql'
# check parameter
if [ $# -ne 1 ]
then
HOST_PORT=3306
else
HOST_PORT=$1
fi
由于文件包含密碼等敏感信息，所以為了安全性，必須要修改文件的權(quán)限：
chmod 600 /data/mysqldata/scripts/mysql_env.ini
當(dāng)然，如果對(duì)密碼安全性要求很高，這里的配置文件中的密碼可以置空，后續(xù)調(diào)用腳本手工輸入密碼即可。
3.2啟動(dòng)MySQL服務(wù)
vi /data/mysqldata/scripts/mysql_db_startup.sh
#!/bin/sh
source /data/mysqldata/scripts/mysql_env.ini
echo "Startup MySQL Service: localhost_"${HOST_PORT}
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/${HOST_PORT}/my.cnf &
3.3關(guān)閉MySQL服務(wù)
vi /data/mysqldata/scripts/mysql_db_shutdown.sh
#!/bin/sh
source /data/mysqldata/scripts/mysql_env.ini
echo "Shutdown MySQL Service: localhost_"${HOST_PORT}
/usr/local/mysql/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${HOST_PORT}/mysql.sock shutdown
3.4快捷登錄MySQL
vi /data/mysqldata/scripts/mysqlplus.sh
#!/bin/sh
source /data/mysqldata/scripts/mysql_env.ini
echo "Login MySQL Service： localhost_"${HOST_PORT}
/usr/local/mysql/bin/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${HOST_PORT}/mysql.sock $2
最后，統(tǒng)一授予所有自定義腳本執(zhí)行的權(quán)限：
chmod u+x /data/mysqldata/scripts/*.sh
配置mysql用戶(hù)的環(huán)境變量，追加一行：
echo "export PATH=/data/mysqldata/scripts:\$PATH" >> ~/.bash_profile
source ~/.bash_profile
至此，就可以在任意路徑下執(zhí)行腳本，提升了MySQL操作的易用性。
四、設(shè)置開(kāi)機(jī)自動(dòng)啟動(dòng)MySQL服務(wù)
在上述配置完成的基礎(chǔ)上，
就可以直接在root用戶(hù)下編輯/etc/rc.local文件，追加內(nèi)容：
# autostart MySQL
sudo -i -u mysql /data/mysqldata/scripts/mysql_db_startup.sh 3306 > /home/mysql/mysql_db_startup.log 2>&1
感謝各位的閱讀！關(guān)于“MySQL如何配置安全性、易用性”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！
重慶分公司
重慶分公司
