mysql学习笔记1
安装
1.更新
sudo apt update
2.安装
$ sudo apt install mysql-server
3.查看运行状况
$ sudo systemctl status mysql.service
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:>
Active: active (running) since Mon 2024-09-23 16:13:22 CST; 48s ago
Process: 3135 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=e>
Main PID: 3143 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 1917)
Memory: 366.0M
CPU: 980ms
CGroup: /system.slice/mysql.service
└─3143 /usr/sbin/mysqld
Sep 23 16:13:21 iZbp15gacvms2qkuahiq0oZ systemd[1]: Starting MySQL Community Se>
Sep 23 16:13:22 iZbp15gacvms2qkuahiq0oZ systemd[1]: Started MySQL Community Ser>
4.运行安全脚本
$ sudo mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No:
输入y，选择启用并设置密码验证组建。
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:
```bash
有三个级别的密码验证策略：
LOW：长度至少为 8 个字符。
MEDIUM：长度至少为 8 个字符，并且包含数字、大小写字母和特殊字符。
STRONG：长度至少为 8 个字符，并且包含数字、大小写字母、特殊字符，并且不在字典文件中。
输入 0 1 2分别代表低 中 高 三档。 `1`
```bash
Skipping password set for root as authentication with auth_socket is used by default.
If you would like to use password authentication instead, this can be done with the "ALTER_USER" command.
See https://dev.mysql.com/doc/refman/8.0/en/alter-user.html#alter-user-password-management for more information.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) :
询问是否需要移除匿名用户。 y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :
是否禁止root用户远程登陆 n
... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No)
是否删除 test 数据库 n
... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) :
是否重新加载权限表使设置生效？y
Success.
All done!
至此，安装完成。
试运行
1.登陆
$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.39-0ubuntu0.22.04.1 (Ubuntu)
Copyright (c) 2000, 2024, 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>
指令中-u代表-user，登陆用户为root，-p代表-password，输入密码
2.查看现有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
show databases列出mysql服务器上所有数据库。
information_schema：这是一个系统数据库，提供了关于所有其他数据库的信息。
mysql：这是 MySQL 系统数据库，存储了用户账户、权限和其他系统级别的信息。
performance_schema：这是一个性能模式数据库，用于监控 MySQL 服务器的性能。
sys：这是一个系统数据库，提供了一组视图和过程，帮助分析 MySQL 性能。
3.创建一个数据库
mysql> create database test_database;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_database |
+--------------------+
5 rows in set (0.00 sec)
使用create database + 数据库名 可以新建数据库。
4.选择一个数据库
mysql> use test_database
Database changed
mysql> select database();
+---------------+
| database() |
+---------------+
| test_database |
+---------------+
1 row in set (0.00 sec)
使用use进行数据库选择，再使用select database();进行查看已选择的对象。
5.删除数据库
drop database test_database;
使用drop database+数据库名，删除数据库。
删除完成后，查看下
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
可见新建的test_database已被删除。
6.退出
mysql> quit
Bye
或者
mysql> exit
Bye
补充：创建用户
CREATE USER 'John_Lenon'@'%' IDENTIFIED BY 'Beatles!666';
Query OK, 0 rows affected (0.17 sec)
