MySQL基础（九）—–备份与恢复、用户与权限、应用程序连接MySQL服务器

今天这一期，是MySQL基础的最后一期了，希望这一栏的MySQL基础知识能够对您有所帮助，喜欢的话麻烦给往期作品来个三连、写作不易请多多支持，后面会更新进阶的一些技术栈知识点。欢迎大家在评论区一起讨论技术，互相学习！

 MySQL的备份和恢复主要就是针对我们对数据库数据的错误操作导致的一系列问题做出的应对之策；

用户与权限一般是公司DBA给高级开发、普通开发的一个账号使用权限，用来保证一些数据的安全和表结构的正常维护等等；

连接MySQL服务器就是原始的jdbc连接mysql的一个代码，现在基本上都用的mybatis或者mybatis-plus了。但是还是要掌握一下原始的方法。

一、备份与恢复

我们在日常开发中可能会在某一刻脑子短路了，把库或者表里的数据全部删除了。这样对我们的一个整个研发和进展都带来了不小的损失，尤其是企业里工作出现这种低级错误，估计。。。

为了在发生这种情况时能不慌，所以我们要学一下数据备份，提前做好数据的备份，即使不小心删了，我们也可以恢复。

1、mysqldump

1.1、使用mysqldump备份数据

我们之前一直使用名为mysql的可执行文件（位于我们mysql安装的目录的bin目录下） 作为MySQL客户端与服务端进行交互。其实，在这个彬目录下还有许多可执行文件，其中这个mysqldump的可执行文件就是用来备份数据的。

这里强调一下哈，mysqldump是一个可执行文件，我们需要在命令行解释器（比如Unix系统的shell或者Windows的cmd.exe）中执行它。

接下来我们就来具体的操作一下吧

1、备份指定数据库中的指定表

先看命令：

mysqldump [其他选项] 数据库名 [表1，表2，表3...]
/**其他选项可以指的是数据库连接的账号，地址，密码等**/

 假如我们要备份学生成绩表

先打开cmd命令，输入下面这个命令

然后我们可以看到，因为本身mysqldump也是一个客户端程序，在进行备份数据时需要与服务器进行通信，所以我们需要填写账号、主机、密码。

因为mysqldump的输出内容较多，所以我们一般会把输出内容重定向到某个文件里，而不是直接在屏幕中输出。这个地方我直接定位到student_score.sql(上面写漏一个t)。输出的位置就在前面的路径下。我们可以打开看看：

-- MySQL dump 10.13  Distrib 5.7.38, for Win64 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.7.38-log


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `student_score`
--

DROP TABLE IF EXISTS `student_score`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student_score` (
  `number` int(10) unsigned NOT NULL COMMENT '学生id',
  `subject` varchar(30) NOT NULL COMMENT '学科',
  `score` tinyint(4) DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`number`,`subject`),
  CONSTRAINT `student_score_ibfk_1` FOREIGN KEY (`number`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生成绩表';
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Dumping data for table `student_score`
--

LOCK TABLES `student_score` WRITE;
/*!40000 ALTER TABLE `student_score` DISABLE KEYS */;
INSERT INTO `student_score` VALUES (20230101,'MySQL技术',88),(20230101,'计算机科学与技术',78),(20230102,'MySQL技术',98),(20230102,'计算机科学与技术',100),(20230103,'MySQL技术',61),(20230103,'计算机科学与技术',89),(20230104,'MySQL技术',59),(20230104,'计算机科学与技术',55);
/*!40000 ALTER TABLE `student_score` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2023-06-18  4:05:43

这里面就是我们导出的一个数据，包括表结构、表数据。如果这个表后面被误删，或者想要在其他服务器上创建这个表，直接执行这个sql文件就行了。

2、备份指定数据库中的所有表

先看命令

mysqldump [其他选项] --databases 数据库1名，数据库2名，数据库3名...

样例，这个地方我只放命令，不展示操作后的结果，太多了，就不展示了，大家自己在电脑上试试就行，如果有问题，欢迎评论区讨论。

mysqldump -uroot -hlocalhost -p --databases test test1

3、备份所有数据库的所有表

先看命令

mysqldump [其他选项] --all-databases

样例-不展示，只给命令

mysqldump -uroot -hlocalhost -p --all-databases

1.2、使用source语句恢复数据

在得到我们的备份文件后，可以通过source语句来执行备份文件中的语句。

先执行删除命令，在查看，发现我们的数据库中已经没有这个表了，所以查询保存，这个时候我们在执行这个命令：

source C:\Users\Administrator\studen_score.sql

从图中可以看到，已经执行成功，我们再去查一下数据库，看是否可以查到数据 

经过恢复后，我们的数据有返回了，和之前的一模一样。

所以，我们只要将备份的文件的路径放到source单词后面，就可以执行备份文件中的语句，也就可以恢复表中的数据了。

2、以文本形式导出或导入

这个就要涉及到实际工作中了，实际工作中，我们可能会遇到将表中的数据以文本的形式进行导出或者将文本形式的数据导入到表中。比如说，将表中的数据在Excel中展现或者将Excel文件中的数据导入到表中。此时，select…into outfile 和load data语句就要支棱起来了。（目前Navicat上有相应的功能可以直接导出，但是基本命令我们还是要掌握一下，不能总是依靠工具）

2.1、导出数据

先看SQL

select ... into outfile '文件路径' [导出选项]

 常用的导出选项：

• fields terminated by: 表示列分隔符，也就是各列的值之间使用什么符号进行分隔，默认以’\t’（也就是制表符）分隔（制表符就是键盘上的tab键对应的字符，效果等效于若干个空格）。demo：fields terminated by ‘,’表示各列的值以逗号分隔
• fields [optionally] enclosed by：表示列引用符，也就是每个列的值被什么符号包裹起来，默认是空字符串’ ‘。如果加上optionally则只会作用于字符串类型的列。demo：fields enclosed by ‘ ” ‘表示每个列的值都被双引号引起来；
• lines starting by：表示行开始符，也就是每一行以什么符号开头，默认是空字符串’ ‘。demo：lines starting by ‘%%’表示每行都是以两个百分号开头的
• lines terminated by：表示行结束符，也就是每一行以什么符号结尾，默认是换行符’\n’。demo：lines terminated by ‘$$’表示每行都以两个美元符号结尾

看样例：

select * from student_score INTO OUTFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\student_score.txt';

其他的就不演示了，大家自己可以操作一下。

使用select … into outfile语句导出的数据会被存储到运行服务器程序的主机上。

3、导入数据

先看命令

load data [local] infile '文件路径' into table 表名 [导入选项]

 如果填入local，则表明要导入的数据文件在运行客户端的主机中，否则就是在运行服务器的主机中。由于目前我们的客户端和服务器都在同一台主机上，所以填不填无所谓。

导入选项和导出选项类似，就不多介绍了。

样例：

load data infile 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\student_score.txt' into table student_score;

二、用户与权限

1、用户管理

在我们安装MySQL的时候，我们就创建了一个root的用户，之后使用的都是root。其实安装的时候，也会自动创建一些别的用户，这些用户都被存储到mysql数据库的user表中。我们可以先看看自己的有多少个用户，我这边加上root一共是三个

除了root以外的用户都是有特殊用处的，不能用于客户端连接。

1.1、创建用户

 我们可以使用create user 语句创建多个可以使用客户端连接服务器的用户。在创建用户时，一般要指定3项内容：

• 用户名；
• 主机名–指明客户端可以在那些机器上启动（ip地址也可以），如果省略则默认值为‘%’，表示该用户任意主机上都可以启动客户端；
• 密码-如果省略表示此用户暂不需要密码

样例：

 create user 'testuser'@'localhost' identified by '88888888';

上面介绍的用户名和主机名都被单引号引起来，其实这里的用户名和主机名都不包含特殊字符，不用引号引起来也可以。如果有特殊字符需要注意一下，必须要用引号引起来

使用新建的用户登录连接服务器成功。 

通过上图可以看到，新用户得权限非常的小，无法查看到其他的数据库，也无法操作其他的数据库。先介绍怎么修改密码和删除用户，我们再来讲怎么给用赋权限。

1.2、修改密码 

可以通过alter user 语句为某个用户修改密码

alter user 'testuser'@'localhost' identified by '12345678';

 testuser这个用户的密码就由88888888改为12345678了。

1.3、删除用户

如果我们觉得某个用户没有存在的必要了，则可以使用drop user删除它

 drop user testuser@localhost;

可以看到我们的testuser用户已经被删除了 

2、权限管理

2.1、授予权限

我们可以使用grant语句来为某个用户授予权限，先看模板：

grant 权限名称
on 应用级别

to '用户名'@'主机名'
[with grant option]

1、权限名称

MySQL中提供了许多种类的权限，权限不同，可进行的操作也不同。只有这个用户被赋予了那些权限才可以操作这些权限内的命令。MySQL提供的权限名称及描述如下：

 

这表里面的只是静态权限，还有一批动态权限的权限，用的不多，感兴趣的可以自己去了解一下。 

看完这些你是不是已经开始慌了，不要着急，你只需要掌握基本的增删改查的命令就好了。比如select、insert、update、delete、create、alter、drop等

2、应用级别

这里主要介绍几个常用的就OK啦

•  .：代表全局级别。全局级别的权限作用于任何数据库下的任何对象（诸如表、视图等）；
• 数据库名.*：代表数据库级别。数据库级别的权限作用于指定数据库下的任何对象；
• 数据库名.表名：代表表级别。表级别的权限作用于表中的任何列。

3、with grant option

如果在使用grant语句为某个用户授予权限时添加了with grant option子句，则表示该用户可以将自己拥有的权限授予他人。

root用户默认拥有最高权限，它可以把任何权限授予其他用户。

demo：

mysql> create user 'testuser'@'localhost' identified by '88888888';
Query OK, 0 rows affected (0.00 sec)



mysql> grant select on *.* to testuser@localhost with grant option;
Query OK, 0 rows affected (0.00 sec)


mysql> grant update on *.* to testuser@localhost with grant option;
Query OK, 0 rows affected (0.00 sec)

 其他的大家可以自己去试一下，这里不做多的展开。

2.2、查看权限

我们可以使用show grants语句查看某个用户的权限

模板：

show grants for '用户名'@'主机名';

demo： 

mysql> show grants for testuser@localhost;
+-------------------------------------------------------------------------+
| Grants for testuser@localhost                                           |
+-------------------------------------------------------------------------+
| GRANT SELECT, UPDATE ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

2.3、移除权限

给某个用户移除权限时，需要使用revoke语句

模板：

revoke 权限名称
on 应用级别

from '用户名'@'主机名'

demo：

mysql> revoke update on *.* from testuser@localhost;
Query OK, 0 rows affected (0.00 sec)



mysql> show grants for testuser@localhost;
+-----------------------------------------------------------------+
| Grants for testuser@localhost                                   |
+-----------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

三、应用程序连接MySQL服务器

在实现较为复杂的应用时，我们一般使用传统的编程语言进行实现。比如C、Java、PHP、Python等。这些应用产生的数据一般都是保存在DBMS里面，MySQL也是其一。这个地方就以Java为例给大家介绍如何用编程语言和MySQL服务器进行相互。

1、jdbc规范

除了MySQL以外，市场用的比较的DBMS还有Oracle、SQL server、pgsql等、所以Java这边有一个jdbc（Java database connectivity）的规范。它规定了使用Java访问DBMS的步骤，不管将来我们访问哪一种，按照它的规范操作步骤准没问题啦。

首先要支棱起jdbc前我们要先去MySQL官网下载一个jar包或者直接去maven上下载也行。版本自行选择。

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
            
</dependency>

2、demo

然后我们就开始先把代码写起来：

import java.sql.*;

public class JdbcDemo {
    public static final String URL = "jdbc:mysql://localhost:3306/test?user=root&password=root123321";

//    public static final String URL = "jdbc:mysql://localhost:3306/test?user=root&password=root123321&useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC";
    public static final String driver = "com.mysql.jdbc.Driver";

//    public static final String driver = "com.mysql.cj.jdbc.Driver";



    public static void main(String[] args) throws Exception{

        //1.加载驱动程序

        Class.forName(driver);

        //2.获得数据库链接

        Connection conn = DriverManager.getConnection(URL);

        //3.通过数据库的连接操作数据库，实现增删改查

        //生成statement对象，执行sql

        Statement stmt = conn.createStatement();

        //执行sql语句，并返回结果

        ResultSet rs = stmt.executeQuery("select * from student");
        //4.处理数据库的返回结果(使用ResultSet类)
        while(rs.next()){
            int number = rs.getInt("id");
            String name = rs.getString("name");
            String major = rs.getString("major");
            System.out.println(number + "\t" + name + "\t" + major);
        }
        //关闭连接，释放资源
        //关闭结果集
        rs.close();
        //关闭执行
        stmt.close();
        //关闭连接
        conn.close();



    }
}

在代码里面可能会因为版本的问题导致驱动有问题，两个里面选择一个符合版本的就行。另外这个地方为了更加直观的实现jdbc连接数据库的步骤，所以在main上面使用了throws exception。忽略掉各个方法可能出现的异常，实际工作，我们要对异常做必要处理。

分析代码：

1、加载驱动类

 com.mysql.jdbc.Driver/com.mysql.cj.jdbc.Driver是MySQL驱动类的类名。Class.forName(driver)语句的作用就是说明我们现在使用的MySQL的驱动程序来连接MySQL服务器。如果需要连接别的DBMS，需要填入相应的驱动名。

2、获得数据库连接

调用DriverManager.getConnection方法与MySQL服务器简历连接，该方法接收一个字符串作为参数。

协议://主机名?user=用户名&password=密码[&编码格式]

• 协议：代表采用那种协议与服务器进行沟通。这里填入jdbc.mysql就好了；
• 主机名：代表服务器所在的主机的名字是啥。代码中的示例是localhost，表名服务器就在本机运行；
• 用户名：代表以那个用户的身份与服务器建立连接。代码中是root；
• 密码：用户对应的密码。代码中是root用户的密码root123321。

3、生成Statement对象

MySQL语句对应的就是Statement，我们需要调用connection类的createStatement方法创建一个Statement对象。

4、执行语句并获取结果集对象

Statement类提供了一个executeQuery的方法来执行查询语句，我们只需要将相应的语句作为该方法的参数即可。executeQuery方法返回一个名为ResultSet类型的对象，该对象代表查询语句执行后的结果集。

5、处理结果集

在调用executeQuery方法执行了查询语句并得到ResultSet对象后，就可以通过ResultSet对象的方法来读取结果集的内容了。

ResultSet对象内部维护了一个变量，用来标记当前正在处理结果集中的那条记录，这个变量我们称之为cursor。（这个地方可以理解我们前面介绍的游标，类似于把游标放在循环里面了，done默认为0，没有记录了就改为1跳出循环。大家没印象了可以去前面看看）

ResultSet对象的next方法用于调整cursor指向记录的位置，每调用一次next方法，cursor就指向下一条记录。在cursor指向结果集中的记录之后，就可以通过ResultSet对象的一系列get方法来读取结果集记录中某个列的值了。这些get方法可以接收列名或者该列在结果集中的位置作为参数。

例如：getString(”name”)表示获取结果记录中列名为name的列的值，getString(1)表示结果集记录中第一个列的值。

常用的get方法：

• getString：将结果集中对应的列的值转为Java中的String类型返回；
• getBoolean：将结果集中对应的列的值转为Java中的Boolean类型返回；
• getByte：将结果集中对应的列的值转为Java中的Byte类型返回；
• getShort：将结果集中对应的列的值转为Java中的Short类型返回；
• getInt：将结果集中对应的列的值转为Java中的Int类型返回；
• getLong：将结果集中对应的列的值转为Java中的Long类型返回；
• getFloat：将结果集中对应的列的值转为Java中的Float类型返回；
• getDouble：将结果集中对应的列的值转为Java中的Double类型返回；
• getDate：将结果集中对应的列的值转为Java中的Date类型返回；
• getTime：将结果集中对应的列的值转为Java中的Time类型返回；
• getTimestamp：将结果集中对应的列的值转为Java中的Timestamp类型返回。

代码中获取到数据后拼接成一个字符串。

6、关闭连接

该步骤用来释放连接过程中占用的各种资源

结果如下：

3、执行更新和删除语句

Statement的executeQuery方法是用来执行查询语句的。如果我们想要执行update、delete语句的话，就得使用executeUpdate方法了。

import java.sql.*;

public class JdbcUpdateDemo {
    public static final String URL = "jdbc:mysql://localhost:3306/test?user=root&password=root123321";

    //    public static final String URL = "jdbc:mysql://localhost:3306/test?user=root&password=root123321&useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC";
    public static final String driver = "com.mysql.jdbc.Driver";

//    public static final String driver = "com.mysql.cj.jdbc.Driver";



    public static void main(String[] args) throws Exception{

        //1.加载驱动程序

        Class.forName(driver);

        //2.获得数据库链接

        Connection conn = DriverManager.getConnection(URL);

        //3.通过数据库的连接操作数据库，实现增删改查

        //生成statement对象，执行sql

        Statement stmt = conn.createStatement();

        //执行sql语句，并返回结果

        int update = stmt.executeUpdate("update student set name='张三' where id=20230101");
        int delete = stmt.executeUpdate("delete from student where id=20230106");
        //4.处理数据库的返回结果(使用ResultSet类)
        System.out.println(update);
        System.out.println(delete);
        //关闭连接，释放资源
        //关闭执行
        stmt.close();
        //关闭连接
        conn.close();
    }
}

 

4、使用PreparedStatement

在编写应用程序时，往往需要在接收到用户输入的参数后，才可以拼装起要发送给MySQL服务器的语句，这往往会引发SQL注入的风险。为了避免SQL注入的发生，我们应该使用PrepareStatement来代替Statement。参考文档1，参考文档2。PrepareStatement的方法就类似与现在的mybatis中占位符一样，就是防止用户输入导致的SQL注入问题。使用PrepareStatement可以有效避免SQL注入的问题发生。

MySQL基础的全部内容就到此结束了，希望可以对你MySQL学习有所帮助。喜欢作者内容的可以留个关注或者点赞收藏，后期会继续更新新的技术栈知识，敬请期待！！

今天的分享就到此结束了，如果觉得对您有帮助，麻烦给个三连！

以上内容为本人的经验总结和平时操作的笔记。若有错误和重复请联系作者删除！！感谢支持！！