Showing entries 11 to 12
« 10 Newer Entries
Displaying posts with tag: innodb_file_per_table (reset)
Fixing InnoDB IMPORT TABLESPACE Error: ERROR 1030 (HY000): Got error -1 from storage engine

SetupWe have one InnoDB file per table on our database, which was set with the following option: innodb_file_per_table.This allows me to portably transport Innodb files on a system level with minimal hassle, and is much faster than mysqldump, as these tables are several GB each.ProblemWhen transporting an .idb file from one server to another, I ran into the following error:ERROR 1030 (HY000): Got

innodb_file_per_table Revisited

In a previous post, I was trying to figure out the most optimal way to switch from two large innodb table space files to using innodb_file_per_table to take advantage of some of the benefits of this setting. I had one part of it solved, which was to stop MySQL, add innodb_file_per_table to the my.cnf, then restart, perform a "no-op" alter of "ALTER TABLE t1 ENGINE=InnoDB" which would cause the table to be re-created an it's own .ibd file. The remaining problem was how to be able to resize the huge table space files after converting all the tables to a smaller size (in my case from 10GB to 10MB).

Someone suggested a better way:

1. Alter all innodb tables to MyISAM
2. Stop the server
3. Add innodb_file_per_table to my.cnf
4. Change innodb_data_file_path to new settings (10MB tablespaces) in my.cnf
5. Move all innodb files (logs, data) to a backup directory
6. Restart MySQL
7. Alter …

[Read more]
Showing entries 11 to 12
« 10 Newer Entries