第六周作业

lixuan8817

已于 2023-11-08 17:55:59 修改

阅读量109

点赞数

文章标签： linux 服务器运维

于 2023-11-05 23:39:19 首次发布

本文链接：https://blog.csdn.net/lixuan8817/article/details/134131415

版权

1. 完成将server和client端的mysql配置默认字符集为utf8mb4;

8.0 以后不用修改

默认5.5version
MariaDB [(none)]> SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)


vim  /etc/my.cnf.d
# include all files from the config directory
#
。。。。。。。。。。。。。。。。。。。。。。。
!includedir

[mysqld]
character-set-server=utf8mb4

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

验证：
MariaDB [(none)]> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]>

2. 掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin

MariaDB [(none)]> ? create 
MariaDB [(none)]> ? CREATE DATABASE
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: http://dev.mysql.com/doc/refman/5.5/en/create-database.html


MariaDB [(none)]> 

MariaDB [(none)]> create database testdb1 CHARACTER SET=utf8  COLLATE=utf8_bin;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| help               |
| mysql              |
| performance_schema |
| test               |
| testdb1            |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]>

3.总结mysql常见的数据类型。

MySQL支持多种内置数据类型
数值类型
日期/时间类型
字符串(字符)类型


选择正确的数据类型对于获得高性能至关重要，三大原则：
1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型
2. 简单就好，简单数据类型的操作通常需要更少的CPU周期
3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化

在这里插入图片描述

4. 创建一个主机表host,放在testdb中，要求字段 1) 主键自增id 无符号, tinyint. 2) hostname可变字符长度256，可为空。。3）ip 可变字符长度256，可为空。4）账号，可变字符长度256，可为空。5）密码，可变字符长度256，可为空。6）创建时间，时间类型，非空。7）更新时间，时间类型，默认当前时间。8）区域，只能在华南，华北，华东，三个区域之一。9）端口，无符号整数，可为空。10）外网地址，可变字符长度256，可为空。11）内网地址，可变字符长度256，可为空。

mysql> create table host8 (
    -> id tinyint unsigned  auto_increment primary key,
    -> hostname varchar(256) ,
    -> ip varchar(256) ,
    -> `账号`  varchar(256) ,
    -> `密码` varchar(256) ,    
    -> `创建时间` timestamp not null,
    -> `区域`  enum('华南','华北','华东') ,
    -> `端口` int  unsigned ,
    -> `内网地址`  varchar(256) ,    
    -> `外网地址` varchar(256) ); 
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> desc host8
    -> ;
+--------------+----------------------------------+------+-----+---------+----------------+
| Field        | Type                             | Null | Key | Default | Extra          |
+--------------+----------------------------------+------+-----+---------+----------------+
| id           | tinyint(3) unsigned              | NO   | PRI | NULL    | auto_increment |
| hostname     | varchar(256)                     | YES  |     | NULL    |                |
| ip           | varchar(256)                     | YES  |     | NULL    |                |
| 账号         | varchar(256)                     | YES  |     | NULL    |                |
| 密码         | varchar(256)                     | YES  |     | NULL    |                |
| 创建时间     | timestamp                        | NO   |     | NULL    |                |
| 区域         | enum('华南','华北','华东')       | YES  |     | NULL    |                |
| 端口         | int(10) unsigned                 | YES  |     | NULL    |                |
| 内网地址     | varchar(256)                     | YES  |     | NULL    |                |
| 外网地址     | varchar(256)                     | YES  |     | NULL    |                |
+--------------+----------------------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql>

5. 给testdb.host表中添加多条数据。

mysal>insert host (id,area,ip,port) values (1,'华北','192.168.44.3',23)
mysal>insert host (hostname,ip,password,area,port) values ('magedu.org','192.168.1.3',1234567,'华北',80 ) ,('golang.magedu.com','192.168.32.1',1223,'华南',87);
mysql> selcet *from host;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selcet *from host' at line 1

验证：
mysql> select * from host;
+----+-------------------+--------------+---------+----------+------------+------------+--------+------+-----------------+-----------+
| id | hostname          | ip           | account | password | createtime | updatetime | area   | port | externaladdress | inaddress |
+----+-------------------+--------------+---------+----------+------------+------------+--------+------+-----------------+-----------+
|  1 | NULL              | 192.168.44.3 | NULL    | NULL     | NULL       | NULL       | 华北   |   23 | NULL            | NULL      |
|  2 | magedu.com        | 192.168.1.2  | NULL    | 1234566  | NULL       | NULL       | 华北   |  443 | NULL            | NULL      |
|  3 | magedu.org        | 192.168.1.3  | NULL    | 1234567  | NULL       | NULL       | 华北   |   80 | NULL            | NULL      |
|  4 | magedu.org        | 192.168.1.3  | NULL    | 1234567  | NULL       | NULL       | 华北   |   80 | NULL            | NULL      |
|  5 | golang.magedu.com | 192.168.32.1 | NULL    | 1223     | NULL       | NULL       | 华南   |   87 | NULL            | NULL      |
+----+-------------------+--------------+---------+----------+------------+------------+--------+------+-----------------+-----------+
5 rows in set (0.00 sec)

6. 根据表扩展出几个语句，完成总结DDL, DML的用法，并配上示例。

# 创建表
mysql> create table student (id tinyint unsigned primary key auto_increment , name char(4) not null , gender char(1) , age tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)
 
# 查看所创建的表
mysql> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                          |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `name` char(4) COLLATE utf8mb3_bin NOT NULL,
  `gender` char(1) COLLATE utf8mb3_bin DEFAULT NULL,
  `age` tinyint unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
# 查看表的结构
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | tinyint unsigned | NO   | PRI | NULL    | auto_increment |
| name   | char(4)          | NO   |     | NULL    |                |
| gender | char(1)          | YES  |     | NULL    |                |
| age    | tinyint unsigned | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
 
# 删除表
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)

7. 导入hellodb库，总结DQL, alias, where子句，gruop by, order by, limit, having使用示例。

SELECT语法：
SELECT
 [ALL | DISTINCT | DISTINCTROW ]
 [SQL_CACHE | SQL_NO_CACHE]
 select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [FOR UPDATE | LOCK IN SHARE MODE]

字段显示可以使用别名：
col1 AS alias1, col2 AS alias2, ...
WHERE子句：指明过滤条件以实现"选择"的功能：
过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
范例查询: BETWEEN min_num AND max_num
不连续的查询: IN (element1, element2, ...)
空查询: IS NULL, IS NOT NULL
DISTINCT 去除重复行，范例：SELECT DISTINCT gender FROM students;
模糊查询: LIKE 使用 % 表示任意长度的任意字符 _ 表示任意单个字符
RLIKE：正则表达式，索引失效，不建议使用
REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：NOT，AND，OR，XOR
GROUP BY：根据指定的条件把查询结果进行"分组"以用于做"聚合"运算
常见聚合函数： count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计
HAVING: 对分组聚合运算后的结果指定过滤条件
一旦分组 group by ，select语句后只跟分组的字段，聚合函数
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制,跳过offset,显示row_count
行,offset默为值为0
对查询结果中的数据请求施加"锁"
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写操作
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读操作

字段显示使用别名范例：

mysql> select  stuid,name,gender from students ;
+-------+---------------+--------+
| stuid | name          | gender |
+-------+---------------+--------+
|     1 | Shi Zhongyu   | M      |
mysql> select  stuid 学生ID,name 姓名,gender 性别 from students ;
+----------+---------------+--------+
| 学生ID   | 姓名          | 性别   |
+----------+---------------+--------+
|        1 | Shi Zhongyu   | M      |

where字段显示使用别名范例：

mysql> select  stuid,name,gender from students  where gender = 'F'
    -> ;
+-------+---------------+--------+
| stuid | name          | gender |
+-------+---------------+--------+
|     7 | Xi Ren        | F      |
|     8 | Lin Daiyu     | F      |
|     9 | Ren Yingying  | F      |
|    10 | Yue Lingshan  | F      |
|    12 | Wen Qingqing  | F      |
|    14 | Lu Wushuang   | F      |
|    19 | Xue Baochai   | F      |
|    20 | Diao Chan     | F      |
|    21 | Huang Yueying | F      |
|    22 | Xiao Qiao     | F      |
+-------+---------------+--------+
10 rows in set (0.00 sec)
mysql> select  stuid,name,gender from students  where name = 'xiao qiao';
+-------+-----------+--------+
| stuid | name      | gender |
+-------+-----------+--------+
|    22 | Xiao Qiao | F      |
+-------+-----------+--------+
1 row in set (0.00 sec)

select  stuid,name,gender from students  where gender = 'F'
mysql> select  *  from students  where classid  is null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    24 | Xu Xian     |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)

gruop by, 分组


mysql> select  gender 性别   from students group by gender  ;
+--------+
| 性别   |
+--------+
| M      |
| F      |
+--------+
2 rows in set (0.00 sec)

mysql> select  gender  from students group by gender  ;
+--------+
| gender |
+--------+
| M      |
| F      |
+--------+
2 rows in set (0.00 sec)

mysql> select  gender 性别,count(*)   from students group by gender  ;
+--------+----------+
| 性别   | count(*) |
+--------+----------+
| M      |       15 |
| F      |       10 |
+--------+----------+
2 rows in set (0.00 sec)

mysql> select  gender 性别,count(*)数量   from students group by gender  ;
+--------+--------+
| 性别   | 数量   |
+--------+--------+
| M      |     15 |
| F      |     10 |
+--------+--------+
2 rows in set (0.00 sec)

order by, 排序

mysql> select * from students order by age ;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NUL  |
mysql> select * from students order  by age desc ; 倒序
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |

mysql> select * from students order  by age ，; 先对age排序在对班级排序。
mysql> select * from students order  by age ,classid ;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |

limit, 分页查询

mysql> select * from  students  limit 5 ;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)

mysql> select * from  students  limit 5,7;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
7 rows in set (0.00 sec)

having 对分组后的数再处理。

mysql> select gender,count(*)  from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| M      |       15 |
| F      |       10 |
+--------+----------+
2 rows in set (0.00 sec)

mysql> select gender,count(*)  from students group by gender having gender = 'm';
+--------+----------+
| gender | count(*) |
+--------+----------+
| M      |       15 |
+--------+----------+
1 row in set (0.00 sec)

mysql>

8. 基于hellodb 库, 总结子查询，关联查询，交叉连接，内连接，左连接，右连接，完全连接，自连接。

总结子查询：把一张表的select查询结果作为结果给另外一张表。

mysql> select max(age) from students;
+----------+
| max(age) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

mysql> update teachers set age  = (select max(age) from students) where tid =1 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teachers ;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    | 100 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

将一个表的查询结果作为另外一张表的查询条件。

mysql> select  *  from teachers where age = (select max(age) from students);
+-----+------------+-----+--------+
| TID | Name       | Age | Gender |
+-----+------------+-----+--------+
|   1 | Song Jiang | 100 | M      |
+-----+------------+-----+--------+
1 row in set (0.00 sec)

联合查询
联合查询 Union 实现的条件,多个表的字段数量相同,字段名和数据类型可以不同,但一般数据类型是相同
的.

mysql> select stuid,name,age from students union  select  tid,name,age from teachers ;
+-------+---------------+-----+
| stuid | name          | age |
+-------+---------------+-----+
|     1 | Shi Zhongyu   |  22 |
|     2 | Shi Potian    |  22 |
|     3 | Xie Yanke     |  53 |
|     4 | Ding Dian     |  32 |
|     5 | Yu Yutong     |  26 |
|     6 | Shi Qing      |  46 |
|     7 | Xi Ren        |  19 |
|     8 | Lin Daiyu     |  17 |
|     9 | Ren Yingying  |  20 |
|    10 | Yue Lingshan  |  19 |
|    11 | Yuan Chengzhi |  23 |
|    12 | Wen Qingqing  |  19 |
|    13 | Tian Boguang  |  33 |
|    14 | Lu Wushuang   |  17 |
|    15 | Duan Yu       |  19 |
|    16 | Xu Zhu        |  21 |
|    17 | Lin Chong     |  25 |
|    18 | Hua Rong      |  23 |
|    19 | Xue Baochai   |  18 |
|    20 | Diao Chan     |  19 |
|    21 | Huang Yueying |  22 |
|    22 | Xiao Qiao     |  20 |
|    23 | Ma Chao       |  23 |
|    24 | Xu Xian       |  27 |
|    25 | Sun Dasheng   | 100 |
|     1 | Song Jiang    | 100 |
|     2 | Zhang Sanfeng |  94 |
|     3 | Miejue Shitai |  77 |
|     4 | Lin Chaoying  |  93 |
+-------+---------------+-----+
29 rows in set (0.00 sec)

交叉连接

交叉连接：
cross join 即多表的记录之间做笛卡尔乘积组合，并且多个表的列横向合并相加, “雨露均沾”
比如: 第一个表3行4列,第二个表5行6列,cross join后的结果为3*5=15行,4+6=10列
交叉连接生成的记录可能会非常多,建议慎用


mysql> select * from students cross join teachers ;
mysql> select * from students , teachers ;
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   4 | Lin Chaoying  |  93 | F      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   2 | Zhang Sanfeng |  94 | M

内连接，

inner join 内连接取多个表的交集
范例：内连接

mysql>  select * from students inner join teachers on  students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    | 100 | M      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

左连接，右连接

左连接: 以左表为主根据条件查询右表数据﹐如果根据条件查询右表数据不存在使用null值填充
右连接: 以右表为主根据条件查询左表数据﹐如果根据条件查询左表数据不存在使用null值填充

mysql> select * from students  left  join teachers on students.teacherid=teachers.tid;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |    4 | Lin Chaoying  |   93 | F      |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |    1 | Song Jiang    |  100 | M      |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
25 rows in set (0.00 sec)

mysql> select * from students  right  join teachers on students.teacherid=teachers.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age  | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu |   22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |   32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
|     5 | Yu Yutong   |   26 | M      |       3 |         1 |   1 | Song Jiang    | 100 | M      |
|  NULL | NULL        | NULL | NULL   |    NULL |      NULL |   2 | Zhang Sanfeng |  94 | M      |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

9. 总结select语句处理顺序。

在这里插入图片描述

10. 总结mysql事件管理,用户管理，权限管理。

mysql事件管理,

用户管理，权限管理

用户管理
MySQL 用户管理
用户帐号 结构:
'USERNAME'@'HOST'
@'HOST': 主机名： user1@'web1.magedu.org'
IP地址或Network
通配符： %   _
示例：wang@'172.16.%.%'  
    user2@'192.168.1.%'
    mage@'10.0.0.0/255.255.0.0'

创建用户：CREATE USER

CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；
#示例:
create user test@'10.0.0.0/255.255.255.0' identified by '123456';
create user test2@'10.0.0.%' identified by 123456;

新建用户的默认权限：USAGE

用户重命名：RENAME USER
RENAME USER old_user_name TO new_user_name;

删除用户：
DROP USER 'USERNAME'@'HOST'
范例：删除默认的空用户
DROP USER ''@'localhost';



修改密码：
注意：
新版mysql中用户密码可以保存在mysql.user表的authentication_string字段中
如果mysql.user表的authentication_string和password字段都保存密码，authentication_string
优先生效
#方法1,用户可以也可通过此方式修改自已的密码
SET PASSWORD FOR 'user'@'host' = PASSWORD('password');  #MySQL8.0 版本不支持此方法,
因为password函数被取消
set password for root@'localhost'='123456' ;  #MySQL8.0版本支持此方法,此方式直接将密码
123456加密后存放在mysql.user表的authentication_string字段
#方法2
ALTER  USER test@'%' IDENTIFIED BY 'centos';  #通用改密码方法, 用户可以也可通过此方式修
改自已的密码,MySQL8 版本修改密码
#方法3 此方式MySQL8.0不支持,因为password函数被取消
UPDATE mysql.user SET password=PASSWORD('password') WHERE clause;
#mariadb 10.3
update mysql.user set authentication_string=password('ubuntu') where
user='mage';
#此方法需要执行下面指令才能生效：
FLUSH PRIVILEGES;

忘记管理员密码的解决办法：
1. 启动mysqld进程时，为其使用如下选项：
--skip-grant-tables
--skip-networking
2. 使用UPDATE命令修改管理员密码
3. 关闭mysqld进程，移除上述两个选项，重启mysqld
范例：Mariadb 和MySQL5.6版之前破解root密码
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
skip-grant-tables  
skip-networking  
[root@centos8 ~]#systemctl restart mysqld|mariadb
[root@centos8 ~]#mysql
#方法1
#mariadb 旧版和MySQL5.6版之前
MariaDB [(none)]> update mysql.user set password=password('ubuntu') where
user='root';
#mariadb 新版
MariaDB [(none)]> update mysql.user set authentication_string=password('ubuntu')
where user='root';
#方法2
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> alter user root@'localhost' identified by 'ubuntu';
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
#skip-grant-tables                                                              
         
#skip-networking

[root@centos8 ~]#systemctl restart mysqld|mariadb
[root@centos8 ~]#mysql -uroot -pubuntu

范例: MySQL5.7和8.0 破解root密码

[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
skip-grant-tables  
skip-networking  #MySQL8.0不需要
[root@centos8 ~]#systemctl restart mysqld
#方法1
mysql> update mysql.user set authentication_string='' where user='root' and
host='localhost';
#方法2
mysql> flush privileges;
#再执行下面任意一个命令
mysql> alter user root@'localhost' identified by 'ubuntu';
mysql> set password for root@'localhost'='ubuntu';
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
#skip-grant-tables                                                              
         
#skip-networking
[root@centos8 ~]#systemctl restart mysqld
[root@centos8 ~]#mysql -uroot -pubuntu


注意：8.06的管理员密码有安全等级，需要设置复杂密码。


3.14.2 授权

授权：GRANT

GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host'
[IDENTIFIED BY 'password'] [WITH GRANT OPTION];
priv_type: ALL [PRIVILEGES]
object_type:TABLE | FUNCTION | PROCEDURE
priv_level:  *(所有库)  |*.*   | db_name.*  | db_name.tbl_name  | tbl_name(当前库的
表)  | db_name.routine_name(指定库的函数,存储过程,触发器)
with_option: GRANT OPTION
 | MAX_QUERIES_PER_HOUR count
 | MAX_UPDATES_PER_HOUR count
 | MAX_CONNECTIONS_PER_HOUR count
 | MAX_USER_CONNECTIONS count
 
 
 范例：
 GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';
GRANT ALL ON wordpress.* TO wordpress@'10.0.0.%' ;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%'  WITH GRANT OPTION;
#创建用户和授权同时执行的方式在MySQL8.0取消了
GRANT ALL ON wordpress.* TO wordpress@'192.168.8.%' IDENTIFIED BY 'magedu';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.8.%' IDENTIFIED BY 'magedu'
WITH GRANT OPTION;

3.14.3 取消权限
取消授权：REVOKE
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON
[object_type] priv_level FROM user [, user] ...
REVOKE DELETE ON *.* FROM 'testuser'@'172.16.0.%';

3.14.4 查看指定用户获得的授权
Help SHOW GRANTS
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];

11. 基于apache, php, mysql搭建wordpress站点。

  1，安装软件： yum install httpd  php php-mysqlnd  php-json -y
  2，创建MySQL的wordpress的数据库  wordpress
  3，创建mysql的用户 wordpress@localhost 和密码；
  4，授权mysql用户 wordpress@localhost 对wordpress数据库  有所有的权限。
  5，下载php的wordpress的程序并解压至 /var/www/html/下面
  6，修改/var/www/html 下所有资源的包含html文件夹得所有者和所属组为 apache。chown  -R /var/www/html
  7，修改登陆机器得host文件实现域名访问。    hostip   blog.lixx.com
  8，浏览器 输入 blog.lixx.com  初始化wordpress 。
  9，输入mysql为wordpress创建得数据库名称，用户，密码。

12. 总结mysql架构原理

MySQL 是一个典型的客户端/服务端模式的数据库管理系统，它的架构主要分为以下三个部分：

    连接层（Connection Layer）：处理客户端连接请求，并进行身份验证、权限验证等操作。一旦连接建立成功，它将负责管理客户端和服务端之间的通信，并通过缓存、预读等技术提高数据查询的效率。
    服务层（Server Layer）：该层是 MySQL 的核心组成部分，由多个线程池、缓存和存储引擎组成。所有的 SQL 查询请求都发送到该层处理，其中相关的 SQL 解析和优化工作由 Query Cache 和 Optimizer 处理，然后将查询请求发送到相应的存储引擎中执行。
    存储引擎层（Storage Engine Layer）：MySQL 支持多种存储引擎，例如 InnoDB、MyISAM 等。每个存储引擎都可以独立处理数据的读写，因此在存储引擎层中，数据会被不同的存储引擎以不同的方式进行存储和管理。

其中，还有以下几个重要的组件：

    连接器（Connector）：负责连接客户端和服务端、进行身份认证和权限验证。
    查询缓存（Query Cache）：缓存查询结果，提高重复查询的效率。
    SQL 解析器和优化器（SQL Parser & Optimizer）：将 SQL 语句转换为执行计划，进行查询优化。
    存储引擎（Storage Engine）：负责数据的存储和管理，提供不同的数据访问方式、锁机制以及事务支持等功能。

总体来说，MySQL 的架构设计非常清晰，各个组件之间分工明确、相互独立，使得 MySQL 具备高效、灵活和可扩展的特性。同时，MySQL 还提供了多种配置参数以及插件接口，能够满足各种不同的需求和场景。

在这里插入图片描述

13. 总结myisam和Innodb存储引擎的区别。

MyISAM 和 InnoDB 是 MySQL 中两种常用的存储引擎。它们之间有以下几个主要区别：

    数据库锁定方式不同：

MyISAM 存储引擎采用表级锁定，即当对一个表进行读写操作时，该表会被锁定，其他用户无法对该表进行修改操作。这种锁定方式会导致高并发下的性能问题。

而 InnoDB 存储引擎采用行级锁定，即只锁定被访问的行，而不是整张表。这样可以避免表级锁定下的性能瓶颈和冲突，并提高数据并发处理能力。

    支持事务处理机制的能力不同：

MyISAM 不支持事务，因此不支持事务处理机制和回滚操作，一旦发生错误就无法恢复。而 InnoDB 支持事务处理机制，并且具有 ACID（原子性、一致性、隔离性和持久性）属性，能够保证数据库完整性和一致性。

    索引方式不同：

MyISAM 存储引擎采用 B+Tree 索引结构，适合用于无事务、插入、删除操作较多的应用场景。而 InnoDB 存储引擎也使用 B+Tree 索引结构，并支持主键、唯一键和外键。

不过，在大数据量、高并发等场景下，InnoDB 的多版本并发控制（MVCC）机制，可能会导致锁竞争问题，影响数据库的性能。

    数据存储方式不同：

MyISAM 存储引擎采用表级别的压缩技术，可以节省磁盘空间，提高查询速度。而 InnoDB 存储引擎则采用基于页的存储模型，支持从磁盘上读取部分页面，一方面避免了频繁的磁盘 I/O 操作，另一方面也可以更灵活地利用内存。

总体来说，MyISAM 适合读取操作比较多、写入操作比较少的应用场景，而 InnoDB 则适合数据更新操作频繁、事务处理和并发读取的场景。需要根据具体的需求和业务场景来选择适合的存储引擎。

14. 总结mysql索引作用，同时总结哪些查询不会使用到索引。

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引
擎实现
优点：
索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序 I/O
缺点：
占用额外空间，影响插入速度
索引类型：
B+ TREE、HASH、R TREE、FULL TEXT
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引: 是否是多个字段的索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高

哪些查询不会使用到索引

一、数据类型不匹配 在查询时，如果where子句中的条件中的数据类型与索引列的数据类型不匹配，那么MySQL就不会使用索引来查询。例如，如果索引列是int类型，但是查询条件中使用了varchar类型的值，那么MySQL就不会使用索引进行查询。 
二、使用函数操作 在查询时，如果使用了MySQL内置函数或者自定义函数，那么MySQL就无法使用任何索引，因为MySQL需要将索引列进行计算或者操作才能匹配函数，这显然不是索引所擅长的操作。
 三、模糊查询 在查询时，如果使用了模糊查询，例如like '%xxx%'，那么MySQL也无法使用索引。因为这种查询需要对索引列进行全表扫描匹配，无法利用索引的有序性进行查询优化。 
 四、数据量太少 在查找数据时，如果数据表的记录行数非常少，那么MySQL就不会使用索引进行查询优化。因为MySQL认为全表扫描的效率更高，不需要使用索引。 
 五、不满足索引最左前缀法则 在MySQL中，如果使用了联合索引，那么联合索引中的第一个索引列可以单独使用索引进行查询优化，但如果第一个索引列匹配不到数据，那么后续的索引列就无法利用索引进行查询优化。 
 以上就是 MySQL 不走索引的主要情况，如果我们在日常开发中能够避免这些情况的出现，就可以最大程度地利用索引进行查询优化，提高数据库的查询性能。

15. 总结事务ACID事务特性

ACID是数据库管理中重要的概念，是指数据库事务应该具备的四个特性，分别为原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）

1.原子性（Atomicity）：指事务是一个不可分割的操作单元，要么全部执行成功，要么全部执行失败。如果事务执行失败，在撤销所有操作时必须将数据库恢复到事务开始前的状态，以保证数据的正确性。

2.一致性（Consistency）：指在事务执行结束后，数据库的状态应该满足预定义的约束条件，如外键约束、非空约束等，确保数据的正确性。

3.隔离性（Isolation）：指多个事务之间相互隔离，彼此不会产生影响，每个事务应该感觉不到其他事务的存在。这样可以避免由于并发访问导致的数据异常或不一致。

4.持久性（Durability）：指事务完成后对数据的修改是永久性的，即使出现系统故障，也可以通过数据恢复技术将数据恢复到上次提交事务的状态。

所以ACID特性确保了数据库事务的可靠性和数据一致性，是数据库管理的核心理念，也是设计高度可用、高性能数据库系统必须要考虑的因素。

事务隔离级别
MySQL 支持四种隔离级别，事务隔离级别从上至下更加严格

READ UNCOMMITTED 
可读取到未提交数据，产生脏读
READ COMMITTED
可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次
读取数据不一致
REPEATABLE READ 
可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍
只能读取到未修改前的旧数据。此为MySQL默认设置
SERIALIZABLE 
可串行化，未提交的读事务阻塞写事务（加读锁，但不阻塞读事务），或者未提交的写事务阻塞读
和写事务（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

在这里插入图片描述

16. 总结事务日志工作原理。

事务日志（Transaction Log）是数据库管理系统中的一种重要机制，用于记录每个事务所做出的修改操作。其工作原理可以简单概括如下：

1.事务启动：当用户发起一个事务时，数据库开始记录该事务的相关信息，并在事务日志中创建一个新的日志文件，以记录此次事务的所有操作。

2.日志记录：在事务执行过程中，数据库系统将所有对数据进行的修改操作，都以日志的形式记录下来，包括对数据的插入、更新、删除等操作。每个日志条目都包含了该操作的详细信息，如何修改数据，修改之前和之后的值等。

3.日志缓存：由于频繁写磁盘会影响性能，为了提高性能，数据库通常会使用缓存技术，将日志信息先暂存在内存中，等到缓存满或到达一定时间后再一次性写入硬盘。

将事务提交：当事务执行完成后，如果用户提交了该事务，那么就会触发事务的提交操作，同时也会将该事务所产生的所有日志记录写入磁盘上的日志文件中。

4.恢复操作：当数据库系统重新启动时，会检查每个已提交的事务是否正确执行，并根据事务日志对未提交的事务进行恢复。如果有未完成的事务，则进行回滚操作，将数据库恢复到事务开始前的状态。

所以事务日志是确保数据库数据正确性和可靠性的重要手段，在数据库管理中扮演着至关重要角色。

17. 总结mysql日志类型，并说明如何启动日志。

MySQL的日志类型可以分为以下几类：

1.错误日志（Error Log）: 记录了MySQL运行过程中出现的所有错误信息。


```bash
yum安装默认位置
/var/log/mysqld.log

修改位置：
vim /etc/my.cnf
[mysqld]
log-error=/data/log/mysqld.log
wq！
需先新建文件：mkdir -p /data/log/  
更改为权限mysql：chown mysql：mysql  /data/log/

systemctl restart mysqd

2.慢查询日志（Slow Query Log）: 记录了执行时间超过指定阈值的SQL语句，方便性能优化。
记录执行查询时长超出指定时长的操作
慢查询相关变量：

slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值，单位秒,默认为10s
slow_query_log_file=HOSTNAME-slow.log  #慢查询日志文件 yum  ，默认路径：/var/lib/mysql/centos8-slow.log

log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 
#上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON  #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语
句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain #记录内容
log_slow_queries = OFF    #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除

慢查询开启：默认关闭

修改：
vim /etc/my.cnf
[mysqld]
slow_query_log=ON
wq！

文件路径查询：
mysql> select @@slow_query_log_file;
+---------------------------------+
| @@slow_query_log_file           |
+---------------------------------+
| /var/lib/mysql/centos7-slow.log |
+---------------------------------+

3.查询日志（General Query Log）: 记录了MySQL服务器收到的所有 SQL 查询语句，包括查询、更新、删除等操作。

4.二进制日志（备份）（Binary Log）：记录了对数据库进行任何更改操作的详细情况，以实现基于日志的数据库备份和灾难恢复。

记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能：通过"重放"日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放
二进制日志记录三种格式
基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少。
**基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式**推荐使用。
混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）
MariaDB [hellodb]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.001 sec)
#MySQL 8.0 默认使用ROW方式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.07 sec)

二进制日志文件的构成

有两类文件
1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002
2.索引文件：mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表

二进制日志相关的服务器变量：
##重要√
sql_log_bin=ON
log_bin=/PATH/BIN_LOG_FILE
上述两项都开启才可以

sql_log_bin=ON|OFF：#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=/PATH/BIN_LOG_FILE：#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开
启才可以
binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，mariadb5.5默认STATEMENT
max_binlog_size=1073741824：#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0：#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：#二进制日志可以自动删除的天数。 默认为0，即不自动删除

二进制日志开启：##重要√
8.0默认开启，yum默认路径： /var/lib/mysql/binlog.0001
其他安装需配置文件指定路径：log_bin=/PATH/BIN_LOG_FILE

vim /etc/my.cnf
[mysqld]
sql_log_bin=ON
log_bin=/data/binlog/binlog  #后面的binlog为文件前缀，生成文件为binlog.0001  后面的binlog改成mysqlbinlog后生成文件为mysqlbinlog.0001
#####log_bin=必须加文件前缀#####
wq！

systemctl restart mysqld

临时关闭二进制日志：用于还原前或者测试。
√测试为会话级别；

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

二进制日志相关配置

查看mariadb自行管理使用中的二进制日志文件列表，及大小
SHOW {BINARY | MASTER} LOGS     
查看使用中的二进制日志文件
SHOW MASTER STATUS
在线查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
范例：
show binlog events in 'mysql-bin.000001' from 6516 limit 2,3

范例：同步远程主机的二进制日志

#从10.0.0.8远程主机同步二进制binlog.000002日志向后到当前目录
[root@centos8 data]#mysqlbinlog -R --host=10.0.0.8 --user=test --password=123456 
--raw --stop-never binlog.000002

5.事务日志（Transaction Log）：也称为重做日志（Redo Log），记录了已提交的事务对数据库所作的修改操作，以确保数据能够在系统故障后恢复到已提交的状态

mysql> show variables like '%innodb_log%';
+------------------------------------+----------+
| Variable_name                      | Value    
+------------------------------------+----------+
| innodb_log_buffer_size             | 16777216 |  
| innodb_log_checksums               | ON       |
| innodb_log_compressed_pages        | ON       |
| innodb_log_file_size               | 50331648 |#每个日志文件大小
| innodb_log_files_in_group          | 2        |#日志组成员个数
| innodb_log_group_home_dir          | ./       |#事务文件路径
| innodb_log_spin_cpu_abs_lwm        | 80       |
| innodb_log_spin_cpu_pct_hwm        | 50       |
| innodb_log_wait_for_flush_spin_hwm | 400      |
| innodb_log_write_ahead_size        | 8192     |
| innodb_log_writer_threads          | ON       |
+------------------------------------+----------+
11 rows in set (0.00 sec)


查看：
mysql> select    @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

修改：
vim /etc/my.cnf
[mysqld]
innodb_flush_log_at_trx_commit=0|1|2
wq！

systemctl restart mysqd

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更
好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系
统或停电可能导致最后一秒的交易丢失

在这里插入图片描述 6，通用日志
通用日志：记录对数据库的通用操作，包括:错误的SQL语句
通用日志可以保存在：file（默认值）或 table（mysql.general_log表）
通用日志相关设置；
general_log=ON|OFF

general_log_file=HOSTNAME.log

log_output=TABLE|FILE|NONE

vim /etc/my.cnf
[mysqld]
general_log=ON
wq！

systemctl restart mysqd
--------------------------------
查询状态：

mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

general_log=ON|OFF
------------------------------------
查询开启状态后文件默认位置：
/var/lib/mysql/centos7.log 

mysql> select @@general_log_file;
+----------------------------+
| @@general_log_file         |
+----------------------------+
| /var/lib/mysql/centos7.log |
+----------------------------+
1 row in set (0.00 sec)


general_log_file=HOSTNAME.log
vim  /etc/my.cnf
[mysqld]
general_log=ON
general_log_file=/xxx位置/xxx.log
wq！
需先新建文件：mkdir -p /xxx位置/
更改为权限mysql ；
systemctl restart mysqd
------------------------------------

修改通用日志存在mysql数据库的general_log表中。
vim  /etc/my.cnf
[mysqld]
general_log=ON
log_output=TABLE
wq！
systemctl restart mysqd
general_log表是CSV的文本文件
文件路径： /var/lib/mysql/mysql/general_log.CSV
也可以用select在mysql中查看

18. 总结二进制日志的不同格式的使用场景。

mySQL的二进制日志（Binary Log）可以分为两种不同格式：语句格式（statement-based）和行格式（row-based）。

1.语句格式记录的是事务中执行的SQL语句，可以用于复制、恢复、回滚等操作。但是也存在一些缺点，如对于某些复杂的语句或函数可能无法正确记录，因此可能导致数据不一致的问题。

2.行格式记录的是每行数据的具体修改操作，更加精确地记录了事务执行过程中的数据变化。由于记录的信息比较详细，因此行格式在进行数据复制和故障恢复时更加可靠，但是也会给硬盘写入带来更大的压力，并且日志文件会相对比较大。

使用场景：

语句格式适合轻量级的系统，查询和写入都不多，而且性能要求没有那么高的场景。

对于高性能和高可靠性要求的场景，推荐使用行格式。比如需要进行主从复制、故障恢复、数据同步等操作时，行格式可以更好地保证数据一致性，减少故障发生的概率。当然，如果存储空间受限，或者IO写入压力过大，可以选择关闭二进制日志或者调整成语句格式进行记录。

19. 总结mysql备份类型，并基于mysqldump, xtrabackup完成数据库备份与恢复验证。

mysql备份类型

注意：二进制日志文件不应该与数据文件放在同一磁盘

完全备份，部分备份

完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表
完全备份、增量备份、差异备份

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

在这里插入图片描述

差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单

在这里插入图片描述

冷、温、热备份

冷备：读、写操作均不可进行，数据库停止服务
温备：读操作可执行；但写操作不可执行
热备：读、写操作均可执行
MyISAM：温备，不支持热备
InnoDB：都支持

物理和逻辑备份

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快。
逻辑备份：从数据库中"导出"数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可
能丢失精度。
备份什么
1，数据
2，二进制日志、InnoDB的事务日志
3，用户帐号，权限设置，程序代码（存储过程、函数、触发器、事件调度器）
服务器的配置文件

备份注意要点：

能容忍最多丢失多少数据
备份产生的负载
备份过程的时长
温备的持锁多久
恢复数据需要在多长时间内完成
需要备份和恢复哪些数据

还原要点

做还原测试，用于测试备份的可用性
还原演练，写成规范的技术文档

备份工具

cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部
分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
MariaDB Backup：从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
mysqlbackup：热备份， MySQL Enterprise Edition 组件
mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、
FLUSH TABLES和cp或scp来快速备份数据库

基于mysqldump, xtrabackup完成数据库备份与恢复验证

mysqldump

mysqldump, mydumper, phpMyAdmin
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件
mysqldump是MySQL的客户端命令，通过mysql协议连接至mysql服务器进行备份

命令格式:
mysqldump [OPTIONS] database [tables] #支持指定数据库和指定多表的备份，但数据库本身定义
不备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3…] #支持指定数据库备份，包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS] #备份所有数据库，包含数据库本身定义也会备份
mysqldump 常见通用选项：

-u, --user=name     User for login if not current user
-p, --password[=name]  Password to use when connecting to server
-A, --all-databases #备份所有数据库，含create database
-B, --databases db_name…  #指定备份的数据库，包括create database语句
-E, --events：#备份相关的所有event scheduler
-R, --routines：#备份所有存储过程和自定义函数
--triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集
--master-data[=#]：#注意：MySQL8.0.26版以后，此选项变为--source-data
#此选项须启用二进制日志
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复
制多机使用
#2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原
#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--
single-transaction）
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，
配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--singletransaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact        #去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data    #只备份表结构,不备份数据,即只备份create table 
-t, --no-create-info #只备份数据,不备份表结构,即不备份create table 
-n,--no-create-db #不备份create database，可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force       #忽略SQL错误，继续执行
--hex-blob        #使用十六进制符号转储二进制列，当有包括BINARY,VARBINARY,BLOB，
BIT的数据类型的列时使用，避免乱码
-q, --quick     #不缓存查询，直接输出，加快备份速度

备份单表：

[root@centos7 ~]# mysqldump -uroot -p123456  hellodb students > /data/students.sql

[root@centos7 data]# ll
total 4
-rw-r--r-- 1 root root 2834 Nov  7 19:53 students.sql
#删除students表
mysql> drop table students;
Query OK, 0 rows affected (0.00 sec)
#查看students表是否存在
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| teachers          |
| toc               |
+-------------------+
6 rows in set (0.00 sec)

mysql> 
##用备份还原数据库
mysql> source /data/students.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

##查看还原的数据
mysql>  select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

备份数据库：

mysqldump -uroot -p123456  hellodb > /data/hellodb.sql
## 删除整个数据库以后没有数据库不能还原，需要先新建数据库，新建数据库可以和之前的名称不一样。
###创建数据的选项（字符集，等等 ）都没加需要和之前一样才能算是完全还原。
mysql>
mysql>create database hellodb2
mysql> use hellodb2
mysql> suorce > /data/hellodb.sql
##完全备份某些数据库需使用-B选项
-B, --databases db_name…  #指定备份的数据库，包括create database语句
用法：mysqldump  -uroot -p123456   -B [数据库1] [数据库1]
[root@centos7 ~] mysqldump   -uroot -p123456 -B  hellodb > /data/hellodb.sql
##还原
mysql> suorce > /data/hellodb.sql
√##完全备份数据库需使用-A选项（包含系统数据库sys，infoXXXX，performance_schema，包用户权限的mysql数据库）
-A, --all-databases #备份所有数据库，含create database
[root@centos7 ~] mysqldump -uroot -p123456 -A > /data/alldatabases.sql

InnoDB建议备份策略

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1
--flush-privileges  --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

20. 编写crontab，每天按表备份所有mysql数据。将备份数据放在以天为时间的目录下。

[root@centos7 ~]# systemctl status crond
● crond.service - Command Scheduler
   Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2023-11-07 12:10:01 CST; 9h ago
 Main PID: 540 (crond)
   CGroup: /system.slice/crond.service
           └─540 /usr/sbin/crond -n
[root@centos7 cron.d]# pwd
/etc/cron.d
[root@centos7 cron.d]# cat backupmysql-oneday 
SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
MAILTO=root
0 1 * * * root  mkdir -p /backup/mysql/`date +\%F`&& mysqldump -uroot -p123456  -A  >/backup/mysql/`date +\%F`/mysql-full.sql


生产建议使用如下命令：
mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1
--flush-privileges  --hex-blob >/backup/mysql/`date +\%F`/mysql-full.sql








### 21. 编写crontab, 基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份。

```bash
[root@centos7 cron.d]# cat backupmysql-week
SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
MAILTO=root
0 1 * * 1,5  root  xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
0 1 * * 2-4  root  xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

22. 总结mysql主从复制原理。

在这里插入图片描述

主从复制相关线程
主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放

23. 实现mysql主从复制，主主复制，半同步复制，过滤复制，

主服务器：
[root@centos7 ~]# cat /etc/my.cnf
[mysql]
server-id=48
##二进制日志mysql8.0以上默认开启，其他版本需手动开启；
其他版本开启二进制日志的配置：
# sql_log_bin=ON
# log_bin=/data/binlog/mysql-binlog

重启 mysql

[mysql]
新建账号用于从节点与主节点链接：
mysql> create user repluser@'192.168.44.%' identified by '123456';
##由于8.0启用了sh2的身份插件，用简单密码会导致主从I/O线程不同步，建议使用复杂密码；
详见：https://blog.csdn.net/GreatSQL2021/article/details/127647735
mysql> grant replication slave on *.* to repluser@'192.168.44.%';
## 授权从节点的授权  也可以all 但是权限太大不安全。


##查看主机点的二进制的位置（用于从节点的二进制的同步的起始位置）：
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000019 |      882 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
##完全备份主数据库的数据：
[root@centos7 ~]#mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1
--flush-privileges --hex-blob  >/data/fullbak_`date +\%F`.sql

 #将备份的文件拷贝至从节点的下。
 
rsync -a /data/fullbak_`date +\%F`.sql   192.168.44.58:/data/



从节点服务器：
[root@centos7 ~]# cat /etc/my.cnf
[mysql]
server-id=58
##二进制日志mysql8.0以上默认开启，其他版本需手动开启；
其他版本开启二进制日志的配置：
# sql_log_bin=ON
# log_bin=/data/binlog/mysql-binlog
重启 mysql
#准备导入主节点完全备份的数据文件
[root@centos7 ~]# grep ^CHANGE MASTER TO *  /data/full.sql
/data/full.sql:CHANGE MASTER TO MASTER_LOG_FILE='binlog.000019', MASTER_LOG_POS=156;
## 完全备份在binlog.000019', MASTER_LOG_POS=156，之后的需要从主节点的二进制文件自动同步。
[root@centos7 ~]# mysql -uroot -p123456 >/data/full.sql
mysql>show databases；
mysql>show  count（*）
看下数据是否全部导入
mysql>CHANGE MASTER TO
  MASTER_HOST='192.168.44.48',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=156,
  MASTER_CONNECT_RETRY=5;
4) start slave;
查看从节点是否开启了I/O线程和sql线程。
mysql> SHOW slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.44.48
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000019
          Read_Master_Log_Pos: 882
               Relay_Log_File: centos7-relay-bin.000004
                Relay_Log_Pos: 1048
        Relay_Master_Log_File: binlog.000019
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0