MYSQL进阶

1mysql配置文件

mysq的配置文件为/etc/my.cnf
配置文件查找次序：

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

mysql常用配置文件参数

参数	说明
port = 3306	设置监听端口
socket = /tmp/mysql.sock	指定套接字文件位置
basedir = /usr/local/mysql	指定MySQL的安装路径
datadir = /data/mysql	指定MySQL的数据存放路径
pid-file = /data/mysql/mysql.pid	指定进程ID文件存放路径
user = mysql	指定MySQL以什么用户的身份提供服务
skip-name-resolve	禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。若开启该选项，则所有远程主机连接授权都要使用IP地址方式否则MySQL将无法正常处理连接请求

2mysql数据库备份与恢复

2.1数据库备份分类

• 从物理与逻辑的角度，备份可分为

1. 物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
   物理备份方法

• 物理备份方法
  1.1冷备份(脱机备份) :是在关闭数据库的时候进行的，依赖于数据文件
  1.2 热备份(联机备份) :数据库处于运行状态，依赖于数据库的日志文件
  1.3温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作

2. 逻辑备份:对数据库逻辑组件(如: 表等数据库对象)的备份

• 按照数据库的备份策略角度,备份可分为

1. 完全备份：每次对数据进行完整的备份
2. 差异备份：备份那些自从上次完全备份之后被修改过的文件
3. 增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份

2.2数据库常用备份方案

1. 数据库完全备份
   

2. 增量备份：二进制日志备份
   

3. 差异备份
   备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

2.3mysql备份工具mysqldump

//语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

// 备份整个数据库（全备）

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| zhaojie            |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> use zhaojie;
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 [zhaojie]> show tables;
+-------------------+
| Tables_in_zhaojie |
+-------------------+
| student           |
+-------------------+
1 row in set (0.000 sec)
mysql: unknown option '--all-databases'
[root@localhost ~]# mysqldump -uroot -p123 --all-databases > backup/all-20210826.sql
[root@localhost ~]# ls backup/
all-20210826.sql

//备份zhaojie库里的student表（部分备份）
[root@localhost ~]# mysqldump -uroot -p123 zhaojie student > backup/table-20210826.sql
[root@localhost ~]# ls backup/
all-20210826.sql  table-20210826.sql

//备份zhaojie库
[root@localhost ~]# mysqldump -uroot -p123 --databases zhaojie > backup/zj-20210826.sql
[root@localhost ~]# ls backup/
all-20210826.sql  table-20210826.sql  zj-20210826.sql

2.4数据库数据恢复

2.4.1全量备份和部分备份

//恢复zhaojie库student表

MariaDB [zhaojie]> show tables;
Empty set (0.000 sec)

MariaDB [zhaojie]> 
[root@localhost ~]# mysql -uroot -p123 zhaojie < backup/table-20210826.sql 
[root@localhost ~]# mysql -uroot -p123
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 46
Server version: 10.3.17-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 |
| zhaojie            |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> use zhaojie;
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 [zhaojie]> show tables;
+-------------------+
| Tables_in_zhaojie |
+-------------------+
| student           |
+-------------------+
1 row in set (0.000 sec)


// 恢复zhaojie库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

[root@localhost ~]# mysql -uroot -p123  < backup/zj-20210826.sql
[root@localhost ~]# mysql -uroot -p123 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 48
Server version: 10.3.17-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 |
| zhaojie            |
+--------------------+
4 rows in set (0.000 sec)

2.4.2 差异备份与恢复

开启mysql服务器的二进制日志

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve

server-id=1         //设置服务器标识符
log-bin=mysql_bin    //开启二进制日志功能

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

对数据库进行完全备份

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log 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 |
| zhaojie            |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> use zhaojie;
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 [zhaojie]> show tables;
+-------------------+
| Tables_in_zhaojie |
+-------------------+
| student           |
+-------------------+
1 row in set (0.000 sec)

MariaDB [zhaojie]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jeery       |   23 |
|  3 | wangqing    |  100 |
|  4 | sean        |   28 |
|  6 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |   10 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotiao |   20 |
+----+-------------+------+
10 rows in set (0.000 sec)

//完全备份
[root@localhost ~]# mysqldump -uroot -p123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > backup/all-20210826.sql
[root@localhost ~]# ls backup/
all-20210826.sql

//增加新内容
MariaDB [zhaojie]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jeery       |   23 |
|  3 | wangqing    |  100 |
|  4 | sean        |   28 |
|  6 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |   10 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotiao |   20 |
+----+-------------+------+
10 rows in set (0.000 sec)

MariaDB [zhaojie]> insert student(name,age) values('xk',30),('xm',25);
Query OK, 2 rows affected (0.002 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [zhaojie]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jeery       |   23 |
|  3 | wangqing    |  100 |
|  4 | sean        |   28 |
|  6 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |   10 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotiao |   20 |
| 12 | xk          |   30 |
| 13 | xm          |   25 |
+----+-------------+------+
12 rows in set (0.000 sec)

MariaDB [zhaojie]> update  student set age = 29 where name = 'wangwu';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [zhaojie]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jeery       |   23 |
|  3 | wangqing    |  100 |
|  4 | sean        |   28 |
|  6 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |   29 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotiao |   20 |
| 12 | xk          |   30 |
| 13 | xm          |   25 |
+----+-------------+------+
12 rows in set (0.000 sec)

2.4.3mysql差异备份恢复

刷新创建新的二进制日志

[root@localhost ~]# ls /var/lib/mysql/  
aria_log.00000001  ib_logfile0        mysql             mysql_upgrade_info
aria_log_control   ib_logfile1        mysql_bin.000003  performance_schema
ib_buffer_pool     ibtmp1             mysql_bin.index   zhaojie
ibdata1            multi-master.info  mysql.sock
[root@localhost ~]# mysqladmin -uroot -p123 flush-logs 
[root@localhost ~]# ls /var/lib/mysql/
aria_log.00000001  ib_logfile0        mysql             mysql.sock
aria_log_control   ib_logfile1        mysql_bin.000003  mysql_upgrade_info
ib_buffer_pool     ibtmp1             mysql_bin.000004  performance_schema
ibdata1            multi-master.info  mysql_bin.index   zhaojie

// 恢复完全备份
[root@localhost ~]# mysql -uroot -p123 < backup/all-20210826.sql 
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.3.17-MariaDB-log 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 |
| zhaojie            |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> use zhaojie;
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 [zhaojie]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jeery       |   23 |
|  3 | wangqing    |  100 |
|  4 | sean        |   28 |
|  6 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |   10 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotiao |   20 |
+----+-------------+------+
10 rows in set (0.000 sec)


// 恢复差异备份

MariaDB [(none)]> show binlog events in 'mysql_bin.000003';
+------------------+-----+-------------------+-----------+-------------+-------
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info  
+------------------+-----+-------------------+-----------+-------------+-------
| mysql_bin.000003 |   4 | Format_desc       |         1 |         256 | Server
| mysql_bin.000003 | 256 | Gtid_list         |         1 |         285 | []    
| mysql_bin.000003 | 285 | Binlog_checkpoint |         1 |         328 | mysql_
| mysql_bin.000003 | 328 | Binlog_checkpoint |         1 |         371 | mysql_
| mysql_bin.000003 | 371 | Gtid              |         1 |         413 | BEGIN 
| mysql_bin.000003 | 413 | Intvar            |         1 |         445 | INSERT
| mysql_bin.000003 | 445 | Query             |         1 |         565 | use `z
| mysql_bin.000003 | 565 | Xid               |         1 |         596 | COMMIT
| mysql_bin.000003 | 596 | Gtid              |         1 |         638 | BEGIN 
| mysql_bin.000003 | 638 | Query             |         1 |         758 | use `z
| mysql_bin.000003 | 758 | Xid               |         1 |         789 | COMMIT
| mysql_bin.000003 | 789 | Rotate            |         1 |         836 | mysql_
+------------------+-----+-------------------+-----------+-------------+-------
12 rows in set (0.000 sec)

// 使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=758 /var/lib/mysql/mysql_bin.000003 | mysql -uroot -p123
[root@localhost ~]# mysql -uroot -p123 -e 'select * from zhaojie.student;'
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jeery       |   23 |
|  3 | wangqing    |  100 |
|  4 | sean        |   28 |
|  6 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |   10 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotiao |   20 |
| 12 | xk          |   30 |
| 13 | xm          |   25 |
+----+-------------+------+

3多表查询

3.1SQL查询的基本原理

1. 单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果
2. 两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果
3. 多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果

3.2 连接查询

3.2.1 内连接 INNER JOIN

内连接（INNER JOIN）有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行。（所谓的链接表就是数据库在做查询形成的中间表），主要用于查询多张表中都存在的数据。

语法：select * from staff inner join department on 条件(表1.字段=表2.字段)
MariaDB [zhaojie]> select *from staff;
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  1 | chen | male   |   38 |    200 |
|  2 | lu   | female |   26 |    201 |
|  3 | li   | male   |   38 |    201 |
|  4 | bai  | female |   28 |    202 |
|  5 | dian | male   |  118 |    200 |
|  6 | qiao | female |   16 |    204 |
+----+------+--------+------+--------+
6 rows in set (0.000 sec)

MariaDB [zhaojie]> select *from ment;
+------+------+
| id   | name |
+------+------+
|  200 | xiao |
|  201 | ren  |
|  202 | shou |
|  203 | yun  |
+------+------+
4 rows in set (0.000 sec)


MariaDB [zhaojie]> select *from staff inner join ment on staff.dep_id = ment.id;
+----+------+--------+------+--------+------+------+
| id | name | sex    | age  | dep_id | id   | name |
+----+------+--------+------+--------+------+------+
|  1 | chen | male   |   38 |    200 |  200 | xiao |
|  2 | lu   | female |   26 |    201 |  201 | ren  |
|  3 | li   | male   |   38 |    201 |  201 | ren  |
|  4 | bai  | female |   28 |    202 |  202 | shou |
|  5 | dian | male   |  118 |    200 |  200 | xiao |
+----+------+--------+------+--------+------+------+
5 rows in set (0.000 sec)

3.2.2外连接

内连接只返回满足连接条件的数据行，外连接不只列出与连接条件相匹配的行，而是列出左表（左外连接时）、右表（右外连接时）或两个表（全外连接时）中所有符合搜索条件的数据行

1. LEFT JOIN或LEFT OUTER JOIN
   左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值

2. RIGHT JOIN 或 RIGHT OUTER JOIN
   右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值

3. FULL JOIN 或 FULL OUTER JOIN
   完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值

// 左连接语法 select * from staff left join department on 条件(表1.字段=表2.字段)
MariaDB [zhaojie]> select * from staff left join ment on staff.dep_id = ment.id;
+----+------+--------+------+--------+------+------+
| id | name | sex    | age  | dep_id | id   | name |
+----+------+--------+------+--------+------+------+
|  1 | chen | male   |   38 |    200 |  200 | xiao |
|  5 | dian | male   |  118 |    200 |  200 | xiao |
|  2 | lu   | female |   26 |    201 |  201 | ren  |
|  3 | li   | male   |   38 |    201 |  201 | ren  |
|  4 | bai  | female |   28 |    202 |  202 | shou |
|  6 | qiao | female |   16 |    204 | NULL | NULL |
+----+------+--------+------+--------+------+------+
6 rows in set (0.000 sec)


//右连接语法 select * from staff right join department on 条件(表1.字段=表2.字段)
MariaDB [zhaojie]> select * from staff right join ment on staff.dep_id = ment.id;
+------+------+--------+------+--------+------+------+
| id   | name | sex    | age  | dep_id | id   | name |
+------+------+--------+------+--------+------+------+
|    1 | chen | male   |   38 |    200 |  200 | xiao |
|    2 | lu   | female |   26 |    201 |  201 | ren  |
|    3 | li   | male   |   38 |    201 |  201 | ren  |
|    4 | bai  | female |   28 |    202 |  202 | shou |
|    5 | dian | male   |  118 |    200 |  200 | xiao |
| NULL | NULL | NULL   | NULL |   NULL |  203 | yun  |
+------+------+--------+------+--------+------+------+
6 rows in set (0.000 sec)

4. group by 对表数据进行计数，求和，算平均值

// 求个数/记录数/项目数等：count()
MariaDB [zhaojie]> select * from staff;
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  1 | chen | male   |   38 |    200 |
|  2 | lu   | female |   26 |    201 |
|  3 | li   | male   |   38 |    201 |
|  4 | bai  | female |   28 |    202 |
|  5 | dian | male   |  118 |    200 |
|  6 | qiao | female |   16 |    204 |
+----+------+--------+------+--------+
6 rows in set (0.000 sec)

MariaDB [zhaojie]> select count(age) from staff;
+------------+
| count(age) |
+------------+
|          6 |
+------------+
1 row in set (0.000 sec)

// 求某一列平均数 :avg()
MariaDB [zhaojie]> select * from staff;
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  1 | chen | male   |   38 |    200 |
|  2 | lu   | female |   26 |    201 |
|  3 | li   | male   |   38 |    201 |
|  4 | bai  | female |   28 |    202 |
|  5 | dian | male   |  118 |    200 |
|  6 | qiao | female |   16 |    204 |
+----+------+--------+------+--------+
6 rows in set (0.000 sec)

MariaDB [zhaojie]> select avg(age) from staff;
+----------+
| avg(age) |
+----------+
|  44.0000 |
+----------+
1 row in set (0.000 sec)

// 求总和，总分等：sum() --必须为数字列
MariaDB [zhaojie]> select * from staff;
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  1 | chen | male   |   38 |    200 |
|  2 | lu   | female |   26 |    201 |
|  3 | li   | male   |   38 |    201 |
|  4 | bai  | female |   28 |    202 |
|  5 | dian | male   |  118 |    200 |
|  6 | qiao | female |   16 |    204 |
+----+------+--------+------+--------+
6 rows in set (0.000 sec)

MariaDB [zhaojie]> select sum(age) from staff;
+----------+
| sum(age) |
+----------+
|      264 |
+----------+
1 row in set (0.000 sec)



// 求最大值，最高分，最高工资等：max()
MariaDB [zhaojie]> select * from staff;
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  1 | chen | male   |   38 |    200 |
|  2 | lu   | female |   26 |    201 |
|  3 | li   | male   |   38 |    201 |
|  4 | bai  | female |   28 |    202 |
|  5 | dian | male   |  118 |    200 |
|  6 | qiao | female |   16 |    204 |
+----+------+--------+------+--------+
6 rows in set (0.000 sec)

MariaDB [zhaojie]> select max(age) from staff;
+----------+
| max(age) |
+----------+
|      118 |
+----------+
1 row in set (0.000 sec)


// 求最小值，最低分，最低工资等：min()
MariaDB [zhaojie]> select * from staff;
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  1 | chen | male   |   38 |    200 |
|  2 | lu   | female |   26 |    201 |
|  3 | li   | male   |   38 |    201 |
|  4 | bai  | female |   28 |    202 |
|  5 | dian | male   |  118 |    200 |
|  6 | qiao | female |   16 |    204 |
+----+------+--------+------+--------+
6 rows in set (0.000 sec)

MariaDB [zhaojie]> select min(age) from staff;
+----------+
| min(age) |
+----------+
|       16 |
+----------+
1 row in set (0.000 sec)

4mysql数据库破解密码步骤

1. 绕过密码验证
   将skip-grant-tables加入数据库的配置文件中/etc/my.cnf
2. 重启数据库服务

systemctl restart mysqld

3. 修改密码

// 进入数据库密码库
use mysql；
// 修改密码
update mysql.user set authentication_string = password('密码') where User = 'root' and Host = 'localhost';

4. 恢复密码验证
   将skip-grant-tables从数据库配置文件中删除
5. 重启数据库服务