MYSQL备份与多表查询
mysql 备份与恢复
数据库备份分类
数据库备份方式分很多种，从物理与逻辑的角度来看，备份可分为：
- 物理备份：指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可以分为脱机备份(冷备份)和联机备份(热备份)。
- 冷备份:在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性。
- 热备份:在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件。
- 逻辑备份：指对数据库逻辑组件(如"表"等数据库对象)的备份。
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 每次对数据进行完整的备份。 可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。 但它需要花费更多的时间和空间，所以，做一次完全备份的周期要长些。 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后， 以后每次的备份只需备份与前一次相比增加和者被修改的文件。 这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件； 第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
mysql备份工具mysqldump
//语法：
mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
//常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3306
备份所有数据库(全备)：
[root@localhost ~]# mysqldump -uroot -predhat123 --all-databases > all-$(date '+%Y%m%d').sql
[root@localhost ~]# ls
all-20210825.sql anaconda-ks.cfg
恢复完全备份：
[root@localhost ~]# mysql -uroot -predhat123 < all-20210825.sql
备份指定数据库：
[root@localhost ~]# mysqldump -uroot -predhat123 --databases hhr > hhr-$(date '+%Y+%m%d').sql
[root@localhost ~]# ls
all-20210825.sql anaconda-ks.cfg hhr-20210825.sql
恢复指定库：
[root@localhost ~]# mysql -uroot -predhat123 < hhr-xxx.sql
备份指定数据库中的某一个表：
[root@localhost ~]# mysqldump -uroot -predhat123 hhr student > student-$(date '+%Y+%m%d').sql
[root@localhost ~]# ls
all-20210825.sql hhr-20210825.sql
anaconda-ks.cfg student-20210825.sql
方法1：恢复表(指定库名)
[root@localhost ~]# mysql -uroot -predhat123 hhr < student-xxx.sql
方法2:在指定的库里恢复表
MariaDB [hhr]> source student-xxx.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)
差异备份与恢复
开启MySQL服务器的二进制日志功能
//在数据库中查看二进制日志是否开启
MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.001 sec)
//在配置文件中开启二进制日志
[root@hhr ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=1 #设置服务器标识符
log-bin=mysql_bin #开启二进制日志功能
//重启服务
[root@localhost ~]# systemctl restart mariadb.service
[root@hhr ~]# ls /var/lib/mysql/
aria_log.00000001 ibdata1 multi-master.info mysql_bin.000005 mysql_upgrade_info
aria_log_control ib_logfile0 mysql mysql_bin.000006 performance_schema
hhr ib_logfile1 mysql_bin.000003 mysql_bin.index
ib_buffer_pool ibtmp1 mysql_bin.000004 mysql.sock
//登录数据库查看二进制日志已经开启
MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.001 sec)
//在数据库中查看有哪些二进制日志文件
MariaDB [(none)]> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000003 | 1446 |
| mysql_bin.000004 | 907269 |
| mysql_bin.000005 | 365 |
| mysql_bin.000006 | 342 |
+------------------+-----------+
4 rows in set (0.000 sec)
对数据库进行完全备份
//查看数据库数据
MariaDB [hhr]> show databases;
+--------------------+
| Database |
+--------------------+
| hhr |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [hhr]> show tables;
+---------------+
| Tables_in_hhr |
+---------------+
| jj |
| student |
+---------------+
2 rows in set (0.000 sec)
MariaDB [hhr]> select * from jj;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
3 rows in set (0.000 sec)
MariaDB [hhr]> 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.000 sec)
//存放密码
[root@hhr ~]# cat .my.cnf
[client]
user=root
password=redhat123
//完全备份
[root@hhr ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20210826.sql
[root@hhr ~]# ls
all-20210825.sql all-20210826.sql student-20210825.sql
//添加新内容
MariaDB [(none)]> select * from hhr.jj;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
3 rows in set (0.001 sec)
MariaDB [hhr]> insert jj values (4,'aixinjueluo');
Query OK, 1 row affected (0.001 sec)
MariaDB [hhr]> select * from jj;
+------+-------------+
| id | name |
+------+-------------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
| 4 | aixinjueluo |
+------+-------------+
4 rows in set (0.000 sec)
//修改内容
MariaDB [hhr]> update jj set id = 10 where name = 'zhangsan';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hhr]> select * from jj;
+------+-------------+
| id | name |
+------+-------------+
| 10 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
| 4 | aixinjueluo |
+------+-------------+
4 rows in set (0.000 sec)
mysql差异备份恢复
模拟误删数据
MariaDB [hhr]> drop database hhr;
Query OK, 2 rows affected (0.005 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
刷新创建新的二进制日志
[root@hhr ~]# mysql -e "show binary logs;"
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000007 | 867 |
+------------------+-----------+
1 row in set (0.000 sec)
[root@hhr ~]# ls /var/lib/mysql/
aria_log.00000001 ibdata1 ibtmp1 mysql_bin.000007 mysql_upgrade_info
aria_log_control ib_logfile0 multi-master.info mysql_bin.index performance_schema
ib_buffer_pool ib_logfile1 mysql mysql.sock
//刷新创建新的二进制日志
[root@hhr ~]# mysqladmin flush-logs
[root@hhr ~]# ls /var/lib/mysql/
aria_log.00000001 ibdata1 ibtmp1 mysql_bin.000007 mysql.sock
aria_log_control ib_logfile0 multi-master.info mysql_bin.000008 mysql_upgrade_info
ib_buffer_pool ib_logfile1 mysql mysql_bin.index performance_schema
[root@hhr ~]# mysql -e "show binary logs;"
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000007 | 914 |
| mysql_bin.000008 | 484032 |
+------------------+-----------+
恢复完全备份
[root@hhr ~]# mysql < all-20210826.sql
[root@hhr ~]# mysql -e "show databases;"
+--------------------+
| Database |
+--------------------+
| hhr |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
[root@hhr ~]# mysql -e "show tables from hhr;"
+---------------+
| Tables_in_hhr |
+---------------+
| jj |
| student |
+---------------+
[root@hhr ~]# mysql -e "select * from hhr.jj;"
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
恢复差异备份
//在数据库中查看有哪些二进制日志文件
[root@hhr ~]# mysql
MariaDB [hhr]> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000007 | 914 |
| mysql_bin.000008 | 483675 |
+------------------+-----------+
2 rows in set (0.000 sec)
//在数据库中查看正在写入的是哪个二进制日志文件
MariaDB [hhr]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000008 | 483675 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
//检查误删数据库的位置在什么地方
MariaDB [hhr]> show binlog events in 'mysql_bin.000007';
+------------------+-----+-------------------+-----------+-------------+----------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+----------------------------------------------------------+
| mysql_bin.000007 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.28-MariaDB-log, Binlog ver: 4 |
| mysql_bin.000007 | 256 | Gtid_list | 1 | 299 | [0-1-253] |
| mysql_bin.000007 | 299 | Binlog_checkpoint | 1 | 342 | mysql_bin.000006 |
| mysql_bin.000007 | 342 | Binlog_checkpoint | 1 | 385 | mysql_bin.000007 |
| mysql_bin.000007 | 385 | Gtid | 1 | 427 | BEGIN GTID 0-1-254 |
| mysql_bin.000007 | 427 | Query | 1 | 527 | use `hhr`; insert jj values (4,'aixinjueluo') |
| mysql_bin.000007 | 527 | Xid | 1 | 558 | COMMIT /* xid=461 */ |
| mysql_bin.000007 | 558 | Gtid | 1 | 600 | BEGIN GTID 0-1-255 |
| mysql_bin.000007 | 600 | Query | 1 | 711 | use `hhr`; update jj set id = 10 where name = 'zhangsan' |
| mysql_bin.000007 | 711 | Xid | 1 | 742 | COMMIT /* xid=463 */ |
| mysql_bin.000007 | 742 | Gtid | 1 | 784 | GTID 0-1-256 |
| mysql_bin.000007 | 784 | Query | 1 | 867 | drop database hhr |
| mysql_bin.000007 | 867 | Rotate | 1 | 914 | mysql_bin.000008;pos=4 |
+------------------+-----+-------------------+-----------+-------------+----------------------------------------------------------+
13 rows in set (0.000 sec)
//使用mysqlbinlog恢复差异备份
这里的784是你开始删除库之前的修改过数据的位置
[root@hhr ~]# mysqlbinlog --stop-position=784 /var/lib/mysql/mysql_bin.000007 | mysql
[root@hhr ~]# mysql -e "select * from hhr.jj;"
+------+-------------+
| id | name |
+------+-------------+
| 10 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
| 4 | aixinjueluo |
+------+-------------+
多表查询
Group By的使用
概述
“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。
Group By与聚合函数
| 函数 | 作用 |
|---|---|
| sum(列名) | 求和 |
| max(列名) | 最大值 |
| min(列名) | 最小值 |
| avg(列名) | 平均值 |
| count(列名) | 统计记录数 |
//创建表
MariaDB [hhr]> create table test (id varchar(20),name varchar(20), company varchar(20));
Query OK, 0 rows affected (0.007 sec)
//查看表
MariaDB [hhr]> show tables;
+---------------+
| Tables_in_hhr |
+---------------+
| student |
| test |
+---------------+
2 rows in set (0.000 sec)
//查看表结构
MariaDB [hhr]> desc test;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | varchar(20) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| company | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)
//插入数据
MariaDB [hhr]> insert test values(1,'a','jia'),(1,'a','jia'),(1,'a','jia'),(1,'a','jia'),(1,'a','yi'),(1,'b','yi'),(1,'b','yi'),(1,'b','yi');
Query OK, 8 rows affected (0.001 sec)
Records: 8 Duplicates: 0 Warnings: 0
//查看数据
MariaDB [hhr]> select * from test;
+------+------+---------+
| id | name | company |
+------+------+---------+
| 1 | a | jia |
| 1 | a | jia |
| 1 | a | jia |
| 1 | a | jia |
| 1 | a | yi |
| 1 | b | yi |
| 1 | b | yi |
| 1 | b | yi |
+------+------+---------+
8 rows in set (0.000 sec)
MariaDB [hhr]> 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.000 sec)
Group By应用
//统计name这一列中a和b出现的次数
MariaDB [hhr]> select count(1),name from test group by name;
+----------+------+
| count(1) | name |
+----------+------+
| 5 | a |
| 3 | b |
+----------+------+
2 rows in set (0.000 sec)
//统计company这一列中jia和yi出现的次数
MariaDB [hhr]> select count(1),company from test group by company;
+----------+---------+
| count(1) | company |
+----------+---------+
| 4 | jia |
| 4 | yi |
+----------+---------+
2 rows in set (0.000 sec)
//统计test这个表中compary这一列jia和yi出现的次数
MariaDB [hhr]> select company,count(8484) from test group by company; //count后面括号里写什么都可以，可以是数字，*号，但是1的效率比*号要高，所以通常用1
+---------+-------------+
| company | count(8484) |
+---------+-------------+
| jia | 4 |
| yi | 4 |
+---------+-------------+
2 rows in set (0.000 sec)
//求test中age这一列的平均值
MariaDB [hhr]> select sum(id) from test;
+---------+
| sum(id) |
+---------+
| 8 |
+---------+
1 row in set (0.001 sec)
//求student中age这一列的平均值
MariaDB [hhr]> select avg(age) from student;
+----------+
| avg(age) |
+----------+
| 31.7000 |
+----------+
1 row in set (0.000 sec)
连接
//创建两张表
MariaDB [hhr]> create table jj(id int,name varchar(30));
Query OK, 0 rows affected (0.004 sec)
MariaDB [hhr]> create table yy(id int,job int,parent_id int);
Query OK, 0 rows affected (0.003 sec)
MariaDB [hhr]> show tables;
+---------------+
| Tables_in_hhr |
+---------------+
| jj |
| student |
| test |
| yy |
+---------------+
4 rows in set (0.000 sec)
MariaDB [hhr]> insert jj values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
Query OK, 3 rows affected (0.001 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [hhr]> insert yy values(1,23,1),(2,344,2),(3,34,4);
Query OK, 3 rows affected (0.001 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [hhr]> select * from jj;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
3 rows in set (0.000 sec)
MariaDB [hhr]> select * from yy;
+------+------+-----------+
| id | job | parent_id |
+------+------+-----------+
| 1 | 23 | 1 |
| 2 | 34 | 2 |
| 3 | 34 | 4 |
+------+------+-----------+
3 rows in set (0.000 sec)
// inner join（内连接）查询两张表相同的数据
MariaDB [hhr]> select jj.*,yy.* from jj inner join yy on jj.id = yy.parent_id;
+------+----------+------+------+-----------+
| id | name | id | job | parent_id |
+------+----------+------+------+-----------+
| 1 | zhangsan | 1 | 23 | 1 |
| 2 | lisi | 2 | 34 | 2 |
+------+----------+------+------+-----------+
2 rows in set (0.001 sec)
//left join（左连接）以左边为标准：在left join左边的就是左，在left join右边的就是右如果右边跟左边不匹配的值则显示空null
MariaDB [hhr]> select jj.*,yy.* from jj left join yy on jj.id = yy.parent_id;
+------+----------+------+------+-----------+
| id | name | id | job | parent_id |
+------+----------+------+------+-----------+
| 1 | zhangsan | 1 | 23 | 1 |
| 2 | lisi | 2 | 34 | 2 |
| 3 | wangwu | NULL | NULL | NULL |
+------+----------+------+------+-----------+
3 rows in set (0.000 sec)
//right join（右连接）以右边为标准：在right join左边的就是左，在right join右边的就是右如果左边跟右边不匹配的值则显示空null
MariaDB [hhr]> select jj.*,yy.* from jj right join yy on jj.id = yy.parent_id;
+------+----------+------+------+-----------+
| id | name | id | job | parent_id |
+------+----------+------+------+-----------+
| 1 | zhangsan | 1 | 23 | 1 |
| 2 | lisi | 2 | 34 | 2 |
| NULL | NULL | 3 | 34 | 4 |
+------+----------+------+------+-----------+
3 rows in set (0.000 sec)
扫一扫
2826
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
