Shulou(Shulou.com)05/31 Report--

This article is about how MySQL is configured for security and ease of use. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

First, set the administrator user and password

Clear the unsafe user information, set the administrator user to system and the password to mysql.

The specific steps are as follows:

[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.05sec)

(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

After the above modification and refresh permissions, test the MySQL database connection again, you must specify the user name and password to log in. The specific steps are as follows:

[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)] >

Second, deal with the hidden dangers of test database permissions.

View current mysql.db information:

(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)] >

Deal with the security risks of test library permissions:

(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)] >

Custom scripts to improve ease of use

3.1 Intermediate definition file

Create intermediate definition files to improve the reusability of scripts.

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

Because the file contains sensitive information such as passwords, you must modify the permissions of the file for the sake of security:

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

Of course, if the password security requirement is very high, the password in the configuration file here can be left empty, and the subsequent call script can enter the password manually.

3.2 start the MySQL service

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 shut down the MySQL service

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.Quick login to 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

Finally, uniformly grant permissions to execute all custom scripts:

Chmod uplix / data/mysqldata/scripts/*.sh

Configure the environment variable for the mysql user by appending a line:

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

Source / .bash_profile

At this point, the script can be executed in any path, improving the ease of use of MySQL operations.

4. Set the boot to start the MySQL service automatically.

On the basis of the above configuration

You can edit the / etc/rc.local file directly under the root user and append the content:

# autostart MySQL

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

Thank you for reading! This is the end of the article on "how to configure security and ease of use of MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.