MySQL 全、差备份及恢复
group by、inner join、left join、right join的应用
文章目录
- 一、Mysqldump简介
- 二、MySQLdump的应用
- 1.全量备份与恢复
- 2.差量备份
- 三、 left join、right join、inner join、group by的应用
- 1.left join
- 2.right join
- 3.inner join
- 4.group by
- 1.1计数 count 函数
- 1.2 sum求和函数
- 实例
一、Mysqldump简介
mysqldump是逻辑备份工具，支持MyISAM和InnoDB引擎。数据库运行时，MyISAM引擎只支持温备，InnoDB支持热备和温备。
二、MySQLdump的应用
全量备份
全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
1.全量备份与恢复
备份：
现有文件 两个库（xu、xx），一个表（student）
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xu |
| xx |
+--------------------+
MariaDB [xu]> show tables;
+--------------+
| Tables_in_xu |
+--------------+
| student |
+--------------+
MariaDB [xu]> 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@localhost ~]# mysqldump --all-databases > all_$(date +'%y%m%d').sql
[root@localhost ~]# ls
all_210826.sql anaconda-ks.cfg //生成一个txt文件
[root@localhost ~]cat all_210826.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: `xx`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `xx` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `xx`;
.........模仿误删数据库
MariaDB [(none)]> drop database xu;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> drop database xx;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)通过all_210826.sql文件恢复
[root@localhost ~]# mysql < all_210826.sql
//这里省略了部分命令完整的命令是(mysql - uroot -p 你mysql的密码 < all_210826.sql )
[root@localhost ~]# mysql 登录Mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.68-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases; //数据库恢复了
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xu |
| xx |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use xu;
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 [xu]> select *from student; 查询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)备份数据库中的某一个表
[root@localhost ~]# mysqldump xu student > student-$(date +'%y%m%d').sql
[root@localhost ~]# ls
all_210826.sql anaconda-ks.cfg student-210826.sql
[root@localhost ~]#模仿误删一个表
MariaDB [xu]> drop table student;
Query OK, 0 rows affected (0.00 sec)
MariaDB [xu]> show tables;
Empty set (0.00 sec)恢复时 要指定恢复到哪个库中
[root@localhost ~]# mysql xu < student-210826.sql
[root@localhost ~]# ls
all_210826.sql anaconda-ks.cfg student-210826.sql
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.68-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use xu;
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 [xu]> show tables;
+--------------+
| Tables_in_xu |
+--------------+
| student |
+--------------+
1 row in set (0.00 sec)
MariaDB [xu]> 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)2.差量备份
差异备份是针对完全备份：备份上一次的完全备份后发生变化的所有文件。
在进行差量备份之前，先进行以下配置：
[root@hostlocal]# 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@hostlocal# systemctl restart mysqld对数据库进行全量备份
[root@hostlocal# mysqldump -uroot -p'xu1' /etc/my.cnf /opt/data/ --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@hostlocal# ll
总用量 8
-rw-r--r--. 1 root root 2021 8月 26 18:55 all.sql
-rw-------. 1 root root 2021 8月 26 16:56 anaconda-ks.cfg在表里更新内容
mysql> use xu;
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 xu;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | xixi | 20 |
| 2 | haha | 22 |
+----+------+------+
2 rows in set (0.00 sec)
mysql> insert xu values (3,'tom',15),(4,'jerry',10);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from xu;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | xixi | 20 |
| 2 | haha | 22 |
| 3 | tom | 15 |
| 4 | jerry | 10 |
+----+-------+------+
4 rows in set (0.00 sec)修改表内内容
mysql> update xu set age = 25 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from xu;
+----+-------+------+
| 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 xu;
Query OK, 2 rows affected (0.01 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zabbix |
+--------------------+
5 rows in set (0.00 sec)刷新创建新的二进制日志
[root@hostlocal]# ll /opt/data/
总用量 188504
-rw-r-----. 1 mysql mysql 56 8月 22 14:15 auto.cnf
-rw-r-----. 1 mysql mysql 4392 8月 22 19:28 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 8月 22 19:36 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月 22 19:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月 22 14:15 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月 22 19:33 ibtmp1
drwxr-x---. 2 mysql mysql 4096 8月 22 14:15 mysql
-rw-r-----. 1 mysql mysql 857 8月 22 19:36 mysql_bin.000002
-rw-r-----. 1 mysql mysql 19 8月 22 19:33 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 8月 22 19:28 mysql.pid
drwxr-x---. 2 mysql mysql 8192 8月 22 14:15 performance_schema
drwxr-x---. 2 mysql mysql 8192 8月 22 14:15 sys
-rw-r-----. 1 mysql mysql 16735 8月 22 19:28 xu.err
drwxr-x---. 2 mysql mysql 12288 8月 22 16:02 zabbix
[root@hostlocal]# mysqladmin -uroot -p'xu1!' flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@hostlocal# ll /opt/data/
总用量 188508
-rw-r-----. 1 mysql mysql 56 2月 22 14:15 auto.cnf
-rw-r-----. 1 mysql mysql 4392 2月 22 19:28 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 2月 22 19:36 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月 22 19:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月 22 14:15 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 2月 22 19:33 ibtmp1
drwxr-x---. 2 mysql mysql 4096 2月 22 14:15 mysql
-rw-r-----. 1 mysql mysql 904 2月 22 19:39 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 2月 22 19:39 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 2月 22 19:39 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 2月 22 19:28 mysql.pid
drwxr-x---. 2 mysql mysql 8192 2月 22 14:15 performance_schema
drwxr-x---. 2 mysql mysql 8192 2月 22 14:15 sys
-rw-r-----. 1 mysql mysql 16735 2月 22 19:28 xu.err
drwxr-x---. 2 mysql mysql 12288 2月 22 16:02 zabbix恢复全量备份
mysql> select * from xu.xu;
+----+-------+------+
| 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.23-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@hostlocal]# mysqlbinlog --stop-position=768 /opt/data/mysql_bin.000002 | mysql -uroot -p'xu1'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@hostlocal]# mysql -uroot -p'xu1'
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 16
Server version: 5.7.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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 xu.xu;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | tom | 15 |
| 4 | jerry | 25 |
+----+-------+------+
4 rows in set (0.00 sec)三、 left join、right join、inner join、group by的应用
- left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
- right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
- inner join(等值连接) 只返回两个表中联结字段相等的行
举例如下：
表A记录如下：
MariaDB [cha]> select *from A;
+-----+-----------+
| aID | aNum |
+-----+-----------+
| 1 | a20050111 |
| 2 | a20050112 |
| 3 | a20050113 |
| 4 | a20050114 |
| 5 | a20050115 |
+-----+-----------+MariaDB [cha]> select *from B;
+-----+----------+
| aID | aNum |
+-----+----------+
| 1 | 20050111 |
| 2 | 20050112 |
| 3 | 20050113 |
| 4 | 20050114 |
| 8 | 20050115 |
+-----+----------+
5 rows in set (0.00 sec)1.left join
sql语句如下:
select * from A
left join B
on A.aID = B.aID
结果如下:
MariaDB [cha]> select * from A left 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 |
| 5 | a20050115 | NULL | NULL |
+-----+-----------+------+----------+（所影响的行数为 5 行）
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.aID).
B表记录不足的地方均为NULL.
2.right join
sql语句如下:
select * from A
right join B
on A.aID = B.aID
结果如下:
MariaDB [cha]> select * from A right 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 |
| NULL | NULL | 8 | 20050115 |
+------+-----------+-----+----------+（所影响的行数为 5 行）
结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
3.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)结果说明:
很明显,这里只显示出了 A.aID = B.aID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
4.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 |
+----------+------+1.1计数 count 函数
sql语句如下：
select name,count
from test
group by name;
MariaDB [cha]> select name,count(1) from test group by name;
+----------+----------+
| name | count(1) |
+----------+----------+
| xiaofang | 1 |
| xiaohu | 2 |
| xiaoming | 2 |
| xiaowu | 1 |
+----------+----------+
4 rows in set (0.00 sec)结果说明:
来统计这个表名字相同的人数
1.2 sum求和函数
sql语句如下：
select
sum(age)
from test;
MariaDB [cha]> select sum(age) from test;
+----------+
| sum(age) |
+----------+
| 95 |
+----------+
1 row in set (0.00 sec)结果说明:
计算表中的age字段总和
实例
创建两个表 web和log
MariaDB [cha]> select *from web;
+----+---------------+
| id | name |
+----+---------------+
| 1 | google |
| 2 | taobao |
| 3 | cainiao |
| 4 | weibo |
| 5 | facebook |
| 6 | stackoverflow |
+----+---------------+
6 rows in set (0.00 sec)
MariaDB [cha]> select *from log;
+-----+---------+-------+
| aid | site_id | count |
+-----+---------+-------+
| 1 | 1 | 45 |
| 2 | 3 | 100 |
| 3 | 1 | 230 |
| 4 | 2 | 10 |
| 5 | 5 | 205 |
| 6 | 4 | 13 |
| 7 | 3 | 220 |
| 8 | 5 | 545 |
| 9 | 3 | 201 |
+-----+---------+-------+
9 rows in set (0.00 sec)GROUP BY 简单应用
统计 log 各个 site_id 的访问量：
sql语句如下：
select
site_id,
sum(log.count)
as
nums
from log
group by
site_id
MariaDB [cha]> select site_id,sum(log.count) as nums from log group by site_id;
+---------+------+
| site_id | nums |
+---------+------+
| 1 | 275 |
| 2 | 10 |
| 3 | 521 |
| 4 | 13 |
| 5 | 750 |
+---------+------+
5 rows in set (0.00 sec)SQL GROUP BY 多表连接
下面的 SQL 语句统计有记录的网站的记录数量：
sql语句如下：
select
web.name
,count(log.aid)
as
nums
from log
left join web on log.site_id = web.id group by web.name;
MariaDB [cha]> select web.name,count(log.aid) as nums from log left join web on log.site_id = web.id group by web.name;
+----------+------+
| name | nums |
+----------+------+
| cainiao | 3 |
| facebook | 2 |
| google | 2 |
| taobao | 1 |
| weibo | 1 |
+----------+------+
5 rows in set (0.00 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
