看了这篇MySQL，开发功力又升级

一、MySQL架构

1）MySQL简介

MySQL是一个关系型数据库管理系统，由瑞典MYSQL AB公司开发，目前属于Oracle公司。
MySQL 是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库中，这样就增加了速度并提高了灵活性。
Mysql是开源的，是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。
MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
MySQL支持大型数据库，支持5000条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。

2）MySQL配置文件

• binlog(二进制日志)
  用于主从复制及备份恢复：binlog中存放了所有操作记录，可用于恢复。相当于Redis中的AOF，my.ini中binlog配置（默认是关闭的）如何开启：

[mysqld]
log-bin = mysql-bin
binlog-format = row
复制代码

• Error log(错误日志)
  默认是关闭的，通常用于记录数据库服务端启动、重启、主从复制时，记录错误，将日志详情保留在文件中，方便DBA、运维开发人员阅读。如何开启：

[mysqld]
log-error=/data/mysql_error.log
复制代码

• 慢查询日志log
  默认是关闭的。记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗系统资源的。如何开启：

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log     //linux
long_query_time = 1
复制代码

• 数据文件
  windows：
  ..\mysql-8.0.19-winx64\data目录下存储数据库文件linux：
  默认路径/var/lib/mysql（可在配置文件中更改/usr/share/mysql/下的my-huge.cnf）每个目录代表一个同名的库。Myisam存放方式：
• frm文件（framework）：存放表结构
• myd文件（data）：存放表数据
• myi文件（index）：存放表索引
  innodb存放方式：
• ibdata1：Innodb引擎将所有表的数据都存放在这里面/usr/share/mysql/ibdata1而frm文件存放在库同名的包下
• frm文件：存放表结构
• 配置方式

windows：my.ini 配置文件

linux：my.cnf 配置文件
3）MySQL的用户与权限管理

• MysSQL用户管理

1.创建用户
create user cbuc identified by '123456'

2.关于 user 表
select host,user,select_priv,insert_priv,drop_priv from mysql.user;

host： 表示连接类型
user：表示用户名
select_priv，insert_priv，drop_priv等：该用户所拥有的权限

3.设置密码

--- 修改当前用户的密码
set password = password('123456')
--- 修改某个用户的密码
update mysql.user set password = password('123456') where user = 'cbuc'

4.修改用户
--- 修改用户名：
update mysql.user set user = 'cbuc' where user='c1';
--- 所有通过user表修改后必须用该命令才能生效
flush privileges;

5.删除用户

--- 不要通过delete from user t1 where t1.user='cbuc'进行删除，系统会有残留信息保留
drop user cbuc;

• 权限管理

1.授予权限：

--- 如果发现没有该用户，则会直接创建一个用户
grant 权限1，权限2,…,权限n on 数据库名.表名 to 用户名@用户地址 identified by '密码'
--- 给cbuc用户赋予对表增删改查的权限
grant select,insert,delete,update on db_crm.* to cbuc@localhost;

2.收回权限：

--- 如果已赋全库的表，就回收全库全表的所有权限
revoke 权限1，权限2,…,权限n on 数据库名.表名 from 用户名@用户地址
revoke all privileges on mysql.* from cbuc@localhost

3.收回权限：

--- 查看当前用户权限
show grants;
--- 查看某用户的全局权限
select * from mysql.user;
--- 查看某用户的某库的权限
select * from mysql.db;
--- 查看某用户的某个表的权限
select * from mysql.tables_priv;

4）MySQL其他配置

• 大小写问题

windows系统默认是大小写不敏感，但是linux系统是大小写敏感的。

0（默认）： 大小写敏感

1： 大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘中，对于sql语句都是转换为小写对表的DB进行查找。

2： 创建的表和DB依据语句上格式存放，凡是查找都是转换为小写进行

SHOW VARIABLES LIKE '%lower_case_table_names%';

 

设置：

set lower_case_table_names = 1;   #此变量是只读权限，需要在配置文件中修改
复制代码

1. 在my.inni / my.cnf中添加

    [mysqld]
    lower_case_table_names = 1 
复制代码

1. 重启服务器（重启前要将原来的数据库和表转换为小写，否则更改后将找不到数据库名）

• sql_mode

sql_mode 是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的， 比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。

常用设置：

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
复制代码

5）MySQL存储引擎

• 查看引擎

show engines;
复制代码

 

可以看出默认的存储引擎是InooDB

• 各引擎简介

1.InnoDB存储引擎：

InnoDB是MySQL默认的事务型引擎，它被设计用来处理大量的短期（short-lived）事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。具有行级锁，外键，事务等优势，适合高并发情况。

2.MyISAM存储引擎：

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁（MyISAM改表时会将整个表全锁住），缺陷：崩溃后无法安全恢复。

3.Archive引擎：

rchive存储引擎只支持 insert 和 select操作，在MySQL5.1之前不支持索引。Archive表适合日志和数据采集类引用。适合低访问量大数据等情况。

4.Blackhole引擎

Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

5.CSV引擎