InnoDB - Revision history

Jan: 10 revisions imported

2016-08-26T19:10:42Z

10 revisions imported

← Older revision

Revision as of 21:10, 26 August 2016

Jan: 1 revision imported

2016-08-26T19:09:41Z

1 revision imported

← Older revision

Revision as of 21:09, 26 August 2016

Jan: /* Script */

2013-11-08T19:00:00Z

Script

← Older revision		Revision as of 21:00, 8 November 2013
Line 55:		Line 55:
	echo "done"		echo "done"
	echo -n "dropping database ${DB} ... "		echo -n "dropping database ${DB} ... "
	/usr/bin/mysqladmin drop ${DB}		/usr/bin/mysqladmin -f drop ${DB}
	echo "done"		echo "done"
	done < ${CWD}/innodb-databases.list		done < ${CWD}/innodb-databases.list

Jan: /* Script */

2013-11-06T19:53:36Z

Script

← Older revision		Revision as of 21:53, 6 November 2013
Line 64:		Line 64:
	echo -n "backing up 'ibdata1', 'ib_logfile0' and 'ib_logfile1' files ... "		echo -n "backing up 'ibdata1', 'ib_logfile0' and 'ib_logfile1' files ... "
	mv -v /var/lib/mysql/ibdata1 /var/lib/mysql/bak.ibdata1		mv -v /var/lib/mysql/ibdata1 /var/lib/mysql/bak.ibdata1
	mv -v /var/lib/mysql/~~bak.~~ib_logfile0 /var/lib/mysql/bak.ib_logfile0		mv -v /var/lib/mysql/ib_logfile0 /var/lib/mysql/bak.ib_logfile0
	mv -v /var/lib/mysql/~~bak.~~ib_logfile1 /var/lib/mysql/bak.ib_logfile1		mv -v /var/lib/mysql/ib_logfile1 /var/lib/mysql/bak.ib_logfile1
	echo "done"		echo "done"

Jan: /* Script */

2013-08-27T07:08:27Z

Script

← Older revision		Revision as of 09:08, 27 August 2013
Line 79:		Line 79:
	echo "done"		echo "done"
	done < ${CWD}/innodb-databases.list		done < ${CWD}/innodb-databases.list
			rm -v ${CWD}/innodb-databases.list
	</pre>		</pre>

	When the script is done and everything's working as expected, you can remove 'bak.ibdata1', 'bak.ib_logfile0', 'bak.ib_logfile1' and all the SQL dump files.		When the script is done and everything's working as expected, you can remove 'bak.ibdata1', 'bak.ib_logfile0', 'bak.ib_logfile1' and all the SQL dump files.

Jan: /* Reclaim Disk Space */

2013-03-03T13:51:39Z

Reclaim Disk Space

← Older revision		Revision as of 15:51, 3 March 2013
Line 2:		Line 2:
	If you have a couple of tables that tend to grow and shrink a lot, your 'ibdata1' file will grow and never shrink again, even if you drop all those tables or the whole database. The right solution for me was to make MySQL use separate tablespaces for all InnoDB tables, because those can be shrunk by running an OPTIMIZE on the table.		If you have a couple of tables that tend to grow and shrink a lot, your 'ibdata1' file will grow and never shrink again, even if you drop all those tables or the whole database. The right solution for me was to make MySQL use separate tablespaces for all InnoDB tables, because those can be shrunk by running an OPTIMIZE on the table.
	If you set '''innodb_file_per_table''' before creating an InnoDB table, it will be created with its own tablespace.		If you set '''innodb_file_per_table''' before creating an InnoDB table, it will be created with its own tablespace.
	If you already have existing an InnoDB table and would like to convert it to its own tablespace, you need to set '''innodb_file_per_table=1''' and then run the following for each InnoDB table:		If you already have an existing InnoDB table and would like to convert it to its own tablespace, you need to set '''innodb_file_per_table=1''' and then run the following for each InnoDB table:
	<pre>		<pre>
	ALTER TABLE myDatabase.myTable ENGINE=InnoDB;		ALTER TABLE myDatabase.myTable ENGINE=InnoDB;

Jan: /* Reclaim Disk Space */

2013-02-20T22:28:52Z

Reclaim Disk Space

← Older revision		Revision as of 00:28, 21 February 2013
Line 15:		Line 15:
	The conversion process may take quite a while, depending on the amount of data, as it basically creates a copy of the table in the separate tablespace file. You better make sure you have at least the same amount of free disk space as the size of your tables.		The conversion process may take quite a while, depending on the amount of data, as it basically creates a copy of the table in the separate tablespace file. You better make sure you have at least the same amount of free disk space as the size of your tables.

	After you've converted all your tables, you may notice that there's still the '''ibdata1''' file, which is probably rather huge, as it contained all the data that's now in separate files. There is no easy way to reclaim the disk space it occupies, you will have to dump all tables that use InnoDB (really, absolutely all in your server), then drop them, shut down the MySQL daemon, move/delete 'ibdata1' and the log files, start the MySQL daemon again, and then import the dumps.		After you've converted all your tables, you may notice that there's still the '''ibdata1''' file, which is probably rather huge, as it contained all the data that's now in separate files. There is no easy way to reclaim the disk space it occupies, you can't just purge the file or truncate or optimize it, you will have to dump all tables that use InnoDB (really, absolutely all in your server), then drop them, shut down the MySQL daemon, move/delete 'ibdata1' and the log files, start the MySQL daemon again, and then import the dumps.

	If you only remove the ibdata1 file without dumping and then importing the databases again, you MySQL daemon won't know anything about any InnoDB tables and just silently ignore them, that's why you must re-recreate them from the dumps.		If you only remove the ibdata1 file without dumping and then importing the databases again, you MySQL daemon won't know anything about any InnoDB tables and just silently ignore them, that's why you must re-recreate them from the dumps.

Jan: /* Reclaim Disk Space */

2013-02-20T22:25:33Z

Reclaim Disk Space

← Older revision		Revision as of 00:25, 21 February 2013
Line 7:		Line 7:
	</pre>		</pre>

	Here's an quick command to generate ~~those queries~~ for ~~you~~:		Here's an quick command to generate this query for all your InnoDB tables:
	<pre>		<pre>
	/usr/bin/mysql -NB -e "SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', table_name, ' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE' AND engine = 'InnoDB';"		/usr/bin/mysql -NB -e "SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', table_name, ' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE' AND engine = 'InnoDB';"
	</pre>		</pre>

	The conversion process may take quite a while, depending on the amount of data, as it basically creates a copy of the table in the separate tablespace file.		It's probably a good idea to run each of the generated queries manually, just in case something unforeseen (disk space?) happens.
			The conversion process may take quite a while, depending on the amount of data, as it basically creates a copy of the table in the separate tablespace file. You better make sure you have at least the same amount of free disk space as the size of your tables.

	After you're converted all your tables, you may notice that there's still the '''ibdata1''' file, which is probably rather huge, as it contained all the data that's now in separate files. There is no easy way to reclaim the disk space it occupies, you will have to dump all tables that use InnoDB (really, absolutely all in your server), then drop them, shut down the MySQL daemon, move/delete 'ibdata1' and the log files, start the MySQL daemon again, and then import the dumps.		After you've converted all your tables, you may notice that there's still the '''ibdata1''' file, which is probably rather huge, as it contained all the data that's now in separate files. There is no easy way to reclaim the disk space it occupies, you will have to dump all tables that use InnoDB (really, absolutely all in your server), then drop them, shut down the MySQL daemon, move/delete 'ibdata1' and the log files, start the MySQL daemon again, and then import the dumps.

	If you only remove the ibdata1 file without dumping and then importing the databases again, you MySQL daemon won't know anything about any InnoDB tables and just silently ignore them, that's why you must re-recreate them from the dumps.		If you only remove the ibdata1 file without dumping and then importing the databases again, you MySQL daemon won't know anything about any InnoDB tables and just silently ignore them, that's why you must re-recreate them from the dumps.

Jan: /* Reclaim Disk Space */

2013-02-20T22:23:00Z

Reclaim Disk Space

← Older revision		Revision as of 00:23, 21 February 2013
Line 7:		Line 7:
	</pre>		</pre>

	Here's an quick command to generate those ~~commands~~ for you:		Here's an quick command to generate those queries for you:
	<pre>		<pre>
	/usr/bin/mysql -NB -e "~~select concat~~('ALTER TABLE ', TABLE_SCHEMA, '.', table_name, ' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE' AND engine = 'InnoDB';"		/usr/bin/mysql -NB -e "SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', table_name, ' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE' AND engine = 'InnoDB';"
	</pre>		</pre>

Jan: /* Reclaim Disk Space */

2013-02-20T22:22:21Z

Reclaim Disk Space

← Older revision		Revision as of 00:22, 21 February 2013
Line 2:		Line 2:
	If you have a couple of tables that tend to grow and shrink a lot, your 'ibdata1' file will grow and never shrink again, even if you drop all those tables or the whole database. The right solution for me was to make MySQL use separate tablespaces for all InnoDB tables, because those can be shrunk by running an OPTIMIZE on the table.		If you have a couple of tables that tend to grow and shrink a lot, your 'ibdata1' file will grow and never shrink again, even if you drop all those tables or the whole database. The right solution for me was to make MySQL use separate tablespaces for all InnoDB tables, because those can be shrunk by running an OPTIMIZE on the table.
	If you set '''innodb_file_per_table''' before creating an InnoDB table, it will be created with its own tablespace.		If you set '''innodb_file_per_table''' before creating an InnoDB table, it will be created with its own tablespace.
	If you already have existing an InnoDB table and would like to convert it to its own tablespace, you need to run:		If you already have existing an InnoDB table and would like to convert it to its own tablespace, you need to set '''innodb_file_per_table=1''' and then run the following for each InnoDB table:
	<pre>		<pre>
	ALTER TABLE myDatabase.myTable ENGINE=InnoDB;		ALTER TABLE myDatabase.myTable ENGINE=InnoDB;