mysql差异性备份数据库如何差异备份

转载

数据探索者 2024-07-23 22:50:34

文章标签 mysql差异性备份 mysql sql big data 数据库 文章分类 MySQL 数据库

mysql数据库备份与恢复与SQL语句

文章目录

mysql数据库备份与恢复与SQL语句

数据库常用备份方案
mysql备份工具mysqldump

差异备份与恢复

mysql差异备份

对数据库进行完全备份
恢复差异备份

group by的使用
**自然连接**INNER JOIN
**联合连接（UNION JOIN）**
外连接

数据库常用备份方案

数据库备份方案：

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

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

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

##备份整个数据库

mysql> show databases;   #先查看库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liuqiang           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

[root@localhost ~]# ls   
-all-20210826.sql  anaconda-ks.cfg  mm.txt
[root@localhost ~]# mysqldump -uroot -plq123456! --all-databases > all-$(date '+%Y%m%d').sql   #备份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls   查看备份文件是否存在
-all-20210826.sql  all-20210826.sql  anaconda-ks.cfg  mm.txt
##测试
mysql> drop database liuqiang;  #删除一个库
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)

[root@localhost ~]# mysql -uroot -plq123456! < all-20210826.sql  #进行恢复
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> show databases;    #登录mysql查看库已经恢复
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liuqiang           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

##备份数据库中的某一个表
mysql> select * from student; #查看是否存在表
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   45 |
|  3 | ss    |   78 |
|  4 | dwdw  |   45 |
+----+-------+------+
3 rows in set (0.00 sec)

[root@localhost ~]# mysqldump -uroot -plq123456! liuqiang student > student-$(date '+%Y%m%d').sql  #备份表

mysql> drop table student; # 删除数据库中的表
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
ERROR 1146 (42S02): Table 'liuqiang.student' doesn't exist

mysql> source student-20210826.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> select * from student; #恢复成功
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   45 |
|  3 | ss    |   78 |
|  4 | dwdw  |   45 |
+----+-------+------+
3 rows in set (0.00 sec)

差异备份与恢复

mysql差异备份

开启MySQL服务器的二进制日志功能

[root@localhost ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
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 -plq123456!  #登录数据库查看库
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 2
Server version: 5.7.34-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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 |
| liuqiang           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

[root@localhost ~]# mysqldump -uroot -plq123456! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > cyall-$(date '+%Y%m%d').sql  #进行差异备份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll  #查看
总用量 2580
-rw-r--r--. 1 root root      0 8月  26 04:23 -all-20210826.sql
-rw-r--r--. 1 root root 873495 8月  26 04:26 all-20210826.sql
-rw-------. 1 root root   1023 7月  16 07:36 anaconda-ks.cfg
-rw-r--r--. 1 root root 873643 8月  26 04:58 cyall-20210826.sql

mysql> create table lq;  #增加内容
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table lq(name varchar(45),id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into lq values('ded',13),('dwqd',78);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from lq;
+------+------+
| name | id   |
+------+------+
| ded  |   13 |
| dwqd |   78 |
+------+------+
2 rows in set (0.00 sec)

mysql> drop database liuqiang;   #删除库
Query OK, 2 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

[root@localhost ~]# ll /opt/data/
总用量 123012
-rw-r-----. 1 mysql mysql       56 8月  25 02:34 auto.cnf
-rw-r-----. 1 mysql mysql    50126 8月  26 04:52 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 8月  26 04:30 mysql
-rw-r-----. 1 mysql mysql      788 8月  26 05:06 mysql_bin.000005
·····
[root@localhost ~]# mysqladmin -uroot -plq123456! flush-logs   #刷新二进制日志
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
总用量 123016
-rw-r-----. 1 mysql mysql       56 8月  25 02:34 auto.cnf
drwxr-x---. 2 mysql mysql     4096 8月  26 04:30 mysql
-rw-r-----. 1 mysql mysql      835 8月  26 05:08 mysql_bin.000005
-rw-r-----. 1 mysql mysql      154 8月  26 05:08 mysql_bin.000006

[root@localhost ~]# mysql -uroot -plq123456! < cyall-20210826.sql  #恢复完全备份
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -plq123456!  #查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liuqiang           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use liuqiang;
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;     #发现lq这个表没有恢复，是因为该表是在备份后创建，此时就会用到差异备份
+--------------------+
| Tables_in_liuqiang |
+--------------------+
| student            |
+--------------------+
1 row in set (0.00 sec)

恢复差异备份

[root@localhost ~]# mysql -uroot -plq123456!  #登录数据库
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 10
Server version: 5.7.34-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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 binlog events in 'mysql_bin.000005';  #检查误删数据库的位置在什么地方
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                     |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------+
| mysql_bin.000005 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.34-log, Binlog ver: 4                    |
| mysql_bin.000005 | 123 | Previous_gtids |         1 |         154 |                                                          |
| mysql_bin.000005 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                     |
| mysql_bin.000005 | 219 | Query          |         1 |         341 | use `liuqiang`; create table lq(name varchar(45),id int) |
| mysql_bin.000005 | 341 | Anonymous_Gtid |         1 |         406 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                     |
| mysql_bin.000005 | 406 | Query          |         1 |         482 | BEGIN                                                    |
| mysql_bin.000005 | 482 | Table_map      |         1 |         534 | table_id: 174 (liuqiang.lq)                              |
| mysql_bin.000005 | 534 | Write_rows     |         1 |         588 | table_id: 174 flags: STMT_END_F                          |
| mysql_bin.000005 | 588 | Xid            |         1 |         619 | COMMIT /* xid=931 */                                     |
| mysql_bin.000005 | 619 | Anonymous_Gtid |         1 |         684 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                     |
| mysql_bin.000005 | 684 | Query          |         1 |         788 | drop database liuqiang                                   |
| mysql_bin.000005 | 788 | Rotate         |         1 |         835 | mysql_bin.000006;pos=4                                   |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------+
12 rows in set (0.00 sec)

[root@localhost ~]# mysqlbinlog --stop-position=648 /opt/data/mysql_bin.000005 | mysql -uroot -plq123456!   #使用mysqlbinlog恢复差异备份
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -plq123456! 
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 12
Server version: 5.7.34-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> use liuqiang;
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;  #查看lq表已经恢复成功
+--------------------+
| Tables_in_liuqiang |
+--------------------+
| lq                 |
| student            |
+--------------------+
2 rows in set (0.00 sec)

group by的使用

Group by根据一个或多个列对结果集进行分组，例如聚合函数 (比如 SUM) 常常需要添加 Group by语句用于分组。

语法：SELECT “栏位1”, SUM(“栏位2”) FROM “表格名” GROUP BY “栏位1”

group by 有一个原则,就是select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面

说白了就是select 所取出的字段在分组里必须有

但是排除被合计的字段

比如 selecta,b,c from table group by a,b,c

这里 group by 里必须有a,b,c

如果改成 selecta,b,sum© from table groupby a,b
这里因为c被合计了所以按照a,b分组就可以了

同样的合计函数还包括maxcount 等等！

group by

在select 语句中可以使用group by 子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。group by 子句可以将查询结果分组，并返回行的汇总信息Oracle 按照group by 子句中指定的表达式的值分组查询结果。

在带有group by 子句的查询语句中，在select 列表中指定的列要么是group by 子句中指定的列，要么包含聚组函数

select max(sal),job emp group by job;
(注意max(sal),job的job并非一定要出现，但有意义)

查询语句的select 和group by ,having 子句是聚组函数唯一出现的地方，在where 子句中不能使用聚组函数。

select deptno,sum(sal) from emp where sal>1200 group by deptno having sum(sal)>8500 order by deptno;

当在gropu by 子句中使用having 子句时，查询结果中只返回满足having条件的组。在一个sql语句中可以有where子句和having子句。having 与where 子句类似，均用于设置限定条件

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   45 |
|  3 | ss    |   78 |
|  4 | dwdw  |   45 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> select avg(age) from student;   #输出age的平均值
+----------+
| avg(age) |
+----------+
|  56.0000 |
+----------+
1 row in set (0.00 sec)

mysql> select max(age) from student;    #输出age的最大值
+----------+
| max(age) |
+----------+
|       78 |
+----------+
1 row in set (0.01 sec)

自然连接INNER JOIN

等值连接中去掉重复的列，形成的连接。

说真的，这种连接查询没有存在的价值，既然是SQL2标准中定义的，就给出个例子看看吧。自然连接无需指定连接列，SQL会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列。不允许使用ON语句，不允许指定显示列，显示列只能用*表示（ORACLE环境下测试的）。对于每种连接类型（除了交叉连接外），均可指定NATURAL。

语句1：
SELECT *
FROM ORDERS O NATURAL INNER JOIN CUSTOMERS C;

语句2：
SELECT *
FROM ORDERS O NATURAL LEFT OUTER JOIN CUSTOMERS C;

语句3：
SELECT *
FROM ORDERS O NATURAL RIGHT OUTER JOIN CUSTOMERS C;

语句4：
SELECT *
FROM ORDERS O NATURAL FULL OUTER JOIN CUSTOMERS C;

联合连接（UNION JOIN）

这是一种很少见的连接方式。Oracle、MySQL均不支持，其作用是：找出全外连接和内连接之间差异的所有行。这在数据分析中排错中比较常用。也可以利用数据库的集合操作来实现此功能。
语句1：联合查询（UNION JOIN）例句，还没有找到能执行的SQL环境。
SELECT1 O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O UNION JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID

语句2：语句11在DB2下的等价实现。还不知道DB2是否支持语句11呢！
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
EXCEPT
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O INNER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

语句3：语句1在Oracle下的等价实现。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
MINUS
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O INNER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

外连接

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

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

1）LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

mysql> select * from lq;
+------+------+
| name | id   |
+------+------+
| ded  |   13 |
| dwqd |   78 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   45 |
|  3 | ss    |   78 |
|  4 | dwdw  |   45 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> select student.*,lq.* from student inner join lq on student.age=lq.id; #内连接
+----+------+------+------+------+
| id | name | age  | name | id   |
+----+------+------+------+------+
|  3 | ss   |   78 | dwqd |   78 |
+----+------+------+------+------+
1 row in set (0.00 sec)
mysql> select student.*,lq.* from student left join lq on student.age=lq.id;  # 左连接
+----+-------+------+------+------+
| id | name  | age  | name | id   |
+----+-------+------+------+------+
|  3 | ss    |   78 | dwqd |   78 |
|  2 | jerry |   45 | NULL | NULL |
|  4 | dwdw  |   45 | NULL | NULL |
+----+-------+------+------+------+
3 rows in set (0.00 sec）

mysql> select student.*,lq.* from student right join lq on student.age=lq.id;  #右连接
+------+------+------+------+------+
| id   | name | age  | name | id   |
+------+------+------+------+------+
|    3 | ss   |   78 | dwqd |   78 |
| NULL | NULL | NULL | ded  |   13 |
+------+------+------+------+------+
2 rows in set (0.00 sec)

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。