MySQL数据库学习(一)——软件的安装配置

1 安装

yum install mysql mysql-devel -y

查看是否安装成功:

[fancy@localhost ~]$ yum list mysql-server
CentOS-8 - AppStream                                                                   3.7 kB/s | 4.3 kB     00:01    
CentOS-8 - Base                                                                        1.3 kB/s | 3.9 kB     00:03    
CentOS-8 - Extras                                                                      1.0 kB/s | 1.5 kB     00:01    
Adobe Systems Incorporated                                                             3.2 kB/s | 2.9 kB     00:00    
Docker CE Stable - x86_64                                                               16 kB/s | 3.5 kB     00:00    
google-chrome                                                                           15 kB/s | 1.3 kB     00:00    
google-chrome                                                                          236  B/s | 3.5 kB     00:15    
Visual Studio Code                                                                     8.5 kB/s | 3.0 kB     00:00    
Visual Studio Code                                                                     2.9 MB/s | 7.8 MB     00:02    
Installed Packages
mysql-server.x86_64                           8.0.21-1.module_el8.2.0+493+63b41e36                           @AppStream
[fancy@localhost ~]$
[fancy@localhost ~]$ yum list mysql
Last metadata expiration check: 0:51:45 ago on Thu 19 Nov 2020 03:19:59 PM CST.
Installed Packages
mysql.x86_64                              8.0.21-1.module_el8.2.0+493+63b41e36                               @AppStream
[fancy@localhost ~]$

表明mysql已经安装成功。

2 启动服务

[root@localhost my.cnf.d]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service

3 停止服务

[root@localhost my.cnf.d]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service
[root@localhost my.cnf.d]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@localhost my.cnf.d]#

4 重启服务

service mysqld restart需要注意，是mysqld而不是mysql

[root@localhost my.cnf.d]# service mysql restart
Redirecting to /bin/systemctl restart mysql.service
Failed to restart mysql.service: Unit mysql.service not found.
[root@localhost my.cnf.d]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@localhost my.cnf.d]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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>

5 修改密码

在MySQL 8.04前，执行：SET PASSWORD=PASSWORD(‘[新密码]’);

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.21 Source distribution

mysql> SET PASSWORD=PASSWORD('root');
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('root')' at line 1

MySQL8.0.4开始，这样默认是不行的。因为之前，MySQL的密码认证插件是“mysql_native_password”，而现在使用的是“caching_sha2_password”。

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.36 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

mysql> quit
Bye
[root@localhost my.cnf.d]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

输入密码进入数据库

[root@localhost my.cnf.d]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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>

6 MYSQL添加、删除用户

6.1 添加用户

格式: create user “username”@“host” identified by “password”;
host=“localhost” 允许本地登录，host=“ip” 允许ip地址，host="%"，允许所以ip登录

root账户下：

mysql> create user 'test'@'localhost' identified by '123456'; # 创建用户名test的账户，密码为123456。只能本地登陆。
Query OK, 0 rows affected (0.37 sec)

mysql>

登陆刚才创建的用户：

[fancy@localhost ~]$ mysql -utest -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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>

6.2 删除用户

格式：drop user ‘username’@‘host’;

mysql> select user,host from mysql.user;    # 显示当前的所有用户名
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| fancy            | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> create user 'test'@'localhost' identified by '123456';   # 添加新用户
Query OK, 0 rows affected (0.13 sec)

mysql> select User,Host,authentication_string from mysql.user;  # 显示添加新用户后的所有用户
+------------------+-----------+------------------------------------------------------------------------+
| User             | Host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| fancy            | %         | *01940738CD1B79FE9BB805BD5508E5A77B02025A                              |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |
| test             | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |
+------------------+-----------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql> drop user 'test'@'localhost';    # 删除test用户
Query OK, 0 rows affected (0.35 sec)

mysql> select user,host from mysql.user;    # 显示删除test后的所有用户
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| fancy            | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql>

7 MYSQL用户权限管理参照以下链接

用户权限管理