sqldump_0">1.Mysqldump简介
sql>mysqldump是逻辑备份工具，支持MyISAM和InnoDB引擎。数据库运行时，MyISAM引擎只支持温备，InnoDB支持热备和温备。
2.MySQLdump的应用
2.1全量备份与恢复
全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| sql>mysql |
| performance_schema |
| xiongke |
+--------------------+
4 rows in set (0.003 sec)
MariaDB [(none)]> use xiongke;
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 [xiongke]> show tables;
+-------------------+
| Tables_in_xiongke |
+-------------------+
| xk |
+-------------------+
1 row in set (0.000 sec)
MariaDB [xiongke]> select * from xk;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | xiongke | 25 |
| 3 | sean | 28 |
| 4 | zhangshan | 26 |
| 5 | zhangshan | 20 |
| 6 | lisi | 50 |
| 7 | chensuo | 10 |
| 8 | qiuyi | 15 |
+----+-----------+------+
8 rows in set (0.000 sec)
MariaDB [xiongke]>
进行全量备份
[root@master sql>mysqldump]# sql>mysqldump -uroot -p1 --all-databases > /root/sql>mysqldump/all-`date +%F`.sql
[root@master sql>mysqldump]# ls
all-2021-08-25.sql all-210826.sql site-2021-08-25.sql
all-2021-08-26.sql all_.sql
[root@master sql>mysqldump]# cat all-2021-08-26.sql
-- MySQL dump 10.19 Distrib 10.3.28-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 10.3.28-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
误删数据库
MariaDB [(none)]> drop database xiongke;
Query OK, 1 row affected (0.002 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| sql>mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]>
文件恢复
[root@master sql>mysqldump]# sql>mysql -uroot -p1 < /root/sql>mysqldump/all-2021-08-26.sql
[root@master sql>mysqldump]# sql>mysql -uroot -p1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.28-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 |
| sql>mysql |
| performance_schema |
| xiongke |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> select * from xiongke.xk;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | xiongke | 25 |
| 3 | sean | 28 |
| 4 | zhangshan | 26 |
| 5 | zhangshan | 20 |
| 6 | lisi | 50 |
| 7 | chensuo | 10 |
| 8 | qiuyi | 15 |
+----+-----------+------+
8 rows in set (0.000 sec)
MariaDB [(none)]>
2.2差异备份
[root@xk ~]# vim /etc/my.cnf
[root@xk ~]# service sql>mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@xk ~]# tail -15 /etc/my.cnf
[sql>mysqld]
basedir = /usr/local/sql>mysql
datadir = /opt/data
socket = /tmp/sql>mysql.sock
port = 3306
pid-file = /opt/data/sql>mysql.pid
user = sql>mysql
skip-name-resolve
server-id = 1 // 设置服务器标识
log-bin = sql>mysql_bin //开启二进制功能
数据库日志文件
[root@xk ~]# ll /opt/data/
-rw-r-----. 1 sql>mysql sql>mysql 154 2月 22 16:37 sql>mysql_bin.000001
-rw-r-----. 1 sql>mysql sql>mysql 19 2月 22 16:37 sql>mysql_bin.index
对数据库进行完全备份
[root@xk ~]# sql>mysqldump -uroot -p1 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201902221646
sql>mysqldump: [Warning] Using a password on the command line interface can be insecure.
在数据数据库中增加类容
sql>mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ming |
| sql>mysql |
| performance_schema |
| sys |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)
sql>mysql> drop database ming;
Query OK, 2 rows affected (0.00 sec)
sql>mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| sql>mysql |
| performance_schema |
| sys |
| zabbix |
+--------------------+
5 rows in set (0.00 sec)
sql>mysql>
模拟删库
sql>mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ming |
| sql>mysql |
| performance_schema |
| sys |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)
sql>mysql> drop database ming;
Query OK, 2 rows affected (0.00 sec)
sql>mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| sql>mysql |
| performance_schema |
| sys |
| zabbix |
+--------------------+
5 rows in set (0.00 sec)
sql>mysql>
恢复完全备份
[root@xk ~]# sql>mysql -uroot -p < all-20210826
Enter password:
[root@xk ~]# sql>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
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.
sql>mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ming |
| sql>mysql |
| performance_schema |
| sys |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)
sql>mysql>
// 查看表发现没有内容
sql>mysql> select * from student;
Empty set (0.00 sec)
sql>mysql>
查看binlog 日志,发现1180为删库前，所以恢复到1180
sql>mysql> show binlog events in 'sql>mysql_bin.000002'\G
*************************** 1. row ***************************
Log_name: sql>mysql_bin.000002
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 123
Info: Server ver: 5.7.23-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: sql>mysql_bin.000002
Pos: 123
Event_type: Previous_gtids
Server_id: 1
End_log_pos: 154
Info:
*************************** 18. row ***************************
Log_name: sql>mysql_bin.000002
Pos: 1115
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 1180
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 19. row ***************************
Log_name: sql>mysql_bin.000002
Pos: 1180
Event_type: Query
Server_id: 1
End_log_pos: 1272
Info: drop database ming
*************************** 20. row ***************************
//恢复到删库前
[root@xk ~]# sql>mysql -uroot -p < all-201902221646
Enter password:
[root@xk ~]#
[root@xk ~]#
[root@xk ~]#
[root@xk ~]# sql>mysqlbinlog --stop-position=1180 /opt/data/sql>mysql_bin.000002 |sql>mysql -uroot -pming123
sql>mysql: [Warning] Using a password on the command line interface can be insecure.
[root@xk ~]# sql>mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@xk ~]# sql>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
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.
sql>mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ming |
| sql>mysql |
| performance_schema |
| sys |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)
sql>mysql> use ming;
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
sql>mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | xiongke | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | ming | 22 |
| 13 | wbk | 25 |
+----+-------------+------+
13 rows in set (0.00 sec)
3.left join、right join、inner join、group by的应用
- left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
- right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
- inner join(等值连接) 只返回两个表中联结字段相等的行
MariaDB [xx]> select *from A;
±----±----------+
| aID | aNum |
±----±----------+
| 1 | a20050111 |
| 2 | a20050112 |
| 3 | a20050113 |
| 4 | a20050114 |
| 5 | a20050115 |
±----±----------+
MariaDB [xx]> select *from B;
±----±---------+
| aID | aNum |
±----±---------+
| 1 | 20050111 |
| 2 | 20050112 |
| 3 | 20050113 |
| 4 | 20050114 |
| 8 | 20050115 |
±----±---------+
5 rows in set (0.00 sec)
left join
sql语句如下:
select * from A
left join B
on A.aID = B.aID
结果如下:
MariaDB [xx]> 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.
right join
sql语句如下:
select * from A
right join B
on A.aID = B.aID
结果如下:
MariaDB [xx]> 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填充.
inner join
sql语句如下:
select * from A
innerjoin B
on A.aID = B.aID
结果如下:
MariaDB [xx]> 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并不以谁为基础,它只显示符合条件的记录.
group by
group by语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
group by语法:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
test 表如下：
MariaDB [xx]> 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 [xx]> 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 [xx]> select sum(age) from test;
+----------+
| sum(age) |
+----------+
| 95 |
+----------+
1 row in set (0.00 sec)
结果说明:
计算表中的age字段总和
实例
创建两个表 web和log
MariaDB [xx]> 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 [xx]> 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 [xx]> 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)
