How do I reduce InnoDB file size?

How do I reduce InnoDB file size?

Decreasing the Size of the InnoDB System Tablespace

1. Use mysqldump to dump all of your InnoDB tables, including InnoDB tables located in the mysql schema.
2. Stop the server.
3. Remove all of the existing tablespace files ( *.
4. Remove any .
5. Configure the data files for the new system tablespace.
6. Restart the server.

How do I shrink the purge ibdata1 file in MySQL?

How to Shrink MySQL ibdata1 Size using innodb_file_per_table

1. Big MySQL (and MariaDB) System Tablespace.
2. Set the innodb_file_per_table parameter.
3. New Tables (and index) as individual files.
4. Extract existing tables from ibdata1.
5. Shrink ibdata1 File Size.
6. Backup the Database.
7. Drop all your database.
8. Delete ibdata and ib_logfile.

Can I delete ibdata1 file in MySQL?

That ibdata1 isn’t shrinking is a particularly annoying feature of MySQL. The ibdata1 file can’t actually be shrunk unless you delete all databases, remove the files and reload a dump. But you can configure MySQL so that each table, including its indexes, is stored as a separate file.

Can I remove ibdata1?

The ibdata1 file can be shrinked if you delete all databases, remove the files and reload the mysqldump. We can configure MySQL so that each table, including its indexes, is stored as a separate file. As you want to reclaim the space from ibdata1 you actually have to delete the file: Stop mysql.

How big is the ibdata1 file in MySQL?

As you may have noticed in your MySQL’s data directory (in Debian/Ubuntu – /var/lib/mysql) lies a file called ‘ibdata1′. It holds almost all the InnoDB data (it’s not a transaction log) of the MySQL instance and could get quite big. By default this file has a initial size of 10Mb and it automatically extends.

How to shrink MySQL ibdata1 size using InnoDB?

How to Shrink MySQL ibdata1 Size using innodb_file_per_table. In MySQL, when you are using InnoDB, all the tables and indexes are stored under the MySQL system tablespace. The single ibdata1 file contains all the tables and indexes in your MySQL database. So, if you have a big database, this file size will grow really big.

How to tell the size of an ibdata file?

If you are convinced the data dictionary is the cause, there is only one sure way you can verify this. The number that comes out will be the size will be the size of ibdata1 with just data dictionary entries for the current list of tables, but with no data in them.

What is the best way to reduce the size of ibdata?

To shrink ibdata1 once and for all you must do the following Step 01) MySQLDump all databases into a SQL text file (call it SQLData.sql) ( More details here) Step 02) Drop all databases (except mysql, performance_schema, and information_schema)