投稿
  1. 极客之音首页
  2. 操作系统

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

小半 • • 操作系统 • 阅读 215

导读：本篇文章讲解 MySQL数据备份与恢复以及多表查询，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

文章目录

一：数据库备份方案：

  • 全量备份
  • 增量备份
  • 差异备份
  • 冷备：备份的时候需要停掉 – 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)

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/5641.html

赞 (0)
小半的头像小半
0 0

相关推荐

极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！