mysql

最新推荐文章于 2023-07-22 18:14:59 发布

不赖床会死星人

最新推荐文章于 2023-07-22 18:14:59 发布

阅读量122

点赞数

分类专栏：个人笔记

本文链接：https://blog.csdn.net/panqicofco/article/details/84564453

版权

个人笔记专栏收录该内容

20 篇文章 0 订阅

订阅专栏

安装方式：

1、源代码：编译安装

2、二进制格式：展开至特定路径，并经简单配置后使用

3、程序包管理器管理的程序包

二进制安装：

内连接：

左外连接：

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。

左(外)连接，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL

右外连接：

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。

与左(外)连接相反，右(外)连接，左表只会显示符合搜索条件的记录，而右表的记录将会全部表示出来。左表记录不足的地方均为NULL

完全外连接：

UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。
UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。
同时，每条 SELECT 语句中的列的顺序必须相同.
SQL UNION 语法：
sql脚本代码如下:

SELECT column_name FROM table1

UNION

SELECT column_name FROM table2

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行

union:联合的意思，即把两次或多次查询结果合并起来。
要求：两次查询的列数必须一致

SQL UNION ALL 语法
sql脚本代码如下:

SELECT column_name FROM table1

UNION ALL

SELECT column_name FROM table2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同

执行流程：

MariaDB [hellodb]> select classid,count(*) from students where classid is not null group by classid;                                     
+---------+----------+
| classid | count(*) |
+---------+----------+
|       1 |        4 |
|       2 |        3 |
|       3 |        4 |
|       4 |        4 |
|       5 |        1 |
|       6 |        4 |
|       7 |        3 |
+---------+----------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> select gender,sum(stuage) from students group by gender;                                                              
+--------+-------------+
| gender | sum(stuage) |
+--------+-------------+
| M      |         495 |
| F      |         220 |
+--------+-------------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> select classid,avg(stuage) from students where classid is not null group by classid having avg(stuage) > 25;          
+---------+-------------+
| classid | avg(stuage) |
+---------+-------------+
|       2 |     36.0000 |
|       5 |     46.0000 |
+---------+-------------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> select gender,sum(stuage) from ( select classid,stuage,gender from students) as s where s.stuage > 25 group by gender;
+--------+-------------+
| gender | sum(stuage) |
+--------+-------------+
| M      |         317 |
| F      |          30 |
+--------+-------------+
2 rows in set (0.00 sec)

MariaDB [mysql]> set password for pqq@'192.168.1.166'=password('140903');
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> update mysql.user set password=password('100908') where user='root';      
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql]> grant select,update on hellodb.students to pqqpqq@'192.168.1.%';
Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql]> grant all on *.* to pqq@'192.168.1.166';                                   
Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> revoke select,update on hellodb.students from pqqpqq@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)     取消先前所赋权限

MariaDB [(none)]> flush privileges;                                                  
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for pqqpqq@'192.168.1.%';                              
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for pqqpqq@192.168.1.%                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'pqqpqq'@'192.168.1.%' IDENTIFIED BY PASSWORD '*00DE453221329E3C1A54CFB834B6488BFFC5633C' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查询缓存

MariaDB [(none)]> show variables like 'query_cache_min_res_unit';  
#查询缓存中内存块的最小分配单位，默认4k
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| query_cache_min_res_unit | 4096  |
+--------------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'query_cache_type'; #是否开启缓存
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show global status like 'Qcache%';  #查询缓存相关状态
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33536824 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

MariaDB [(none)]> show variables like 'query_cache_size';
#查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 33554432 |
+------------------+----------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select name from students where stuid=2;
+------------+
| name       |
+------------+
| Shi Potian |
+------------+
1 row in set (0.01 sec)

MariaDB [hellodb]> show global status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33535288 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 1        |   #将查询记录插入  此值加1 
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> select name from students where stuid=2;
+------------+
| name       |
+------------+
| Shi Potian |
+------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> show global status like 'Qcache%';      
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33535288 |
| Qcache_hits             | 1        |   #这条查询命中缓存，此值加1
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

命中率计算 4/(24+4)

1、单进程多线程

2、mysql协议连接到服务端

3、先看看缓存中有没有，有就直接返回

那么查询这个操作就结束了，一般来说查询的命中率有3成就可以了

不过有时可能命中只有一成，但那一成是一个非常复杂的查询命令也是可以的

判断好坏要以开销比（资源消耗）

4、没有命中的话

5、解析器，语法分析

6、有时需要预处理器的配合，生成解析树

生成多条路可走（那应该走哪条，理论上是语句最简单的）

7、应该走开销最小的路径（但未必是语句最简单的（堵））

8、那谁来选择走——查询优化器（它还能可能会改写）

9、然后给执行计划，因为有多个线程，但引擎只有一个，要有队列

10、查询执行引擎执行——转换成对应的存储引擎的API调用

11、对应的存储引擎到磁盘上获取数据，再返还给执行引擎，再返还给客户端

12、同时判断下是否需要缓存

MariaDB [hellodb]> rename table coc to coc111;  #改表名
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> alter table coc111 engine=MyISAM;  #改引擎
Query OK, 14 rows affected (0.02 sec)              
Records: 14  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> show table status like 'coc111'\G   #查看表状态
*************************** 1. row ***************************
           Name: coc111
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 14
 Avg_row_length: 8
    Data_length: 112
Max_data_length: 2251799813685247
   Index_length: 2048
      Data_free: 0
 Auto_increment: 15
    Create_time: 2018-11-28 21:46:27
    Update_time: 2018-11-28 21:46:27
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)


MariaDB [hellodb]> alter table students add index(Name);   #增加索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> show indexes from students;  #查看索引
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | StuID       | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | Name     |            1 | Name        | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> alter table students drop Name;     #删除索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> alter table students modify gender ENUM('M','F') not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0           #修改字段属性

MariaDB [hellodb]> alter table students change Age StuAge tinyint(3) unsigned;
Query OK, 0 rows affected (0.03 sec)             #修改字段名
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> desc students;                         
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| StuID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| StuAge    | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender    | enum('M','F')       | NO   |     | NULL    |                |
| ClassID   | tinyint(3) unsigned | YES  |     | NULL    |                |
| TeacherID | int(10) unsigned    | YES  |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

MariaDB [hellodb]> create table test (name varchar(50) not null,   #创建一个测试表
    -> age tinyint unsigned not null,
    -> primary key(name,age));
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> desc test;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(50)         | NO   | PRI | NULL    |       |
| age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

一般先第三种，再第二种

先复制结构，再复制数据

第二种方式只是复制了数据，结构没有复制

MariaDB [hellodb]> desc toc;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| ID       | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| CourseID | smallint(5) unsigned | YES  |     | NULL    |                |
| TID      | smallint(5) unsigned | YES  |     | NULL    |                |
| phone    | tinyint(3) unsigned  | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> alter table toc modify phone bigint not null;    #修改phone字段属性   
Query OK, 0 rows affected (0.04 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> insert toc (ID,phone)values('9527','13160180088');
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> select id,phone from toc where id = '9527';
+------+-------------+
| id   | phone       |
+------+-------------+
| 9527 | 13160180088 |
+------+-------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> alter table toc add version char(8) not null after id;
Query OK, 0 rows affected (0.03 sec)        #具体在哪个字段后面
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> desc toc;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| ID       | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| version  | char(8)              | NO   |     | NULL    |                |
| CourseID | smallint(5) unsigned | YES  |     | NULL    |                |
| TID      | smallint(5) unsigned | YES  |     | NULL    |                |
| phone    | bigint(20)           | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

MariaDB [hellodb]> alter table toc drop version;  #删除字段
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
+---------+----------------+----------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> select class from classes where NumOfStu between 10 and 20;                    
+---------------+
| class         |
+---------------+
| Shaolin Pai   |
| QingCheng Pai |
| Wudang Pai    |
| Xiaoyao Pai   |
+---------------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> select class from classes where NumOfStu in (11,13,12);    
+---------------+
| class         |
+---------------+
| QingCheng Pai |
| Wudang Pai    |
+---------------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> select class from classes where class like 'M%';       
+-----------+
| class     |
+-----------+
| Ming Jiao |
+-----------+
1 row in set (0.00 sec)

ASC升序

DESC降序

MariaDB [hellodb]> select gender,sum(stuage) from students group by gender;
+--------+-------------+
| gender | sum(stuage) |     #group by 分组 以Gender统计F，M共有多少个
+--------+-------------+
| M      |         495 |
| F      |         220 |
+--------+-------------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> select classid,count(stuid) from students group by classid;               
+---------+--------------+
| classid | count(stuid) |
+---------+--------------+    #以班组ID，统计各班人数	
|    NULL |            3 |
|       1 |            4 |
|       2 |            3 |
|       3 |            4 |
|       4 |            4 |
|       5 |            1 |
|       6 |            4 |
|       7 |            3 |
+---------+--------------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> select classid,count(stuid) from students group by classid having count(classid) >= 4;
+---------+--------------+
| classid | count(stuid) |   #以班组ID，统计各班人数大于等4的
+---------+--------------+
|       1 |            4 |
|       3 |            4 |
|       4 |            4 |
|       6 |            4 |
+---------+--------------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> select * from students limit 2,3
    -> ;                                              # 从第二行之后取三行
+-------+--------+--------+---------+-----------+
| StuID | StuAge | gender | ClassID | TeacherID |
+-------+--------+--------+---------+-----------+
|     3 |     53 | M      |       2 |        16 |
|     4 |     32 | M      |       4 |         4 |
|     5 |     26 | M      |       3 |         1 |
+-------+--------+--------+---------+-----------+
3 rows in set (0.00 sec)

MariaDB [hellodb]> select stuid as iddd from students;
+------+
| iddd |  #取别名只限于你这一次查询，并不是在数据库里永久的把这一列增加了名称
+------+
|    1 |
|    2 |
|    3 |
+------+

AVG函数求平均值

取年龄大于平均值的人


MariaDB [hellodb]> select s.stuid,s.stuage,s.gender from (select * from students where gender='M') as s where s.stuage > 25;
+-------+--------+--------+
| StuID | StuAge | gender |
+-------+--------+--------+
|     3 |     53 | M      |
|     4 |     32 | M      |
|     5 |     26 | M      |
|     6 |     46 | M      |
|    13 |     33 | M      |  #性别为M，且年龄大于25岁的
|    24 |     27 | M      |
|    25 |    100 | M      |
+-------+--------+--------+
7 rows in set (0.00 sec)

插入字段、数据

MariaDB [hellodb]> select count(*) from students where stuage >25;
+----------+
| count(*) |   #统计年龄大于25的出现次数
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select sum(stuage) from students where stuage >25;         
+-------------+
| sum(stuage) |  #统计年龄大于25之和
+-------------+
|         347 |
+-------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select classid,count(stuid) from students where classid is not null group by classid;
+---------+--------------+
| classid | count(stuid) |
+---------+--------------+
|       1 |            4 |
|       2 |            3 |
|       3 |            4 |
|       4 |            4 |
|       5 |            1 |
|       6 |            4 |
|       7 |            3 |
+---------+--------------+
7 rows in set (0.01 sec)
 
MariaDB [hellodb]> select classid,count(stuid) from students where classid is not null group by classid having count(stuid) >=3;    
+---------+--------------+
| classid | count(stuid) |
+---------+--------------+
|       1 |            4 |
|       2 |            3 |
|       3 |            4 |
|       4 |            4 |
|       6 |            4 |
|       7 |            3 |
+---------+--------------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> select distinct stuage from students where stuage > 45;
+--------+
| stuage |
+--------+  #不显示重复的
|     53 |
|     46 |
|    100 |
+--------+
3 rows in set (0.00 sec)

MariaDB [hellodb]> select * from students as s,classes as c where s.classid = c.classid;
+-------+--------+--------+---------+-----------+---------+----------------+----------+
| StuID | StuAge | gender | ClassID | TeacherID | ClassID | Class          | NumOfStu |
+-------+--------+--------+---------+-----------+---------+----------------+----------+
|     1 |     22 | M      |       2 |         3 |       2 | Emei Pai       |        7 |
|     2 |     22 | M      |       1 |         7 |       1 | Shaolin Pai    |       10 |
+-------+--------+--------+---------+-----------+---------+----------------+----------+
# 第一张表的classid与第二张表的classid匹配

MariaDB [hellodb]> select s.stuid,c.class from students as s,classes as c where s.classid = c.classid;  
+-------+----------------+
| stuid | class          |
+-------+----------------+
|     1 | Emei Pai       |
|     2 | Shaolin Pai    |
|     3 | Emei Pai       |
|     4 | Wudang Pai     |
|     5 | QingCheng Pai  |
+-------+----------------+
# 表名加字段 stu表的name字段 classes表的class字段

联合查询合并成一个表输出显示

MariaDB [hellodb]> create view view_stu as select * from students where stuage >= 45;
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> select * from view_stu;
+-------+--------+--------+---------+-----------+
| StuID | StuAge | gender | ClassID | TeacherID |
+-------+--------+--------+---------+-----------+
|     3 |     53 | M      |       2 |        16 |
|     6 |     46 | M      |       5 |      NULL |
|    25 |    100 | M      |    NULL |      NULL |
+-------+--------+--------+---------+-----------+
3 rows in set (0.00 sec)

MariaDB [hellodb]> drop view view_stu;
Query OK, 0 rows affected (0.00 sec)

执行操作时施加的锁模式：

读锁：共享锁

写锁：独占锁，排它锁

锁粒度：

表锁：table lock 锁定整张表

行锁：row lock 锁定需要的行

粒度越小，开销越大，并发性能越好

粒度越大，开销越小，并以性能越差

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
+---------+----------------+----------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> lock tables classes read;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> insert into classes value (10,'hotwing',41); 
ERROR 1099 (HY000): Table 'classes' was locked with a READ lock and can't be updated
创建读锁，再写就阻塞了（可以读） 不过一旦取消锁，就立刻修改成功了 （需在另一个窗口操作）

MariaDB [hellodb]> lock tables classes write;  
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from classes;
如果把锁改成write锁，就排他，别人看都看不了

新建一张表--把原来数据导进入--删除原来的--再把新表改回原来名字

备份时，要手动对表施加读锁

MariaDB [hellodb]> start transaction; 启动一个事务
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select database();
+------------+
| database() |
+------------+
| hellodb    |
+------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|      10 | hotwing        |       41 |
+---------+----------------+----------+
9 rows in set (0.00 sec)

MariaDB [hellodb]> delete from classes where classid = 10;  删除一行
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from classes;     查看没有了           
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
+---------+----------------+----------+
8 rows in set (0.00 sec)

但是通过另一个窗口查看 内容还在，因为目前还在事务中，没有提交（MariaDB [hellodb]> commit;）
如果提交了，大家内容就都一样了
MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|      10 | hotwing        |       41 |
+---------+----------------+----------+
9 rows in set (0.00 sec)

当然也可以回滚
MariaDB [hellodb]> rollback;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|      10 | hotwing        |       41 |
+---------+----------------+----------+
9 rows in set (0.00 sec)

如果你删除了好多，想回滚，它会全部恢复，这样力度太多了

可以定义保存点

MariaDB [hellodb]> start transaction;                     
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delete from classes where classid = 4;
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> savepoint a;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delete from classes where classid = 10;
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
+---------+----------------+----------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> rollback to a;  回滚到保存点a之后，恢复classid=10
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|      10 | hotwing        |       41 |
+---------+----------------+----------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> commit;  提交     保存点a之前的生效 classid=4 删除生效
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|      10 | hotwing        |       41 |
+---------+----------------+----------+
8 rows in set (0.00 sec)

mysql日志

记录查询日志

MariaDB [hellodb]> set global general_log='on';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> show global variables like '%log%';

general_log                               | ON  


[root@mysql-6 mysql]#ls
aria_log.00000001  ib_logfile1        mysql-6.pid       mysql-bin.000006  mysql-bin.000012
aria_log_control   ibtmp1             mysql-bin.000001  mysql-bin.000007  mysql-bin.000013
hellodb            multi-master.info  mysql-bin.000002  mysql-bin.000008  mysql-bin.index
ib_buffer_pool     mysql              mysql-bin.000003  mysql-bin.000009  performance_schema
ibdata1            mysql-6.err        mysql-bin.000004  mysql-bin.000010  test
ib_logfile0        mysql-6.log        mysql-bin.000005  mysql-bin.000011  testdb1
[root@mysql-6 mysql]#cat mysql-6.log 
/usr/local/mysql/bin/mysqld, Version: 10.2.18-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
181130 17:01:47    12 Query     show global variables like '%log%'

MariaDB [hellodb]> set global log_output='table';       
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from toc;
+------+----------+------+-------------+
| ID   | CourseID | TID  | phone       |
+------+----------+------+-------------+
| 9527 |     NULL | NULL | 13160180088 |
+------+----------+------+-------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select * from mysql.general_log; #设置成记录到表里
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| event_time                 | user_host                 | thread_id | server_id | command_type | argument                        |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| 2018-11-30 17:09:24.252644 | root[root] @ localhost [] |        12 |         1 | Query        | select * from toc               |
| 2018-11-30 17:09:37.720222 | root[root] @ localhost [] |        12 |         1 | Query        | select * from mysql.general_log |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
2 rows in set (0.00 sec)

慢查询日志

slow_query_log = ON
log_output = file
slow_query_log_file = /data/mysql/mysql-6-slow.log
long_query_time = 10

MariaDB [hellodb]> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

MariaDB [hellodb]> show variables like 'slow%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_launch_time    | 2                            |
| slow_query_log      | ON                           |
| slow_query_log_file | /data/mysql/mysql-6-slow.log |
+---------------------+------------------------------+
3 rows in set (0.00 sec)

日志文件：六类

一般查询日志 log, general_log, log_output

慢查询日志

错误日志

二进制日志

中继日志

事务日志

二进制日志：“修改”

position：位置

time：时间

滚动：

1、大小

2、时间

/etc/my.cnf

[mysqld]

log-bin=mysql-bin

binlog_format=mixed(混合模式：语句+行) 还有：基于语句，基于行

如果对存储空间，以及I/O没什么要求的话也可以用row格式行准确点

MariaDB [hellodb]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      342 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> flush logs;
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      385 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       389 |
| mysql-bin.000002 |       385 |
+------------------+-----------+
2 rows in set (0.00 sec)


查看二进制日志
MariaDB [hellodb]> show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                           |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc       |         1 |         256 | Server ver: 10.2.18-MariaDB-log, Binlog ver: 4 |
| mysql-bin.000002 | 256 | Gtid_list         |         1 |         299 | [0-1-157]                                      |
| mysql-bin.000002 | 299 | Binlog_checkpoint |         1 |         342 | mysql-bin.000001                               |
| mysql-bin.000002 | 342 | Binlog_checkpoint |         1 |         385 | mysql-bin.000002                               |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> insert into toc (id,courseid,tid,phone)values('9528','34','35','13160180098');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> flush logs;        
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       389 |
| mysql-bin.000002 |       652 |
+------------------+-----------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------------+-----------+-------------+----------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                                                                         |
+------------------+-----+-------------------+-----------+-------------+----------------------------------------------------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc       |         1 |         256 | Server ver: 10.2.18-MariaDB-log, Binlog ver: 4                                               |
| mysql-bin.000002 | 256 | Gtid_list         |         1 |         299 | [0-1-157]                                                                                    |
| mysql-bin.000002 | 299 | Binlog_checkpoint |         1 |         342 | mysql-bin.000001                                                                             |
| mysql-bin.000002 | 342 | Binlog_checkpoint |         1 |         385 | mysql-bin.000002                                                                             |
| mysql-bin.000002 | 385 | Gtid              |         1 |         427 | BEGIN GTID 0-1-158                                                                           |
| mysql-bin.000002 | 427 | Query             |         1 |         574 | use `hellodb`; insert into toc (id,courseid,tid,phone)values('9528','34','35','13160180098') |
| mysql-bin.000002 | 574 | Xid               |         1 |         605 | COMMIT /* xid=13 */                                                                          |
| mysql-bin.000002 | 605 | Rotate            |         1 |         652 | mysql-bin.000003;pos=4                                                                       |
+------------------+-----+-------------------+-----------+-------------+----------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

记录了刚插入数据的信息

MariaDB [hellodb]> show binlog events in 'mysql-bin.000002' from 427\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000002
        Pos: 427
 Event_type: Query
  Server_id: 1
End_log_pos: 574
       Info: use `hellodb`; insert into toc (id,courseid,tid,phone)values('9528','34','35','13160180098')
*************************** 2. row ***************************
   Log_name: mysql-bin.000002
        Pos: 574
 Event_type: Xid
  Server_id: 1
End_log_pos: 605
       Info: COMMIT /* xid=13 */

mysqlbinlog mysql-bin.000002

多久从缓冲区同步二进制数据到二进制文件中去

时间越长，性能越好，但安全性也就越差

为0表示不同步，（不基于时间来同步），仅在事务提交时同步

而默认事务提交又为ON（表示自动提交）所以也就是二进制日志也是跟着自动同步的，影响性能

建议autocommit改为OFF的原因之一

建议：切勿将二进制日志与数据文件放在同一设备

如果不想记录到二进制文件当中

sql_log_bin 会话层面修改临时的

大数据量备份，用物理备份

mysqldump --databases hellodb --lock-all-tables > /tmp/hellodb.sql

不过这样锁定表，性能不好

如果能保证底层都是innodb引擎的话：

先把所有的缓存同步到磁盘上，并施加读锁

滚动日志(看看记录到哪个二进制文件上的什么位置了，以后只要恢复这之后的就行)

进行备份

mysqldump --databases hellodb > /tmp/hellodb.sql

释放锁

也可以用这一条命令:

[root@mysql-6 mysql]#mysqldump --databases hellodb --lock-all-tables --flush-logs --master-data=2 > /tmp/hdb.sql

如果都是Innodb引擎：热备，如下

[root@mysql-6 mysql]#mysqldump --databases hellodb --single-transaction --flush-logs --master-data=2 > /tmp/hdb1.sql

模拟一次即时点恢复

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
先看下，目前二进制日志到哪了

mysqldump --databases hellodb --lock-all-tables --flush-logs --master-data=1 > /tmp/hdb.sql

进行备份

MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> create table newtable(name char(33));
Query OK, 0 rows affected (0.02 sec)

MariaDB [hellodb]> insert into newtable values('tom'),('jerry');  
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> drop database hellodb;
Query OK, 8 rows affected (0.06 sec)

再模拟创建表，插入表，误删除库，准备恢复（完全+即时）

[root@mysql-6 mysql]#cat /tmp/hdb.sql
-- MySQL dump 10.16  Distrib 10.2.18-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: hellodb
-- ------------------------------------------------------
-- Server version       10.2.18-MariaDB-log

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=371;

查看刚刚备份的文件，发现目前已经备份到了mysql-bin.000002的371位置之后

[root@mysql-6 mysql]#mysqlbinlog --start-position=371 mysql-bin.000002
# at 413
#181201 14:31:53 server id 1  end_log_pos 519 CRC32 0x06d8ad01  Query   thread_id=10    exec_time=0     error_code=0
use `hellodb`/*!*/;
SET TIMESTAMP=1543645913/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table newtable(name char(33))
/*!*/;
# at 519
#181201 14:32:40 server id 1  end_log_pos 561 CRC32 0x0b0db5ca  GTID 0-1-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
BEGIN
/*!*/;
# at 561
#181201 14:32:40 server id 1  end_log_pos 675 CRC32 0x5bd7f512  Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1543645960/*!*/;
insert into newtable values('tom'),('jerry')
/*!*/;
# at 706
#181201 14:33:00 server id 1  end_log_pos 748 CRC32 0x9345e9e9  GTID 0-1-3 ddl
/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
# at 748  ##############这句就不用再恢复了，恢复到这之前就行了
#181201 14:33:00 server id 1  end_log_pos 839 CRC32 0xeb498401  Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1543645980/*!*/;
drop database hellodb

[root@mysql-6 mysql]#mysqlbinlog --start-position=371 --stop-position=706 mysql-bin.000002 > /tmp/hellodb.2.inc.sql

将000002文件的371之后到706之间的内容重定向出来

开始恢复：先进Mysql，关掉二进制日志

MariaDB [(none)]> set session sql_log_bin=0;   #会话层面
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> source /tmp/hdb.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)

MariaDB [hellodb]> source /tmp/hellodb.2.inc.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)

MariaDB [hellodb]> set session sql_log_bin=1;    #再把二进制日志开启  
Query OK, 0 rows affected (0.00 sec)

思考：如果完全备份后的误删，不是删的库而是只删了表，应该就只要还原二进制文件就行

MariaDB [hellodb]> show master status;             
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      385 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> create table cao(name char(22));
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> create table kao(name char(22));
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> insert into cao values('gg');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> drop table cao;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> drop table kao;              
Query OK, 0 rows affected (0.01 sec)

做一些误操作

然后将要恢复的二进制数据重定向出来

[root@mysql-6 mysql]#mysqlbinlog --start-position=385 mysql-bin.000007
[root@mysql-6 mysql]#mysqlbinlog --start-position=385 --stop-position=842 mysql-bin.000007 > /tmp/hellodb.7.inc.sql

再开始恢复

MariaDB [hellodb]> set session sql_log_bin=0;      
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> source /tmp/hellodb.7.inc.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)

MariaDB [hellodb]> set session sql_log_bin=1;    
Query OK, 0 rows affected (0.00 sec)

建议：关闭二进制日志，关闭其他用户连接

备份策略：基于mysqldump

备份：mysqldump+二进制日志文件

周日做一次完全备份：备份的同时，滚动日志

周一至周六：备份二进制日志；

恢复：完全备份+各二进制日志文件中至此刻的事件

mysqladmin flush-logs

不赖床会死星人

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql

安装方式：1、源代码：编译安装2、二进制格式：展开至特定路径，并经简单配置后使用3、程序包管理器管理的程序包二进制安装：内连接：左外连接：left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。左(外)连接，左表...
复制链接

扫一扫