mysql数据库二

全量备份、增量备份和差异备份的特点

	全量备份	增量备份	差异备份
备份方法	备份所有文件	备份从上一次备份后的全部改动和新文件	备份从上一次完全备份后的全部改动和新文件
备份速度	最慢	最快	较快
恢复速度	最快	最慢	较快
空间要求	最多	最少	较多

全量备份

备份指定目标下的所有有效数据;
全量备份可以对 系统分区,数据分区,整块磁盘分区,文件或者文件夹进行备份;
一般在系统安装好后会进行一次备份

全量备份示意图：

实例：

[root@localhost ~]# mysqldump -uroot -ppass123! lly student > student-$(date "+%y%m%d").sql
[root@localhost ~]# ll
总用量 16
drwxr-xr-x. 2 root root    6 7月  19 17:43 公共
drwxr-xr-x. 2 root root    6 7月  19 17:43 模板
drwxr-xr-x. 2 root root    6 7月  19 17:43 视频
drwxr-xr-x. 2 root root    6 7月  19 17:43 图片
drwxr-xr-x. 2 root root    6 7月  19 17:43 文档
drwxr-xr-x. 2 root root    6 7月  19 17:43 下载
drwxr-xr-x. 2 root root    6 7月  19 17:43 音乐
drwxr-xr-x. 2 root root    6 7月  19 17:43 桌面
-rw-------. 1 root root 1390 7月  19 17:39 anaconda-ks.cfg
-rw-r--r--. 1 root root 1545 7月  19 17:41 initial-setup-ks.cfg
-rw-r--r--  1 root root 1900 8月  25 21:19 student-210825.sql
-rw-r--r--  1 root root 2124 8月  26 20:55 student-210826.sql
[root@localhost ~]# mysql -uroot -ppass123! -e "drop table lly.student;"
[root@localhost ~]# mysql -uroot -ppass123!
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
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)]> use lly;
Database changed
MariaDB [lly]> show tables;
Empty set (0.002 sec)

MariaDB [lly]> exit
Bye
[root@localhost ~]# mysql -uroot -ppass123! lly < student-210826.sql 
[root@localhost ~]# mysql -uroot -ppass123!
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 21
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)]> use lly;
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 [lly]> show tables;
+---------------+
| Tables_in_lly |
+---------------+
| student       |
+---------------+
1 row in set (0.001 sec)

差异备份

类似于增量备份;

区别在于:
    1.差异备份是基于上一次的全量备份基础上进行的增量备份;
	2. 备份速度相对于增量备份慢
	3. 恢复速度快.

[root@localhost ~]# mysql -uroot -ppass123! -e 'drop database lly;'
[root@localhost ~]# mysql -uroot -ppass123! -e 'show databases;'mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ll                 |
+--------------------+
[root@localhost ~]# mysqlbinlog --stop-position=949 /opt/data/mysql_bin.000004 | mysql -uroot -p
Enter password: 
[root@localhost ~]# mysql -uroot -p -e "show tables from lly;"
Enter password: 
+---------------------+
| Tables_in_lly       |
+---------------------+
| student             |
+---------------------+

密码破解

1、绕过密码验证
跳过skip-grant-tables加入/etc/my.cnf

[root@localhost ~]# cat /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

skip_grant-tables 

2、重启服务

[root@localhost ~]# systemctl restart mysqld.service 

3、修改密码

[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
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> use mysql;
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>  update user set authentication_string = password('111') where User = 'root' and Host = 'localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

4、恢复密码验证
从/etc/my.cnf文件中删除skip-grant-tables

[root@localhost ~]# vim /etc/my.cnf 
[root@localhost ~]# cat /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

SQL多表查询

order by

根据指定的列对结果集进行排序（默认按照升序对记录进行排序）
order by放在from table_name后面（简单来说就是放在表名后面）
实例：

MariaDB [lly]> select * from student order by age desc limit 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  7 | lisi |   50 |
+----+------+------+
1 row in set (0.001 sec)

group by

计数，算平均值，求和等等

第一种：按照‘分组的名字’进行分组, 然后求‘求的列名’列各组的平均值&最大值：

求平均值：
df.groupby(‘分组的名字’)[‘求的列名’].mean()
求最大值：

df.groupby(‘分组的名字’)[‘求的列名’].max()

第二种：是先分组, 然后求所有列分组后的平均值, 再取出‘求的列名’列的平均值&最大值：

求平均值：

df.groupby(‘分组的名字’).mean()[‘求的列名’]

求最大值：

df.groupby(‘分组的名字’).max()[‘求的列名’]

实例：

MariaDB [lly]> select * from student group by age;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  9 | wangwu    |    3 |
|  8 | chenshuo  |   10 |
| 10 | qiuyi     |   15 |
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  5 | zhangshan |   26 |
|  4 | sean      |   28 |
|  7 | lisi      |   50 |
+----+-----------+------+
9 rows in set (0.001 sec)

inner join

查询多张表中都存在的数据（只返回两个表中联结字段相等的行）

使用实例：

SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

left join

显示以左边表为主的内容，右边表有匹配左边表内容时完整显示，无匹配时留NULL

sql语句如下:
select * from A
left join B
on A.aID = B.bID

mysql> select tablea.*,tableb.* from tablea left join tableb on tablea.id = tableb.parent_id;
+------+----------+------+------+-----------+
| id   | name     | id   | job  | parent_id |
+------+----------+------+------+-----------+
|    1 | zhangsan |    1 |   23 |         1 |
|    2 | lisi     |    2 |   34 |         2 |
|    3 | wangwu   | NULL | NULL |         3 |
+------+----------+------+------+-----------+
3 rows in set (0.00 sec)

right join

显示以右边表为主的内容，左边表有匹配右边表内容时完整显示，无匹配时留NULL

mysql> select tablea.*,tableb.* from tablea right join tableb on tablea.id = tableb.parent_id;
+------+----------+------+------+-----------+
| id   | name     | id   | job  | parent_id |
+------+----------+------+------+-----------+
|    1 | zhangsan |    1 |   23 |         1 |
|    2 | lisi     |    2 |   34 |         2 |
| NULL | NULL     |    3 |   25 |         3 |
+------+----------+------+------+-----------+
3 rows in set (0.00 sec)

注意事项：

• 在输入字母过程中，一定要用英文半角标点符号，单词之间留一半角空格；
• 在建立数据表时，如果一个表与多个表联接，那么这一个表中的字段必须是“数字”数据类型，而多个表中的相同字段必须是主键，而且是“自动编号”数据类型。否则，很难联接成功。
• 代码嵌套快速方法：如，想连接五个表，则只要在连接四个表的代码上加一个前后括号（前括号加在FROM的后面，后括号加在代码的末尾即可），然后在后括号后面继续添加“INNER JOIN 表名X ON 表1.字段号=表X.字段号”代码即可，这样就可以无限联接数据表了：）