Delete ibddata1 file on innodb database

Question

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?

Answer 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.

Answer 2

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.

Answer 3

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?