Vryjdfkk

After having set innodb_file_per_table=1, dumping and dropping all db's, deleting ibdata1, and restarting mysql ... My ibdata1 file is small at last...

However, directly after reading in my dump, and seeing that all tables now all have their own big ibd files (approx 15GB), but still the ibdata1 is over 2.6 GB again...

The way I dump my data is with:

mysqldump --tab="D:Mysql Databasesdumpsdump%%a" --opt --single-transaction --no-autocommit -u root -p

This dumps to .sql (DDL / table structures) and .txt (actual data) files. These files I can use to recover with with:

create database myDb;

use myDb;

SET autocommit=0;

SET unique_checks=0;

SET foreign_key_checks=0;

SOURCE *.sql; -- for each table

COMMIT;

SET autocommit=0;

LOAD DATA INFILE '*.txt'; -- for each table

COMMIT;

SET autocommit=1;

SET foreign_key_checks=1;

SET unique_checks=1;

Why is the ibdata1 file growing again after enabling innodb_file_per_table and having succesfully shrunk ibdata1? And is there a way now to reclaim again this data, seen as all ibd data is already present per table?

Can I for example copy the small ibdata1 after recreating the database with the *.sql files, and placing back that ibdata1 file after reading in the data from the *.txt files?

Here are the non-default items in my.ini:

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=151

tmp_table_size=256M

thread_cache_size=20

myisam_max_sort_file_size=1G

myisam_sort_buffer_size=32M

key_buffer_size=64M

innodb_log_buffer_size=8M

innodb_buffer_pool_size=6G

innodb_log_file_size=1G

innodb_thread_concurrency=16

innodb_open_files=1000

join_buffer_size=2M

max_allowed_packet=16M

group_concat_max_len = 10M

bulk_insert_buffer_size=512M

collation-server = utf8_unicode_ci

Ibdata1 file holds a lot of information and allocates space for different innodb functionality (double write buffer, undo logs, tablespace header, etc.). Therefore even with innodb_file_per_table you cannot drop/replace the ibdata1 file.

One common reason why ibdata can grow is because it has the undo space. If you have long running transactions the previous version of rows can pile up in the undo log easily. More about this can be read on person's blog:
https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

In MySQL 5.7 there are a lot of improvements around undo tablespace management including truncation and separate files to hold this.