2

I have a large database with hundreds of tables and over millions of records. I recently noticed that the ibdata1 file has a size of 360GB. It occupies a huge size in my server. I searched for several ways to reduce it and found it couldn't be shrunk.

Just for reference, what would happen if I just delete that ibdata1 file and create a empty new file in it's place...

Edit: In this question, the user deletes his file and unable to find older data after restarting the 'mysqld' service.

I'm planning to delete the old ibdata1 file and replace is with a empty ibdata1 file and want to know if it'll work.

Community
  • 1
  • 1
Dani Akash
  • 6,828
  • 3
  • 35
  • 47
  • yeah i checked that question but mine is different.. see the edit please – Dani Akash May 25 '16 at 11:19
  • It depends on what you mean by "it'll work" - it certainly won't work the way that it did initially. You will lose all of your data. Depending on your config, you'll lose table structure too, but that depends how you set up your innodb. You'll need to be more specific about what results you are expecting. Also your assertion that "it couldn't be shrunk" isn't really true - see this question for why and a possible solution for you: http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql – Kristen Waite May 25 '16 at 12:02
  • 1
    If ibdata1 is missing when mysqld starts, it creates the file. You will have no tables. – Rick James May 31 '16 at 22:28

2 Answers2

2

ibdata1 contains InnoDB dictionary which is vitally important for InnoDB. If you delete it any access to a table will fail with Table doesn't exist error.

akuzminsky
  • 2,190
  • 15
  • 21
1

if innodb_file_per_table is enabled then the tables can be restored via this and this.

Blatent copy of https://dba.stackexchange.com/a/57157/189538

MyISAM

For a MyISAM table mydb.mytable, you should have three files

  • \bin\mysql\mysql5.6.12\data\mydb\mytable.frm
  • \bin\mysql\mysql5.6.12\data\mydb\mytable.MYD
  • \bin\mysql\mysql5.6.12\data\mydb\mytable.MYI

They should already be accessible as a table since each file contains needed data, metadata, and index info. Collectively, they form the table. There are no external storage engine mecahnisms to access.

InnoDB

Take a look at this Pictorial Representation of InnoDB

InnoDB Architecture

The only thing that attaches ibdata1 to the .ibd files is the data dictionary.

Your mission, should you decide to accept it, is to create each table and swap in the .ibd

Before you do anything, make a full copy of "\bin\mysql\mysql5.6.12\data" to another

Here is a sample

Suppose you have a database mydb with the table mytable. This means

  • You have the folder \bin\mysql\mysql5.6.12\data\mydb
  • Inside that folder, you have
    • mytable.frm
    • mytable.ibd

You need the .frm. If you look at my post https://dba.stackexchange.com/questions/44314/how-can-extract-the-table-schema-from-just-the-frm-file/44316#44316, you can download a MySQL utility that can generate the SQL needed to create the table.

You should now do the following

  • Move mytable.ibd to \bin\mysql\mysql5.6.12\data
  • Run the SQL to create the InnoDB table
  • Login to mysql and run ALTER TABLE mydb.mytable DISCARD TABLESPACE; (This will delete \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd)
  • Copy \bin\mysql\mysql5.6.12\data\mytable.ibd into \bin\mysql\mysql5.6.12\data\mydb
  • Login to mysql and run ALTER TABLE mydb.mytable IMPORT TABLESPACE; (This will register \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd into the data dictionary)

After this, the table mydb.mytable should be fully accessible. You can test that accessibility by simply running:

SELECT * FROM mydb.mytable LIMIT 10;

Give it a Try !!!

DRINK (Data Recovery Incorporates Necessary Knowledge) Responsibly

Blatant copy of https://dba.stackexchange.com/a/93511/189538

Not as part of MySQL, but tools like Percona Xtrabackup makes the process a bit faster for exporting/backing up the tables, allowing things like using regular expressions or lists for filtering:

For importing a list of tables, you can use some automation oneliners like this one found on Bill Karwin tools:

mysqldump --no-data $schema > schema-ddl.sql
mysql -N -B <<'EOF' > discard-ddl.sql
SELECT CONCAT('ALTER TABLE `', table_name, '` DISCARD TABLESPACE;') AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$schema' AND ENGINE='InnoDB';
EOF
mysql -N -B <<'EOF' > import-ddl.sql
SELECT CONCAT('ALTER TABLE `', table_name, '` IMPORT TABLESPACE;') AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$schema' AND ENGINE='InnoDB';
EOF

Basically, you can use the information_schema.tables table for listing the tables you want using "dynamic sql". For example, change $schema above for the desired database name.

Brenda.ZMPOV
  • 61
  • 1
  • 7