【网络服务&数据库教程】10 MySQL 基础

1 MySQL 数据库 - 理论基础

1.1 什么是数据库？

数据：描述事物的符号记录，可以是数字、文字、图形、图像、声音、语言等，数据有多种形式，它们都可以经过数字化后存入计算机（各种形式存入计算机后均会变成二进制0101的格式）。

数据库：存储数据的仓库，是长期存放在计算机内、有组织、可共享的大量数据的集合。数据库中的数据按照一定数据模型组织、描述和存储，具有较小的冗余度，较高的独立性和易扩展性，并为各种用户共享，总结为以下几点：

• 数据结构化。
• 数据的共享性高，冗余度低，易扩充
• 数据独立性高
• 数据由 DBMS 统一管理和控制（安全性、完整性、并发控制、故障恢复）

解释：DBMS数据库管理系统（能够操作和管理数据库的大型软件）

1.2 数据库与文件系统的区别

文件系统：文件系统是操作系统用于明确存储设备（常见的是磁盘）或分区上的文件的方法和数据结构；即在存储设备上组织文件的方法。操作系统中负责管理和存储文件信息的软件机构称为文件管理系统，简称文件系统。

可以理解文件系统是操作系统的一个分支系统（子系统）。文件系统也是一套管理软件，主要用于管理磁盘以及分区上的文件数据。

底层如何管理磁盘和分区上文件，则是由不同的文件系统类型进行负责，例如常见的文件系统类型有 NTFS，EXT3/4，XFS，FAT32等

数据库系统：数据库管理系统（Database Management System）是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称 DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。

文件系统和数据库系统对比区别：

1. 管理对象不同：文件系统的管理对象是文件，并非直接对数据进行管理，不同的数据结构需要使用不同的文件类型进行保存（举例：txt 文件和 doc 文件不能通过修改文件名后缀完成转换，因为不同的文件类型所使用的编码格式完全不同，无法直接通过后缀名的修改，从而改变文件类型）；而数据库直接对数据进行存储和管理。
2. 存储方式不同：文件系统使用不同的文件将数据分类（.doc / .mp4 / .jpg）保存在外部存储上；数据库系统使用标准统一的数据类型进行数据保存（字母、数字、符号、时间）

文件系统中的不同的文件需要有不同的管理软件进行管理，例如 .mp4 需要使用 mp4的管理软件，.jpg 需要使用 jpg 的管理软件，以此类推。要打开不同类型的文件，就需要不同的管理软件进行操作。存储同理。

数据库系统则解决了管理不同文件类型的问题，使用统一的数据类型进行数据的保存和管理。

1. 调用数据的方式不同：文件系统使用不同的软件打开不同类型的文件；数据库系统由DBMS统一调用和管理。如下图：

优缺点总结：

• 由于DBMS的存在，用户不再需要了解数据存储和其他实现的细节，直接通过 DBMS 就能获取数据，为数据的使用带来极大便利。
• 具有以数据为单位的共享性，具有数据的并发访问能力。DBMS保证了在并发访问时数据的一致性。
• 低延时访问，典型例子就是线下支付系统的应用，支付规模巨大的时候，数’厍尔统的衣儿远心优于文件系统。
• 能够较为频繁的对数据进行修改，在需要频繁修改数掂的功京下，数据库系统可以依赖 DBMS 对数据进行操作且对性能的消耗相比文件系统比较小。
• 对事务的支持。DBMS 支持事务，即一系列对数据的操作集合要么都完成，要么都不完成。在 DBMS上对数据的各种操作都是原子级的。

数据库系统：

• 表示层：数据库 - 数据表 - 记录（字段）- 属性
• 逻辑层：数据库的存储引擎（MyISAM，InnoDB）
• 物理层：数据库文件（*.sql）

文件系统：

• 表示层：文件名（文件类型：.txt .mp4 …）
• 逻辑层：文件系统类型（EXT4 / NTFS / XFS）如何把文件数据保存到文件系统内部
• 物理层：分区块（数据块 Data Block） --> 扇区

数据库系统的物理层就是文件系统的表示层。数据库的存储系统是建立在文件系统层次以上的一种新的数据存储模型。

1.3 常见数据库有哪些？

• 关系型数据库

关系数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。简单说，关系型数据库是由多张能互相联接的二维行列表格组成的数据库。

关系模型就是指二维表格模型，因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。当前主流的关系型数据库有 Oracle、IBM DB2、Microsoft SQL Server、 Microsoft Access、MySQL、浪潮 K-DB等。

实体关系模型 简称 E-R模型，是一套数据库的设计工具，他运用真实世界中事物与关系的观念，来解释数据库中的抽象的数据架构。实体关系模型利用图形的方式(实体-关系图)来表示数据库的概念设计，有助于设计过程中的构思及沟通讨论。

• 非关系型数据库

非关系型数据库：又被称为NoSQL（Not Only SQL），意为不仅仅是SQL，是一种轻量、开源、不兼容SQL功能的数据库，对 NoSQL最普遍的定义是“非关联型的”，强调 Key-Value 存储和文档数据库的优点，而不是单纯地反对RDBMS（关系型数据库管理系统）。

1.4 关系型数据库（MySQL）的特征及组成结构介绍

关系型数据库的发展历程

• 层次模型

• 网状模型

层次模型和网状模型已经淘汰；取而代之的是关系模型。

关系模型（Relation）

关系模型以二维表结构来表示实体与实体之间的联系，关系模型的数据结构是一个“二维表框架”组成的集合。每个二维表又可称为关系。在关系模型中，操作的对象和结果都是二维表。

关系模型是目前最流行的数据库模型。支持关系模型的数据库管理系统称为关系数据库管理系统，Access 就是一种关系数据库管理系统。图所示为一个简单的关系模型，其中图(a)所示为关系模式，图(b)所示为这两个关系模型的关系，关系名称分别为教师关系和课程关系，每个关系均含3个元组，其主码均为“教师编号”。

在关系模型中基本数据结构就是二维表，不用像层次或网状那样的链接指针。记录之间的联系是通过不同关系中同名属性来体现的。例如，要查找“刘晋”老帅所上的课程，可以先仕教帅大职T能据姓名找到教师编号“1984030”，然后在课程关系中找到“1984030”任课教师编号对应的课程名即可。通过上述查询过程，同名属性教师编号起到了连接两个关系的纽带作用。由此可见，关系模型中的各个关系模式不应当是孤立的，也不是随意拼凑的一堆二维表，它必须满足相应的要求。必须在两张数据表中设置相同属性字段的数据内容进行连接。

关系式数据库的组成结构和名词解释：

数据以表格的形式出现，每行为单独的一条记录，每列为一个单独的字段，许多的记录和字段组成一张表单 （table），若干的表单组成库（database）

• 记录（一条数据）

在数据库当中，表当中的行称之为记录

• 字段（id name …）

在数据库当中，表当中的列称之为字段（记录同类型的数据，还需要对数据类型范围进行约束）

• MySQL数据类型

数据类型用于指定特定字段所包含数据的规则，它决定了数据保存在字段里的方式，包括分配给字段的宽度，以及值是否可以是字母、数字、日期和时间等。任何数据或数据的组合都有对应的数据类型，用于存储字母、数字、日期和时间、图像、二进制数据等。数据类型是数据本身的特征，其特性被设置到表里的字段。其实是对该字段的类型进行约束。

MySQL常见基础数据类型：

*字符串类型【CHAR （0-255固定长度）VARCHAR （0-255可变长度）】

*数值类型【INT（整数型）、FLOAT（浮点型）】

*日期和时间类型【DATE（年月日）、TIME（时分秒）】

• MySQL约束类型

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

（1）*主键约束 primary key：主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

（2）*外键约束 foreign key：外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

（3）*唯一约束 unique：唯一约束是指定 table 的列或列组合不能重复，保证数据的唯一性。唯一约束不允许出现重复的值，但是可以为多个null。同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

（4）*非空约束 not null 与 默认值default：非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。Null类型特征：所有的类型的值都可以是null，包括int、float等数据类型。

• MySQL 索引

索引是一个单独的、物理的数据库结构，它是某个表中一字段或若干字段值的集合。表的存储由两部分组成，一部分用来存放数据，另一部分存放索引页面。通常，索引页面相对于数据页面来说小得多。数据检索花费的大部分开销是磁盘读写，没有索引就需要从磁盘上读表的每一个数据页，如果有索引，则只需查找索引页面就可以了。所以建立合理的索引，就能加速数据的检索过程。

索引和数据表是同一级别的。将数据表（大表）中的唯一约束以及其他的某一两列的字段单独取出来并创建出新的数据表，查询的时候就从新的数据表进行查询，查询到记录之后，再通过该记录的数据信息，来从之前的数据表（大表）进行过滤查找。

> 索引就是为了加快数据的查询效率。

• MySQL 锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况（这一情况也叫对数据的并发操作）。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

• MySQL 的存储引擎

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

MYISAM：默认引擎、插入和查询速度较快，支持全文索引，不支持事务、行级锁和外键约束等功能。

INNODB：支持事务、行级锁和外键约束等功能。适合于大并发的场景。

MEMORY：工作在内存中，通过散列字段保存数据，速度快、不能永久保存数据。需要MEMORY的数据持久化的保存到磁盘中。

• 事务（Transaction）是并发控制的基本单位。

可以把一系列要执行的操作称为事务，而事务管理就是管理这些操作要么完全执行，要么完全不执行

经典案例:银行转账工作，从一个账号扣款并使另一个账号增款，这两个操作要么都执行，要么都不执行。所以，应该把它们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

注意： MySQL中并不是所有的数据引擎都支持事务管理的，只有 innodb支持事务管理。

事务是多个事件的集合。要么都完成，要么都不完成。从而确保数据的一致性。

1.5 数据类型概要

在 MySQL中，有三种主要的类型:文本、数字和日期/时间类型。

Text 类型：

Number 类型：

*这些整数类型拥有额外的选项UNVSIGNED。通常，整数可以是负数或正数。如果添加UNSIGNED属性，那么范围将从0 开始，而不是某个负数。

Date 类型：

2 MySQL 数据库 - 操作基础

MySQL官网: https://www.mysql.com/

2.1 MySQL 常见版本

MySQL Community Server：社区版本，开源免费，但不提供官方技术支持。

MySQL Enterprise Edition：企业版本，需付费，可以试用30天。

MySQL Cluster：集群版，开源免费。可将几个MySQL Server 封装成一个Server

MySQL Cluster CGE：高级集群版，需付费。

2.2 MySQL 安装部署

MySQL：MySQL客户端程序,

MySQL-Server： MySQL服务器端程序,

源代码编译安装：

编译工具：configure、cmake、make

数据库常用的配置选项

-DCMAKE_INSTALL_PREFIX=/PREFIX ----指定安装路径（默认的就是/usr/local/mysql)

-DMYSQL_DATADIR=/data/mysql ----mysql 的数据文件路径。

-DSYsCONFDIR=/etc ----配置文件路径,

-DWITH_INNOBASE_STORAGE_ENGINE=1 ----使用INNOBASE存储引擎﹒

-DWITH_READLINE=1 ----支持批量导入mysql数据

-DWITH SSL=system ----mysql支持ssl

-DWITH_ZLIB=system ----支持压缩存储

-DMYSQL_TCP_PORT=3306 ----默认端口3306

-DENABLED_LOCAL_INFILE=1 ----启用加载本地数据

-DMYSQL_USER=mysql ----指定mysql运行用户

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock ----默认套接字文件路径

-DEXTRA_CHARSETS=all ----是否支持额外的字符集

-DDEFAULT_CHARSET=utf8 ----默认编码机制。

-DWITH_DEBUG=0 ----DEBUG功能设置

常见资料：

服务：mysqld 端口：3306

主配置文件：/etc/my.cnf 初始化脚本：mysql_install_db

启动命令：mysqld_safe 数据目录：/var/lib/mysql

套接字文件：/var/lib/mysql/mysql.sock（源码编译安装一般指定在/tmp下，需要有足够的写权限，否则sock文件是生成不了的）

#当意外关闭数据库时，再开启时假如开启不了，找到这个，删除再启动

进程文件：/var/run/mysqld/mysqld.pid

MySQL 登录及退出命令：

设置密码：mysqladmin-uroot password ‘123456’

登录：mysql -u用户名 -p密码 -P端口 -S套接字文件

-p ：用户密码,

-h ：登陆位置（主机名或ip地址）

-P ：端口号（默认端口：3306改了就不是了）

-S ：套接字文件（/var/lib/mysql/mysql.sock）

退出命令: exit 或 Ctrl+D

#使用yum方式安装
yum install -y mysql-server mysql 
#yum install -y mariadb mariadb-server
systemctl enable --now mysqld
#service mysqld start && chkconfig --add mysqld && chkconfig mysqld on

#端口号
netstat -antlp |grep 3306
#主配置文件
vim /etc/my.cnf
#套接字文件
ls -l /var/lib/mysql/mysql.sock
#数据目录
ls -l /var/lib/mysql
#进程文件
#ls -l /var/run/mariadb/mariadb.pid
ls -l /var/run/mysqld/mysqld.pid

#设置数据库root登录密码
mysqladmin -uroot password 123456
#mysql -uroot -p123456
mysql -uroot -p123456 -P3306 -hlocalhost -S /var/lib/mysql/mysql.sock
MariaDB [(none)]>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

2.3 MySQL 管理命令

本次实验部署的是 Mariadb 版本，MySQL版本也可以进行测试实验。

2.3.1 创建登录用户

~ mysql -uroot -p123456
MariaDB [(none)]> create user zhangsan@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
# %：指任意的远程终端
# localhost：只允许本地登录
1
2
3
4
5

2.3.2 测试用户登录

#mysql -uzhangsan -p123456 -h139.198.105.99
#MariaDB [(none)]>

mysql -uzhangsan -h10.0.0.101 -p123456
1
2
3
4

2.3.3 用户为自己更改密码

mysql> set password=password('Admin@h3c');
Query OK, 0 rows affected (0.00 sec)
1
2

2.3.4 root用户为其他用户找回密码

~ mysql -uroot -p123456
MariaDB [(none)]> set password for zhangsan@'%'=password('123456');
Query OK, 0 rows affected (0.01 sec)

~ mysql -uzhangsan -h10.0.0.101 -p123456
1
2
3
4
5

2.3.5 root找回自己的密码并修改

关闭数据库，修改主配置文件（/etc/my.cnf）添加：skip-grant-tables

~ systemctl stop mysqld
#systemctl stop mariadb 

~ vim /etc/my.cnf
[mysqld]
skip-grant-tables
#启动数据库
~ systemctl start mysqld
#systemctl start mariadb
~ mysql -uroot
1
2
3
4
5
6
7
8
9
10

启动数据库，空密码登录并修改密码

update mysql.user set password=password('新密码') where user='root';
1

删除 skip-grant-tables ，重启数据库验证新密码

systemctl restart mysqld
#systemctl restart mariadb

~ mysql -uroot -pAdmin@h3c
1
2
3
4

2.3.6 创建查询数据库

MariaDB [(none)]> create database web;
Query OK, 1 row affected (0.00 sec)
#create database if not exists web ;
MariaDB [(none)]> show databases;
1
2
3
4

2.3.7 创建数据表

#选择要使用的数据库
MariaDB [(none)]>  use web;
Database changed
#创建a1表，并添加id和name字段以及类型
MariaDB [web]> create table a1 (id int,name varchar(30));
Query OK, 0 rows affected (0.00 sec)
#查看表结构（字段）
MariaDB [web]> describe a1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#复杂一些
MariaDB [web]> create table a2 (
    -> id int unsigned not null auto_increment,	#字段要求为正数，并且自增长，主键
    -> name varchar(30) not null default '',		#字符类型长度为30字节，默认值为空格
    -> age int not null default 0,							#字段默认值为0
    -> primary key(id));												#设置id为主键
Query OK, 0 rows affected (0.01 sec)

MariaDB [web]> describe a2; #desc a2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)      | NO   |     |         |                |
| age   | int(11)          | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

2.3.8 插入数据

#指明插入字段和数据
MariaDB [web]> insert into a2(id,name,age) values (1,'zhangsan','21');
Query OK, 1 row affected (0.00 sec)
#查询数据表内容信息
MariaDB [web]> select * from a2;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  21 |
+----+----------+-----+
1 row in set (0.00 sec)
#按顺序插入指定的字段
MariaDB [web]> insert into a2 values (2,'lisi','20');
Query OK, 1 row affected (0.00 sec)
#未声明年龄
MariaDB [web]> insert into a2 values (3,'wangwu','');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [web]> select * from a2;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  21 |
|  2 | lisi     |  20 |
|  3 | wangwu   |   0 |
+----+----------+-----+
3 rows in set (0.00 sec)

#插入多条数据
MariaDB [web]> insert into a2 values (4,'zhao','19') , (5,'sun','25');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

2.3.9 将表a2的数据复制到表a1

MariaDB [web]> select * from a1;
Empty set (0.00 sec)
#查询a2值，并写入到a1
MariaDB [web]> insert into a1(id,name) select id,name from a2;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [web]> select * from a1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
|    4 | zhao     |
|    5 | sun      |
+------+----------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.3.10 删除数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| google             |
| mysql              |
| performance_schema |
| test               |
| web                |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> drop database google; #drop database if not exists google;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| web                |
+--------------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

2.3.11 删除数据表

drop table a1;
show tables;

MariaDB [web]> create table a3 (id int,name char(30));
Query OK, 0 rows affected (0.00 sec)

MariaDB [web]> show tables;
+---------------+
| Tables_in_web |
+---------------+
| a1            |
| a2            |
| a3            |
+---------------+
3 rows in set (0.00 sec)

MariaDB [web]> drop table a3;
Query OK, 0 rows affected (0.00 sec)

MariaDB [web]> show tables;
+---------------+
| Tables_in_web |
+---------------+
| a1            |
| a2            |
+---------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

2.3.12 删除表里的数据记录

#删除id=5的记录
MariaDB [web]> delete from a2 where id=5;
Query OK, 1 row affected (0.00 sec)
#删除年龄子啊23-25之间的数据
MariaDB [web]> delete from a2 where age between 23 and 25;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6

注意：库和表的删除使用的是 drop，记录删除用的是 delete。

2.3.13 修改表中的数据

MariaDB [web]> select * from a2;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  21 |
|  2 | lisi     |  20 |
|  3 | wangwu   |   0 |
|  4 | zhao     |  19 |
+----+----------+-----+
4 rows in set (0.00 sec)
#修改表中的数据
MariaDB [web]> update a2 set age=21 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [web]> select * from a2;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  21 |
|  2 | lisi     |  20 |
|  3 | wangwu   |  21 |
|  4 | zhao     |  19 |
+----+----------+-----+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

2.3.14 修改数据表的名称

#修改数据表的名称
MariaDB [web]> alter table a2 rename a3;
Query OK, 0 rows affected (0.00 sec)

MariaDB [web]> select * from a3;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  21 |
|  2 | lisi     |  20 |
|  3 | wangwu   |  21 |
|  4 | zhao     |  19 |
+----+----------+-----+
4 rows in set (0.00 sec)

MariaDB [web]> select * from a2;
ERROR 1146 (42S02): Table 'web.a2' doesn't exist
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.3.15 修改数据表的字段类型

MariaDB [web]> describe a1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#修改数据表的字段类型
MariaDB [web]> alter table a1 modify name varchar(50);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [web]> describe a1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2.3.16 修改数据表的字段类型详情

MariaDB [web]> describe a1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#修改数据表的字段类型详情
MariaDB [web]> alter table a1 change name username char(50) not null default '';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [web]> describe a1;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int(11)  | YES  |     | NULL    |       |
| username | char(50) | NO   |     |         |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2.3.17 添加字段

MariaDB [web]> describe a1;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int(11)  | YES  |     | NULL    |       |
| username | char(50) | NO   |     |         |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#添加字段
MariaDB [web]> alter table a1 add time datetime;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
#添加位置默认在末尾
MariaDB [web]> describe a1;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int(11)  | YES  |     | NULL    |       |
| username | char(50) | NO   |     |         |       |
| time     | datetime | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [web]> alter table a1 add birthday year first;		#添加字段到第一列
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [web]> alter table a1 add sex nchar(1) after id;	#添加到指定字段的后列
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [web]> describe a1;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| birthday | year(4)  | YES  |     | NULL    |       |
| id       | int(11)  | YES  |     | NULL    |       |
| sex      | char(1)  | YES  |     | NULL    |       |
| username | char(50) | NO   |     |         |       |
| time     | datetime | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

2.3.18 删除字段

MariaDB [web]> describe a1;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| birthday | year(4)  | YES  |     | NULL    |       |
| id       | int(11)  | YES  |     | NULL    |       |
| sex      | char(1)  | YES  |     | NULL    |       |
| username | char(50) | NO   |     |         |       |
| time     | datetime | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#删除字段
MariaDB [web]> alter table a1 drop birthday;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [web]> describe a1;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int(11)  | YES  |     | NULL    |       |
| sex      | char(1)  | YES  |     | NULL    |       |
| username | char(50) | NO   |     |         |       |
| time     | datetime | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

drop 可以结合 alter table 删除表中的字段，delete 是删除表中的数据记录。

2.3.19 MySQL用户授权

授权用户全部权限

MariaDB [web]> select user from mysql.user;
#给已存在的用户授权
MariaDB [web]> grant all on web.a1 to zhangsan@'%'; 
# grant all on web.* to zhangsan@'%';#该数据库下的数据表拥有权限
Query OK, 0 rows affected (0.00 sec)

#创建用户并授权
MariaDB [web]> grant all on web.a1 to abc@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9

取消 abc 用户的数据库，表，表中数据的权限

#取消删除权限（登录abc测试）
MariaDB [web]> revoke drop,delete on web.a1 from abc@'%';
Query OK, 0 rows affected (0.01 sec)
#查看指定用户的授权
MariaDB [web]> show grants for abc@'%';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for abc@%                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'abc'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'                         |
| GRANT SELECT, INSERT, UPDATE, CREATE, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `web`.`a1` TO 'abc'@'%' |
+----------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

#查看指定用户的授权
MariaDB [web]> show grants for zhangsan@'%';
+---------------------------------------------------------------------------------------------------------+
| Grants for zhangsan@%                                                                                   |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `web`.`a1` TO 'zhangsan'@'%'                                                    |
+---------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
~ mysql -uabc -h10.0.0.101 -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| web                |
+--------------------+
3 rows in set (0.00 sec)

mysql> use web;
Database changed
mysql> show tables;
+---------------+
| Tables_in_web |
+---------------+
| a1            |
+---------------+
1 row in set (0.01 sec)

mysql> drop table a1;
ERROR 1142 (42000): DROP command denied to user 'abc'@'10.0.0.102' for table 'a1'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

2.4 备份和还原

mysqldump备份：

备份：

mysqldump -u 用户名 -p 数据库名 > /备份路径/备份文件名（备份整个数据库）

mysqldump -u 用户名 -p 数据库名 表名 > /备份路径/备份文件名（备份数据表）

-p：指定密码

• 备份多个库：–databases 库1，库2。
• 备份所有库：–all-databases
• 备份多个表：库名 表1 表2。

还原：mysql 数据库 < 备份文件。

注意：还原时，若导入的是某表，请指定导入到哪一个库中

范例：mysqldump实验

mkdir -pv /mysqldata

100~ mysqldump -uroot -pAdmin@h3c web > /mysqldata/web.sql
100~ ls -l /mysqldata/web.sql
-rw-r--r-- 1 root root 2715 Jul  8 14:48 /mysqldata/web.sql

101~ scp -r root@10.0.0.101:/mysqldata  /
#101初始化数据库，无自建数据库
101~ mysql -uroot -p123456 < /mysqldata/web.sql
ERROR 1046 (3D000) at line 22: No database selected
101~ mysql -uroot -p123456
mysql> create database web;
#导入到web数据库中
101~ mysql -uroot -p123456 web < /mysqldata/web.sql
#验证
101~ mysql -uroot -p123456
mysql> use web;
Database changed
mysql> show tables;
+---------------+
| Tables_in_web |
+---------------+
| a1            |
| a2            |
+---------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

mysqlhotcopy 备份：

备份：mysqlhotcopy --flushlog -u=‘用户’ -p=‘密码’ --regexp=正则 备份目录

还原：cp -a 备份目录 数据目录（/var/lib/mysql）

mysqlhotcopy 可以在数据库工作的情况下进行备份

范例：mysqlhotcopy 实验

#直接备份数据库目录文件
cp -a /var/lib/mysql/web /mysqldata/

#删除数据库文件
rm -rf /var/lib/mysql/web
~ mysql -uroot -pAdmin@h3c
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

#还原数据库文件
mv /mysqldata/web /var/lib/mysql/
#验证
~ mysql -uroot -pAdmin@h3c
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| web                |
+--------------------+
5 rows in set (0.00 sec)

### 以上的备份策略和 mysqlhotcopy 的备份策略类似，只是使用会更加的方便
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

mysqldump 和 mysqlhotcopy示例：

2.4.1 Mysql备份和还原

把数据库 aa 备份到 /root 目录下

mysqldump -uroot -p${MYSQL_ROOT-PASSWORD} aa > ~/aa.sql
1

模拟数据库 aa 丢失（删除数据库aa）

~ mysql -uroot -p${MYSQL_ROOT-PASSWORD}
#删除数据库aa
mysql> drop database aa;
1
2
3

通过 aa.sql 文件还原（指定导入到哪个库中）

mysql -uroot -p${MYSQL_ROOT-PASSWORD} test < aa.sql
1

备份多个数据库（–databases）

mysqldump -uroot -p${MYSQL_ROOT-PASSWORD} --databases aa test > abc.sql
1

还原（先模拟丢失）

#删除数据库
~ mysql -uroot -p${MYSQL_ROOT-PASSWORD}
mysql> drop database aa;
mysql> drop database test;

#还原数据库
~ mysql -uroot -p${MYSQL_ROOT-PASSWORD} < abc.sql
1
2
3
4
5
6
7

备份有规则的数据库

~ mysql -uroot -p${MYSQL_ROOT-PASSWORD}
mysql> create database a1;
mysql> create database a2;
mysql> create database a3;
mysql> create database a4;

#连续创建三个a开头的数据库
mysqlhotcopy --flushlog -u='root' -p='MYSQL_ROOT_PASSWORD' --regexp=^a /PATH
#范例：提前数据库中要有数据表
mysqlhotcopy --flushlog -u='root' -p='Admin@h3c' --regexp=^a /mysqldata/
1
2
3
4
5
6
7
8
9
10

还原（先模拟丢失）

~ mysql -uroot -p${MYSQL_ROOT-PASSWORD}
mysql> drop database al;					#顺序删除a开头的数据库
mysql> drop database a2;
mysql> drop database a3;
mysql> drop database a4;

cp -a /mysqldata/* /var/lib/mysql/			#复制产生的文件到数据库目录下
#登录数据库查看即可，数据库信息已经还原
1
2
3
4
5
6
7
8

2.4.2 mysql-binlog日志备份

二进制日志（log-bin日志)：所有对数据库状态更改的操作（create、drop、update等）

修改 my.cnf 配置文件开启binlog日志记录功能。

~ vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin		#启动二进制日志

~ systemctl restart mysqld
~ ls /var/lib/mysql/
... mysql-bin.000001 mysql-bin.index ...
#使用 mysqlbinlog 软件进行查看二进制文件，普通的文本查看文件显示为乱码
~ mysqlbinlog /var/lib/mysql/mysql-bin.000001
1
2
3
4
5
6
7
8
9

~ mysql -uroot -pAdmin@h3c
MariaDB [(none)]> create database abc;
MariaDB [(none)]> use abc;
MariaDB [abc]> create table xxx(id int,name char(30));
MariaDB [abc]> create table user(id int,name varchar(30));
MariaDB [abc]> insert into xxx(id,name) values(1,'zhangsan');
MariaDB [abc]> insert into xxx(id,name) values(2,'lisi');
MariaDB [abc]> select * from xxx;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
MariaDB [abc]> exit;
#会发现 binlog 多出很多的数据(只记录增删改，不记录查询)
~ mysqlbinlog /var/lib/mysql/mysql-bin.000001

#模拟数据丢失
mv /var/lib/mysql/abc /mysqldata/
#使用二进制文件进行数据恢复
mysqlbinlog --start-datetime '2022-07-08 15:21:00' --stop-datetime '2022-07-08 15:27:00' /var/lib/mysql/mysql-bin.000001 \
| mysql -uroot -pAdmin@h3c
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

按时间还原：

• –start-datetime
• –stop-datetime

格式：mysqlbinlog --start-datetime ‘YY-MM-DD HH:MM:SS’ --stop-datetime ‘YY-MM-DD HH:MM:SS’ 二进制日志 | mysql -uroot -p

按文件大小还原（以字节为单位）：

• –start-position
• –stop-position

mysql-binlog日志备份示例：

开启二进制日志

~ vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin		#启动二进制日志
1
2
3

查看二进制日志文件

ls /var/lib/mysql/mysql-bin.*
1

• 按时间还原

如果数据库中的 abc 库被删除，需要还原

drop database abc;
1

查看二进制日志内容

mysqlbinlog /var/lib/mysql/mysql-bin.000001
1

还原并查看

#格式：
#mysqlbinlog --start-datetime 'YY-MM-DD HH:MM:SS' --stop-datetime 'YY-MM-DD HH:MM:SS' 二进制日志 | mysql -uroot -p
mysqlbinlog --start-datetime '2022-07-08 15:25:42' --stop-datetime '2022-07-08 15:27:00' /var/lib/mysql/mysql-bin.000001 \
| mysql -uroot -pAdmin@h3c
1
2
3
4

注意：所选时间段一定要完整包含所有动作（可以在原来基础上稍微增加点时间）。

范例：查看是否还原

按文件大小还原：还原到bb库被删除的数据状态

1. 查看bb库被删除前后的文件大小

还原并查看