MYSQL数据库备份与恢复及sql语句

最新推荐文章于 2023-03-23 10:54:16 发布

不知道名字_

最新推荐文章于 2023-03-23 10:54:16 发布

阅读量113

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/bzdmz_/article/details/119940452

版权

MYSQL数据库备份与恢复

数据库常用备份方案

数据库备份方案：

全量备份
增量备份
差异备份

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

mysql备份工具mysqldump

语法：

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

常用的OPTIONS：

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

全量备份

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

MariaDB [(none)]> 
MariaDB [student]> select * from bzm; 
+----+-------------+------+
| 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;  
+--------------------+
| 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 bzm;
+----+-------------+------+
| 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]>

差异备份

MariaDB [student]> select * from bzm;
+----+-------------+------+
| 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 -p student bzm > 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 |
+-------------------+
| bzm       |
+-------------------+
1 row in set (0.000 sec)

MariaDB [student]> 

MariaDB [student]> drop table bzm;  
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 bzm;  
+----+-------------+------+
| 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]>

sql语句

group by

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
SQL GROUP BY 语法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

test 表:

MariaDB [cha]> select *from test;
+----------+------+
| name     | age  |
+----------+------+
| xiaoming |   10 |
| xiaofang |   20 |
| xiaowu   |   25 |
| xiaohu   |   14 |
| xiaoming |   11 |
| xiaohu   |   15 |
+----------+------+

inner join

sql语句如下:
select * from A
innerjoin B
on A.aID = B.aID

MariaDB [cha]> select *from A inner join B on A.aID = B.aID;
+-----+-----------+-----+----------+
| aID | aNum      | aID | aNum     |
+-----+-----------+-----+----------+
|   1 | a20050111 |   1 | 20050111 |
|   2 | a20050112 |   2 | 20050112 |
|   3 | a20050113 |   3 | 20050113 |
|   4 | a20050114 |   4 | 20050114 |
+-----+-----------+-----+----------+
4 rows in set (0.00 sec)

inner join、left join、right join

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

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

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

inner join(等值联接）

MariaDB [student]> select shenlongfei1.*,shenlongfei.* from shenlongfei1 inner join shenlongfei on shenlongfei1.age=shenlongfei.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 shenlongfei1.*,shenlongfei.* from shenlongfei1 left join shenlongfei on shenlongfei1.age=shenlongfei.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 shenlongfei1.*,shenlongfei.* from shenlongfei1 right join shenlongfei on shenlongfei1.age=shenlongfei.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数据库备份与恢复数据库常用备份方案数据库备份方案：全量备份增量备份差异备份备份方案特点全量备份全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长增量备份增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重
复制链接

扫一扫