MySQL如何配置安全性、易用性

這篇文章給大家分享的是有關MySQL如何配置安全性、易用性的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

一、設定管理員用戶和密碼

清除不安全的用戶信息，設定管理員用戶為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

上面修改完成并刷新權限后，再次測試MySQL數據庫連接，就必須需要指定用戶名和密碼登錄了。具體操作步驟如下：

[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庫權限隱患

查看當前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庫權限安全隱患：

(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 中間定義文件

創建中間定義文件，提高腳本的復用性。
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

由于文件包含密碼等敏感信息，所以為了安全性，必須要修改文件的權限：

chmod 600 /data/mysqldata/scripts/mysql_env.ini

當然，如果對密碼安全性要求很高，這里的配置文件中的密碼可以置空，后續調用腳本手工輸入密碼即可。

3.2 啟動MySQL服務

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 關閉MySQL服務

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

最后，統一授予所有自定義腳本執行的權限：

chmod u+x /data/mysqldata/scripts/*.sh

配置mysql用戶的環境變量，追加一行：

echo "export PATH=/data/mysqldata/scripts:\$PATH" >> ~/.bash_profile

source ~/.bash_profile

至此，就可以在任意路徑下執行腳本，提升了MySQL操作的易用性。

四、設置開機自動啟動MySQL服務

在上述配置完成的基礎上，
就可以直接在root用戶下編輯/etc/rc.local文件，追加內容：

# autostart MySQL

sudo -i -u mysql /data/mysqldata/scripts/mysql_db_startup.sh 3306 > /home/mysql/mysql_db_startup.log 2>&1

感謝各位的閱讀！關于“MySQL如何配置安全性、易用性”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！