Mysql User management and access in linux _ MySQL

Mysql User management and access problems in linux 1. install and configure mysql

In linux, there is a magic thing called yum. it is very easy to use yum for installation as long as it is active, so you don't need to worry about anything, it will solve some software dependencies for you. One-click mysql installation:

[root@localhost ~]# yum install mysql-server mysql-devel

After the installation is complete, we can use mysql:

[root@localhost ~]# /etc/init.d/mysqld startStarting mysqld: [OK][root@localhost ~]# mysql -u root -pEnter password: Welcome to the MySQL monitor.Commands end with ; or /g.Your MySQL connection id is 2Server version: 5.1.73 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be 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 operations were successful, including logging on to mysql, creating a database, and creating a new user. In fact, I also encountered several problems during this process, and it took some time, this will be discussed later. Let's take a look at the main configuration file information of mysql:

[root@localhost ~]# 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 storage location of the mysql database. the database db_test I just created is also in this directory. Log-error =/var/log/mysqld. log is the log output by the database, 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 automatically provides a root user with an empty password. this root user is not the root user of linux, and the root user with an empty password can log on like this: mysql-u root does not have the following-p,-u refers to the user,-p refers to the login through the password, and then will prompt to enter the password. For security reasons, you should change the password to a non-empty password at the beginning, which can also avoid future operation errors, because I have encountered several problems that are related to the empty password. Root password change:

[root@localhost ~]# mysqladmin -u root -p password 123Enter password:

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

Of course, in addition to root, there can be other users. in mysql, user management is performed through a table mysql. user, which is actually a database table, can also be operated through SQL statements. There are two ways to create a user:

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 method is grant. you can specify permissions for users. all indicates all permissions. you can change them to select, insert, update, delete, and *. * indicates that all databases have permissions. the general format is database name. * "test" indicates the user name, "123" indicates the password, and "localhost" indicates the matched host. you can also change it to another one, such as "". if it is null, it indicates all hosts. The second method is to directly modify the database table, but you do not have the specified permissions. to ensure security, you should grant the corresponding permissions to the user. Viewing user information is also an SQL statement. of course, you can also use select * from mysql. user; to view more information, the password here is encrypted. Delete a user, that is, an SQL statement: delete from mysql. user where user = 'XXX ';.

Of course, this still doesn't work. after operating the user table, you have to add the command flush privileges; refresh the system permission table; otherwise, even if the added user is not available, the deleted user can log on.

3. mysql access problems

The most common problem with accessing mysql is that the password is empty. at the beginning, I did not change the root password for convenience, however, the subsequent operations are always problematic, which is inexplicable. Therefore, we recommend that you change the password to a non-empty one after installing mysql.

Question 1.Access denied for user 'root' @ 'localhost' (using password: NO)

Access denied for user 'root' @ 'localhost' (using password: YES)

If I didn't change the root password, I asked him to leave it blank and then log on through mysql-u root. after several logins, the using password: NO error was inexplicably reported, well, I will log on through mysql-u root-p, and press enter without prompting me to enter the password. mom still reports the error of using password: NO. So I should try the password mysqladmin-u root-p password '000000'. the error mysqladmin: connect to server at 'localhost' failed and using password: YES is reported.

So I started it in mysqld_safe mode and checked the mysql. user data. the root password in it is indeed empty. Can I have fun together.

After searching on the Internet, many people have encountered the same problem as me. the solution is to modify the user table and change the root password to a non-empty one:

#/Etc/init. d/mysql stop # mysqld_safe -- user = mysql -- skip-grant-tables -- skip-networking & # mysql-u root mysqlmysql> UPDATE user SET Password = PASSWORD ('123 ') where USER = 'root'; mysql> flush privileges; mysql> quit #/etc/init. d/mysqld restart # mysql-uroot-pEnter password:
 <输入新设的密码123>

Question 2.ERROR 1044 (42000): Access denied for user ''@ 'localhost' to database 'DB _ mysql'

By chance, I logged in with an empty root password and created a database db_mysql. the following error was reported: How did I log on as a root user, this is a user. I didn't create this user at all, and then select * from mysql. user; check, there is such a user, the user name and password are empty, but my root login is actually such an empty user, the reason is not clear yet, but it may be related to the root password being empty, so I searched for a solution and deleted the empty user:

# Service mysqld stop # mysqld_safe -- skip-grant-table a new terminal is run # mysql-u root mysqlmysql> delete from user where USER = ''; mysql> flush privileges; mysql>/q

These problems are related to the empty password. when I change the root password to a non-empty password, I have not encountered these problems when I log on again, therefore, we recommend that you change the root password after installing mysql. Of course, the using password: YES error may also occur when the password is incorrect during logon. this is irrelevant to the empty password.