如何:清理mysql InnoDB存储引擎? [英] Howto: Clean a mysql InnoDB storage engine?

问题描述

是否可以清理mysql innodb存储引擎，使其不存储已删除表中的数据?

Is it possible to clean a mysql innodb storage engine so it is not storing data from deleted tables?

还是我每次都必须重建一个新的数据库?

Or do I have to rebuild a fresh database every time?

推荐答案

关于InnoDB，这是一个更完整的答案.这是一个漫长的过程，但是值得付出努力.

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

请记住，/var/lib/mysql/ibdata1是InnoDB基础架构中最繁忙的文件.它通常包含六种类型的信息:

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

• 表格数据
• 表索引
• MVCC(多版本并发控制)数据
  • 回滚段
  • 撤消空间
  • Table Data
  • Table Indexes
  • MVCC (Multiversioning Concurrency Control) Data
    • Rollback Segments
    • Undo Space

    许多人创建了多个ibdata文件，希望获得更好的磁盘空间管理和性能，但是这种想法是错误的.

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

    不幸的是，运行 OPTIMIZE TABLE > 对存储在共享表空间文件ibdata1中的InnoDB表进行两项操作:

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

    • 使表的数据和索引在ibdata1
    内部连续
    • 使ibdata1增长是因为连续数据和索引页被附加到了ibdata1
    • Makes the table’s data and indexes contiguous inside ibdata1
    • Makes ibdata1 grow because the contiguous data and index pages are appended to ibdata1

    但是，您可以将ibdata1中的表数据和表索引分离出来，并分别进行管理.

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

    假设您要添加 innodb_file_per_table 到/etc/my.cnf (my.ini).然后可以只运行 OPTIMIZE TABLE 在所有InnoDB表上?

    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?

    好消息:运行 OPTIMIZE TABLE 与 innodb_file_per_table ，这将为该表生成一个.ibd文件.例如，如果您的表mydb.mytable的数据目录为/var/lib/mysql，它将产生以下内容:

    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
    • /var/lib/mysql/mydb/mytable.frm
    • /var/lib/mysql/mydb/mytable.ibd

    .ibd将包含该表的数据页和索引页.很好.

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

    坏消息:您要做的就是从居住在ibdata中的mydb.mytable提取数据页和索引页.每个表(包括mydb.mytable)的数据字典条目仍保留在数据字典中(请参见

    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.

    要一劳永逸地缩小ibdata1，您必须执行以下操作:

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

    1. 将所有数据库转储(例如，使用mysqldump)到.sql文本文件中(以下使用SQLData.sql)

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

    删除所有数据库(mysql和information_schema除外) CAVEAT :作为预防措施，请运行此脚本以确保您已拥有所有用户授权:

    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
    

  • 登录到mysql并运行SET GLOBAL innodb_fast_shutdown = 0;(这将完全清除ib_logfile0和ib_logfile1的所有剩余事务更改)

  • 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)

    关闭MySQL

    将以下行添加到/etc/my.cnf(或Windows中的my.ini)

    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
    

    (旁注:无论您为innodb_buffer_pool_size设置的是什么，请确保innodb_log_file_size是innodb_buffer_pool_size的25％.

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

    也:innodb_flush_method=O_DIRECT在Windows上不可用)

    Also: innodb_flush_method=O_DIRECT is not available on Windows)

    删除ibdata*和ib_logfile*，您可以选择删除/var/lib/mysql中除/var/lib/mysql/mysql之外的所有文件夹.

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

    启动MySQL(这将重新创建ibdata1 [默认为10MB]以及分别以1G创建ib_logfile0和ib_logfile1).

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

    导入SQLData.sql

    现在，ibdata1仍将增长，但仅包含表元数据，因为每个InnoDB表都将存在于ibdata1之外. ibdata1将不再包含InnoDB数据和其他表的索引.

    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.

    例如，假设您有一个名为mydb.mytable的InnoDB表.如果您查看/var/lib/mysql/mydb，您将看到代表该表的两个文件:

    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(存储引擎标题)
    • mytable.ibd(表数据和索引)
    • mytable.frm (Storage Engine Header)
    • mytable.ibd (Table Data and Indexes)

    使用/etc/my.cnf中的innodb_file_per_table选项，可以运行OPTIMIZE TABLE mydb.mytable，文件/var/lib/mysql/mydb/mytable.ibd实际上会缩小.

    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.

    在我作为MySQL DBA的职业生涯中，我做了很多次.实际上，我第一次这样做是将 50GB ibdata1文件压缩到只有500MB！

    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.

    在步骤6中，如果由于mysql模式开始删除而导致mysql无法重新启动，请回头查看步骤2.您已制作了mysql模式的物理副本.您可以按如下方式还原它:

    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
    

    返回步骤6并继续

    关于将 innodb_log_file_size 设置为在步骤5中， innodb_buffer_pool_size 的25％一揽子规则是一门老派.

    With regard to setting innodb_log_file_size to 25% of innodb_buffer_pool_size in Step 5, that's blanket rule is rather old school.

    回到July 03, 2006，Percona有一篇不错的文章为什么选择适当的innodb_log_file_size .后来，在Nov 21, 2008上，Percona跟进了另一篇关于

    Back on July 03, 2006, Percona had a nice article why to choose a proper innodb_log_file_size. Later, on Nov 21, 2008, Percona followed up with another article on how to calculate the proper size based on peak workload keeping one hour's worth of changes.

    此后，我在DBA StackExchange中撰写了有关计算日志大小以及在何处引用这两篇Percona文章的文章.

    I have since written posts in the DBA StackExchange about calculating the log size and where I referenced those two Percona articles.

    • Aug 27, 2012:在具有48GB RAM的服务器上正确调整30GB InnoDB表
    • Jan 17, 2013: MySQL 5.5-Innodb-innodb_log_file_size大于4GB的总和?
    • Aug 27, 2012 : Proper tuning for 30GB InnoDB table on server with 48GB RAM
    • Jan 17, 2013 : MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

    就个人而言，我仍然会遵循25％的规则进行初始设置.然后，由于可以在生产过程中随着时间确定工作量，因此 您可以调整日志大小 在几分钟内的维护周期中.

    Personally, I would still go with the 25% rule for an initial setup. Then, as the workload can more accurate be determined over time in production, you could resize the logs during a maintenance cycle in just minutes.

    这篇关于如何:清理mysql InnoDB存储引擎?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！