mysql从共享表空间修改为单个表的表空间存储方式是什么

本文向您展示了mysql是如何从共享表空间变成单表空间存储的。内容简洁易懂，一定会让你大放异彩。希望通过这篇文章的详细介绍，你能有所收获。

使用过MySQL的同学，一开始会接触到MyISAM表引擎最多。该引擎的数据库将分别创建三个文件：表结构、表索引和表数据空间。我们可以将一个数据库目录直接迁移到其他数据库，它也可以正常工作。但是，当您使用InnoDB时，一切都会改变。默认情况下，InnoDB会将InnoDB引擎所有数据库的表数据存储在一个共享空间：ibdata1中，这让我感觉很不舒服。添加或删除数据库时，ibdata1文件不会自动收缩，单个数据库的备份将成为问题。通常，数据只能使用mysqldump导出，然后导入来解决这个问题。

在MySQL的配置文件【MySQL】部分，可以添加参数InnoDB_file_per_table，将InnoDB修改为独立表空间模式，每个数据库中的每个表都会生成一个数据空间。

独立表空间

优点：

1.每个表都有自己独立的表空间。

2.每个表的数据和索引将存在于它自己的表空间中。

3.单个表可以在不同的数据库中移动。

4.空间可以回收(删除/截断表操作表空间不能自动回收)

5.对于使用独立表空间的表，无论如何删除，表空间的碎片化都不会严重影响性能，仍然有机会处理。

缺点：

单表的增加大于共享空间的增加。

结论：

共享表空间在插入操作中有一些优势，但在其他操作中不如独立表空间。

启用独立表空间时，请合理调整innodb_open_files参数。

以下是针对在线Zabbix中MySQL数据库历史记录太多导致ibdata1文件太大的问题的实用解决方案。

1.检查文件大小

$ sudo cd /var/lib/mysql

$ ls -lh

总计14G

-rw-r - r - 1根0 12月1日14:31 debian-5.1 .标志

-rw-rw-1 MySQL MySQL 5.0M 1月17日21:31 ib_logfile0

-rw-rw-1 MySQL MySQL 5.0M 1月17日21:29 ib_logfile1

-rw-rw-1 MySQL MySQL 14G 1月17日21:31 ibdata1

drwx-2 MySQL root 4.0K 12月1日14:31 mysql

-rw-rw - 1根6 12月1日14:31 mysql_upgrade_info

drwx-2 MySQL MySQL 4.0K 1月17日21:29 zabbix

共享表数据空间文件ibdata1的大小已达到14G。

登录MySQL查看哪些表占用空间。

$ mysql -uroot -p

mysql选择table_name，(data _ length index _ length)/1024/1024作为total_mb，table_rows来自information_schema.tables，其中table _ schema=' zabbix

- - -

| table _ name | total _ MB | table _ rows |

- - -

|承认| 0.06250000 | 0 |

.

|帮助_项目| 0.04687500 | 103 |

|历史记录b

sp;     | 9678.00000000 |  123981681 |
 | history_log           |    0.04687500 |          0 |
 ...
 | history_text          |    0.04687500 |          0 |
 | history_uint          | 5386.98437500 |   57990562 |
 | history_uint_sync     |    0.04687500 |          0 |
 ...
 | timeperiods           |    0.01562500 |          0 |
 | trends                |   54.54687500 |     537680 |
 | trends_uint           |  100.53125000 |    1035592 |
 ...
 103 rows in set (1.46 sec)
可以看到，history表的记录已经达到了9G，123981681条，即1亿2千万条，同时history_unit也比较大，达到了5G，约6千万条；
另外就是trends,trends_uint中也存在一些数据。
由于数据量太大，按照普通的方式delete数据的话基本上不太可能。
因为我们每天会自动发送数据报表，所以决定直接采用truncate table的方式来快速清空这些表的数据，再使用mysqldump导出数据，删除共享表空间数据文件，重新导入数据。
2.停止相关服务，避免写入数据
$ sudo /etc/init.d/zabbix-server stop
$ sudo /etc/init.d/apache2 stop
3.清空历史数据
$ mysql -uroot -p
 mysql > use zabbix;
 Database changed
 
 mysql > truncate table history;
 Query OK, 123981681 rows affected (0.23 sec)
 
 mysql > optimize table history;
 1 row in set (0.02 sec)
 
 mysql > truncate table history_uint;
 Query OK, 57990562 rows affected (0.12 sec)
 
 mysql > optimize table history_uint;
 1 row in set (0.03 sec)
 
 mysql > truncate table trends;
 Query OK, 537680 rows affected (0.04 sec)
 
 mysql > optimize table trends;
 1 row in set (0.02 sec)
 
 mysql > truncate table trends_uint;
 Query OK, 1035592 rows affected (0.02 sec) 
 
 mysql > optimize table trends_uint;
 1 row in set (0.01 sec)
4.备份数据
$ mysqldump -uroot -p zabbix > ~/zabbix.sql
5.停止数据库
$ sudo stop mysql
6.删除共享表空间数据文件
$ cd /var/lib/mysql
$ rm ib*
7.增加innodb_file_per_table参数
$ sudo vim /etc/mysql/my.cnf
在[mysqld]下设置
1 innodb_file_per_table=1
8.启动MySQL
$ sudo start mysql
9.查看参数是否生效
$ mysql -uroot -p
 mysql> show variables like '%per_table%';
 +-----------------------+-------+
 | Variable_name | Value |
 +-----------------------+-------+
 | innodb_file_per_table | ON |
 +-----------------------+-------+
 1 row in set (0.00 sec)
10.重新导入数据
$ mysql -uroot -p zabbix < ~/zabbix.sql
11.编写每天自动清理数据的脚本，保留30天的数据
$ sudo vim /etc/cron.daily/clean_zabbix_olddata.sh
view source
print?
 #!/bin/bash
 DATE=`date -d "30 days ago"`
 CLOCK=`date +%s -d "${DATE}"`
 MYSQL="mysql -uroot -p zabbix"
 
 for TABLE in history trends
 do
   $MYSQL -e "DELETE FROM ${TABLE} WHERE clock < ${CLOCK};"
   $MYSQL -e "OPTIMIZE TABLE ${TABLE};"
   $MYSQL -e "DELETE FROM ${TABLE}_uint WHERE clock < ${CLOCK};"
   $MYSQL -e "OPTIMIZE TABLE ${TABLE}_uint;"
 done
12.最后，恢复相关服务进程
$ sudo /etc/init.d/zabbix-server start
$ sudo /etc/init.d/apache2 start

上述内容就是mysql从共享表空间修改为单个表的表空间存储方式是什么，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。