0

I have recently changed my mariadb database from innodb_file_per,_table =off to innodb_file_per_table = on . I checked out yesterday and I saw that idbdata 1 file is extreme large 30G .

I believe it has left from the time my db was myIsam.

Is it OK if delete it ? Will be recreated when I restart mariadb?

1
  • Glad you checked your belief before acting, but as a good general rule, if you can't manipulate database files with a SQL interface, and its not documented to manually do anything with the file in the Knowledge Base, its best to just leave it alone. 30G isn't big for database storage.
    – danblack
    Jan 9, 2022 at 23:07

3 Answers 3

1

... changed my mariadb database from myIsam to innodb ... idbdata1 ... extreme large 30G ...
I believe it has left from the time my db was myIsam.

Sorry, but you are incorrect.
myIsam stores its data in .MYD files, one per table.
InnoDB stores all of its its data in the ibdata1 file, so that 30G file is most, if not all, of your database.

Is it OK if delete it?

Given the above? Not really, No.
For that matter, you should not manually delete any database-managed file without thinking very, very long and hard about it.

1
  • Sorry I misstyped. It wasn't myISAM but innodb_file_per_table=off. Really sorry for the mistake. See my updated question. Jan 13, 2022 at 20:34
1

NO, do NOT delete ibdata1.

InnoDB tables tend to be 2x to 3x bigger than MyISAM. That is one of the very few drawbacks of InnoDB.

It might have been better to have innodb_file_per_table = ON when converting from MyISAM. That would not have shrunk the total disk footprint but would have stored things differently. It is probably not worth worrying about now.

2
  • I am having innodb_file_per = ON since almost s year ago. I think file ibdata1 exist before a made this change. I believe it's no longer need. Jan 13, 2022 at 20:30
  • 1
    @Christoforos - It IS still used. Do NOT delete it. (Shrinking is non-trivial.)
    – Rick James
    Jan 13, 2022 at 23:10
0

Warning
Please ensure you have a working backup of your database before you perform any of the following steps.

Follow these steps from the official Maria DB documentation to shrink the size of your idbdata1 file:

Decreasing the Size

In cases where the InnoDB system tablespace has grown too large, the process to reduce it in size is a little more complicated than increasing the size. MariaDB does not allow you to remove data from the tablespace file itself. Instead you need to delete the tablespace files themselves, then restore the database from backups.

The backup utility mysqldump produces backup files containing the SQL statements needed to recreate the database. As a result, it restores a database with the bare minimum data rather than any additional information that might have built up in the tablespace file.

Use mysqldump to backup all of your InnoDB database tables, including the system tables in the mysql database that use InnoDB. You can find out what they are using the Information Schema.

SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mysql' AND ENGINE = 'InnoDB';  

If you only use InnoDB, you may find it easier to back up all databases and tables.

$ mysqldump -u root -p --all-databases > full-backup.sql

Then stop the MariaDB Server and remove the InnoDB tablespace files. In the data directory or the InnoDB data home directory, delete all the ibdata and ib_log files as well as any file with an .ibd or .frm extension.

Once this is done, restart the server and import the dump file:

$ mysql -u root -p < full-backup.sql

Reference: InnoDB System Tablespaces (MariaDB Documentation)

Answering Your Questions

Is it OK if delete it ?

No, unless you have a working backup and have performed the above steps to save relevant data.

Will be recreated when I restart maria db?

Yes, but what use is an empty non-functional idbdata1 file?

1
  • As it was a recent change, a dump/import isn't likely to change much.
    – danblack
    Jan 9, 2022 at 23:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.