精华内容
参与话题
问答
  • MySQL的ibdata1详解

    万次阅读 2018-04-09 11:53:41
    关于 MySQL 的 ibdata1 文件的这个问题:当监控服务器发送一个关于 MySQL 服务器存储的报警时,恐慌就开始了 —— 就是说磁盘快要满了。一番调查后你意识到大多数地盘空间被 InnoDB 的共享表空间 ibdata1 使用。而你...

    关于 MySQL 的 ibdata1 文件的这个问题:

    当监控服务器发送一个关于 MySQL 服务器存储的报警时,恐慌就开始了 —— 就是说磁盘快要满了。

    一番调查后你意识到大多数地盘空间被 InnoDB 的共享表空间 ibdata1 使用。而你已经启用了 innodb_file_per_table,所以问题是:

    ibdata1存了什么?

    当你启用了 innodb_file_per_table,表被存储在他们自己的表空间里,但是共享表空间仍然在存储其它的 InnoDB 内部数据:

    • 数据字典,也就是 InnoDB 表的元数据
    • 变更缓冲区
    • 双写缓冲区
    • 撤销日志

    其中的一些在 Percona 服务器上可以被配置来避免增长过大的。例如你可以通过 innodb_ibuf_max_size 设置最大变更缓冲区,或设置 innodb_doublewrite_file 来将双写缓冲区存储到一个分离的文件。

    MySQL 5.6 版中你也可以创建外部的撤销表空间,所以它们可以放到自己的文件来替代存储到 ibdata1。可以看看这个文档

    什么引起 ibdata1 增长迅速?

    当 MySQL 出现问题通常我们需要执行的第一个命令是:

    1. SHOW ENGINE INNODB STATUS/G

    这将展示给我们一些很有价值的信息。我们从** TRANSACTION(事务)**部分开始检查,然后我们会发现这个:

    1. ---TRANSACTION 36E, ACTIVE 1256288 sec
    2. MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
    3. show engine innodb status
    4. Trx read view will not see trx with id >= 36F, sees < 36F

    这是一个最常见的原因,一个14天前创建的相当老的事务。这个状态是活动的,这意味着 InnoDB 已经创建了一个数据的快照,所以需要在撤销日志中维护旧页面,以保障数据库的一致性视图,直到事务开始。如果你的数据库有大量的写入任务,那就意味着存储了大量的撤销页。

    如果你找不到任何长时间运行的事务,你也可以监控INNODB STATUS 中的其他的变量,“History list length(历史记录列表长度)”展示了一些等待清除操作。这种情况下问题经常发生,因为清除线程(或者老版本的主线程)不能像这些记录进来的速度一样快地处理撤销。

    我怎么检查什么被存储到了 ibdata1 里了?

    很不幸,MySQL 不提供查看什么被存储到 ibdata1 共享表空间的信息,但是有两个工具将会很有帮助。第一个是马克·卡拉汉制作的一个修改版 innochecksum ,它发布在这个漏洞报告里。

    它相当易于使用:

    1. # ./innochecksum /var/lib/mysql/ibdata1
    2. 0 bad checksum
    3. 13 FIL_PAGE_INDEX
    4. 19272 FIL_PAGE_UNDO_LOG
    5. 230 FIL_PAGE_INODE
    6. 1 FIL_PAGE_IBUF_FREE_LIST
    7. 892 FIL_PAGE_TYPE_ALLOCATED
    8. 2 FIL_PAGE_IBUF_BITMAP
    9. 195 FIL_PAGE_TYPE_SYS
    10. 1 FIL_PAGE_TYPE_TRX_SYS
    11. 1 FIL_PAGE_TYPE_FSP_HDR
    12. 1 FIL_PAGE_TYPE_XDES
    13. 0 FIL_PAGE_TYPE_BLOB
    14. 0 FIL_PAGE_TYPE_ZBLOB
    15. 0 other
    16. 3 max index_id

    全部的 20608 中有 19272 个撤销日志页。这占用了表空间的 93%

    第二个检查表空间内容的方式是杰里米·科尔制作的 InnoDB Ruby 工具。它是个检查 InnoDB 的内部结构的更先进的工具。例如我们可以使用 space-summary 参数来得到每个页面及其数据类型的列表。我们可以使用标准的 Unix 工具来统计撤销日志页的数量:

    1. # innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l
    2. 19272

    尽管这种特殊的情况下,innochedcksum 更快更容易使用,但是我推荐你使用杰里米的工具去了解更多的 InnoDB 内部的数据分布及其内部结构。

    好,现在我们知道问题所在了。下一个问题:

    我该怎么解决问题?

    这个问题的答案很简单。如果你还能提交语句,就做吧。如果不能的话,你必须要杀掉线程开始回滚过程。那将停止 ibdata1 的增长,但是很显然,你的软件会出现漏洞,有些人会遇到错误。现在你知道如何去鉴定问题所在,你需要使用你自己的调试工具或普通的查询日志来找出谁或者什么引起的问题。

    如果问题发生在清除线程,解决方法通常是升级到新版本,新版中使用一个独立的清除线程替代主线程。更多信息查看该文档

    有什么方法回收已使用的空间么?

    没有,目前还没有一个容易并且快速的方法。InnoDB 表空间从不收缩...参见10 年之久的漏洞报告,最新更新自詹姆斯·戴(谢谢):

    当你删除一些行,这个页被标为已删除稍后重用,但是这个空间从不会被回收。唯一的方法是使用新的 ibdata1 启动数据库。要做这个你应该需要使用 mysqldump 做一个逻辑全备份,然后停止 MySQL 并删除所有数据库、ib_logfile*ibdata1* 文件。当你再启动 MySQL 的时候将会创建一个新的共享表空间。然后恢复逻辑备份。

    总结

    当 ibdata1 文件增长太快,通常是 MySQL 里长时间运行的被遗忘的事务引起的。尝试去解决问题越快越好(提交或者杀死事务),因为不经过痛苦缓慢的 mysqldump 过程,你就不能回收浪费的磁盘空间。

    也是非常推荐监控数据库以避免这些问题。我们的 MySQL 监控插件包括一个 Nagios 脚本,如果发现了一个太老的运行事务它可以提醒你。


    ============================

       这两种文件都是存放Innodb 数据的文件,之所以有两种文件来存放Innodb 的数据(包
    括索引),是因为Innodb 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数
    据,还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据,且
    每个表一个“.ibd”文件,文件存放在和MyISAM 数据相同的位置
    如果选用共享存储表空
    间来存放数据,则会使用ibdata 文件来存放,所有表共同使用一个(或者多个,可自行配

    置)ibdata 文件

        ibdata 文件可以通过innodb_data_home_dir 和innodb_data_file_path

    两个参数共同配置组成, innodb_data_home_dir 配置数据存放的总目录, 而
    innodb_data_file_path 配置每一个文件的名称。当然, 也可以不配置
    innodb_data_home_dir 而直接在innodb_data_file_path 参数配置的时候使用绝对路径来

    完成配置。innodb_data_file_path 中可以一次配置多个ibdata 文件。

        文件可以是指定大

    小,也可以是自动扩展的,但是Innodb 限制了仅仅只有最后一个ibdata 文件能够配置成自
    动扩展类型。当我们需要添加新的ibdata 文件的时候,只能添加在innodb_data_file_path

    配置的最后,而且必须重启MySQL 才能完成ibdata 的添加工作。

        不过如果我们使用独享表

    空间存储方式的话,就不会有这样的问题,但是如果要使用裸设备的话,每个表一个裸设备,
    可能造成裸设备数量非常大,而且不太容易控制大小,实现比较困难,而共享表空间却不会
    有这个问题,容易控制裸设备数量。我个人还是更倾向于使用独享表空间存储方式。当然,
    两种方式各有利弊,看大家各自应用环境的侧重点在那里了。
        上面仅仅介绍了两种最常用存储引擎的数据文件,此外其他各种存储引擎都有各自的数
    据文件,读者朋友可以自行创建某个存储引擎的表做一个简单的测试,做更多的了解。

    展开全文
  • MySQL的ibdata1文件占用过大瘦身

    万次阅读 2018-10-08 11:43:15
    处理MySQL的ibdata1文件过大问题 本人在对数据库进行大量的数据插入和删除的时候,发现ibdata1的占了将近一个T ibdata1文件是什么? ibdata1是一个用来构建innodb系统表空间的文件,这个文件包含了innodb表的元...

    处理MySQL的ibdata1文件过大问题

    本人在对数据库进行大量的数据插入和删除的时候,发现ibdata1的占了将近一个T

    ibdata1文件是什么?

    ibdata1是一个用来构建innodb系统表空间的文件,这个文件包含了innodb表的元数据、撤销记录、修改buffer和双写buffer。如果file-per-table选项打开的话,该文件则不一定包含所有表的数据。当innodb_file_per_table选项打开的话,新创建表的数据和索引则不会存在系统表空间中,而是存放在各自表的.ibd文件中.

    显然这个文件会越来越大,innodb_autoextend_increment选项则指定了该文件每次自动增长的步进,默认是8M.

    是什么原因导致ibdata1文件会越来越大?

    ibdata1存放数据,索引和缓存等,是MYSQL的最主要的数据。所以随着数据库越来越大,表也会越大,这个无法避免的。如果时间长了,越来越大,我们在处理日志和空间的时候就不是那么方便了,就不知从何入手了。接下来我们就要处理下这样的情况,分库存储数据。

    该如何处理呢?

    首先我们把数据库文件备份下来,然后直接删除ibdata文件(为了保险起见最好先全备一次,做到数据安全和完整),然后再重新导入数据库文件即可!

    具体操作步骤如下(截图并不完整,但是首先要弄懂大概情况和原理):

    第一种方法:

    1、停止业务,备份一次全库

    mysqldump -uroot -ppassword --all-databases  > all_mysql.sql

    2、备份完成,停止数据库

    systemctl stop mariadb 或者 service mysqld stop

    3、修改配置文件

    在[mysqld]下增加下面配置 innodb_file_per_table=1 验证配置是否生效,可以重启mysql后,执行 #service mysqld restart

    4、验证

    mysql -uroot -ppassword mysql

    show variables like '%per_table%';

    +-----------------------+-------+

    | Variable_name | Value |

    +-----------------------+-------+

    | innodb_file_per_table | ON |

    +-----------------------+-------+

    1 row in set (0.00 sec)

    innodb_file_per_table的状态变为ON

    5、删除ibdata1文件和日志

    rm -rf ibdata1

    rm -rf ib_logfile*

    6、还原数据库

    mysql -uuser -ppassword

    source all_mysql.sql

    数据文件单独存放(共享表空间改为每个表独立的表空间文件)。

    第二种方法:

    把数据库的表引擎为InnoDB 的数据表转为MyIsam 后,删除ibdata1,按上面方法修改成独立的表空间,在把改成MyIsam引擎的表改为InnoDB,这个就要衡量那种方法的时间耗时最短,两者取其优。数据的表和库很多的通常不建议这么做,耗时间。

    展开全文
  • 如何在删除ibdata1的情况下恢复

    千次阅读 2018-07-21 16:47:22
    1, 将原数据文件保存至其他路径 2,创建同名表结构 3, 导出表空间 4, 将原数据.ibd文件copy回来 5, 导入表空间   创建表t1,并插入数据 mysql&gt; show create table t1; +-------+---------------...

    参考:https://www.cnblogs.com/ivictor/p/5784258.html

    基本步骤

    1, 将原数据文件保存至其他路径

    2,创建同名表结构

    3, 导出表空间

    4, 将原数据.ibd文件copy回来

    5, 导入表空间

     

    创建表t1,并插入数据

    mysql> show create table t1;
    +-------+--------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                             |
    +-------+--------------------------------------------------------------------------------------------------------------------------+
    | t1    | CREATE TABLE `t1` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(100) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +-------+--------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from t1;
    +------+--------+
    | id   | name   |
    +------+--------+
    |    1 | qing   |
    |    1 | 大秦   |
    +------+--------+
    2 rows in set (0.00 sec)
    
    mysql>

    将ibdata1和ib_logfile0-2移走:

    [root@localhost data]# ls
    auto.cnf  ibdata1      ib_logfile1  itdb                       localhost-relay-bin.000005  master.info  mysql-bin.000001  mysql-bin.000003  mysql-bin.000005  percona             relay-log.info  tony
    books     ib_logfile0  ib_logfile2  localhost.localdomain.pid  localhost-relay-bin.index   mysql        mysql-bin.000002  mysql-bin.000004  mysql-bin.index   performance_schema  test
    [root@localhost data]#
    [root@localhost data]# mkdir /tmp/bak
    [root@localhost data]# mv ibdata1 /tmp/bak
    [root@localhost data]# mv ib_log* /tmp/bak
    [root@localhost data]# ls
    auto.cnf  itdb                       localhost-relay-bin.000005  master.info  mysql-bin.000001  mysql-bin.000003  mysql-bin.000005  percona             relay-log.info  tony
    books     localhost.localdomain.pid  localhost-relay-bin.index   mysql        mysql-bin.000002  mysql-bin.000004  mysql-bin.index   performance_schema  test
    [root@localhost data]#

    重启mysql,系统会重新创建者几个文件

    [root@localhost data]# service mysqld restart
    Shutting down MySQL.... SUCCESS!
    Starting MySQL.. SUCCESS!
    [root@localhost data]# ls
    auto.cnf  ibdata1      ib_logfile1  itdb                       localhost-relay-bin.000006  master.info  mysql-bin.000001  mysql-bin.000003  mysql-bin.000005  mysql-bin.index  performance_schema  test
    books     ib_logfile0  ib_logfile2  localhost.localdomain.pid  localhost-relay-bin.index   mysql        mysql-bin.000002  mysql-bin.000004  mysql-bin.000006  percona          relay-log.info      tony
    [root@localhost data]#

    查看error log也可以看到创建的记录:

    2018-01-21 13:15:32 2753 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
    2018-01-21 13:15:32 2753 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
    2018-01-21 13:15:32 2753 [Note] InnoDB: Database physically writes the file full: wait...
    2018-01-21 13:15:32 2753 [Note] InnoDB: Setting log file ./ib_logfile101 size to 32 MB
    2018-01-21 13:15:32 2753 [Note] InnoDB: Setting log file ./ib_logfile1 size to 32 MB
    2018-01-21 13:15:32 2753 [Note] InnoDB: Setting log file ./ib_logfile2 size to 32 MB
    2018-01-21 13:15:32 2753 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

    然后登陆mysql,访问刚刚创建的t1,显示 ERROR 1146 (42S02): Table 'tony.t1' doesn't exist

    mysql> show tables;
    +----------------+
    | Tables_in_tony |
    +----------------+
    | t1             |
    +----------------+
    1 row in set (0.00 sec)
    
    mysql> desc t1;
    ERROR 1146 (42S02): Table 'tony.t1' doesn't exist
    mysql> select * from t1;
    ERROR 1146 (42S02): Table 'tony.t1' doesn't exist
    mysql>

    为什么呢?虽然我们开启了innodb_file_per_table功能,让innodb的数据存储在t1.ibd下,但是共享表空间ibdata1中存储有数据字典,innodb没有数据字典,那么它就找不到这个表了;参考

    https://blog.csdn.net/jswangchang/article/details/81138240

    [root@localhost data]# cat /etc/my.cnf | grep innodb_file_per_table
    innodb_file_per_table=1					#very table has individule data space
    [root@localhost data]# ls tony/
    db.opt  t1.frm  t1.ibd

    接下来我们把表数据移动至其他地方,然后创建相同表结构的表:

    [root@localhost tony]# mv * /tmp/bak/
    [root@localhost tony]# ls
    [root@localhost tony]# ls /tmp/bak/
    db.opt  ibdata1  ib_logfile0  ib_logfile1  ib_logfile2  t1.frm  t1.ibd
    mysql> show tables;
    Empty set (0.00 sec)
    
    mysql> CREATE TABLE `t1` (
        ->   `id` int(11) DEFAULT NULL,
        ->   `name` varchar(100) DEFAULT NULL
        -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    ERROR 1146 (42S02): Table 'tony.t1' doesn't exist
    mysql>
    mysql> drop table t1;
    ERROR 1051 (42S02): Unknown table 'tony.t1'
    mysql> CREATE TABLE `t1` (
        ->   `id` int(11) DEFAULT NULL,
        ->   `name` varchar(100) DEFAULT NULL
        -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> show tables;
    +----------------+
    | Tables_in_tony |
    +----------------+
    | t1             |
    +----------------+
    1 row in set (0.00 sec)
    

    此时t1是个新表,里面没有数据,此时我们导出表空间,导出后ibd文件就看不到了,然后我们把原t1.ibd文件copy过来;然后修改属组和属主权限

    mysql> select * from  t1;
    Empty set (0.01 sec)
    
    mysql> alter table t1 discard tablespace;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql>
    [2]+  Stopped                 mysql  (wd: /tmp/mysql-5.6/data)
    (wd now: /tmp/mysql-5.6/data/tony)
    [root@localhost tony]# ls
    t1.frm
    [root@localhost tony]# cp /tmp/bak/t1.ibd ./
    [root@localhost tony]# ls
    t1.frm  t1.ibd
    [root@localhost tony]# ll
    total 108
    -rw-rw---- 1 mysql mysql  8586 Jan 21 13:24 t1.frm
    -rw-r----- 1 root  root  98304 Jan 21 13:27 t1.ibd
    [root@localhost tony]# chown mysql.mysql t1.ibd
    mysql> alter table t1 import tablespace;
    Query OK, 0 rows affected, 1 warning (0.03 sec)
    
    mysql> show warnings;
    +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
    | Level   | Code | Message                                                                                                                                 |
    +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
    | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './tony/t1.cfg', will attempt to import without schema verification |
    +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from t1;
    +------+--------+
    | id   | name   |
    +------+--------+
    |    1 | qing   |
    |    1 | 大秦   |
    +------+--------+
    2 rows in set (0.00 sec)
    
    mysql> flush tables;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> select * from t1;
    +------+--------+
    | id   | name   |
    +------+--------+
    |    1 | qing   |
    |    1 | 大秦   |
    +------+--------+
    2 rows in set (0.01 sec)

    然后导入表空间,再select就可以查到数据了;flush tables刷新内存后依然可以;

    删除ibdata1的时候,会影响所有的innodb表,包括系统表中的innodb表;有哪些系统表是Innodb? 在V5.6中比较少,只有下面几个,但是在V5.7以后就有很多了;

    mysqlv5.6 >select table_schema,table_name,engine from information_schema.tables where engine='innodb' and table_schema='mysql';
    +--------------+----------------------+--------+
    | table_schema | table_name           | engine |
    +--------------+----------------------+--------+
    | mysql        | innodb_index_stats   | InnoDB |
    | mysql        | innodb_table_stats   | InnoDB |
    | mysql        | slave_master_info    | InnoDB |
    | mysql        | slave_relay_log_info | InnoDB |
    | mysql        | slave_worker_info    | InnoDB |
    +--------------+----------------------+--------+
    5 rows in set (0.01 sec)
    
    mysqlv5.6 >select * from mysql.innodb_table_stats;
    ERROR 1146 (42S02): Table 'mysql.innodb_table_stats' doesn't exist
    mysql5.7 >select table_schema,table_name,engine from information_schema.tables where engine='innodb' and table_schema in ('mysql','sys','information_schema','performance_schema');
    +--------------------+---------------------------+--------+
    | table_schema       | table_name                | engine |
    +--------------------+---------------------------+--------+
    | information_schema | COLUMNS                   | InnoDB |
    | information_schema | EVENTS                    | InnoDB |
    | information_schema | OPTIMIZER_TRACE           | InnoDB |
    | information_schema | PARAMETERS                | InnoDB |
    | information_schema | PARTITIONS                | InnoDB |
    | information_schema | PLUGINS                   | InnoDB |
    | information_schema | PROCESSLIST               | InnoDB |
    | information_schema | ROUTINES                  | InnoDB |
    | information_schema | TRIGGERS                  | InnoDB |
    | information_schema | VIEWS                     | InnoDB |
    | mysql              | engine_cost               | InnoDB |
    | mysql              | gtid_executed             | InnoDB |
    | mysql              | help_category             | InnoDB |
    | mysql              | help_keyword              | InnoDB |
    | mysql              | help_relation             | InnoDB |
    | mysql              | help_topic                | InnoDB |
    | mysql              | innodb_index_stats        | InnoDB |
    | mysql              | innodb_table_stats        | InnoDB |
    | mysql              | plugin                    | InnoDB |
    | mysql              | server_cost               | InnoDB |
    | mysql              | servers                   | InnoDB |
    | mysql              | slave_master_info         | InnoDB |
    | mysql              | slave_relay_log_info      | InnoDB |
    | mysql              | slave_worker_info         | InnoDB |
    | mysql              | time_zone                 | InnoDB |
    | mysql              | time_zone_leap_second     | InnoDB |
    | mysql              | time_zone_name            | InnoDB |
    | mysql              | time_zone_transition      | InnoDB |
    | mysql              | time_zone_transition_type | InnoDB |
    | sys                | sys_config                | InnoDB |
    +--------------------+---------------------------+--------+
    30 rows in set (0.01 sec)
    

    如果ibdata1被删除了要恢复,必须新建表结构,按照上面的步骤来一遍;

    展开全文
  • MySQL 5.6 如何给ibdata1瘦身

    千次阅读 2018-06-11 15:30:09
    前不久刚给ibdata1瘦身,发篇文章总结下。ibdata1是MySQL使用InnoDB引擎时所产生的文件,其一般存储数据、索引、结构、缓冲数据、共享数据和重做日志等。因为ibdata1只增不减,长期操作数据库,可能会使其越来越大,...

    bcd91d07a0f4e35b0273efed

    前不久刚给ibdata1瘦身,发篇文章总结下。

    ibdata1是MySQL使用InnoDB引擎时所产生的文件,其一般存储数据、索引、结构、缓冲数据、共享数据和重做日志等。因为ibdata1只增不减,长期操作数据库,可能会使其越来越大,而浪费空间。

    加上使用InnoDB引擎时,没有添加innodb_file_per_table参数也是导致ibdata1过大的原因。

    但InnoDB只增不减,也导致给ibdata1瘦身是件比较麻烦的事。

    最大的我见过的是40多G的ibdata1文件,实际数据库差不多是20多G,在做了优化后,ibdata1缩小至20多G,所以说减肥还是有必要的。

    首先,先略微说下innodb_file_per_table参数,使用该参数可使得InnoDB引擎转变为独立表空间模式(默认为共享表空间),也就是每个数据库的每个表都会生成一个数据空间,就像MyISAM引擎一样。

    独立表空间优点就是每个表有独立空间,数据和索引都会存在自已的表空间中,可以实现单表在不同的数据库中移动。重要的是空间可以回收,而且不管日常怎么操作,表空间的碎片不会太严重的影响性能,优化表的速度也快,表文件出现问题不会大动干戈,只要修复对应表即可。缺点是单表占用的空间比共享表空间方式稍大,共享表空间在Insert操作上有一些优势。

    所以没增加innodb_file_per_table参数的同学,建议还是加上吧。因为增加innodb_file_per_table参数,与我们后续给ibdata1瘦身并无冲突,而且对以后也只有好处。

    说了这么多废话,言归正传。谈谈怎么给ibdata1瘦身,唯一的方法是就是备份整个数据库,然后删掉ibdata1和ib_logfile*,再恢复数据库,以此达到瘦身目的。当然了,操作数据库肯定是有风险的,而且也需要生产环境允许MySQL暂停写或访问。

    简单的总结就是以下这几点:

    1.在/etc/my.cnf中添加“innodb_force_recovery=4”使InnoDB成为只读表,这其实应该说是第一个坑。另外确定“innodb_data_file_path”参数限定的初始ibdata1大小在合理范围,一般稍大于现有数据大小。
    2.启动MySQL,使用我给的工具备份除了mysql、information_schema和performance_schema的整个数据库。为以后顺利恢复数据做准备。
    3.删除除了mysql、information_schema和performance_schema的整个数据库,这3个排除在外的其实也删不掉。
    4.停止MySQL,删除ibdata1和ib_logfile*文件。
    5.删除数据库目录中的mysql目录的innodb_index_stats.*、innodb_table_stats.*、slave_master_info.*、slave_relay_log_info.*、slave_worker_info.*文件,这5个是InnoDB的基础表(状态表),这是MySQL 5.6的坑,删除ibdata1后不会自动重建这5个表,而且如果不删除这些旧文件,还不可创建或重建新的。
    6.在/etc/my.cnf把“innodb_force_recovery=4”去除。
    7.启动MySQL,将第2步备份的数据库还原,然后用从MySQL 5.6导出来的InnoDB基础表备份还原回去重建(第5步删掉的)。
    8.搞定,重启下MySQL,确保没有错误,没有异常。

    必要的工具,在文末的Github地址下载。

    下面详细说说每个步骤,首先是设置InnoDB为只读表,这还是比较必要的。可确保你的数据完整性、安全性。为何是坑呢,因为我遇到过没这样设置,导致后续恢复备份时,数据有异常。

    具体操作,在/etc/my.cnf中添加“innodb_force_recovery=4”

    ...
    [mysqld]
    ...
    innodb_force_recovery = 4
    ...

    如此便可,innodb_file_per_table也是在[mysqld]下添加。

    innodb_force_recovery的值可以设置为1-6,大的数字包含其前面所有数字的影响。

    1. (SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
    2. (SRV_FORCE_NO_BACKGROUND):阻止主线程的运行,如主线程需要执行full purge操作,会导致crash。
    3. (SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
    4. (SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
    5. (SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交。
    6. (SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

    接着呢,启动MySQL,备份整个数据库,一般我们会用

    mysqldump --lock-all-tables --all-databases > all-dbs.sql

    来完整备份数据库。但这样的话,就包含了mysql、information_schema和performance_schema这3个数据库,而在后续还原数据库,带着这3个数据库可能会出错(我有几次遇到过)。

    所以我提供了shell脚本工具mysql_dump_all_db.sh,因为怕大家不像我是免密码操作MySQL的,在使用工具前,请先修改脚本中的MySQL帐号和密码,然后再通过

    sh mysql_dump_all_db.sh

    执行备份操作,备份好的文件,会存放在脚本运行所在目录。

    这个工具默认排除mysql、information_schema和performance_schema这3个数据库,如果你有其他想排除的,可以直接修改脚本,增加其他想要排除的数据库。

    完了之后呢,删除所有数据库,可以通过phpMyadmin或直接在shell操作MySQL删除,在shell下删除,可以在/tmp/DatabasesToDump.txt查看到所有数据库,由“mysql_dump_all_db.sh”生成。我一般在phpMyadmin删除,简单,不怕错。如你所见,mysql、information_schema和performance_schema这3个数据库是删不掉的,所以要排除,免得麻烦。

    搞定后,就可以停止MySQL了。删除数据库目录的ibdata1和ib_logfile*文件,一般是在/usr/local/mysql/data,看你怎么配置的了。

    接着,在该目录下的mysql目录(/usr/local/mysql/data/mysql)中,把innodb_index_stats.*、innodb_table_stats.*、slave_master_info.*、slave_relay_log_info.*、slave_worker_info.*共计10个文件删除,这些文件已经无用了,而且占着茅坑不拉屎。MySQL 5.6很煞笔的不会重建这5个表,如果你不删除他们,待会将无法重建或恢复这5个表,接着log一直在报错,死循环。所以要把这5个表的10个文件干掉。所以这个是个坑。

    在/etc/my.cnf把“innodb_force_recovery=4”去除后,就可以启动MySQL了,这时候ibdata1和ib_logfile*文件会重建。噢,上帝,胜利在望,不要激动,让我们继续吧。

    把刚才备份的所有数据库还原,用从MySQL 5.6导出来的InnoDB基础表备份还原回去重建。

    我在后面的Github地址有提供,从全新 MySQL 5.6导出的,名字为“mysql_innodb_basic_tables.sql”的备份文件,通过它可以重建刚才删掉的5个InnoDB基础表。

    还原数据库文件非常简单,不过我还是略微写下,照顾下小白,在shell下:

    mysql < all-dbs.sql
    mysql < mysql_innodb_basic_tables.sql

    这样就OK了,如没有免密码操作权限,请自行添加-u和-p参数。

    好的,做完这些,重启下MySQL,确保没有错误即可。这样就完成了对ibdata1的瘦身。

    以上操作环境为:CentOS 6.6 x64、MySQL 5.6.25。

    工具存放在Github中(原谅我的渣英文),见: https://github.com/kn007/Reduce-Shrink-Purge-the-ibdata1-file-in-MySQL

    写这篇文章就是为了大家少走一点弯路,也把自己遇到的坑说一下。算是个总结,也是给后人的一些经验。原则上不提供技术支持,有问题请自行解决。另外毕竟是数据库,瘦身有风险,操作需谨慎。

    展开全文
  • 如何清理ibdata1

    千次阅读 2018-07-21 18:14:14
    经过一段时间后,DBAer会发现ibdata1越来越大,里面存储的东西我们之前已经说过了 那么如何清理ibdata1呢? 1, 加锁,然后全备份数据,可以用mysqldump,也可以使用其他的工具: [root@localhost data]# ...
  • ibdata1是什么?

    2017-10-24 15:38:11
    MySQL使用InnoDB引擎的时候,ibdata1这个文件会随着时间的增长,会变得越来越大,占据大量的磁盘空间。那么,ibdata1里保存了哪些东西,为什么会变得越来越大呢,让我们开看看ibdata1的构造。ibdata1是InnoDB的共有...
  • 在MySQL 5.6.6版本以前,MySQL默认会把所有的innodb的表都放在同一个文件中(ibdata1),当该文件过大的时候,MySQL容易出错,维护性能差。 但那些在修改之前已经创建了innodb的表怎么办? 一种办法是用mysqldump备份...
  • 提供ibdata1和frm文件恢复数据库的方法之一,仅供参考 ========== 1. 应用背景 ========== 为了应付9月的计算机二级MySQL考试,买了一套题库。但是在练操作题的时候发现其中的十几套试卷大题都只提供了frm文件和...
  • mysqlibdata1 恢复

    万次阅读 2012-07-12 18:28:38
    你好 请问一下你安装mysql时倒数第二步提示输入当前密码的问题怎么解决的 我也遇到了同样的问题. ... ------------------------- ...rar只是人家压缩的而已,要想恢复先把以前的库文件都拷贝出来,就是
  • 如果误删除了在线服务器中mysql innodb相关的数据文件ibdata1以及日志文件 ib_logfile*,应该怎样恢复
  • 没有数据库备份,只剩下数据库下面的一些文件(frm、idb),具体原因是因为出现问题的时候,重装了MySQL,最要命的是ibdata1等文件也没有了,当然这中间细节过程如何,不清楚也不用去纠结了。大概就是这么一个情况。...
  • mysql 误删ibdata1文件 恢复数据

    千次阅读 2019-04-11 16:58:26
    mysql Innodb存储的数据,data目录下 ibd文件存储数据, 把原来的data目录下的数据库文件备份一下 删除数据库 再重建同名数据库,或者直接重命名原来的数据库 新建原来同名数据库 如果有原来数据库的表结构 先...
  • mysql删除了ibdata1恢复数据库

    千次阅读 2018-11-23 17:43:52
    不小心删除了ibdata1且清空回收站。网上各种搜索恢复数据库的方法,这是整理下来的东西. windows系统下,mysql5.6版本 思路: 1. 数据库的表格文件存在且完整无损。 2. MyISAM/InnoDB两种类型的表结构恢复。 3. ...
  • mysql ibdata1

    千次阅读 2018-07-20 21:36:14
    Mysql ibdata1即Innodb data1缩写,是innodb引擎的表空间,用于存放 数据字典Data dictionary: 只读的表,存储对象的相关信息,如占用空间,列的缺省值,约束信息,用户名,权限,审计信息等; 双写缓冲区 ...
  • 关于mysql数据库ibdata1损坏了 恢复

    千次阅读 2018-09-07 22:29:16
    1、公司mysql数据库 2个,合计50G+ 2、准备将其中一个移到新增数据盘中,做ln -s软链接。...损坏了ibdata1文件。 数据库无法启动了   1-----(SRVFORCEIGNORECORRUPT):忽略检查到的corrupt页。 2-----(SRVFORC...
  • mysql的data目录中恢复数据库 某台mysql数据库,突然掉电,重启系统启动数据库失败,进入数据库目录发现数据文件完好,从新初始化数据库,从数据文件中恢复数据 一、将/home/mysql-5.7.26/data/目录复制出来 ...
  • Mysql ibdata1文件恢复问题 1、导入ibdata1文件到data文件夹后Mysql不能正常启动怎么办? 解决办法将data文件夹中的ib_logfile0、ib_logfile1、lenovo-PC.err文件删除,因为mysql会根据你的电脑创建相应的以上...
  • mysql data文件夹下的ibdata1 文件作用

    万次阅读 2012-11-30 10:36:32
    mysql data文件夹下的ibdata1 文件作用   这个文件超级大, 查了一下, 大概的作用如下 是储存的格式 INNODB类型数据状态下, ibdata用来储存文件的数据 而库名的文件夹里面的那些表文件只是结构...
  • 专门针对MYSQLibdata1 引擎 编写,支持MYSQL 3 4 5 6版本,任意平台的IBDATA文件恢复。支持误删除 ,所在分区被格式化,支持***故意破坏等情况,自动侦测半页。提取合成。 转载于:...
  • mysql 里的 ibdata1 文件

    千次阅读 2016-01-06 12:15:07
    为什么 mysql 里的 ibdata1 文件不断的增长? 转自:http://linux.cn/article-5829-rss.html ibdata1 file 我们在 Percona 支持栏目经常收到关于 MySQLibdata1 文件的这个问题。 当监控...

空空如也

1 2 3 4 5 ... 20
收藏数 19,606
精华内容 7,842
关键字:

ibdata1