MySQL Study之--MySQL增加新的表空间（tablespace）

Myth丶恋晨 2022-08-05 08:47 357阅读 0赞

**MySQL Study之--MySQL增加新的表空间（tablespace）**

**系统环境：**

**    操作系统：RedHat EL64(64)**

**    MySQL：  Percona Server 5.6**

**MySQL innodb引擎采用表空间的方式管理数据存储，默认系统会建立一个共享表空间；如果共享表空间，空间不足，需要增加新的tablespace。在MySQL扩展表空间是使用的参数：**

**innodb\_data\_file\_path：  
      该变量设置innoDB数据文件的位置和大小。指定的文件必须大于10M，如果不受系统文件限制，可以设置大于4G。该变量是mysql服务器容量规划和性能扩展能力的核心要素。通常设置是创建一个数据目录内容的基线大小，在10M到128M之间，第二个文件设置为10M并自动扩展。如innodb\_data\_file\_path = ibdata1:128M;ibdata2:10M:autoextend。**

**      当没有使用innodb\_file\_per\_table也没有启用自动扩展，那么随着数据的增长，表空间将满了。在这情况下，需要添加额外的表空间来扩展容量。方法如下：  
1. 停止mysql服务  
2. 备份配置文件，便于出现问题好回退  
3. 编辑innodb\_data\_file\_path值  
根据你的环境更改ibdata1:$size;ibdataN:$size;…ibdataN:$size; 当前定义的表空间或默认表空间是不能改变的，否则启动失败，但是，可以额外的添加表空间，ibdataN序列根据当前的数量递增，$size自定义。  
4. 启动mysql服务  
6. 观察mysql错误日志是否有错**

**1、查看表空间信息**

**mysql> show variables like '%innodb\_data\_file\_path%';  
\+-----------------------+------------------------------------+  
| Variable\_name         | Value                              |  
\+-----------------------+------------------------------------+  
| innodb\_data\_file\_path | ibdata1:10M|  
\+-----------------------+------------------------------------+  
1 row in set (0.01 sec)**

**2、修改配置文件，增加新的表空间**

**\[root@rh64 mysql\]\# vi /etc/my.cnf  
\[mysqld\]**  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
user=mysql  
\# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
**innodb\_data\_file\_path=ibdata1:10M;ibdata2:10M:autoextend  
  
\[mysqld\_safe\]**  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid  
explicit\_defaults\_for\_timestamp=true  
innodb\_buffer\_pool\_size = 128M  
join\_buffer\_size = 128M  
sort\_buffer\_size = 2M  
read\_rnd\_buffer\_size = 2M  
  
**3、重新启动server**  
**\[root@rh64 mysql\]\# service mysql start**  
Starting MySQL (Percona Server).The server quit without updating PID file (/var/lib/mysql/rh64.pid).\[FAILED\]  
  
**启动失败，查看日志：**  
\[root@rh64 mysql\]\# tail -100 /var/log/mysqld.log  
150911 11:51:16 mysqld\_safe mysqld from pid file /var/lib/mysql/rh64.pid ended  
150911 11:54:38 mysqld\_safe Starting mysqld daemon with databases from /var/lib/mysql  
2015-09-11 11:54:38 0 \[Warning\] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit\_defaults\_for\_timestamp server option (see documentation for more details).  
2015-09-11 11:54:38 0 \[Note\] /usr/sbin/mysqld (mysqld 5.6.25-73.1) starting as process 3588 ...  
2015-09-11 11:54:38 3588 \[Note\] Plugin 'FEDERATED' is disabled.  
2015-09-11 11:54:38 3588 \[Note\] InnoDB: Using atomics to ref count buffer pool pages  
2015-09-11 11:54:38 3588 \[Note\] InnoDB: The InnoDB memory heap is disabled  
2015-09-11 11:54:38 3588 \[Note\] InnoDB: Mutexes and rw\_locks use GCC atomic builtins  
2015-09-11 11:54:38 3588 \[Note\] InnoDB: Memory barrier is not used  
2015-09-11 11:54:38 3588 \[Note\] InnoDB: Compressed tables use zlib 1.2.3  
2015-09-11 11:54:38 3588 \[Note\] InnoDB: Using Linux native AIO  
2015-09-11 11:54:38 3588 \[Note\] InnoDB: Not using CPU crc32 instructions  
2015-09-11 11:54:38 3588 \[Note\] InnoDB: Initializing buffer pool, size = 128.0M  
2015-09-11 11:54:39 3588 \[Note\] InnoDB: Completed initialization of buffer pool  
**2015-09-11 11:54:39 3588 \[ERROR\] InnoDB: Data file ./ibdata1 is of a different size 768 pages (rounded down to MB) than specified in the .cnf file 1280 pages!**  
2015-09-11 11:54:39 3588 \[ERROR\] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb\_data\_file\_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!  
2015-09-11 11:54:39 3588 \[ERROR\] Plugin 'InnoDB' init function returned error.  
2015-09-11 11:54:39 3588 \[ERROR\] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.  
2015-09-11 11:54:39 3588 \[ERROR\] Unknown/unsupported storage engine: InnoDB  
2015-09-11 11:54:39 3588 \[ERROR\] Aborting  
  
**----错误提示：ibdata1数据文件size设置大小和系统不匹配！！**

**查看表空间数据文件的大小：**

**\[root@rh64 mysql\]\#  ls -lh**  
total 167M  
\-rw-rw----. 1 mysql mysql   56 Sep  6 18:08 auto.cnf  
**-rw-rw----. 1 mysql mysql  12M Sep 11 11:56 ibdata1**  
\-rw-rw----. 1 mysql mysql  10M Sep 11 11:50 ibdata2  
\-rw-rw----. 1 mysql mysql  48M Sep 11 11:56 ib\_logfile0  
\-rw-rw----. 1 mysql mysql  48M Sep  6 18:06 ib\_logfile1  
\-rw-rw----. 1 mysql mysql  48M Sep 11 11:50 ib\_logfile101  
drwx------. 2 mysql mysql 4.0K Sep  6 18:06 mysql  
srwxrwxrwx. 1 mysql mysql    0 Sep 11 11:56 mysql.sock  
drwx------. 2 mysql mysql 4.0K Sep  6 18:06 performance\_schema  
\-rw-rw----. 1 mysql mysql    5 Sep 11 11:56 rh64.pid  
\-rw-r--r--. 1 root  root   293 Sep  6 18:07 RPM\_UPGRADE\_HISTORY  
\-rw-r--r--. 1 mysql mysql  293 Sep  6 18:07 RPM\_UPGRADE\_MARKER-LAST  
drwx------. 2 mysql mysql 4.0K Sep  6 18:06 test  
  
**重新修改配置文件配置参数：**  
**\[root@rh64 mysql\]\# vi /etc/my.cnf**  
innodb\_data\_file\_path=ibdata1:12  
\[mysqld\]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
user=mysql  
\# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
**innodb\_data\_file\_path=ibdata1:12M;ibdata2:10M:autoextend**  
  
**---对于已有的表空间数据文件的size，必须按照系统查看的size设置，否则报错！**

**（亦可以将表空间建立在不同的目录下如：/data/ibdata3:10M,首先需建立/data目录）**  
  
**\[mysqld\_safe\]**  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid  
explicit\_defaults\_for\_timestamp=true  
innodb\_buffer\_pool\_size = 128M  
join\_buffer\_size = 128M  
sort\_buffer\_size = 2M  
read\_rnd\_buffer\_size = 2M  
~

**重新启动server：**  
**\[root@rh64 mysql\]\# service mysql start**  
Starting MySQL (Percona Server).\[  OK  \]  
  
**登录server，查看tablespace：**  
**\[root@rh64 ~\]\# mysql -u root -p**  
Enter password:   
Welcome to the MySQL monitor.  Commands end with ; or \\g.  
Your MySQL connection id is 1  
Server version: 5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f  
Copyright (c) 2009-2015 Percona LLC and/or its affiliates  
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.  
Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.  
Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.  
  
**mysql> show variables like '%innodb\_data\_file\_path%';**  
\+-----------------------+------------------------------------+  
| Variable\_name         | Value                              |  
\+-----------------------+------------------------------------+  
**| innodb\_data\_file\_path | ibdata1:12M;ibdata2:10M:autoextend |**  
\+-----------------------+------------------------------------+  
1 row in set (0.01 sec)

**---新的tablespace增加成功 ！！**