User management and access issues for MySQL under Linux

Installation configuration for 1.mysql

Linux has a very magical thing called Yum, as long as the source, with Yum to install is a very easy thing, nothing to do, it will solve for you some software dependencies problems. Install MySQL with one click:

[email protected] ~]# Yum install mysql-server mysql-devel

After the installation is complete we can use MySQL:

[Email protected] ~]#/etc/init.d/mysqld startstarting mysqld:                                           [  OK  ][[email protected] ~]# mysql-u root-p Enter Password:welcome to the MySQL Monitor.  Commands End With; or \g.your MySQL connection ID is 2Server version:5.1.73 Source distributioncopyright (c), +, Oracle and/or its a Ffiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> create database db_test; Query OK, 1 row affected (0.02 sec) Mysql> Grant all on * * to "user" @ "localhost" identified by "123"; Query OK, 0 rows Affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec) Mysql> Quitbye

The above actions were successful, including logging in to MySQL, creating a new database, and creating a new user, but in the process I also encountered several problems, which took some time, which I'll talk about later. First look at the main configuration file information for MySQL:

[Email protected] ~]# CAT/ETC/MY.CNF [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/ Log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

Datadir=/var/lib/mysql is the database file location of MySQL database, and the database db_test I just created is also under this directory. Log-error=/var/log/mysqld.log is the log of the database output, and the database log is also in the/var/log directory. The default listening port for MySQL is 3306.

2.mysql User Management

After MySQL is installed, MySQL will automatically provide a root user with a blank password, which is not the root of Linux, and the root user of the empty password can log in like this: Mysql-u Root does not have the-p,-u of the user, the-p refers to the login by password, You will then be prompted to enter your password. Because of security, the first time you should change the password to a non-null password, you can also avoid errors in future operations, because I have encountered several problems are related to the empty password. Root Change Password:

Change the root password to 123, because I have changed the root password, so here to add-P also have to enter the original password.

Of course, in addition to root can have other users, MySQL user management is through a table mysql.user, in fact, this is a database table, but also can be manipulated through SQL statements, new users have two ways:

mysql> Select User, password from Mysql.user; +------+-------------------------------------------+| user | Password |+------+-------------------------------------------+| Root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 | | Root | Root | | Root | Root | | user | *23ae809ddacaf96af0fd78ed04b6a265e05aa257 |+------+-------------------------------------------+4 rows in Set (0.00 SEC) Mysql> Grant all on * * to "test" @ "localhost" identified by "123"; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into Mysql.user (Host, user, Password) values ("localhost", "SqlUser", Password ("123")) Ser ", Password (" 123 "));                                                       Query OK, 1 row affected, 3 warnings (0.05 sec) mysql> Select User, password from Mysql.user; +---------+-------------------------------------------+| user | Password |+---------+-------------------------------------------+| Root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 | | Root | Root | | Root | Root | | user | *23ae809ddacaf96af0fd78ed04b6a265e05aa257 | | Test | *23ae809ddacaf96af0fd78ed04b6a265e05aa257 | | SqlUser | *23ae809ddacaf96af0fd78ed04b6a265e05aa257 |+---------+-------------------------------------------+6 rows in Set ( 0.01 sec)

The first way is grant, you can assign permissions to the user, all on behalf of all permissions, can be changed to another such as Select,insert,update,delete, the following * * represents that all databases have permissions, the general format is: database name. *,test is the user name, 123 is the password, localhost represents the matching host, and can also be changed to something else, such as "", empty, representing all hosts. The second way is to directly modify the database table, but do not specify permissions, for security purposes, should give the user the appropriate permissions. Viewing the user information is also an SQL statement, and of course you can use SELECT * from Mysql.user; see more information, the password is encrypted. Delete User is SQL statement: Delete from mysql.user where user= ' XXX ';.

Of course, this does not work, after the user table, you have to add a command flush privileges; Refresh the system permissions table, or even if the added user is not available, the deleted user can also log in.

3. Accessing MySQL Issues

The most common problem of access to MySQL is the empty password, just at the beginning I did not change the root password for the convenience of the diagram, let it remain empty, but in the back of the operation is always a problem, and is a puzzling problem, so the MySQL installation is recommended to change the password to non-empty.

question 1. Access denied for user ' root ' @ ' localhost ' (using Password:no)

Access denied for user ' root ' @ ' localhost ' (using Password:yes)

I did not change the root password, let him remain empty, and then through the mysql-u root login, login several times, inexplicably reported the use of Password:no error, OK, then I will be mysql-u root-p to log in, Do not prompt for the password to enter directly, the mother egg or the use of the Password:no error. Then I'm going to need the code. Mysqladmin-u root-p Password ' 123 ', reported error mysqladmin:connect to server at ' localhost ' failed,using password:yes 。

So through the Mysqld_safe way to start, see the Mysql.user data, inside the root password is indeed empty Ah, I can not be happy to play together ah.

Search on the Internet, many people also encounter the same problem with me, the solution is to modify the user table to change the root password to non-null:

question 2. ERROR 1044 (42000): Access denied for user "@ ' localhost ' to database ' Db_mysql '

Accidental opportunity incredibly with the empty root password to log in, and then create a new database Db_mysql, the report of such a mistake, I clearly is the root login, how to become empty, this is a user, I did not create this user ah, and then select * FROM Mysql.user; see, there really is such a user, the user name and password are empty, but my root login incredibly turned into such an empty user, the reason I have not yet figured out, but can think of and root password for empty certainly have a relationship, so then search the solution, Deleting this empty user will be OK:

# service Mysqld stop# mysqld_safe--skip-grant-table new terminal execution # mysql-u root mysqlmysql> delete from user where user= ' ;mysql> FLUSH privileges;mysql> \q

These problems are related to the password is empty, when I changed the root password to non-empty, and then log on to the operation has not encountered these problems, so it is recommended to install MySQL after the root password change. Of course, when you log in the wrong password will also appear using Password:yes error, this is not related to the empty password.