MySQL的目录

1
2
3
4
5
6
7
8
9
10
11
1.auto.cnf
	MySQL启动时，会自动从data_dir/auto.cnf 文件中获取server-uuid值，并将这个值存储在全局变量server_uuid中。如果这个值或者这个文件不存在，那么将会生成一个新的uuid值，并将这个值保存在auto.cnf文件中。
	作用是在mysql复制时如果发生故障，slave可以通过uuid辨识master的日志。
2.binlog.index
	用于记录MySQL产生的binlog日志的具体位置，binlog是MySQL记录所有操作的逻辑日志，主要用于故障恢复和主从同步时使用，由于MySQL数据库是一个多类型存储引擎并存的数据库，binlog就起着一个实例（instance）总体重做信息的角色。
3.*.pem
	MySQL8.0以后加入的，用于ssl认证登陆
4.ibdata1
	innodb默认共享表空间，在不指定下默认所有表共享一个表空间，但是一般生产环境中建议修改默认值，每个表一个表空间
5.ib_logfile0
	redo事务日志

存储引擎介绍 及 比较

1
2
3
MySQL引擎： 可以理解为，MySQL的“文件系统”，只不过功能更加强大。
MySQL引擎功能： 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。

1
2
3
4
5
6
MySQL5.5以后默认使用InnoDB存储引擎，其中InnoDB和BDB提供事务安全表，其它存储引擎都是非事务安全表。

若要修改默认引擎，可以修改配置文件中的default-storage-engine。
可以通过：show variables like 'default_storage_engine'; 查看当前数据库到默认引擎。
命令：*show engines*和*show variables like 'have%'*可以列出当前数据库所支持到引擎。其中Value显示为disabled的记录表示数据库支持此引擎，而在数据库启动时被禁用。
在MySQL5.1以后，INFORMATION_SCHEMA数据库中存在一个ENGINES的表，它提供的信息与show engines;语句完全一样，可以使用下面语句来查询哪些存储引擎支持事物处理：select engine from information_schema.engines where transactions = 'yes';

MySQL自带的存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql > show engines;
+--------------------+---------+
| Engine             | Support | 
+--------------------+---------+
| FEDERATED          | NO      | 
| MRG_MYISAM         | YES     | 
| MyISAM             | YES     |	***** 
| BLACKHOLE          | YES     | 
| CSV                | YES     | 
| MEMORY             | YES     | 
| ARCHIVE            | YES     | 
| InnoDB             | DEFAULT |	***** 
| PERFORMANCE_SCHEMA | YES     | 
+--------------------+---------+

InnoDB、MyISAM (最常用的两种)

InnoDB & MyISAM 对比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
## MyIsam数据文件 frm = format
user.frm
user.MYD
user.MYI

frm是表结构，MYD是数据，MYI是索引
'.myd'是数据表的数据文件,它保存了表的结构定义信息,包括表名、字段名、数据类型、索引等


## Innodb数据文件 frm = format
slave_master_info.frm
slave_master_info.ibd

mysql中的'.frm'是指表定义,是描述表结构的文件
mysql中的'.ibd'文件是innodb存储引擎的表空间文件

InnoDB引擎

1
2
3
4
5
1.支持ACID的事务，支持事务的四种隔离级别;
2.支持行级锁及外键约束：因此可以支持写并发;
3.不存储总行数;
4.一个InnoDb擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可		能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G)，受操作系统文件大小的限制；
5.主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索的数据域存储主键的	值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

==innodb的主索引结构==

MyISAM引擎

1
2
3
4
5
1.不支持事务，但是每次查询都是原子的：
2.支持表级锁，即每次操作是对整个表加锁；：
3.存储表的总行数；
4.一个MYI1SAM表有三个文件：索引文件、表结构文件、数据文件：
5.采用菲聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本以	致，但是辅索引不用保证唯一性。

==myisam的主索引结构==

QQ_1723962882533

==两种索引数据查找过程==

QQ_1723963094964

InnoDB存储引擎设置

在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。

innodb引擎优点：

1
2
3
4
5
6
7
8
9
10
a)    事务安全（遵从ACID）
b)    MVCC（Multi-Versioning Concurrency Control，多版本并发控制）
c)    InnoDB行级锁
d)    支持外键引用完整性约束
e)    出现故障后快速自动恢复（crash safe recovery）
f)    用于在内存中缓存数据和索引的缓冲区池（buffer pool（data buffer page  log buffer page） 、undo buffer page）
g)    大型数据卷上的最大性能
h)    将对表的查询与不同存储引擎混合
i)    Oracle样式一致非锁定读取(共享锁)
j)    表数据进行整理来优化基于主键的查询（聚集索引）
功能 支持 功 支持
存储限制 64 TB 索引高速缓存 是
MVCC 是 数据高速缓存 是
B 树索引 是 自适应散列索引 是
群集索引 是 复制 是
压缩数据 是 更新数据字典 是
加密数据[b] 是 地理空间数据类型 是
查询高速缓存 是 地理空间索引 否
事务 是 全文搜索索引 是
锁定粒度 行 群集数据库 否
外键 是 备份和恢复 是
文件格式管理 是 快速索引创建 是
多个缓冲区池 是 PERFORMANCE_SCHEMA 是
更改缓冲 是 自动故障恢复 是

查询存储引擎的方法

1、使用 SELECT 确认会话存储引擎：root@world > show table status like ‘countrylanguage’\G

1
2
3
SELECT @@default_storage_engine;
或
show variables like '%engine%';

2、使用 SHOW 确认每个表的存储引擎：

1
2
show create table city;
show table status 'countrylanguage'\G

3、查看所有支持innodb引擎的表

1
root@world > select * from information_schema.tables where engine='innodb'\G;

4、从5.1版本，迁移到5.5版本以上版本

假如5.1版本数据库所有生产表都是myisam的。

使用mysqldump备份后，一要替换备份的文件中的engine(引擎)字段，从myisam替换为innodb（可以使用sed命令），否则迁移无任何意义。

数据库升级时，要注意其他配套设施的兼容性，注意代码能否兼容新特性。

设置存储引擎

1、在启动配置文件中设置服务器存储引擎：

1
2
3
4
# 修改MySQL配置文件（永久生效）
[root@db01 mysql]# vim /etc/my.cnf
[mysqld]
default-storage-engine=<Storage Engine>

2、使用 SET 命令为当前客户机会话设置：

1
SET @@storage_engine=<Storage Engine>;

3、在 CREATE TABLE 语句指定：

1
CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

企业案例

项目背景：

公司原有的架构：一个展示型的网站，LAMT，MySQL5.1.77版本（MYISAM），50M数据量。

小问题不断：

  • 1、表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。
  • 2、不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题。

如何解决：

  • 1、提建议将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38
    • 1）如果使用MYISAM会产生”小问题”，性能安全不能得到保证，使用innodb可以解决这个问题。
    • 2）5.1.77版本对于innodb引擎支持不够完善，5.6.38版本对innodb支持非常完善了。
  • 2、实施过程和注意要素

==解决流程==

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
## 停库前的操作
# 1.准备一个新环境
# 2.二进制安装一个MySQL5.7.44
# 3.当前正在提供服务的生产库，进行一个全备
[root@db01 mysql]# mysqldump -uroot -p123 -A > /tmp/full.sql

# 4.将全备恢复到新环境
[root@db01 ~]# mysql -uroot -p123 < /tmp/full.sql

# 5.从新环境备份出程序库的所有表数据
[root@db01 ~]# mysqldump -uroot -p123 -B zls > /tmp/zerlog.sql

# 6.修改表的存储引擎
[root@db01 ~]# sed -i 's#ENGINE=MyISAM#ENGINE=InnoDB#g' /tmp/zrlog.sql

# 7.将修改后的程序库数据导入新环境
1）关闭binlog的记录
set sql_log_bin=0;

2）导入数据
source /tmp/zls.sql

3）开启binlog的记录
set sql_log_bin=1;

## 停库操作
# 1.先挂维护页
# 2.旧环境停止连接数据库的服务
systemctl stop tomcat
systemctl stop php-fpm
...
# 3.再停止旧环境数据库
systemctl stop mysqld

# 4.从binlog中截取增量数据
# 5.启动新环境数据库
# 6.恢复截取的增量数据到新环境
# 7.测试
# 8.应用割接（将代码原来连接数据库的信息改为新环境）
# 9.启动连接数据库的服务
# 10.取消维护页

InnoDB的存储结构

innodb系统表空间特性

  1. 默认情况下，InnoDB 元数据、撤消日志和缓冲区存储在系统“表空间”中。
  2. 这是单个逻辑存储区域，可以包含一个或多个文件。
  3. 每个文件可以是常规文件或原始分区。
  4. 最后的文件可以自动扩展。

表空间的定义

表空间：MySQL数据库存储的方式

表空间中包含数据文件

MySQl表空间和数据文件是1:1的关系

共享表空间除外，是可以1:N关系

QQ_1723777681080

表空间类型

  1. 共享表空间：ibdata1~ibdataN,一般是2-3个

  2. 独立表空间：存放在指定库目录下，例如data/world/目录下的city.ibd

    表空间位置（datadir）： data/目录下

  • 共享表空间 (物理存储结构）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ibdata1~N 通常被叫做系统表空间，是数据初始化生成的
作用：
1）存储表和索引数据：ibdata1文件包含了所有InnoDB表和索引的数据。当创建新的InnoDB表时，数据将被存储在ibdata1中。
2）存储回滚段数据：InnoDB使用回滚段来支持事务的ACID特性。回滚段中的数据也被存储在ibdata1文件中。
3）存储系统表空间：系统表空间存储了InnoDB的元数据，比如表结构、索引信息等。系统表空间也被存储在ibdata1中。
存放 undo，临时表，元数据


系统元数据，基表数据，除了表内容数据之外的数据。
 
tmp 表空间（一般很少关注）
　
undo日志 ：数据--回滚数据（回滚日志使用）
　
redo日志 ：ib_logfile0~N 存放系统的innodb表的一些重做日志。
 说明：undo日志默认实在ibdata中的，在5.6以后是可以单独定义的。

tmp 表空间在5.7版本之后被移出了ibdata1，变为ibtmp1

在5.5版本之前，所有的应用数据也都默认存放到了ibdata中。

独立表空间(一个存储引擎的功能)

1
2
3
4
5
6
7
在5.6之后，默认的情况下会单表单独存储到独立表空间文件

除了系统表空间之外，InnoDB 还在数据库目录中创建另外的表空间，用于每个 InnoDB 表的 .ibd 文件。

InnoDB 创建的每个新表在数据库目录中设置一个 .ibd 文件来搭配表的.frm 文件。

可以使用 innodb_file_per_table 选项控制此设置，更改该设置仅会更改已创建的新表的默认值。

img

设置共享表空间

查看当前的共享表空间设置

1
2
3
4
5
6
root@(none) > show variables like 'innodb_data_file_path';
+-----------------------+------------------------------------+
| Variable_name         | Value                              |
+-----------------------+------------------------------------+
| innodb_data_file_path | ibdata1:76M;ibdata2:50M:autoextend |
+-----------------------+------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#共享表空间切割，第二个自懂扩展
[root@db02 world]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:100M:autoextend

### 重启数据库
systemctl restart mysqld

#设置第一个值76M要根据实际大小设置，
例如：innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
重启之后进不去mysql，查看日志报错如下
`设置的50M不够76M`  
一页=16k
3200*16/1024=50M
4864*16/1024=76M

QQ_1723777681080

1
2
3
4
5
6
`为啥要是76M呢？` ,那我们进入data目录，查看共享空间占用内存
[root@db01 data]# du -h ibdata1 
76M     ibdata1

所以至少是76M   ，看自己机器占用实际情况而定

企业案例

在没有备份数据的情况下，突然断电导致表损坏，打不开数据库。

恢复思路

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
#表数据存在独立表空间里，只需要将旧数据的表空间导入到新机器
#挂维护页
# 1.停止连接MySQL的所有服务
# 2.停止MySQL数据库
# 3.准备新环境
[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# rm -fr /app/mysql/data
[root@db01 ~]# /app/mysql/scripts/mysql_install_db --user=mysql --
basedir=/app/mysql --datadir=/app/mysql/data
[root@db01 ~]# /etc/init.d/mysqld start

# 4.找开发要表结构（建表语句）
CREATE TABLE world.city (
	`ID` int(11) NOT NULL AUTO_INCREMENT,
	`Name` char(35) NOT NULL DEFAULT '',
	`CountryCode` char(3) NOT NULL DEFAULT '',
	`District` char(20) NOT NULL DEFAULT '',
	`Population` int(11) NOT NULL DEFAULT '0',PRIMARY KEY (`ID`),
	KEY `CountryCode` (`CountryCode`),
	KEY `Population` (`Population`),
	KEY `inx_aa` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

# 5.在新环境创建表
root@localhost:(none):25: >create database world;
CREATE TABLE world.city (
	`ID` int(11) NOT NULL AUTO_INCREMENT,
	`Name` char(35) NOT NULL DEFAULT '',
	`CountryCode` char(3) NOT NULL DEFAULT '',
	`District` char(20) NOT NULL DEFAULT '',
	`Population` int(11) NOT NULL DEFAULT '0',PRIMARY KEY (`ID`),
	KEY `CountryCode` (`CountryCode`),
	KEY `Population` (`Population`),
	KEY `inx_aa` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

# 6.删除新环境的独立表空间
mysql >alter table world.city discard tablespace;

# 7.从旧环境将city表的表空间拷贝过来
[root@db02 world]# scp city.ibd 172.16.1.51:/app/mysql/data/world

# 8.新环境独立表空间授权
[root@db01 world]# chown mysql.mysql city.ibd

# 9.新环境导入表空间
mysql >alter table world.city import tablespace;

# 10.查询数据
mysql >select * from world.city;

# 11.先测试
# 12.应用割接
- 开发修改代码连接数据库的IP
- 运维导出新环境的数据，恢复到旧环境
# 13.启动连接数据库的服务
#取消维护恢复生产

设置独立表空间

独立表空间在5.6版本是默认开启的。

独立表空间注意事项：不开起独立表空间，共享表空间会占用很大

1
2
3
4
5
6
7
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

在参数文件/etc/my.cnf 可以控制独立表空间

关闭独立表空间 （0是关闭，1是开启）

1
2
3
[root@db02 clsn]# vim /etc/my.cnf
[mysqld]
innodb_file_per_table=0

查看独立表空间配置

1
2
3
4
5
6
7
mysql> show variables like '%per_table%' ;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

小结:

1
2
innodb_file_per_table=0    关闭独立表空间
innodb_file_per_table=1    开启独立表空间，单表单存储