mysql数据备份与sql

最新推荐文章于 2024-06-21 11:49:24 发布

快乐风男！

最新推荐文章于 2024-06-21 11:49:24 发布

阅读量213

点赞数

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/yanghx2/article/details/119943198

版权

mysql数据备份与sql

1.数据库常用备份方案

全量备份
增量备份
差异备份
特点：
全量备份全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长
增量备份增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行
差异备份备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。
语法：

mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

常用的OPTIONS：

    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

2.全量备份

查看库中的内容：

MariaDB [(none)]> show databases;  //查看库，然后进入库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> 
MariaDB [student]> select * from ;  //库中表里面有下面这些内容
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)

MariaDB [student]>

[root@centos8-1 ~]# mkdir mysqldump
[root@centos8-1 ~]# cd mysqldump/
[root@centos8-1 mysqldump]# ls
[root@centos8-1 mysqldump]# mysqldump -u root -p1 --all-databases > all-13174897.sql   //全量备份完成
[root@centos8-1 mysqldump]# ls
all-13174897.sql
[root@centos8-1 mysqldump]# 


MariaDB [(none)]> show databases;  //上面已经全量备份，删除库中的student
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> 


MariaDB [(none)]> drop database student;   //删除成功
Query OK, 1 row affected (0.004 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

MariaDB [(none)]>

还原全量备份

[root@centos8-1 mysqldump]# mysql -u root -p1 < all-13174897.sql 
[root@centos8-1 mysqldump]# 


MariaDB [(none)]> show databases;  //登录数据库已经还原
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> use  student    //进入库中查看内容还原成功
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
MariaDB [student]> select * from yanghaixing;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)

MariaDB [student]>

3.差异备份

查看表的内容

MariaDB [student]> select * from yanghaixing;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)

MariaDB [student]>

进行差异备份
[

root@centos8-1 mysqldump]# ls
all-13174897.sql
[root@centos8-1 mysqldump]# mysqldump -u root -p1 student yanghaixing > size-14366767.sql   //备份成功
[root@centos8-1 mysqldump]# ls
all-13174897.sql  size-14366767.sql
[root@centos8-1 mysqldump]# 

MariaDB [student]> show tables;   //查看库里是有表的
+-------------------+
| Tables_in_student |
+-------------------+
| shenlongfei       |
+-------------------+
1 row in set (0.000 sec)

MariaDB [student]> 

MariaDB [student]> drop table yanghaixing;  //删除表
Query OK, 0 rows affected (0.003 sec)

MariaDB [student]> show tables;
Empty set (0.001 sec)

MariaDB [student]>

恢复差异备份

[root@centos8-1 mysqldump]# mysql -u root -p1 student < size-14366767.sql 
[root@centos8-1 mysqldump]# 

MariaDB [(none)]> use student
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
MariaDB [student]> show tables;   //我们查看表，发现恢复成功
+-------------------+
| Tables_in_student |
+-------------------+
| shenlongfei       |
+-------------------+
1 row in set (0.001 sec)

MariaDB [student]> 
MariaDB [student]> select * from yanghaixing;  //表中内容也恢复
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.001 sec)

MariaDB [student]>

4.group by使用

group by说明

group by 一般和聚合函数一起使用才有意义,比如 count sum avg等,使用group by的两个要素:
(1) 出现在select后面的字段要么是是聚合函数中的,要么就是group by 中的.
(2) 要筛选结果可以先使用where 再用group by 或者先用group by 再用having

MariaDB [student]> select * from yanghaixing;   //表的内容
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.001 sec)

MariaDB [student]> select avg(age) from yanghaixing;  //对表进去求平均值
+----------+
| avg(age) |
+----------+
|  21.8182 |
+----------+
1 row in set (0.000 sec)

MariaDB [student]>

MariaDB [student]> select sum(age) from yanghaixing;  //也可以进行求和
+----------+
| sum(age) |
+----------+
|      240 |
+----------+
1 row in set (0.000 sec)

MariaDB [student]>

MariaDB [student]> select count(age) from yanghaixing;  //也可以进行计数
+------------+
| count(age) |
+------------+
|         11 |
+------------+
1 row in set (0.000 sec)

MariaDB [student]>

除了上面常用函数意外还有以下函数：

MariaDB [student]> select max(age) from yanghaixing;  //最大值
+----------+
| max(age) |
+----------+
|       50 |
+----------+
1 row in set (0.000 sec)

MariaDB [student]> 

MariaDB [student]> select min(age) from yanghaixing; //最小值
+----------+
| min(age) |
+----------+
|        3 |
+----------+
1 row in set (0.000 sec)

MariaDB [student]>

5.inner join

语法：

INNER JOIN 连接两个数据表的用法：
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

INNER JOIN 连接三个数据表的用法：
SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

INNER JOIN 连接四个数据表的用法：
SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号

INNER JOIN 连接五个数据表的用法：
SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号

6.inner join、left join和right join

内连接只返回满足连接条件的数据行，外连接不只列出与连接条件相匹配的行，而是列出左表（左外连接时）、右表（右外连接时）或两个表（全外连接时）中所有符合搜索条件的数据行。

外连接分为左外连接、右外链接、全外连接三种。

1）LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
查看库中两张表的内容

MariaDB [student]> select * from yanghaixing1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | yang |   12 |
|  2 | hai  |   53 |
|  3 | xing |   23 |
+----+------+------+
3 rows in set (0.000 sec)

MariaDB [student]> select * from yanghaixing;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)

MariaDB [student]>

内连（inner join）

MariaDB [student]> select shenlongfei1.*,yanghaixing.* from yanghaixing1 inner join yanghaixing on yanghaixing1.age=yanghaixing.age;
+----+------+------+----+-------+------+
| id | name | age  | id | name  | age  |
+----+------+------+----+-------+------+
|  3 | fei  |   23 |  2 | jerry |   23 |
+----+------+------+----+-------+------+
1 row in set (0.000 sec)

MariaDB [student]>

左连（left join）

MariaDB [student]> select yanghaixing1.*,yanghaixing.* from yanghaixing1 left join yanghaixing on yanghaixing1.age=yanghaixing.age;
+----+------+------+------+-------+------+
| id | name | age  | id   | name  | age  |
+----+------+------+------+-------+------+
|  3 | fei  |   23 |    2 | jerry |   23 |
|  1 | shen |   12 | NULL | NULL  | NULL |
|  2 | long |   53 | NULL | NULL  | NULL |
+----+------+------+------+-------+------+
3 rows in set (0.001 sec)

MariaDB [student]>

右连（right join）

MariaDB [student]> select yanghaixing1.*,yanghaixing.* from yanghaixing1 right join yanghaixing on yanghaixing1.age=yanghaixing.age;
+------+------+------+----+-------------+------+
| id   | name | age  | id | name        | age  |
+------+------+------+----+-------------+------+
|    3 | fei  |   23 |  2 | jerry       |   23 |
| NULL | NULL | NULL |  1 | tom         |   20 |
| NULL | NULL | NULL |  3 | wangqing    |   25 |
| NULL | NULL | NULL |  4 | sean        |   28 |
| NULL | NULL | NULL |  5 | zhangsan    |   26 |
| NULL | NULL | NULL |  6 | zhangsan    |   20 |
| NULL | NULL | NULL |  7 | lisi        |   50 |
| NULL | NULL | NULL |  8 | chenshuo    |   10 |
| NULL | NULL | NULL |  9 | wangwu      |    3 |
| NULL | NULL | NULL | 10 | qiuyi       |   15 |
| NULL | NULL | NULL | 11 | qiuxiaotian |   20 |
+------+------+------+----+-------------+------+
11 rows in set (0.000 sec)

MariaDB [student]>

快乐风男！

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql数据备份与sql

mysql数据备份与sql1.数据库常用备份方案全量备份增量备份差异备份特点：全量备份全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长增量备份增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据备份时间短恢复数据时必须按一定
复制链接

扫一扫