Wolg · April 2, 2015 08:35 · Apr 2, 2015
diff --git a/gistfile1.txt b/gistfile1.txt
@@ -0,0 +1,37 @@
+To shrink ibdata1 once and for all you must do the following:
+
+Dump (e.g., with mysqldump) all databases into a .sql text file (SQLData.sql is used below)
+
+Drop all databases (except for mysql and information_schema) CAVEAT : As a precaution, please run this script to make absolutely sure you have all user grants in place:
+
+mkdir /var/lib/mysql_grants
+cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/.
+chown -R mysql:mysql /var/lib/mysql_grants
+Login to mysql and run SET GLOBAL innodb_fast_shutdown = 0; (This will completely flush all remaining transactional changes from ib_logfile0 and ib_logfile1)
+
+Shutdown MySQL
+
+Add the following lines to /etc/my.cnf (or my.ini on Windows)
+
+[mysqld]
+innodb_file_per_table
+innodb_flush_method=O_DIRECT
+innodb_log_file_size=1G
+innodb_buffer_pool_size=4G
+(Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.
+
+Also: innodb_flush_method=O_DIRECT is not available on Windows)
+
+Delete ibdata* and ib_logfile*, Optionally, you can remove all folders in /var/lib/mysql, except /var/lib/mysql/mysql.
+
+Start MySQL (This will recreate ibdata1 [10MB by default] and ib_logfile0 and ib_logfile1 at 1G each).
+
+Import SQLData.sql
+
+Now, ibdata1 will still grow but only contain table metadata because each InnoDB table will exist outside of ibdata1. ibdata1 will no longer contain InnoDB data and indexes for other tables.
+
+For example, suppose you have an InnoDB table named mydb.mytable. If you look in /var/lib/mysql/mydb, you will see two files representing the table:
+
+mytable.frm (Storage Engine Header)
+mytable.ibd (Table Data and Indexes)
+With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.