mysql数据库-笔记

基本概念篇

SQL语言的4种分类（DDL、DML、DCL、DQL）

对应的英文全程：data （definition、manipulation、control、query）language

参考资料：

https://www.cnblogs.com/kawashibara/p/8961646.html

总结：

DDL操作对象是库表

DML、DQL操作对象是记录

DCL操作对象是用户

DDL包括：create，drop，alter

create database/table/index/view

drop database/table/index/view

alter table

create database qa_test;

create database qa_test2;

create database qa_test3;

create table qa_test.t1(id int);

create table qa_test.t2(id int primary key, id2 int);

create table qa_test.t3(id int, id2 int, id3 int);

create index idx_a on qa_test.t1(id);

create unique index idx_a on qa_test.t3(id3);

create view see_t1 as select * from qa_test.t1;

alter  table qa_test.t1 drop index idx_a;

alter  table qa_test.t1 add primary key(id);

alter  table qa_test.t1 add id2 int;

alter  table qa_test.t1 add id3 int;

alter  table qa_test.t2 drop primary key(id);

drop table qa_test.t2;

drop database qa_test2;

drop view see_t1;

DML包括：insert，delete，update

insert into ... values ...

insert into ... select * from ...

delete from .. where

delete from ...

update ... set ... where ...

update ... set ...

create database qa_ddl;

create table qa_ddl.t(id int);

insert into qa_ddl.t values(1),(2),(3);

update qa_ddl.t set id = 10 where id >2;

delete from qa_ddl.t where id = 1;

create table qa_ddl.t2(id int, id2 int, id3 int);

insert into qa_ddl.t2 values(1,2,3),(2,3,4);

insert into qa_ddl.t2 select * from qa_ddl.t2;

insert into qa_ddl.t2(id, id2) values (100,200);

DCL包括：grant，revoke，commit，rollback

DQL包括：select，from，where

select * from ... where ...

create database qa_ddl;

create table qa_ddl.t(id int);

insert into qa_ddl.t values(1),(2),(3);

update qa_ddl.t set id = 10 where id >2;

delete from qa_ddl.t where id = 1;

create table qa_ddl.t2(id int, id2 int, id3 int);

insert into qa_ddl.t2 values(1,2,3),(2,3,4);

insert into qa_ddl.t2 select * from qa_ddl.t2;

insert into qa_ddl.t2(id, id2) values (100,200);

select * from qa_ddl.t2;

数据库的log

分类5个：err-log，general-log，bin-log，slow-log，relay-log

有时候还有DDL log

参考资料：

https://www.cnblogs.com/f-ck-need-u/p/9001061.html#blog5

https://dev.mysql.com/doc/refman/5.7/en/server-logs.html（mysql官方文档）

这5种log都是什么用途？（下图摘自mysql官方文档）

binlog在什么时候更新？

mysql>flush logs；执行后，关闭旧的binlog，打开新的binlog。

my.cnf中的log参数配置

#=============== [error/general/slow log options] ==============================================

log-error-verbosity                     = 1

log-error                               = /usr/local/nestdb_master/log/mysql.err      error-log的位置

general-log                             = ON                                          允许记录general-log

general-log-file                        = /usr/local/nestdb_master/log/mysql.log      general-log的位置

slow-query-log                          = ON                                          允许记录error-log

slow-query-log-file                     = /usr/local/nestdb_master/log/slow.log       error-log的位置

long-query-time                         = 1              查询时间，如果查询小于等于1s，记录到general-log，如果大于1s，记录到error-log

log-queries-not-using-indexes           = 1

mysql> show global variables like 'log_warnings';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_warnings  | 0     |     0：warning信息不记录到error-log中；1：warning信息记录到error-log中；大于1：各类告警信息写入到error-log中。

+---------------+-------+

1 row in set (0.01 sec)

bin-log配置

#------------------> (2) binlog <---------------------------------------------------------------

log-bin                                 = mysql-bin          bin-log的前缀，当每次flush logs；重启mysql，日志文件达到最大时，都会重新更新bin-log

log-bin-index                           = mysql-bin.index    记录了所有bin-log的文件名

sync-binlog                             = 1000

binlog-format                           = ROW

binlog-cache-size                       = 1M

max-binlog-size                         = 1G     bin-log文件最大值，超过1G，变更bin-log

expire-logs-days                        = 7

查看bin-log

[root@vm10-0-0-8 bin]# ./mysqlbinlog   ../var/mysql-bin.000005

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#191114 16:53:43 server id 9988123  end_log_pos 123 CRC32 0xb80d9c2f    Start: binlog v 4, server v 5.7.27-debug-log created 191114 16:53:43

BINLOG '

FxbNXQ8baJgAdwAAAHsAAAAAAAQANS43LjI3LWRlYnVnLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AS+cDbg=

'/*!*/;

# at 123

#191114 16:53:43 server id 9988123  end_log_pos 154 CRC32 0x0020c8cb    Previous-GTIDs

# [empty]

# at 154

#191115 13:17:42 server id 9988123  end_log_pos 219 CRC32 0x80e8fd47    Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 219

#191115 13:17:42 server id 9988123  end_log_pos 313 CRC32 0x4219fe85    Query   thread_id=73    exec_time=0     error_code=0

SET TIMESTAMP=1573795062/*!*/;

SET @@session.pseudo_thread_id=73/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1436549152/*!*/;

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=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

create database lr_1

/*!*/;

# at 313

#191115 13:17:53 server id 9988123  end_log_pos 378 CRC32 0xbfbb5fc0    Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 378

#191115 13:17:53 server id 9988123  end_log_pos 478 CRC32 0x8217330c    Query   thread_id=73    exec_time=0     error_code=0

SET TIMESTAMP=1573795073/*!*/;

create table lr_1.test(id int)

/*!*/;

# at 478

#191115 13:18:08 server id 9988123  end_log_pos 543 CRC32 0xf0bb2b47    Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 543

#191115 13:18:08 server id 9988123  end_log_pos 611 CRC32 0xdf8eb376    Query   thread_id=73    exec_time=0     error_code=0

SET TIMESTAMP=1573795088/*!*/;

BEGIN

/*!*/;

# at 611

#191115 13:18:08 server id 9988123  end_log_pos 658 CRC32 0xef41195b    Table_map: `lr_1`.`test` mapped to number 109

# at 658

#191115 13:18:08 server id 9988123  end_log_pos 698 CRC32 0xde1f9e3c    Write_rows: table id 109 flags: STMT_END_F

BINLOG '

EDXOXRMbaJgALwAAAJICAAAAAG0AAAAAAAEABGxyXzEABHRlc3QAAQMAAVsZQe8=

EDXOXR4baJgAKAAAALoCAAAAAG0AAAAAAAEAAgAB//4BAAAAPJ4f3g==

'/*!*/;

# at 698

#191115 13:18:08 server id 9988123  end_log_pos 729 CRC32 0x0884f90f    Xid = 63

COMMIT/*!*/;

# at 729

#191115 13:18:19 server id 9988123  end_log_pos 776 CRC32 0x7e8d61f6    Rotate to mysql-bin.000006  pos: 4

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

巴特西

mysql数据库-笔记

基本概念篇

SQL语言的4种分类（DDL、DML、DCL、DQL）

最新文章

热门文章