How to safely configure Mysql database in Linux

The content of this article is to introduce how to safely configure the Mysql database in Linux. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Directory:

1. Modify the password of the mysql administrator account root (2 Method)

2.Modify the mysql administrator account root

## 3.Mysql administrator root account password forgotten solution (2 methods)

4.

Create database user (3 methods)

5.

mysql database permission management

　

Local permission

Network permission

Revoke permission

Delete user

1. Change the password of mysql administrator account root

Method 1: shell command

mysqladmin  -u  root  -p  password  "123"

　

2. View the table where the password is stored

　

Method 2: mysql database command

mysql>use mysql;
mysql>update user set password=password('123') where user='root' and host='localhost';
mysql> FLUSH PRIVILEGES;        #刷新

　

Test whether the password change is successful

　

2. Modify the mysql administrator account root

##

update mysql.user set user="admin" where user="root" and host="localhost";
flush privileges;

　

Test

　

3. Mysql administrator root account password is forgotten

Method 1:

killall -TERM mysqld
mysqld_safe --skip-grant-tables &
mysql -u admin 
mysql>update user set password=password('123456') where user='admin' and host='localhost';
mysql> FLUSH PRIVILEGES;
MySQL> quit

Restart MySQL and log in with a new password

　

Set a new password and refresh it

　

Test, restart the mysqld service and log in with a new password

　

Method 2:

Modify/etc/my.cnf

Add under [Mysqld]:

skip-grant-tables

Restart MySQL service

mysql -u root
mysql>update user set password=password('123') where user='admin' and host='localhost';
mysql> FLUSH PRIVILEGES;
MySQL> quit

Modify /etc/my.cnf Delete skip-grant-tables

Restart MySQL, use new password to log in

　

Test

Modify /etc/my.cnf Delete skip- grant-tables

Restart MySQL and log in with the new password

　

4. Create Database user

Method one:

INSERT INTO user(host, user, password, select_priv, insert_priv, update_priv) VALUES ('localhost', 'yuzly', PASSWORD('yuzly'), 'Y', 'Y', 'Y');

　

Method two:

CREATE  USER  'user1'@'localhost'  IDENTIFIED  BY 'yuzly';

　　

方法三：通过新用户授权创建数据库用户

　　

五、权限管理

本地权限

1.创建一个zhang用户,只给select权限,然后查看权限

　

2.登录zhang用户，mysql -uzhang -pyuzly测试，创建一个表，下图显示被拒绝，当前账户没有create权限

　　

3.登录管理员账户，给zhangsan账户添加create权限

　　

4.重新登录zhangsan账户,测试是否具有create权限，下图说明具有create权限

　　

5.插入一条数据，下图提示没有insert权限

　

6.登录管理员账户，给zhangsan账户添加insert权限

　　

7.重新登录zhangsan账户,测试是否具有insert权限，下图说明具有insert权限

　　

8.创建的新用户，默认没有任何权限,下图可以看到创建的用户默认是没有任何权限的

　　

9.登录新创建的用户mysql -uabcd -pyuzly,测试，下图可以看到abcd没有select等权限，用show只能查看mysql系统自带的东西，别的用户创建的数据库等看不到

　　

远程连接权限

1.创建一个账户，赋予远程登录权限

grant all on userdb.* to 'abc'@'10.10.10.200' identified by 'yuzly';

flush privileges;

　

2.测试，从另一台Linux的Mysql客户端登录验证

　　

3.下图可以看到，abc用户对userdb数据库具有任何权限，但对别的数据库没有任何权限，例如mysql.user

　　

4.远程连接限制一个网段

　　

5.测试

　　

撤销权限

revoke all on userdb.* from 'zhangsan'@'localhost';

　　

测试，撤消zhangsan账户的权限是否生效

　　

删除账户

delete from mysql.user where user="aaa" and host="localhost";

　　

相关视频教程推荐：《MySQL教程》

以上就是本篇文章的全部内容，希望能对大家的学习有所帮助。更多精彩内容大家可以关注php中文网相关教程栏目！！！

The above is the detailed content of How to safely configure Mysql database in Linux. For more information, please follow other related articles on the PHP Chinese website!