文档章节

InnoDB Infrastructure Cleanup

perfectspr
 perfectspr
发布于 2014/11/11 10:52
字数 712
阅读 43
收藏 3

Here is a more complete answer with regard to InnoDB. It is a bit of a lengthy process, but can be worth the effort.

Keep in mind that /var/lib/mysql/ibdata1 is the busiest file in the InnoDB infrastructure. It normally houses six types of information:

InnoDB Architecture

InnoDB Architecture

Many people create multiple ibdata files hoping for better disk-space management and performance, however that belief is mistaken.

Can I run OPTIMIZE TABLE ?

Unfortunately, running OPTIMIZE TABLE against an InnoDB table stored in the shared table-space file ibdata1 does two things:

  • Makes the table’s data and indexes contiguous inside ibdata1

  • Makes ibdata1 grow because the contiguous data and index pages are appended to ibdata1

You can however, segregate Table Data and Table Indexes from ibdata1 and manage them independently.

Can I run OPTIMIZE TABLE with innodb_file_per_table ?

Suppose you were to add innodb_file_per_table to /etc/my.cnf (my.ini). Can you then just run OPTIMIZE TABLE on all the InnoDB Tables?

Good News : When you run OPTIMIZE TABLE with innodb_file_per_table enabled, this will produce a .ibd file for that table. For example, if you have table mydb.mytable witha datadir of /var/lib/mysql, it will produce the following:

  • /var/lib/mysql/mydb/mytable.frm

  • /var/lib/mysql/mydb/mytable.ibd

The .ibd will contain the Data Pages and Index Pages for that table. Great.

Bad News : All you have done is extract the Data Pages and Index Pages of mydb.mytable from living in ibdata. The data dictionary entry for every table, including mydb.mytable, still remains in the data dictionary (See the Pictorial Representation of ibdata1). YOU CANNOT JUST SIMPLY DELETE ibdata1 AT THIS POINT !!! Please note that ibdata1 has not shrunk at all.

InnoDB Infrastructure Cleanup

To shrink ibdata1 once and for all you must do the following:

  1. Dump (e.g., with mysqldump) all databases into a .sql text file (SQLData.sql is used below)

  2. Drop all databases (except for mysql and information_schema) CAVEAT : As a precaution, please run this script to make absolutely sure you have all user grants in place:

    mkdir /var/lib/mysql_grants
    cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/.
    chown -R mysql:mysql /var/lib/mysql_grants
  3. Login to mysql and run SET GLOBAL innodb_fast_shutdown = 0; (This will completely flush all remaining transactional changes from ib_logfile0 and ib_logfile1)

  4. Shutdown MySQL

  5. Add the following lines to /etc/my.cnf (or my.ini on Windows)

    [mysqld]innodb_file_per_table
    innodb_flush_method=O_DIRECT
    innodb_log_file_size=1G
    innodb_buffer_pool_size=4G

    (Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

    Also: innodb_flush_method=O_DIRECT is not available on Windows)

  6. Delete ibdata* and ib_logfile*, Optionally, you can remove all folders in /var/lib/mysql, except /var/lib/mysql/mysql.

  7. Start MySQL (This will recreate ibdata1 [10MB by default] and ib_logfile0 and ib_logfile1 at 1G each).

  8. Import SQLData.sql

Now, ibdata1 will still grow but only contain table metadata because each InnoDB table will exist outside of ibdata1. ibdata1 will no longer contain InnoDB data and indexes for other tables.

For example, suppose you have an InnoDB table named mydb.mytable. If you look in /var/lib/mysql/mydb, you will see two files representing the table:

  • mytable.frm (Storage Engine Header)

  • mytable.ibd (Table Data and Indexes)

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many times in my career as a MySQL DBA. In fact, the first time I did this, I shrank a 50GB ibdata1 file down to only 500MB!

Give it a try. If you have further questions on this, just ask. Trust me; this will work in the short term as well as over the long haul.

CAVEAT

At Step 6, if mysql cannot restart because of the mysql schema begin dropped, look back at Step 2. You made the physical copy of the mysql schema. You can restore it as follows:

mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_grants/* /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql/mysql

Go back to Step 6 and continue


本文转载自:http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine

perfectspr
粉丝 5
博文 43
码字总数 1198
作品 0
大连
技术主管
私信 提问
MariaDB 10.2.14 发布,新增磁盘空间监视插件

MariaDB 10.2.14 发布了。MariaDB 主要由开源社区在维护,采用 GPL 授权许可。 MariaDB 的目的是完全兼容 MySQL,包括 API 和命令行。 Notable Changes Added the DISKS plugin, for monito...

mia0x75
2018/03/28
1K
0
MySQL 5.5.29/5.1.67 发布

MySQL 5.5.29/5.1.67发布。2012-12-21 之前的版本是2012-09-28的5.5.28/5.1.66.其他产品线是2012-03-22的5.0.96 下载: ftp://mirror.switch.ch/mirror/mysql/Downloads/MySQL-5.5/mysql-5.......

fei
2012/12/21
3.2K
4
mysql innodb断电恢复,不支持innodb,表空间丢失

mysql innodb断电恢复,不支持innodb,表空间丢失 一、需求 在办公网络中有一测试pc,跑mysql服务,周末大厦停电,导致mysql 异常; 具体表象为不支持innodb Current database: kkyoo_ucente...

xiaomaimai
2018/06/26
0
0
mysql自动重启 有时重启后程序连接不上求教啊

160108 9:10:46 InnoDB: Started; log sequence number 0 6784661 160108 9:10:46 [Note] Event Scheduler: Loaded 0 events 160108 9:10:46 [Note] /usr/libexec/mysqld: ready for connect......

hphper
2016/01/18
352
0
mysql-5.6.14-winx64 配置版 配置出现错误

my.ini 内容如下: [WinMySQLAdmin] Server=C:/Program Files/MySQL/bin/mysqld.exe [client] no-beep # pipe # socket=mysql port=3306 [mysql] # For advice on how to change settings p......

TimZhou
2013/10/19
1K
4

没有更多内容

加载失败,请刷新页面

加载更多

HeyUI组件库按需加载功能上线,盘点HeyUI组件库有哪些独特功能?

HeyUI组件库 如果你还不了解heyui组件库,欢迎来我们的官网或者github参观。 官网 github 当然,如果能给我们一颗✨✨✨,那是最赞的了! 按需加载 当heyui组件库的组件越来越多的时候,按需...

vvpvvp
25分钟前
7
0
Dockerfile文件详解

Dockerfile文件详解 什么是dockerfile? Dockerfile是一个包含用于组合映像的命令的文本文档。可以使用在命令行中调用任何命令。 Docker通过读取Dockerfile中的指令自动生成映像。 docker bui...

Jeam_
38分钟前
1
0
阿里云PolarDB发布重大更新 支持Oracle等数据库一键迁移上云

5月21日,阿里云PolarDB发布重大更新,提供传统数据库一键迁移上云能力,可以帮助企业将线下的MySQL、PostgreSQL和Oracle等数据库轻松上云,最快数小时内迁移完成。据估算,云上成本不到传统...

zhaowei121
46分钟前
1
0
在数据数据探索过程中的一些常用操作

###pandas在做数据探索时,分组统计均值和中位数参考资料:http://www.cnblogs.com/nxld/p/6058591.htmlhttp://python.jobbole.com/85742/按字典重新赋值,可以直接使用pandas中的repla...

KYO4321
49分钟前
1
0
好程序员分享干货 弹性分布式数据集RDD

一、RDD定义 RDD(Resilient Distributed Dataset)叫做分布式数据集,是Spark中最基本的数据抽象,它代表一个不可变(数据和元数据)、可分区、里面的元素可并行计算的集合。其特点在于自动容...

好程序员IT
50分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部