Contents
How do you reduce the size of ibdata1?
How to Shrink MySQL ibdata1 Size using innodb_file_per_table
- Big MySQL (and MariaDB) System Tablespace.
- Set the innodb_file_per_table parameter.
- New Tables (and index) as individual files.
- Extract existing tables from ibdata1.
- Shrink ibdata1 File Size.
- Backup the Database.
- Drop all your database.
- Delete ibdata and ib_logfile.
How do I change the InnoDB log size?
To change the number or size of InnoDB log files, perform the following steps:
- Stop the MySQL server and make sure that it shuts down without errors.
- Edit my. cnf to change the log file configuration. To change the log file size, configure innodb_log_file_size .
- Start the MySQL server again.
When do I delete ibdata1 file in MySQL?
When you start MySQL in step 5 the ibdata1 and ib_log files will be recreated. Now you’re fit to go. When you create a new database for analysis, the tables will be located in separate ibd* files, not in ibdata1. As you usually drop the database soon after, the ibd* files will be deleted.
How does ibdata1 increase the size of a file?
Using autoextend (probably the most common size setting) ibdata1 preallocates storage, growing each time it is nearly full. That makes writes faster as space is already allocated. When you delete data it doesn’t shrink but the space inside the file is marked as unused.
Is there a way to shrink an IBD file?
As you usually drop the database soon after, the ibd* files will be deleted. By using the command ALTER TABLE ENGINE=innodb or OPTIMIZE TABLE one can extract data and index pages from ibdata1 to separate files. However, ibdata1 will not shrink unless you do the steps above.
Is there a way to shrink an InnoDB file?
Unfortunately, by design InnoDB data files cannot be shrinked. That’s why DELETEs, TRUNCATEs, DROPs, etc. will not reclaim the space used by the file. Save as purge_binlogs.sh and run as root. Excludes mysql, information_schema, performance_schema (and binlog directory).