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 Databases\dumps\dump\%%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