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如何配置安全性、易用性”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！