Mysql 新建用户以及数据库随笔

1. 安装好MySQL之后

假如是通过yum等包控制器安装等mysql，我们没有设置root的初始密码，我们需要通过查找配置文件来得到root用户的默认密码：

1
cat /var/log/mysqld.log |grep password

得到密码：

1
2021-11-16T09:50:21.808068Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: PcWkrqq,/1r+

2.修改默认密码

通过所得默认密码进入root用户mysql：

1
mysql -u root -p

然后通过：

1
SET PASSWORD = PASSWORD("password");

修改默认密码为 password 。

但是有时候这里报错：
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PASSWORD(“password”)’ at line 1

或者通过：

1
2
mysql> Alter user 'root'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

提示Query OK说明修改密码成功！

3.开启用户远程访问

选择数据库‘mysql’：

1
mysql> use mysql;

查看user表，用\G可以格式化输出：

1
2
select * from user \G;
select host,user from user \G;

修改：

1
update user set host="%" where Host='localhost' and user="root";

更新权限：

1
flush privileges;

4.创建数据库和用户

创建数据库：

1
create DATABASE wp_data Default character set utf8 collate utf8_unicode_ci;

创建用户并设置密码，赋予权限：

1
grant all on wp_data.* to 'wp_user'@'localhost' IDENTIFIED BY "password";

第二种方法：

1
2
3
CREATE ROLE 'wp_user';
GRANT ALL ON wp_data.* to 'wp_user';
Alter user 'wp_user'@'localhost' identified by 'Qwe111..';

由于前面我们开启了远程访问，此处’localhost’需要改为’%’，否则会报错！
可以通过 select user,host from user;查看。

当我们输入进入’wp_user’用户界面：

1
2
3
mysql -u wp_user -p
Enter password: 
ERROR 3118 (HY000): Access denied for user 'wp_user'@'localhost'. Account is locked.

报错，用户被锁定。
我们再进入root用户：

1
2
3
4
5
6
7
select user,host,account_locked From mysql.user where user='wp_user';
+---------+------+----------------+
| user    | host | account_locked |
+---------+------+----------------+
| wp_user | %    | Y              |
+---------+------+----------------+
1 row in set (0.00 sec)

然后修改：

1
Alter user 'wp_user'@'%' account unlock;

然后就可以成功进入新用户了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@TSRAL6PZHRZ2 tmp]# mysql -u wp_user -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| wp_data            |
+--------------------+
2 rows in set (0.00 sec)