MySQL数据备份与恢复以及多表查询

最新推荐文章于 2023-04-07 18:08:13 发布

阿木690

最新推荐文章于 2023-04-07 18:08:13 发布

阅读量164

点赞数 1

分类专栏： mysql数据备份多表查询文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Gf19991225/article/details/119941654

版权

mysql数据备份同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

多表查询

1 篇文章 0 订阅

订阅专栏

文章目录

一：数据库备份方案：
二：Mysqldump简介
三：MySQLdump的应用
四：多表查询

一：数据库备份方案：

全量备份
增量备份
差异备份
冷备：备份的时候需要停掉 - xtrabackup //备份工具
热备：备份的时候不需要停掉 - mysqldump //备份工具

二：Mysqldump简介

mysqldump是逻辑备份工具，支持MyISAM和InnoDB引擎。数据库运行时，MyISAM引擎只支持温备，InnoDB支持热备和温备。

三：MySQLdump的应用

3.1全量备份：

全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
数据恢复快。
备份时间长

3.2 全量备份与恢复

备份准备工作：
现有两个数据库库gf、gg，gf数据库有一个student表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gf                 |
| gg                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)


mysql> use gf; 
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 [gf]> show tables;
+--------------+
| Tables_in_gf |
+--------------+
| student      |
+--------------+

MariaDB [gf]> select *from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+

进行全量备份（备份当前所有库）

[root@centos2 ~]# mysqldump -uroot -pgf123! --all-databases > all_$(date +'%Y%m%d').sql
[root@centos2 ~]# ls
all_20210826.sql  anaconda-ks.cfg  //生成一个全备文件

[root@centos2 ~]cat all_20210826.sql 
......
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'tom',20),(2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(7,'lisi',50),(8,'chenshuo',10),(9,'wangwu',100),(10,'qiuyi',15),(11,'qiuxiaotian',20);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Current Database: `gg`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `gg` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `gg`;

.........

删数据库进行恢复测试

MariaDB [(none)]> drop database gf;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> drop database gg;
Query OK, 0 rows affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

通过all_20210826.sql文件恢复

[root@centos2 ~]# mysql -uroot -pgf123! < all_20210826.sql        
[root@centos2 ~]# mysql -uroot -pgf123!
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 3
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 |
| gf                 |
| gg                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> use gf;
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 [gf]> select *from student;  //查询gf数据库student表内容已恢复
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.00 sec)

备份gf数据库中的student表

[root@centos2 ~]# mysqldump gf student > student-$(date +'%Y%m%d').sql
[root@centos2 ~]# ls
all_20210826.sql  anaconda-ks.cfg  student-20210826.sql

删除gf数据库的student表进行恢复测试

mysql> use gf; 
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 [gf]> drop table student;
Query OK, 0 rows affected (0.00 sec)

MariaDB [gf]> show tables;
Empty set (0.00 sec)

开始恢复
恢复表格需要指定恢复的数据库位置

[root@centos2 ~]# mysql -uroot -pgf123! gf < student-20210826.sql 
[root@centos2 ~]# ls
all_20210826.sql  anaconda-ks.cfg  student-20210826.sql
[root@centos2 ~]# mysql -uroot -pgf123!
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 3
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.


MariaDB [(none)]> use gf;
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 [gf]> show tables;
+--------------+
| Tables_in_gf |
+--------------+
| student      |
+--------------+
1 row in set (0.00 sec)

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

3.3 差异备份

差异备份：所有的写操作（增、删、改）都会被记录在日志中

在进行差量备份之前，修改配置文件：

[root@centos2 ~]# vim /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@centos2 ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.. SUCCESS!

对数据库进行全量备份

[root@centos2 ~]# mysqldump -uroot -p'gf123!' /etc/my.cnf /opt/data/ --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20210826.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@centos2 ~]# ll
total 1728
-rw-r--r--. 1 root root 876062 Aug 26 21:00 all-20210826.sql
-rw-------. 1 root root   1092 Aug  8 20:41 anaconda-ks.cfg

在表里更新内容

mysql> use gg;
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> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | xixi |   20 |
|  2 | haha |   22 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> insert student values (3,'tom',15),(4,'jerry',10);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | xixi  |   20 |
|  2 | haha  |   22 |
|  3 | tom   |   15 |
|  4 | jerry |   10 |
+----+-------+------+
4 rows in set (0.00 sec)

修改表内内容

mysql> update student set age = 25 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | xixi  |   20 |
|  2 | haha  |   22 |
|  3 | tom   |   15 |
|  4 | jerry |   25 |
+----+-------+------+
4 rows in set (0.00 sec)

模拟误删数据

mysql> drop database gg;
Query OK, 1 rows affected (0.01 sec)

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| gf                 |
+--------------------+
5 rows in set (0.00 sec)

刷新创建新的二进制日志

[root@centos2 ~]# ll /opt/data
// mysql_bin.000002找到这个文件

[root@centos2 ~]# mysqladmin -uroot -p'gf123!' flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

[root@centos2 ~]# ll /opt/data/
// 多出一个mysql_bin.000003

恢复全量备份

mysql> select * from gg.student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  2 | jerry |   23 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000003 |  3175191 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 219 | Query          |         1 |         290 | BEGIN                                 |
| mysql_bin.000002 | 290 | Table_map      |         1 |         339 | table_id: 180 (wbk.wbk)               |
| mysql_bin.000002 | 339 | Write_rows     |         1 |         396 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000002 | 396 | Xid            |         1 |         427 | COMMIT /* xid=3646 */                 |
| mysql_bin.000002 | 427 | Anonymous_Gtid |         1 |         492 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 492 | Query          |         1 |         563 | BEGIN                                 |
| mysql_bin.000002 | 563 | Table_map      |         1 |         612 | table_id: 180 (wbk.wbk)               |
| mysql_bin.000002 | 612 | Update_rows    |         1 |         672 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000002 | 672 | Xid            |         1 |         703 | COMMIT /* xid=3776 */                 |
| mysql_bin.000002 | 703 | Anonymous_Gtid |         1 |         768 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 768 | Query          |         1 |         857 | drop database wbk                     |
| mysql_bin.000002 | 857 | Rotate         |         1 |         904 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.01 sec)
[root@centos2 ~]# mysqlbinlog --stop-position=768 /opt/data/mysql_bin.000002 | mysql -uroot -p'gf123!'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@centos2 ~]# mysql -uroot -p'gf123!'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
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> select * from gg.student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  2 | jerry |   23 |
|  3 | tom   |   15 |
|  4 | jerry |   25 |
+----+-------+------+
4 rows in set (0.00 sec)

四：多表查询

/提前准备student01、student02 两张表

//查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gf                 |
| gg                 |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
7 rows in set (0.00 sec)


mysql> use gf;   //进入gf数据库
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;
+--------------+
| Tables_in_gf |
+--------------+
| student      |    
+--------------+
1 rows in set (0.00 sec)

//创建表的准备工作
mysql> create table student01(id int not null,name varchar(10) n
ot null,age tinyint);
Query OK, 0 rows affected (0.02 sec)

mysql> create table student02(id int not null,name varchar(10) n
ot null,age tinyint);
Query OK, 0 rows affected (0.02 sec)

mysql> desc student01;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc student02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show tables;
+--------------+
| Tables_in_gf |
+--------------+
| student      |
| student01    |
| student02    |
+--------------+
3 rows in set (0.00 sec)

mysql> insert student01 values(1,'a',15),(2,'b',25);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

//student01表准备完毕
mysql> select * from student01;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   15 |
|  2 | b    |   25 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> insert student02 values(3,'c',30),(4,'d',50);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

//student02表准备完毕
mysql> select * from student02;
+----+------+------+
| id | name | age  |
+----+------+------+
|  3 | c    |   30 |
|  4 | d    |   50 |
+----+------+------+
2 rows in set (0.00 sec)

4.1 groupby

group_by根据by对数据进行分组排列等，一般于count，sum，avg使用
语法：select 字段 from 表名 where 条件 group by 字段
     select 字段 from 表名 group by 字段 having 过滤条件
有过滤的话就先用where。

4.2 单表操作

count()：计数

mysql> select name,count(*) from student01 group by name;
+------+----------+
| name | count(*) |
+------+----------+
| a    |        1 |
| b    |        1 |
+------+----------+
2 rows in set (0.01 sec)

sum()：求和

mysql> select sum(age) from student02;
+----------+
| sum(age) |
+----------+
|       80 |
+----------+
1 row in set (0.00 sec)

avg()：求平均数

mysql> select avg(age) from student02;
+----------+
| avg(age) |
+----------+
|  40.0000 |
+----------+
1 row in set (0.00 sec)

max()查看最大值

mysql> select max(age) from student01;
+----------+
| max(age) |
+----------+
|       25 |
+----------+
1 row in set (0.00 sec)

min()查看最小值

mysql> select min(age) from student01;
+----------+
| min(age) |
+----------+
|       15 |
+----------+
1 row in set (0.00 sec)

4.2 多表查询

// 使用select把student01和student02一起查询，变成一个表显示出来

mysql> select * from student01,student02;
+----+------+------+----+------+------+
| id | name | age  | id | name | age  |
+----+------+------+----+------+------+
|  1 | a    |   15 |  3 | c    |   30 |
|  2 | b    |   25 |  3 | c    |   30 |
|  1 | a    |   15 |  4 | d    |   50 |
|  2 | b    |   25 |  4 | d    |   50 |
+----+------+------+----+------+------+
4 rows in set (0.00 sec)

内连接-inner join
inner join 来连接俩张表 on子句来设置连接俩个表的条件

mysql> select student01.*,student01.* from  student01 join stude
nt02;
+----+------+------+----+------+------+
| id | name | age  | id | name | age  |
+----+------+------+----+------+------+
|  1 | a    |   15 |  1 | a    |   15 |
|  2 | b    |   25 |  2 | b    |   25 |
|  1 | a    |   15 |  1 | a    |   15 |
|  2 | b    |   25 |  2 | b    |   25 |
+----+------+------+----+------+------+
4 rows in set (0.00 sec)

外连接(left join right join)
外连接分为左连接和右连接

左连接
left join：匹配左表的所有行，即使右表没有内容则返回null

mysql> select student01.*,student02.* from  student01 left join
student02 on student01.name=student02.name;
+----+------+------+------+------+------+
| id | name | age  | id   | name | age  |
+----+------+------+------+------+------+
|  1 | a    |   15 | NULL | NULL | NULL |
|  2 | b    |   25 | NULL | NULL | NULL |
+----+------+------+------+------+------+
2 rows in set (0.00 sec)

右连接
right join ：匹配右表的所有行，即使左表没有内容则返回null

mysql> select student01.*,student02.* from  student01 right join
 student02 on student01.name=student02.name;
+------+------+------+----+------+------+
| id   | name | age  | id | name | age  |
+------+------+------+----+------+------+
| NULL | NULL | NULL |  3 | c    |   30 |
| NULL | NULL | NULL |  4 | d    |   50 |
+------+------+------+----+------+------+
2 rows in set (0.00 sec)

交叉连接-cross join

mysql> select student01.*,student02.* from student01 cross join
student02;
+----+------+------+----+------+------+
| id | name | age  | id | name | age  |
+----+------+------+----+------+------+
|  1 | a    |   15 |  3 | c    |   30 |
|  2 | b    |   25 |  3 | c    |   30 |
|  1 | a    |   15 |  4 | d    |   50 |
|  2 | b    |   25 |  4 | d    |   50 |
+----+------+------+----+------+------+
4 rows in set (0.00 sec)

阿木690

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据备份与恢复以及多表查询

文章目录一：数据库备份方案：二：Mysqldump简介三：MySQLdump的应用3.1全量备份：3.2 全量备份与恢复3.3 差异备份四：多表查询4.1 groupby4.2 单表操作4.2 多表查询一：数据库备份方案：全量备份增量备份差异备份冷备：备份的时候需要停掉 - xtrabackup //备份工具热备：备份的时候不需要停掉 - mysqldump //备份工具二：Mysqldump简介mysqldump是逻辑备份工具，支持MyISAM和InnoDB引擎。数据库运行
复制链接

扫一扫

专栏目录