MYSQL备份与多表查询

mysql 备份与恢复数据库备份分类mysql备份工具mysqldump差异备份与恢复开启MySQL服务器的二进制日志功能对数据库进行完全备份mysql差异备份恢复刷新创建新的二进制日志恢复完全备份恢复差异备份多表查询Group By的使用概述Group By与聚合函数Group By应用连接

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 ~]# lsall-0825.sql anaconda-ks.cfg恢复完全备份：[root@localhost ~]# mysql -uroot -predhat123 < all-0825.sql备份指定数据库：[root@localhost ~]# mysqldump -uroot -predhat123 --databases hhr > hhr-$(date '+%Y+%m%d').sql [root@localhost ~]# lsall-0825.sql anaconda-ks.cfg hhr-0825.sql恢复指定库：[root@localhost ~]# mysql -uroot -predhat123 < hhr-xxx.sql备份指定数据库中的某一个表：[root@localhost ~]# mysqldump -uroot -predhat123 hhr student > student-$(date '+%Y+%m%d').sql[root@localhost ~]# lsall-0825.sql hhr-0825.sqlanaconda-ks.cfg student-0825.sql方法1：恢复表(指定库名)[root@localhost ~]# mysql -uroot -predhat123 hhr < student-xxx.sql方法2:在指定的库里恢复表MariaDB [hhr]> source student-xxx.sqlQuery 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/f.d/mariadb-f [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mariadb/mariadb.logpid-file=/run/mariadb/mariadb.pidserver-id=1 #设置服务器标识符log-bin=mysql_bin #开启二进制日志功能//重启服务[root@localhost ~]# systemctl restart mariadb.service[root@hhr ~]# ls /var/lib/mysql/aria_log.00000001 ibdata1multi-master.info mysql_bin.000005 mysql_upgrade_infoaria_log_control ib_logfile0 mysql mysql_bin.000006 performance_schemahhrib_logfile1 mysql_bin.000003 mysql_bin.indexib_buffer_poolibtmp1 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 .f [client]user=rootpassword=redhat123//完全备份[root@hhr ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-0826.sql[root@hhr ~]# lsall-0825.sql all-0826.sql student-0825.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: 0MariaDB [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 ibdata1ibtmp1 mysql_bin.000007 mysql_upgrade_infoaria_log_control ib_logfile0 multi-master.info mysql_bin.index performance_schemaib_buffer_poolib_logfile1 mysql mysql.sock//刷新创建新的二进制日志[root@hhr ~]# mysqladmin flush-logs [root@hhr ~]# ls /var/lib/mysql/aria_log.00000001 ibdata1ibtmp1 mysql_bin.000007 mysql.sockaria_log_control ib_logfile0 multi-master.info mysql_bin.000008 mysql_upgrade_infoib_buffer_poolib_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-0826.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 ~]# mysqlMariaDB [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与聚合函数

//创建表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: 0MariaDB [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: 0MariaDB [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右边的就是右如果右边跟左边不匹配的值则显示空nullMariaDB [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右边的就是右如果左边跟右边不匹配的值则显示空nullMariaDB [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)