MySQL 系列之服务部署与 SQL 基础（一）
站长
· 阅读数 4
Debian 11 部署 MySQL 服务
在 Debian 11.x Bullseye 上安装部署 MySQL 8.0.35 版本数据库
下载 MySQL APT Reposirotory
- 从
MySQL官方网站 dev.mysql.com/downloads/r… 上下载最新版本，点击Download按钮：
- 复制下载链接地址到粘贴板：
- 在服务器上使用
wget进行下载：
root@db-server:~# wget https://dev.mysql.com/get/mysql-apt-config_0.8.28-1_all.deb
--2023-12-09 18:33:54-- https://dev.mysql.com/get/mysql-apt-config_0.8.28-1_all.deb
Resolving dev.mysql.com (dev.mysql.com)... 23.15.25.107, 2600:140b:400:19f::2e31, 2600:140b:400:180::2e31
Connecting to dev.mysql.com (dev.mysql.com)|23.15.25.107|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://repo.mysql.com//mysql-apt-config_0.8.28-1_all.deb [following]
--2023-12-09 18:33:56-- https://repo.mysql.com//mysql-apt-config_0.8.28-1_all.deb
Resolving repo.mysql.com (repo.mysql.com)... 104.85.17.181, 2600:140b:2c00:59a::1d68, 2600:140b:2c00:586::1d68
Connecting to repo.mysql.com (repo.mysql.com)|104.85.17.181|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 18124 (18K) [application/x-debian-package]
Saving to: ‘mysql-apt-config_0.8.28-1_all.deb’
mysql-apt-config_0.8.28-1_all.deb 100%[==============================================================================================================================================>] 17.70K --.-KB/s in 0.006s
2023-12-09 18:33:57 (2.84 MB/s) - ‘mysql-apt-config_0.8.28-1_all.deb’ saved [18124/18124]
root@db-server:~# ll -h
total 20K
-rw-r----- 1 root root 18K Oct 26 17:24 mysql-apt-config_0.8.28-1_all.deb
安装 MySQL APT Repo Pkg
- 使用
dpkg命令来安装这个包：
root@db-server:~# dpkg -i mysql-apt-config_0.8.28-1_all.deb
Selecting previously unselected package mysql-apt-config.
(Reading database ... 81773 files and directories currently installed.)
Preparing to unpack mysql-apt-config_0.8.28-1_all.deb ...
Unpacking mysql-apt-config (0.8.28-1) ...
Setting up mysql-apt-config (0.8.28-1) ...
- 在安装过程中，会弹出一个配置界面，可选择你想要使用的
MySQL产品和版本：
更新 apt 源列表
- 上面安装的
MySQL APT Repository包本质上会在/etc/apt/sources.list.d/目录下自动创建一个file，并添加MySQL的官方存储库源：
root@db-server:~# cat /etc/apt/sources.list.d/mysql.list
### THIS FILE IS AUTOMATICALLY CONFIGURED ###
# You may comment out entries below, but any other modifications may be lost.
# Use command 'dpkg-reconfigure mysql-apt-config' as root for modifications.
deb [signed-by=/usr/share/keyrings/mysql-apt-config.gpg] http://repo.mysql.com/apt/debian/ bullseye mysql-apt-config
deb [signed-by=/usr/share/keyrings/mysql-apt-config.gpg] http://repo.mysql.com/apt/debian/ bullseye mysql-8.0
deb [signed-by=/usr/share/keyrings/mysql-apt-config.gpg] http://repo.mysql.com/apt/debian/ bullseye mysql-tools
#deb [signed-by=/usr/share/keyrings/mysql-apt-config.gpg] http://repo.mysql.com/apt/debian/ bullseye mysql-tools-preview
deb-src [signed-by=/usr/share/keyrings/mysql-apt-config.gpg] http://repo.mysql.com/apt/debian/ bullseye mysql-8.0
- 更新
apt软件源
root@db-server:~# apt-get update
安装最新版 mysql
- 安装
mysql社区版：
root@db-server:~# apt-get install -y mysql-community-server
- 输入
mysql的root密码，并再次确认：
- 选择使用新的基于
SHA256的密码认证机制（caching_sha2_password），而非传统的mysql_native_password：
检查并启动服务
- 检查安装的软件列表：
root@db-server:~# dpkg -l | grep -i mysql
ii mysql-apt-config 0.8.28-1 all Auto configuration for MySQL APT Repo.
ii mysql-client 8.0.35-1debian11 amd64 MySQL Client meta package depending on latest version
ii mysql-common 8.0.35-1debian11 amd64 Common files shared between packages
ii mysql-community-client 8.0.35-1debian11 amd64 MySQL Client
ii mysql-community-client-core 8.0.35-1debian11 amd64 MySQL Client Core Binaries
ii mysql-community-client-plugins 8.0.35-1debian11 amd64 MySQL Client plugin
ii mysql-community-server 8.0.35-1debian11 amd64 MySQL Server
ii mysql-community-server-core 8.0.35-1debian11 amd64 MySQL Server Core Binaires
- 检查安装版本：
root@db-server:~# mysql --version
mysql Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)
- 启动并检查
mysql服务：
# 启动并设置开机自启动
root@db-server:~# systemctl enable --now mysql
# 检查服务状态
root@db-server:~# systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2023-12-09 18:45:02 CST; 17min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 3222 (mysqld)
Status: "Server is operational"
Tasks: 37 (limit: 9481)
Memory: 390.5M
CPU: 2.258s
CGroup: /system.slice/mysql.service
└─3222 /usr/sbin/mysqld
Dec 09 18:45:02 db-server systemd[1]: Starting MySQL Community Server...
Dec 09 18:45:02 db-server systemd[1]: Started MySQL Community Server.
# 检查默认服务端口
root@db-server:~# lsof -i TCP:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 3222 mysql 4u IPv6 173370 0t0 TCP *:mysql (LISTEN)
初始化 mysql 安全性脚本
- 设定
root账户密码、删除匿名用户、禁止root账户远程登录、删除测试数据库
root@db-server:~# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
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: N
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : 1qaz!QAZ
... skipping.
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) : Y
Success.
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) : Y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
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!
- 登录客户端检查（
root已禁止远程访问）：
root@db-server:~# mysql -h localhost -P 3306 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.35 MySQL Community Server - GPL
Copyright (c) 2000, 2023, 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 |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> select user, host, plugin from mysql.user where user = 'root';
+------+-----------+-----------------------+
| user | host | plugin |
+------+-----------+-----------------------+
| root | localhost | caching_sha2_password |
+------+-----------+-----------------------+
1 row in set (0.01 sec)
mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> \q
Bye
创建非 root 的数据库超管账号
- 创建一个
admin超级管理员账号：
-- 创建一个 admin 用户，可以从任何远程地址连接到数据库
create user 'admin'@'%' identified by 'admin@12345';
-- 赋予给新创建的 admin 用户所有的权限
grant all privileges on *.* to 'admin'@'%' with grant option;
-- 刷新权限，使生效
flush privileges;
- 尝试远程登录：
root@db-server:~# mysql -h 10.2.102.245 -P 3306 -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'10.2.102.245' (using password: YES)
root@db-server:~# mysql -h 10.2.102.245 -P 3306 -u admin -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.35 MySQL Community Server - GPL
Copyright (c) 2000, 2023, 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>
SQL 基础使用
SQL 的分类
- 结构化查询语言
SQL (Structured Query Language)，其主要可分为以下几类：
| 分类 | 说明 | 命令示例 |
|---|---|---|
数据定义语言 （DDL） | 用于创建、修改、删除数据库中的结构对象 | CREATE, ALTER, DROP |
数据操作语言 （DML） | 用于插入、修改、删除数据库中的数据 | INSERT, UPDATE, DELETE |
数据查询语言 （DQL） | 用于查询数据库中的数据信息 | SELECT |
数据控制语言 （DCL） | 用于定义或更改用户的权限，并控制数据的访问 | GRANT, REVOKE |
事务控制语言 （TCL） | 用于管理事务的提交和回滚 | COMMIT, ROLLBACK, SAVEPOINT |
库的操作
# 创建数据库
mysql> create database cmdb default charset utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)
# 列出当前所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| cmdb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
# 展示创建数据库的语句
mysql> show create database cmdb;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| cmdb | CREATE DATABASE `cmdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 切换数据库
mysql> use cmdb;
Database changed
# 删除数据库
mysql> drop database cmdb;
Query OK, 0 rows affected (0.01 sec)
表的操作
创建表
- 该表已涵盖了大多数会用到字段类型。
CREATE TABLE tt (
id INT AUTO_INCREMENT COMMENT '自增主键', -- 整型，自动递增
name VARCHAR(100) NOT NULL COMMENT '姓名', -- 变长字符串，不能为空
age SMALLINT UNSIGNED COMMENT '年龄', -- 小整型，无符号
salary DECIMAL (10, 2) COMMENT '薪资', -- 十进制类型，用于表示货币
birth_date DATE COMMENT '出生日期', -- 日期类型
birth_time TIME COMMENT '出生时刻', -- 时间类型
is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用', -- 微型整数，通常用作布尔值
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', -- 时间日期类型，每次新增记录时自动设定为当前时间
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', -- 时间日期类型，每次更新记录时自动设定为当前时间
content TEXT COMMENT '内容', -- 长文本类型，用于存储大量文字
personal_notes BLOB COMMENT '个人笔记', -- 二进制长文本类型，用于存储大量的二进制数据
choice ENUM('CHOICE1', 'CHOICE2', 'CHOICE3') COMMENT '选项', -- 枚举类型，字段的取值限于列举的选择中
data JSON COMMENT '数据', -- JSON类型，用于存储和管理JSON数据
PRIMARY KEY (id) -- 设定主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表tt'; -- 表的引擎，字符集，以及整个表的注释
删除表
- 该操作不可逆，需谨慎执行。
drop table tt;
清空表
- 删除表内容，保留表结构，并释放空间（速度快、无法回滚）
truncate table tt;
- 删除表内容，保留表结构，不释放空间（速度慢、可以回滚）
delete from tt;
查看表结构
- 获取有关表的详细信息：
mysql> desc tt;
+----------------+-------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------------------------------+------+-----+-------------------+-----------------------------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | smallint unsigned | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
| birth_date | date | YES | | NULL | |
| birth_time | time | YES | | NULL | |
| is_active | tinyint(1) | YES | | 1 | |
| created_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| content | text | YES | | NULL | |
| personal_notes | blob | YES | | NULL | |
| choice | enum('CHOICE1','CHOICE2','CHOICE3') | YES | | NULL | |
| data | json | YES | | NULL | |
+----------------+-------------------------------------+------+-----+-------------------+-----------------------------------------------+
13 rows in set (0.01 sec)
- 返回一个完整的
CREATE TABLE语句：
mysql> show create table tt;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt | CREATE TABLE `tt` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(100) NOT NULL COMMENT '姓名',
`age` smallint unsigned DEFAULT NULL COMMENT '年龄',
`salary` decimal(10,2) DEFAULT NULL COMMENT '薪资',
`birth_date` date DEFAULT NULL COMMENT '出生日期',
`birth_time` time DEFAULT NULL COMMENT '出生时刻',
`is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`content` text COMMENT '内容',
`personal_notes` blob COMMENT '个人笔记',
`choice` enum('CHOICE1','CHOICE2','CHOICE3') DEFAULT NULL COMMENT '选项',
`data` json DEFAULT NULL COMMENT '数据',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='测试表tt' |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
修改表结构
-- 添加字段
ALTER TABLE <table_name> ADD <column_name> <column_type>; -- 添加一个新的字段到表中
ALTER TABLE <table_name> ADD <column_name> <column_type> DEFAULT <default_value> COMMENT '注释'; -- 添加字段并设置默认值和注释
ALTER TABLE <table_name> ADD <column_name> <column_type> NOT NULL PRIMARY KEY AUTO_INCREMENT; -- 添加字段作为自增的主键
ALTER TABLE <table_name> ADD <column_name> <column_type> AFTER <existing_column_name>; -- 在某个已存在的字段之后添加新字段
-- 删除字段
ALTER TABLE <table_name> DROP COLUMN <column_name>; -- 删除一个字段
-- 修改字段类型
ALTER TABLE <table_name> MODIFY COLUMN <column_name> <column_type>; -- 修改字段类型
-- 修改字段类型 + 名称
ALTER TABLE <table_name> CHANGE <original_column_name> <new_column_name> <new_column_type>; -- 修改字段名并更改其类型
ALTER TABLE tt CHANGE id nid INT NOT NULL PRIMARY KEY AUTO_INCREMENT; -- 修改主键的字段名及其类型
-- 修改字段默认值
ALTER TABLE <table_name> ALTER <column_name> SET DEFAULT <default_value>; -- 设置字段的默认值
-- 删除字段默认值
ALTER TABLE <table_name> ALTER <column_name> DROP DEFAULT; -- 删除字段的默认值
-- 添加主键
ALTER TABLE <table_name> ADD PRIMARY KEY (<column_name>); -- 设定一个字段作为主键
-- 删除主键
ALTER TABLE <table_name> DROP PRIMARY KEY; -- 删除主键
表的 CRUD
新增数据
-- 向表中插入新数据
INSERT INTO <table_name> (<field1>, <field2>, ..., <fieldn>) values (<value1>, <value2>, ..., <valuen>);
删除数据
-- 删除表中的所有数据
DELETE FROM <table_name>;
-- 删除符合指定条件的数据
DELETE FROM <table_name> WHERE <condition>;
修改数据
-- 不设定条件，更新表中所有行中的特定字段
UPDATE <table_name> SET <field>=<value>;
-- 根据条件更新表中某些行的若干字段
UPDATE <table_name> SET <field1>=<value1>, <field2>=<value2>, ..., <fieldn>=<valuen> WHERE <condition>;
查询数据
-- 查询表中所有行的所有字段
SELECT * from <table_name>;
-- 查询特定字段并为某个字段重命名
SELECT <field1>, <field2> as <new_alias_name> from <table_name>;
-- 根据条件查询表中的特定字段
SELECT <field1>, <field2>, ..., <fieldn> FROM <table_name> WHERE <condition>;