概述
数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓
数据库的分类
1.网络数据库
网络数据库是指把数据库技术引入到计算机网络系统中，借助于网络技术将存储于数据库中的大量信息及时发布出去；而计算机网络借助于成熟的数据库技术对网络中的各种数据进行有效管理，并实现用户与网络中的数据库进行实时动态数据交互。
2.层级数据库
层次结构模型实质上是一种有根结点的定向有序树(在数学中"树"被定义为一个无回的连通图)。
关系数据库
3.关系数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
数据库的另外一种区分方式：基于存储介质
存储介质分为两种：磁盘和内存
关系型数据库：存储在磁盘中
非关系型数据库：存储在内存中
关系数据结构
关系数据结构：指的数据以什么方式来存储，是一种二维表的形式存储
本质：二维表
姓名 年龄 身高 体重
张三 30 187 70
李四 40
约束
表内约束：对应的具体列只能放对应的数据（不能乱放）
表间约束：自然界各实体都是有着对应的关联关系（外键）
表操作
约束：constraint，向数据表提供的数据要遵守的限制
主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。
一个表只能存在一个
唯一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）
一个表可以存在多个
外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
检查性约束：检查约束指的是在数据列上设置一些过滤条件，当过滤条件满足的时候才可以进行，如果不满足，则出现错误
典型关系型数据库
Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL、SQLite
mysql
sql介绍
SQL就是专门为关系型数据库而设计出来的。
1、数据查询语言（DQL:Data Query Language）：
其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。
专门用于查询：代表指令为select/show
2、数据操作语言（DML：Data Manipulation Language）：
其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
专门用于写数据：代表指令为insert，update和delete，增查改删
3、事务处理语言（TPL）：
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。（不是所有的关系型数据库都提供事务安全处理）
专门用于事务安全处理：transaction，COMMIT,ROLLBACK
4、数据控制语言（DCL）：
它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
专门用于权限管理：代表指令为grant和revoke
5、数据定义语言（DDL）：
其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
专门用于结构管理：代表指令create和drop（alter）
安装配置好mysql
DDL语句——创建数据库和表（数据定义语言）
创建数据库——Create databases
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database class;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| class |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
创建数据表——Create table
mysql> create table class (id int not null,name char(15) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| class |
+-----------------+
1 row in set (0.00 sec删除数据库——Drop database
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| class |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database class; // 指定需要删除的数据库
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
删除数据表——Drop table
mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| class |
| class2 |
+-----------------+
2 rows in set (0.00 sec)
mysql> drop table class;
#删除指定数据表
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| class2 |
+-----------------+
1 row in set (0.00 sec)
————————————————
DML——管理表中的数据（数据操纵语言）
DML语句用于对表中的数据进行管理，用来插入、删除和修改数据库中的数据
- Insert：插入新数据
- Update：更新原有数据
- Delete：删除不需要的数据
插入数据——Insert
mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| class |
+-----------------+
1 row in set (0.00 sec)
mysql> desc class;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(15) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.03 sec)
mysql> insert into class values(1,'cxk'),(2,'mjq');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from class;
+----+------+
| id | name |
+----+------+
| 1 | cxk |
| 2 | mjq |
+----+------+
修改、更新数据表中的数据记录——Update
mysql> select * from class1;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | tom | 90 |
| 2 | marry | 80 |
| 3 | xh | 70 |
| 4 | zhangsan | 80 |
| 5 | lisi | 90 |
| 6 | wangwu | 60 |
+----+----------+-------+
6 rows in set (0.00 sec)
mysql> update class1 set score ='90';
// 将数据表class1中所有score字段都修改为90
Query OK, 4 rows affected (0.00 sec)
Rows matched: 6 Changed: 4 Warnings: 0
mysql> select * from class1;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | tom | 90 |
| 2 | marry | 90 |
| 3 | xh | 90 |
| 4 | zhangsan | 90 |
| 5 | lisi | 90 |
| 6 | wangwu | 90 |
+----+----------+-------+
6 rows in set (0.00 sec)
mysql> update class1 set score ='80' where num <= 3; // 修改筛选条件下的字段
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from class1;
+------+----------+-------+
| num | name | score |
+------+----------+-------+
| 1 | tom | 80 |
| 2 | marry | 80 |
| 3 | xh | 80 |
| 4 | zhangsan | 90 |
| 5 | lisi | 90 |
+------+----------+-------+
5 rows in set (0.00 sec)
删除数据——Delete
mysql> select * from class1;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | tom | 90 |
| 2 | marry | 90 |
| 3 | xh | 90 |
| 4 | zhangsan | 90 |
| 5 | lisi | 90 |
| 6 | wangwu | 90 |
+----+----------+-------+
6 rows in set (0.00 sec)
mysql> delete from class1 where id=6; // 删除某一条数据，此时是删除第六条数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from class1;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | tom | 90 |
| 2 | marry | 90 |
| 3 | xh | 90 |
| 4 | zhangsan | 90 |
| 5 | lisi | 90 |
+----+----------+-------+
5 rows in set (0.00 sec)
truncate语句
truncate与delete的区别：
语句类型 特点
delete DELETE删除表内容时仅删除内容，但会保留表结构
DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项
可以通过回滚事务日志恢复数据
非常占用空间
truncate 删除表中所有数据，且无法恢复
表结构、约束和索引等保持不变，新添加的行计数值重置为初始值
执行速度比DELETE快，且使用的系统和事务日志资源少
通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放
对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据
不能用于加入了索引视图的表
//语法：TRUNCATE table_name;
DQL——管理表中的数据（数据查询语言）
DQL是数据查询语句，只有Select命令行，用于从数据表中查找符合条件的数据记录（查询时可以不指定条件）
Select * ——查询所有
Select 字段1,字段2,......from 表名
mysql> select * from class1; // 查询所有的字段
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | tom | 90 |
| 2 | marry | 90 |
| 3 | xh | 90 |
| 4 | zhangsan | 90 |
| 5 | lisi | 90 |
+----+----------+-------+
5 rows in set (0.00 sec)
mysql> select id,name from class1; // 查询id和name字段的数据
+----+----------+
| id | name |
+----+----------+
| 1 | tom |
| 2 | marry |
| 3 | xh |
| 4 | zhangsan |
| 5 | lisi |
+----+----------+
5 rows in set (0.00 sec)
Where——特定条件查找
Select 字段1,字段2......from 表名 Where 条件表达式
操作符 >，<，>=，<=，=，!=
BETWEEN column# AND column#
LIKE：模糊匹配
RLIKE：基于正则表达式进行模式匹配
IS NOT NULL：非空
IS NULL：空
条件逻辑操作 AND
mysql> select * from class1 where id=2;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 2 | marry | 90 |
+----+-------+-------+
1 row in set (0.00 sec)
mysql> select * from class1 where name='zhangsan';
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 4 | zhangsan | 90 |
+----+----------+-------+
1 row in set (0.00 sec)
mysql> select * from class1 where num >= 3;
+------+----------+-------+
| num | name | score |
+------+----------+-------+
| 3 | xh | 90 |
| 4 | zhangsan | 90 |
| 5 | lisi | 90 |
+------+----------+-------+
3 rows in set (0.00 sec)
limit——按行数查询
mysql> select id,name from class1 limit 2; // 显示第0行的后两行
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 2 | marry |
+----+-------+
2 rows in set (0.00 sec)
mysql> select id,name from class1 limit 1,3; // 显示第一行后的三行
+----+----------+
| id | name |
+----+----------+
| 2 | marry |
| 3 | xh |
| 4 | zhangsan |
+----+----------+
3 rows in set (0.00 sec)
mysql> select id,name from class1 limit 2,3; // 显示第二行后的三行
+----+----------+
| id | name |
+----+----------+
| 3 | xh |
| 4 | zhangsan |
| 5 | lisi |
+----+----------+
3 rows in set (0.00 sec)按照降序或者升序来排序
mysql> select * from class1 order by num; //默认降序
+------+----------+-------+
| num | name | score |
+------+----------+-------+
| 1 | tom | 90 |
| 2 | marry | 90 |
| 3 | xh | 90 |
| 4 | zhangsan | 90 |
| 5 | lisi | 90 |
+------+----------+-------+
5 rows in set (0.00 sec)
mysql> select * from class1 order by num DESC; // 升序
+------+----------+-------+
| num | name | score |
+------+----------+-------+
| 5 | lisi | 90 |
| 4 | zhangsan | 90 |
| 3 | xh | 90 |
| 2 | marry | 90 |
| 1 | tom | 90 |
+------+----------+-------+
5 rows in set (0.00 sec)
\G——以竖向方向显示
mysql> select * from class1;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | tom | 90 |
| 2 | marry | 90 |
| 3 | xh | 90 |
| 4 | zhangsan | 90 |
| 5 | lisi | 90 |
+----+----------+-------+
5 rows in set (0.00 sec)
mysql> select * from class1\G; //竖方向显示
*************************** 1. row ***************************
id: 1
name: tom
score: 90
*************************** 2. row ***************************
id: 2
name: marry
score: 90
*************************** 3. row ***************************
id: 3
name: xh
score: 90
*************************** 4. row ***************************
id: 4
name: zhangsan
score: 90
*************************** 5. row ***************************
id: 5
name: lisi
score: 90
5 rows in set (0.00 sec)
ERROR:
No query specifiedDCL——数据库用户授权（数据控制语言）
DCL语句设置用户权限（用户不存在时，则新建用户）
Grant 权限列表 ON 数据库名.表名 to 用户名@来源地址 [ Identified by '密码' ]
Alter 修改字段、数据表
rename——更改数据表名称
mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| class |
| class1 |
+-----------------+
2 rows in set (0.00 sec)
mysql> alter table class rename class0； // 更改表名
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| class0 |
| class1 |
+-----------------+
2 rows in set (0.00 sec)add——扩展表结构字段
mysql> select * from class1; // 原字段，没有添加字段
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | tom | 90 |
| 2 | marry | 90 |
| 3 | xh | 90 |
| 4 | zhangsan | 90 |
| 5 | lisi | 90 |
+----+----------+-------+
5 rows in set (0.00 sec)
mysql> alter table class1 add Phone varchar(11) default '199999999'; // 增加字段
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from class1；
+----+----------+-------+-----------+
| id | name | score | Phone |
+----+----------+-------+-----------+
| 1 | tom | 90 | 199999999 |
| 2 | marry | 90 | 199999999 |
| 3 | xh | 90 | 199999999 |
| 4 | zhangsan | 90 | 199999999 |
| 5 | lisi | 90 | 199999999 |
+----+----------+-------+-----------+
5 rows in set (0.00 sec)
Change——更改数据字段内容
（相当于是添加一个别名）
mysql> alter table class1 change id num varchar(15); //修改
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from class1;
+------+----------+-------+-----------+
| num | name | score | Phone |
+------+----------+-------+-----------+
| 1 | tom | 90 | 199999999 |
| 2 | marry | 90 | 199999999 |
| 3 | xh | 90 | 199999999 |
| 4 | zhangsan | 90 | 199999999 |
| 5 | lisi | 90 | 199999999 |
+------+----------+-------+-----------+
5 rows in set (0.00 sec)
Drop——删除指定数据字段内容
mysql> select * from class1;
+------+----------+-------+-----------+
| num | name | score | Phone |
+------+----------+-------+-----------+
| 1 | tom | 90 | 199999999 |
| 2 | marry | 90 | 199999999 |
| 3 | xh | 90 | 199999999 |
| 4 | zhangsan | 90 | 199999999 |
| 5 | lisi | 90 | 199999999 |
+------+----------+-------+-----------+
5 rows in set (0.00 sec)
mysql> alter table class1 drop Phone; // 删除字段
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from class1;
+------+----------+-------+
| num | name | score |
+------+----------+-------+
| 1 | tom | 90 |
| 2 | marry | 90 |
| 3 | xh | 90 |
| 4 | zhangsan | 90 |
| 5 | lisi | 90 |
+------+----------+-------+
5 rows in set (0.00 sec)
数据库用户管理
新建用户
数据库用户创建：create user 'username'@'host' [identified by 'password']
mysql用户帐号由两部分组成，如'USERNAME'@'HOST'，表示此USERNAME只能从此HOST上远程登录
这里（'USERNAME'@'HOST'）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：
（1）IP地址，如：172.16.12.129
（2）通配符
%：匹配任意长度的任意字符，常用于设置允许从任何主机登录
_：匹配任意单个字符
创建一个普通用户
mysql> create user 'chen'@'192.168.100.10' identified by 'linux';
Query OK, 0 rows affected (0.00 sec)
// 退出再登录
[root@node1 ~]# mysql -u chen -h 192.168.100.10 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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 |
+--------------------+
1 row in set (0.00 sec)
普通用户能查看的很有限，安全性大大提高
删除mysql用户
语法：drop user 'username'@'host';
mysql> drop user 'chen'@'192.168.100.10'
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql>
数据库用户授权——grant
创建授权grant
权限类型(priv_type)
权限类型 代表什么？
ALL 所有权限
SELECT 读取内容的权限
INSERT 插入内容的权限
UPDATE 更新内容的权限
DELETE 删除内容的权限
指定要操作的对象db_name.table_name
表示方式 意义
*.* 所有库的所有表
db_name 指定库的所有表
db_name.table_name 指定库的指定表
给数据库用户添加select权限
[root@node1 ~]# mysql -uroot -plinux // 此时是root用户登录
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> create user 'chenchen'@'%' identified by 'linux'; // 创建一个新的用户
Query OK, 0 rows affected (0.00 sec)
mysql> grant select on class.class1 to 'chenchen'@'%';
// 给chenchen用户添加select权限到class数据库下的class数据表
Query OK, 0 rows affected, 1 warning (0.00 sec)
测试：使用chenchen用户来登录、
[root@node1 ~]# mysql -uchenchen -plinux
登录信息省略
mysql> show databases; // 此时已经可以查看到class这个数据库了
+--------------------+
| Database |
+--------------------+
| information_schema |
| class |
+--------------------+
2 rows in set (0.00 sec)
mysql> use class;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from class1；
+------+----------+-------+
| num | name | score |
+------+----------+-------+
| 1 | tom | 80 |
| 2 | marry | 80 |
| 3 | xh | 80 |
| 4 | zhangsan | 90 |
| 5 | lisi | 90 |
| 6 | jj | 90 |
+------+----------+-------+
6 rows in set (0.00 sec)
查看用户权限
mysql> show grants; // root用户的权限
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for 'chenchen'@'%'; // 查看chenchen用户的权限
+----------------------------------------------------+
| Grants for chenchen@% |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'chenchen'@'%' |
| GRANT SELECT ON `class1`.* TO 'chenchen'@'%' |
| GRANT SELECT ON `class`.`class1` TO 'chenchen'@'%' |
+----------------------------------------------------+
3 rows in set (0.00 sec)
撤销用户权限——REVOKE
mysql> show grants for 'chenchen'@'%';
+----------------------------------------------------+
| Grants for chenchen@% |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'chenchen'@'%' |
| GRANT SELECT ON `class1`.* TO 'chenchen'@'%' |
| GRANT SELECT ON `class`.`class1` TO 'chenchen'@'%' |
+----------------------------------------------------+
3 rows in set (0.00 sec)
/ 此时具有的权限是有三条 //
mysql> revoke all on class.class1 from 'chenchen'@'%'; // 撤销对class数据库下的class1的权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'chenchen'@'%';
+----------------------------------------------+
| Grants for chenchen@% |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'chenchen'@'%' |
| GRANT SELECT ON `class1`.* TO 'chenchen'@'%' |
+----------------------------------------------+
2 rows in set (0.00 sec)
// 此时就只剩两条了总结
1.查看数据库、数据表和表结构的操作
查看数据库show databases
查看数据表show tables
查看表结构describe tablename或desc tablename
2.创建库和表的操作及删除库和表的操作
创建库create database databasename
创建表create table tablename
删除库drop database databasename
删除表drop table tablename
3.数据表的增、删、改、查等操作
数据表增：Insert into tablename
数据表改：update tablename set 指定内容
数据表删：delete from tablename
数据表查：select（*、where、limit、\G）
数据表结构名称改：alter table tablename rename 新名称
数据表扩展结构增：alter table tablename add 扩展名
数据表字段内容改：alter table tablename change 原结构字段名称 新结构字段名称
数据表字段内容删：alter table tablename drop 指定结构字段
4.数据库表的清空表、临时表和克隆表操作
4.1清空表
delete from tablename
truncate table tablename
drop from tablename
4.2临时表
create temporary tabletablename
4.3克隆表
Like方法：create table 新表 like旧表------->insert into 新表 select *from旧表
Show create table方法：create table 新表(select * from 旧表)
5.数据库的用户授权相关操作
5.1数据库用户管理
新建用户：create user '用户名'@'localhost(或者指定IP)' identified by '密码'
重命名：rename user '旧用户名'@'localhost(或者指定IP)' to '新用户名'@'localhost(或者指定IP)'
删除用户：drop user '用户名'@'localhost(或者指定IP)'
修改当前密码：set password = password（'密码'）
修改其他用户密码：set password for '用户名'@'localhost' = password('新密码');
修改密码：update mysql.user set authentication_string = password('abc123') where user='root'
修改密码：flush privileges------>set password for root@localhost=password('123123')
5.2数据库授权
授权：grant 权限列表 ON 数据库名.表名 to '用户名'@'来源地址' identified by '密码'
查看权限：show grants '用户名'@'来源地址'
撤销权限：revoke 权限列表 on 数据库名.表名 from '用户名'@'来源地址'
数据库备份与恢复
数据库备份方案：
全量备份
全量备份 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
数据恢复快。备份时间长。
增量备份
增量备份 是指在一次全备份或上一次增量备份后，以后每次的备份只需备份
与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象
是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量
备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据，，备份时间短
恢复数据时必须按一定的顺序进行
差异备份
备份上一次的完全备份后发生变化的所有文件。
差异备份是指在一次全备份后到进行差异备份的这段时间内
对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。
备份工具mysqldump
语法：
mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
//常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
实例
备份整个数据库
[root@node1 ~]# mysqldump -uroot -p --all-databases > all-20240802.sql
Enter password:
[root@node1 ~]# ls
all-20240802.sql anaconda-ks.cfg
备份某个数据库的某张表
[root@node1 ~]# mysqldump -uroot -p test students > test_table-20240802.sql
Enter password:
[root@node1 ~]# ls
all-20240802.sql anaconda-ks.cfg test_table-20240802.sql
备份test库
[root@node1 ~]# mysqldump -uroot -p --databases test > test-20240802.sql
Enter password:
[root@node1 ~]# ls
all-20240802.sql anaconda-ks.cfg test-20240802.sql test_table-20240802.sql
模拟误删cy数据库
mysql> drop database test;
Query OK, 2 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chenzhen |
| class |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database test;
Query OK, 2 rows affected (0.00 sec)
[root@node1 ~]# mysql -uroot -p < test-20240802.sql
Enter password:
[root@node1 ~]# mysql -uroot -p
/// 省略 //
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chenzhen |
| class |
| mysql |
| performance_schema |
| sys |
| test | // 恢复成功
+--------------------+
7 rows in set (0.00 sec)
恢复数据表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students |
| td_course |
+----------------+
2 rows in set (0.00 sec)
mysql> drop table students; // 删除数据表
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| td_course |
+----------------+
1 row in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| td_course |
+----------------+
1 row in set (0.00 sec)
mysql> source test_table-20240802.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables; // 恢复成功
+----------------+
| Tables_in_test |
+----------------+
| students |
| td_course |
+----------------+
2 rows in set (0.00 sec)
差异备份与恢复
mysql差异备份
开启MySQL服务器的二进制日志功能
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve
server-id=1 //设置服务器标识符
log-bin=mysql_bin //开启二进制日志功能
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
对数据库进行完全备份
[root@localhost ~]# mysql -uroot -predhat
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| chenyu |
+--------------------+5 rows in set (0.00 sec)
mysql> show tables from chenyu;
+--------------------+
| Tables_in_chenyu |
+--------------------+
| runtime |
| student |
+--------------------+2 rows in set (0.01 sec)
mysql> select * from chenyu.runtime;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | tom | 10 |
| 2 | jerry | 30 |
+------+-------+------+2 rows in set (0.01 sec)
mysql> select * from chenyu.student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | chenyu | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+11 rows in set (0.00 sec)
// 完全备份
[root@localhost ~]# mysqldump -uroot -predhat --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202407301642.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll
总用量 792
-rw-r--r--. 1 root root 803946 7月 30 13:47 all-202407301642.sql
-rw-------. 1 root root 1259 1月 7 16:39 anaconda-ks.cfg
// 增加新内容
[root@localhost ~]# mysql -uroot -predhat
mysql> use chenyu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into chenyu values(3,'hehe',20),(4,'xixi',50);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from chenyu;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | tom | 10 |
| 2 | jerry | 30 |
| 3 | hehe | 20 |
| 4 | xixi | 50 |
+------+-------+------+4 rows in set (0.00 sec)
mysql> update chenyu set age = 40 where id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from chenyu;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | tom | 10 |
| 2 | jerry | 30 |
| 3 | hehe | 40 |
| 4 | xixi | 50 |
+------+-------+------+4 rows in set (0.00 sec)
4.4.2. mysql差异备份恢复
模拟误删数据
[root@localhost ~]# mysql -uroot -predhat -e 'drop database chenyu;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -predhat -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
//由上可以看到chenyu这个数据库已被删除
刷新创建新的二进制日志
[root@localhost ~]# ll /opt/data/
//这里就只复制mysql的日志文件
-rw-r-----. 1 mysql mysql 154 2月 21 16:09 mysql_bin.000001 // mysql的日志文件
//刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -predhat flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
//这里就只复制mysql的日志文件
-rw-r-----. 1 mysql mysql 945 2月 21 16:18 mysql_bin.000001
-rw-r-----. 1 mysql mysql 154 2月 21 16:18 mysql_bin.000002
恢复完全备份
[root@localhost ~]# mysql -uroot -predhat < all-202407301642.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -predhat -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| cy |
+--------------------+
[root@localhost ~]# mysql -uroot -predhat -e 'show tables from chenyu;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Tables_in_cy |
+--------------------+
| runtime |
| student |
+--------------------+
[root@localhost ~]# mysql -uroot -predhat -e 'select * from cy.chenyu;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | tom | 10 |
| 2 | jerry | 30 |
+------+-------+------+
[root@localhost ~]# mysql -uroot -predhat -e 'select * from cy.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | chenyu | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
恢复差异备份
[root@localhost ~]# ll /opt/data/
总用量 189572
-rw-r-----. 1 mysql mysql 56 2月 20 10:11 auto.cnf
-rw-r-----. 1 mysql mysql 996 2月 21 14:54 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 2月 21 16:20 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月 21 16:20 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月 20 10:11 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 2月 21 14:54 ibtmp1
-rw-r-----. 1 mysql mysql 8304 2月 21 08:45 linux-node1.com.err
-rw-r-----. 1 mysql mysql 74620 2月 21 14:54 localhost.err
drwxr-x---. 2 mysql mysql 4096 2月 21 16:20 mysql
-rw-r-----. 1 mysql mysql 945 2月 21 16:18 mysql_bin.000001
-rw-r-----. 1 mysql mysql 786443 2月 21 16:20 mysql_bin.000002
-rw-r-----. 1 mysql mysql 38 2月 21 16:18 mysql_bin.index
drwxr-x---. 2 mysql mysql 8192 2月 20 10:11 performance_schema
drwxr-x---. 2 mysql mysql 8192 2月 20 10:11 sys
drwxr-x---. 2 mysql mysql 96 2月 21 16:20 cy
//检查误删数据库的位置在什么地方
[root@localhost ~]# mysql -uroot -predhat
mysql> show binlog events in 'mysql_bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.22-log, Binlog ver: 4 |
| mysql_bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000001 | 219 | Query | 1 | 295 | BEGIN |
| mysql_bin.000001 | 295 | Table_map | 1 | 353 | table_id: 330 (cy.chenyu) |
| mysql_bin.000001 | 353 | Write_rows | 1 | 410 | table_id: 330 flags: STMT_END_F |
| mysql_bin.000001 | 410 | Xid | 1 | 441 | COMMIT /* xid=2628 */ |
| mysql_bin.000001 | 441 | Anonymous_Gtid | 1 | 506 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000001 | 506 | Query | 1 | 582 | BEGIN |
| mysql_bin.000001 | 582 | Table_map | 1 | 640 | table_id: 330 (cy.chenyu) |
| mysql_bin.000001 | 640 | Update_rows | 1 | 698 | table_id: 330 flags: STMT_END_F |
| mysql_bin.000001 | 698 | Xid | 1 | 729 | COMMIT /* xid=2630 */ |
| mysql_bin.000001 | 729 | Anonymous_Gtid | 1 | 794 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000001 | 794 | Query | 1 | 898 | drop database cy | //此处就是删除数据库的位置，对应的pos位置是794
| mysql_bin.000001 | 898 | Rotate | 1 | 945 | mysql_bin.000002;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+15 rows in set (0.00 sec)
//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=794 /opt/data/mysql_bin.000001 |mysql -uroot -predhat
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -predhat -e 'select * from cy.chenyu;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | tom | 10 |
| 2 | jerry | 30 |
| 3 | hehe | 40 |
| 4 | xixi | 50 |
+------+-------+------+
实验
1.搭建mysql服务
2.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下：
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+3 rows in set (0.01 sec)
mysql> create table student (id int(11) not null primary key,name varchar(100) not nu
ll,age tinyint(4));
Query OK, 0 rows affected (0.00 sec)
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
3.查看下该新建的表有无内容（用select语句）
4.往新建的student表中插入数据（用insert语句），结果应如下所示：
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | chenyu | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | chenyu | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
5.修改lisi的年龄为50
mysql> update student set age =50 where name ='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | chenyu | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)6.以age字段降序排序
mysql> select * from student order by age;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 9 | wangwu | 3 |
| 8 | chenshuo | 10 |
| 10 | qiuyi | 15 |
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
| 11 | qiuxiaotian | 20 |
| 2 | jerry | 23 |
| 3 | chenyu | 25 |
| 5 | zhangshan | 26 |
| 4 | sean | 28 |
| 7 | lisi | 50 |
+----+-------------+------+
11 rows in set (0.00 sec)7.查询student表中年龄最小的3位同学跳过前2位
mysql> select * from student order by age limit 2,3;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 10 | qiuyi | 15 |
| 1 | tom | 20 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
3 rows in set (0.00 sec)8.查询student表中年龄最大的4位同学
mysql> select * from student order by age desc limit 4;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | chenyu | 25 |
+----+-----------+------+
4 rows in set (0.00 sec)
9.查询student表中名字叫zhangshan的记录
10.查询student表中名字叫zhangshan且年龄大于20岁的记录
mysql> select * from student where name ='zhangshan';
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
+----+-----------+------+
2 rows in set (0.00 sec)
mysql> select * from student where name ='zhangshan' and age > 20;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.00 sec)
11.查询student表中年龄在23到30之间的记录
mysql> select * from student where age between 23 and 30;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | chenyu | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
+----+-----------+------+
4 rows in set (0.00 sec)
12.修改wangwu的年龄为100
mysql> update student set age =100 where name ='wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student where name ='wangwu';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 9 | wangwu | 100 |
+----+--------+------+
1 row in set (0.00 sec)
13.删除student中名字叫zhangshan且年龄小于等于20的记录
mysql> delete from student where name ='zhangshan' and age <= 20;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | chenyu | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
10 rows in set (0.00 sec)
2173
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
