这篇文章给大家分享的是有关MySQL如何配置安全性、易用性的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。

一、设定管理员用户和密码

清除不安全的用户信息,设定管理员用户为system,密码为mysql。
具体操作步骤如下:

[mysql@JY-DB ~]$ mysql

Welcome to the MySQL monitor.Commands end with ; or \g.

Your MySQL connectionid 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 registeredtrademark of Oracle Corporation and/or its

affiliates. Othernames 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 rowsaffected (0.05 sec)

(root@localhost)[(none)]>update mysql.user set user='system', password=password('mysql');

Query OK, 1 rowaffected (0.03 sec)

Rows matched:1 Changed: 1 Warnings: 0

(root@localhost)[(none)]>flush privileges;

Query OK, 0 rowsaffected (0.03 sec)

(root@localhost)[(none)]>\q

Bye

上面修改完成并刷新权限后,再次测试MySQL数据库连接,就必须需要指定用户名和密码登录了。具体操作步骤如下:

[mysql@JY-DB ~]$ mysql

ERROR 1045 (28000): Accessdenied for user 'root'@'localhost' (using password: NO)

[mysql@JY-DB ~]$mysql -usystem -pmysql

Warning: Using apassword on the command lineinterface can be insecure.

Welcome to the MySQLmonitor. Commands end with ; or \g.

Your MySQL connectionid 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 registeredtrademark of Oracle Corporation and/or its

affiliates. Othernames may be trademarks of their respective

owners.

Type 'help;' or '\h'for help. Type '\c' toclear 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.00sec)

(system@localhost)[(none)]>

处理test库权限安全隐患:

(system@localhost)[(none)]>truncate table mysql.db;

Query OK, 0 rowsaffected (0.04 sec)

(system@localhost)[(none)]>flush privileges;

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