文档章节

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
大连
技术主管
mysql 启动错误日志,导致网站缓慢

InnoDB: The InnoDB memory heap is disabled InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Mutexes and rw_locks use GCC a......

rianbow
09/21
0
0
mysql innodb断电恢复,不支持innodb,表空间丢失

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

xiaomaimai
06/26
0
0
关于innodb_data_file_path设置

关于innodbdatafile_path设置 启动Mysql时报错,无法正常启动,通过日志发有数据文件大小错误,日志如下截图: [ERROR] InnoDB: auto-extending data file /usr/local/mysql/data/ibdata1 i...

xiaocao13140
05/25
0
0
phpMyAdmin/nginx: 502 bad gateway 解决记录

  今天开机运行Nginx准备做本地测试,浏览器打开 phpMyAdmin 后提示 Nginx 进程崩溃。打开Nginx错误日志“error.log”没有任何有效信息。然后打开了phpMyAdmin的错误日志“***-PC.err”,提...

草原小木屋
2014/09/20
0
0
innodb_file_format设置

在更改innodbfileformat值的设定时,除innodbfileper_table以外,还需多注意另外两个参数: innodbfileformat_check As of MySQL 5.5.5, this variable can be set to 1 or 0 at server sta......

旺旺的钥匙
2012/07/03
0
0

没有更多内容

加载失败,请刷新页面

加载更多

一个可能的NEO链上安全随机数解决方案

0x00 困境 链上安全随机数生成应该算是一个比较蛋疼的问题,哪怕你的系统再牛逼,合约程序困在小小的虚拟机里,哪怕天大的本事也施展不开。 更悲催的是,交易执行的时候,是在每一个节点都执...

暖冰
今天
1
0
【大福利】极客时间专栏返现二维码大汇总

我已经购买了如下专栏,大家通过我的二维码你可以获得一定额度的返现! 然后,再给大家来个福利,只要你通过我的二维码购买,并且关注了【飞鱼说编程】公众号,可以加我微信或者私聊我,我再...

飞鱼说编程
今天
3
0
Spring5对比Spring3.2源码之容器的基本实现

最近看了《Spring源码深度解析》,该书是基于Spring3.2版本的,其中关于第二章容器的基本实现部分,目前spring5的实现方式已有较大改变。 Spring3.2的实现: public void testSimpleLoad(){...

Ilike_Java
今天
1
0
【王阳明心学语录】-001

1.“破山中贼易,破心中贼难。” 2.“夫万事万物之理不外于吾心。” 3.“心即理也。”“心外无理,心外无物,心外无事。” 4.“人心之得其正者即道心;道心之失其正者即人心。” 5.“无...

卯金刀GG
今天
2
0
OSChina 周三乱弹 —— 我们无法成为野兽

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @ _刚刚好: 霸王洗发水这波很骚 手机党少年们想听歌,请使劲儿戳(这里) hahahahahahh @嘻酱:居然忘了喝水。 让你喝可乐的话, 你准忘不了...

小小编辑
今天
16
2

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部