MySQL Getting Started 03-mysql configuration security, ease of use

First, set the administrator user and password

Second, handling the test library permissions hidden Trouble

Iii. custom scripts for improved ease of use

• Intermediate definition File
• Start the MySQL service
• Turn off MySQL service
• Quick Login MySQL

Four, set the boot automatically start the MySQL service

Reference

First, set the administrator user and password

Clear insecure user information, set Administrator user to system, password to MySQL.
The following are the steps:

[Email protected] ~]$ Mysqlwelcome to the MySQL Monitor. Commands End With;or \g.your MySQL connection IDIs1Server version:5.6.30-log JSSFor Mysqltestcopyright (c)2000,, Oracleand/or its affiliates. All rights reserved. Oracleis a registered trademarkof Oracle Corporationand/or itsaffiliates. Names May trademarksof their respectiveowners. Type' Help; 'Or' \h 'For help. Type' \c ' to clear the current input statement. ([email protected]) [(None)]> ([email protected]) [(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 rowsIn Set (0.04 sec) ([email protected]) [(none)]>delete from Mysql.user where (user,host) not in (select  ' localhost '); Query OK, 5 rows affected (0.05 sec) ([email protected]) [( 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 ([email protected]) [ (none)] > Flush Privileges; Query OK, 0 rows affected (0.03 sec) ([email protected]) [( None)]> \qbye              

Once the above modification is complete and the permissions are refreshed, testing the MySQL database connection again requires that you specify a username and password to log in. The following are the steps:

[Email protected] ~]$ Mysqlerror1045 (28000): Access deniedfor user' Root ' @' localhost ' (using password:no) [[email protected] ~]$ mysql-usystem-pmysqlwarning:using a passwordOn the command lineinterface can be insecure. WelcomeTo the MySQL Monitor. CommandsEndwith;or \g.your MySQL connection ID is6Server version:5.6.30-log JSS for Mysqltestcopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names could be trademarks of their respectiveowners. Type  ' help; ' or  ' \h ' for help. type  ' \c ' to clear the current input statement. ( [email protected]) [(None)]>               

Second, handling the test library permissions hidden Trouble

View current mysql.db information:

([email protected]) [(None)]> select * from Mysql.db \g***************************1. Row ***************************Host:%Db:testUser:Select_priv:yInsert_priv:yUpdate_priv:yDelete_priv:yCreate_priv:yDrop_priv:yGrant_priv:nReferences_priv:yIndex_priv:yAlter_priv:yCreate_tmp_table_priv:yLock_tables_priv:yCreate_view_priv:yShow_view_priv:yCreate_routine_priv:yAlter_routine_priv:nExecute_priv:nEvent_priv:ytrigger_priv:y***************************2. Row ***************************Host:%db:test\_%User:Select_priv:yInsert_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:yCreate_ 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:y2 rows in Set (0.00 sec) ( [email protected]) [(none)] >             

Handling Test Library Permissions security implications:

([email protected])[(none)]> truncate table mysql.db;Query OK, 0 rows affected (0.04 sec)([email protected])[(none)]> flush privileges;Query OK, 0 rows affected (0.00 sec)([email protected])[(none)]> select * from mysql.db \GEmpty set (0.00 sec)([email protected])[(none)]> 

Iii. customizing scripts to enhance ease of Use 3.1 intermediate definition file

Create an intermediate definition file to improve the reusability of the script.
Vi/data/mysqldata/scripts/mysql_env.ini

# set envMYSQL_USER=systemMYSQL_PASS=‘mysql‘# check parameterif [ $# -ne 1 ]then HOST_PORT=3306else HOST_PORT=$1fi

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

600 /data/mysqldata/scripts/mysql_env.ini

Of course, if the password security requirements are very high, here the password in the configuration file can be empty, the subsequent call script to manually enter the password.

3.2 Starting the MySQL service

vi/data/mysqldata/scripts/mysql_db_startup.sh

#!/bin/shsource /data/mysqldata/scripts/mysql_env.iniecho "Startup MySQL Service: localhost_"${HOST_PORT}/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/${HOST_PORT}/my.cnf &

3.3 Turning off the MySQL service

vi/data/mysqldata/scripts/mysql_db_shutdown.sh

#!/bin/shsource /data/mysqldata/scripts/mysql_env.iniecho "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 MySQL

vi/data/mysqldata/scripts/mysqlplus.sh

#!/bin/shsource /data/mysqldata/scripts/mysql_env.iniecho "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, grant all custom script execution permissions uniformly:

/data/mysqldata/scripts/*.sh

To configure the environment variables for the MySQL user, append a row:

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

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

Four, set the boot automatically start the MySQL service

On the basis of the above configuration completion,
You can edit the/etc/rc.local file directly under root user, append content:

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

MySQL Getting Started 03-mysql configuration security, ease of use