MySQL如何创建表空间自动增长 mysql表空间大小设置

文章目录

• 第1章 基础环境和官方架构图

• 1.1 我的实践环境
• 1.2 官方的架构图

• 第2章 系/独表空间的基本认识一

• 2.1 系统表空间的认识
• 2.2 独立表空间的认识

第1章 基础环境和官方架构图

1.1 我的实践环境

#### 特别说明
01:我的mysql实例的my.cnf配置文件中没有配置如下的参数
   innodb_data_file_path                      # 设置系统表空间大小和个数的参数
   innodb_autoextend_increment                # 设置系统表空间最后一个文件不够时每次自增多大
   innodb_file_per_table                      # 设置独立表空间的参数
   innodb_doublewrite                         # 是否开启双写的参数
02:然后才进行的mysql实例初始化
   mysqld --defaults-file=/data/mysql/3306/etc/my.cnf --basedir=/data/apps/mysql \
   --datadir=/data/mysql/3306/data --initialize    


#### 我的mysql实例其它情况如下
mysql> select version();                       # 数据库的版本
+------------+
| version()  |
+------------+
| 5.7.28-log |
+------------+
1 row in set (0.00 sec)

mysql> show databases;                          # 所有库(只是默认的库,我没有另外创建)
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> select @@global.default_storage_engine;  # 默认的引擎
+---------------------------------+
| @@global.default_storage_engine |
+---------------------------------+
| InnoDB                          |
+---------------------------------+
1 row in set (0.01 sec)

1.2 官方的架构图

官网地址：https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html 这里为什么把官网地址贴出来是因为MySQL的各个版本的架构是有所不同的。MySQL有六种表空间: 系统表空间(也叫共享表空间) 、独立表空间 、临时表空间 、undo表空间 、通用表空间、 传输表空间。本篇文章只是对系统表空间和独立表空间进行总结，下图中用红框标识的.

第2章 系/独表空间的基本认识一

2.1 系统表空间的认识

系统表空间（system tablespace）就是我们常说的共享表空间，系统表空间（在操作系统上体现就是ibdata文件）是我们在初始化mysql实例时生成的（在初始化mysql实例时会读取my.cnf中的innodb_data_file_path参数，然后初始出相应的文件ibdata1、ibdata2 …，至于文件多大，有多少个，看你my.cnf中的参数是怎样设置的）。mysql默认的系统表空间文件大小是12M，只有一个文件(ibdata1)，它默认是保存到你mysql实例的datadir变量的目录下，它的的默认值如下所示：

#### 在mysql实例中查看共享表空间的大小
mysql> select @@global.innodb_data_file_path;
+--------------------------------+
| @@global.innodb_data_file_path |
+--------------------------------+
| ibdata1:12M:autoextend         |  # 共享表空间的文件是ibdata1,大小是12M
+--------------------------------+  # autoextend这里不进行说明
1 row in set (0.00 sec)  


#### 在mysql的datadir变量所指定的目录下查看系统表空间文件
mysql> select @@global.datadir;
+------------------------+
| @@datadir              |
+------------------------+
| /data/mysql/3307/data/ |
+------------------------+
1 row in set (0.00 sec)


#### 在操作系统下查看/data/mysql/3306/data目录下的文件
[root@node21 ~]# ls -ld /data/mysql/3306/data/
drwxr-xr-x 5 mysql mysql 4096 10月 14 14:36 /data/mysql/3306/data/
[root@node21 ~]#
[root@node21 ~]# tree -Lp 1 /data/mysql/3306/data/
/data/mysql/3306/data/
├── [-rw-r-----]  auto.cnf
├── [-rw-------]  ca-key.pem
├── [-rw-r--r--]  ca.pem
├── [-rw-r--r--]  client-cert.pem
├── [-rw-------]  client-key.pem
├── [-rw-r-----]  ib_buffer_pool
├── [-rw-r-----]  ibdata1             <== 系统表空间文件
├── [-rw-r-----]  ib_logfile0
├── [-rw-r-----]  ib_logfile1
├── [-rw-r-----]  ibtmp1
├── [drwxr-x---]  mysql
├── [drwxr-x---]  performance_schema
├── [-rw-------]  private_key.pem
├── [-rw-r--r--]  public_key.pem
├── [-rw-r--r--]  server-cert.pem
├── [-rw-------]  server-key.pem
├── [drwxr-x---]  sys
├── [-rw-r-----]  undo001
├── [-rw-r-----]  undo002
└── [-rw-r-----]  undo003

3 directories, 17 files

[root@node21 ~]# du -sh /data/mysql/3306/data/ibdata1   # 查看大小
12M	/data/mysql/3306/data/ibdata1

当系统表空间不够用时（也就是ibdata1文件），它会自动扩展（autoextend），那么autoextend默认每次扩展多大呢？这个扩展不是说再重新生成一个文件ibdat2，而是在ibdata1的基础上进行扩展。

mysql> select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
|                            64 |  # 每次自动扩展64M
+-------------------------------+
1 row in set (0.00 sec)

当扩展了一次后，又不够用时，又会在ibdat1文件的基础上再扩展64M，至于这个文件能够扩展到多大，默认是没有限制的，就看你的磁盘有多大了？

#### 系统表空间的默认默认是：ibdata1:12M:autoextend 
那么ibdata1文件不够用时会自动在ibdata1文件的基础上进行每次64M的扩展,至于
ibdata1能够自动扩展到多大,是没有限制的,就看你的磁盘有多大空间。


#### 假如我这样配置呢？ ibdata1:12M:autoextend:max:20G  【生产不是这样配置的】
那么ibdata1文件不够用时会自动在ibdata1文件的基础上进行每次64M的扩展,最大能
够扩展到20G,因为max:20G就给限制了。

系统表空间是不能在线进行更改的（不能在线对相关参数的更改），只能在my.cnf中修改参数（修必时是有注意点的）后，然后重启mysql实例(在重启的过程中会对生成相应的文件)。但是生产中mysql是不能随便停止服务的。所以在我们安装部署mysql实例时就要考虑到这一点，我生产中对MySQL系统表空间的配置如下所示：

innodb_data_file_path=ibdata1:5G;ibdata2:5G;ibdata3:5G:autoextend:max:20G
innodb_autoextend_increment=64

在初始化mysql实例时会先初始成三个ibdata文件,每个文件大小5G,当空间用到不够
时(也就到了ibdata3),会在ibdata3文件的基础上每次自增64M,最大自增到20G;

2.2 独立表空间的认识

从mysql 5.6.6版本开始，独立表空间（file-per-table tablespaces）默认是开启的（也就是innodb_file_per_table参数不设置时，它默认等于1），在开启的情况下，你创建一个innodb引擎的表，那么表有自己独立的一些数据文件。这些数据文件在操作系统上的文件体现如下所示：

表名.frm   # 表的表结构文件(里面存放的是表的创建语句)
表名.ibd   # 表的数据文件(当有数据往表中插入时,数据就保存之个文件中的)

独立表空间的好处:

01:表数据分开存放(不把所有鸡蛋放在1个蓝子里面);损坏1个文件不至于影响所有表
02:容易维护,查询速度快（IO分散）

我在生产会在my.cnf配置文件中开启"独立表空间"，虽然独立表空间默认是开启的，但是我也会在my.cnf文件的的[mysqld]下进行设置,如下：

innodb_file_per_table=1

独立表空间是一个全局变量(不用考虑会话模式层面)，它是可以在线进行全局更改的。
例如：set global inno_file_per_table=0; 这就是关闭独立表空间了。但不要这样做。
当关闭后，你新创建的innodb表没有.ibd文件,表中产生中的数据就保存到了系统表
空间中。所以这也是mysql监控时需要加上的一项。