InnoDB and the innodb file per table option - Revision history

Docs admin: /* Converting existing InnoDB tables */

2013-01-22T21:45:26Z

Converting existing InnoDB tables

← Older revision		Revision as of 17:45, 22 January 2013
Line 36:		Line 36:
	The changes made above will only apply to new tables created after innodb_file_per_table was enabled. Any tables that previously existed prior to enabling this option will still be using the shared tablespace in ibdata1. We'll now need to switch these tables over to per-table tablespaces. The following SQL query will compile a list of tables using InnoDB and generate the necessary commands to convert them each of them to a per-table tablespace. This query should be run from the MySQL command line.		The changes made above will only apply to new tables created after innodb_file_per_table was enabled. Any tables that previously existed prior to enabling this option will still be using the shared tablespace in ibdata1. We'll now need to switch these tables over to per-table tablespaces. The following SQL query will compile a list of tables using InnoDB and generate the necessary commands to convert them each of them to a per-table tablespace. This query should be run from the MySQL command line.

	<syntaxhighlight lang="bash">select concat('alter table ',TABLE_SCHEMA ,'.',table_name,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables \		<syntaxhighlight lang="bash">select concat('alter table ',TABLE_SCHEMA ,'.',table_name,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables
	where table_type='BASE TABLE' and engine = 'InnoDB';</syntaxhighlight>		where table_type='BASE TABLE' and engine = 'InnoDB';</syntaxhighlight>

Docs admin at 21:27, 22 January 2013

2013-01-22T21:27:14Z

← Older revision		Revision as of 17:27, 22 January 2013
Line 1:		Line 1:
	{{DISPLAYTITLE:InnoDB and the innodb_file_per_table option}}		{{DISPLAYTITLE:InnoDB and the innodb_file_per_table option}}

	==Introduction==		==Introduction==

Docs admin at 21:27, 22 January 2013

2013-01-22T21:27:07Z

← Older revision		Revision as of 17:27, 22 January 2013
Line 1:		Line 1:
			{{DISPLAYTITLE:InnoDB and the innodb_file_per_table option}}

	==Introduction==		==Introduction==

Docs admin: /* Continued use of ibdata1 */

2013-01-22T21:24:12Z

Continued use of ibdata1

← Older revision		Revision as of 17:24, 22 January 2013
Line 66:		Line 66:
	===Continued use of ibdata1===		===Continued use of ibdata1===

	If you've followed this guide, the majority of your MySQL data is no contained within separate .ibd files. However, the ibdata1 file is still used by MySQL for the storage of its internal data dictionary and undo logs. The ibdata1 file should never be deleted from the server.		If you've followed this guide, the majority of your MySQL data is now contained within separate .ibd files. However, the ibdata1 file is still used by MySQL for the storage of its internal data dictionary and undo logs. The ibdata1 file should never be deleted from the server.

Docs admin at 21:24, 22 January 2013

2013-01-22T21:24:02Z

← Older revision		Revision as of 17:24, 22 January 2013
Line 54:		Line 54:
	Each of these commands can be run to move the respective table from the shared tablespace to per-table. These commands would need to be run via the MySQL command line.		Each of these commands can be run to move the respective table from the shared tablespace to per-table. These commands would need to be run via the MySQL command line.

	==~~Issues you may encounter~~==		==Further Considerations==

	===System Load===		===System Load===
Line 63:		Line 63:

	As MySQL creates a new ''.ibd'' file for your table, it will require the necessary disk space to do so. Please ensure you have plenty of free space on your MySQL partition before attempting this process. Please also note that your ibdata1 file may not reduce in size after converting your tables to per-table tablespaces.		As MySQL creates a new ''.ibd'' file for your table, it will require the necessary disk space to do so. Please ensure you have plenty of free space on your MySQL partition before attempting this process. Please also note that your ibdata1 file may not reduce in size after converting your tables to per-table tablespaces.

			===Continued use of ibdata1===

			If you've followed this guide, the majority of your MySQL data is no contained within separate .ibd files. However, the ibdata1 file is still used by MySQL for the storage of its internal data dictionary and undo logs. The ibdata1 file should never be deleted from the server.

Docs admin: /* Introduction */

2013-01-22T21:20:09Z

Introduction

← Older revision		Revision as of 17:20, 22 January 2013
Line 11:		Line 11:
	Since all MySQL data is stored within this file, a single corrupt table can create problems for your entire MySQL service. When corruption occurs within an InnoDB table, it is often necessary to drop the table and restore it from a current copy of your backups. As always, the key to running a reliable database service is to run frequent backups of your databases. If your InnoDB engine is storing the data and indexes for all tables within ibdata1, recovering from a crash can mean restoring '''all''' of your database backups, a time-consuming and tedious process.		Since all MySQL data is stored within this file, a single corrupt table can create problems for your entire MySQL service. When corruption occurs within an InnoDB table, it is often necessary to drop the table and restore it from a current copy of your backups. As always, the key to running a reliable database service is to run frequent backups of your databases. If your InnoDB engine is storing the data and indexes for all tables within ibdata1, recovering from a crash can mean restoring '''all''' of your database backups, a time-consuming and tedious process.

	To address this issue, you should enable the innodb_file_per_table option in your my.cnf file.		To address this issue, you should enable the innodb_file_per_table option in your my.cnf file which will create a separate file and tablespace for each InnoDB table.

	==Enabling the innodb_file_per_table option==		==Enabling the innodb_file_per_table option==

Docs admin: /* Introduction */

2013-01-22T21:19:35Z

Introduction

← Older revision		Revision as of 17:19, 22 January 2013
Line 9:		Line 9:
	<syntaxhighlight lang="bash">/var/lib/mysql/ibdata1</syntaxhighlight>		<syntaxhighlight lang="bash">/var/lib/mysql/ibdata1</syntaxhighlight>

	Since all MySQL data is stored within this file, a single corrupt table can create problems for your entire MySQL service. When corruption occurs within an InnoDB table, it is often necessary to drop the table and restore it from a current copy of your backups. As always, the key to running a reliable database service is to run frequent backups of your databases. ~~However, if~~ your InnoDB engine is storing the data and indexes for all tables within ibdata1, recovering from a crash can mean restoring '''all''' of your database backups, a time-consuming and tedious process.		Since all MySQL data is stored within this file, a single corrupt table can create problems for your entire MySQL service. When corruption occurs within an InnoDB table, it is often necessary to drop the table and restore it from a current copy of your backups. As always, the key to running a reliable database service is to run frequent backups of your databases. If your InnoDB engine is storing the data and indexes for all tables within ibdata1, recovering from a crash can mean restoring '''all''' of your database backups, a time-consuming and tedious process.

	To address this issue, you should enable the innodb_file_per_table option in your my.cnf file.		To address this issue, you should enable the innodb_file_per_table option in your my.cnf file.

Docs admin: /* Introduction */

2013-01-22T21:18:53Z

Introduction

← Older revision		Revision as of 17:18, 22 January 2013
Line 1:		Line 1:
	==Introduction==		==Introduction==

	Starting with MySQL 5.5, InnoDB is the default storage engine for MySQL. In ~~earlier~~ versions, you can enable or disable the InnoDB storage engine as you desire. If you are running a default installation of MySQL 5.5 or have explicitly enabled InnoDB, there are some recommended practices you should be aware of before using your system in a production environment.		Starting with MySQL 5.5, InnoDB is the default storage engine for MySQL. In most versions of MySQL, you can enable or disable the InnoDB storage engine as you desire. If you are running a default installation of MySQL 5.5 or have explicitly enabled InnoDB, there are some recommended practices you should be aware of before using your system in a production environment.

	Within this article, we will be specifically talking about the innodb_file_per_table option. For a full list of recommended best practices, please see the MySQL Reference Manual [http://dev.mysql.com/doc/refman/5.5/....html#id971855 Best Practices for InnoDB Tables]		Within this article, we will be specifically talking about the innodb_file_per_table option. For a full list of recommended best practices, please see the MySQL Reference Manual [http://dev.mysql.com/doc/refman/5.5/....html#id971855 Best Practices for InnoDB Tables]
Line 11:		Line 11:
	Since all MySQL data is stored within this file, a single corrupt table can create problems for your entire MySQL service. When corruption occurs within an InnoDB table, it is often necessary to drop the table and restore it from a current copy of your backups. As always, the key to running a reliable database service is to run frequent backups of your databases. However, if your InnoDB engine is storing the data and indexes for all tables within ibdata1, recovering from a crash can mean restoring '''all''' of your database backups, a time-consuming and tedious process.		Since all MySQL data is stored within this file, a single corrupt table can create problems for your entire MySQL service. When corruption occurs within an InnoDB table, it is often necessary to drop the table and restore it from a current copy of your backups. As always, the key to running a reliable database service is to run frequent backups of your databases. However, if your InnoDB engine is storing the data and indexes for all tables within ibdata1, recovering from a crash can mean restoring '''all''' of your database backups, a time-consuming and tedious process.

	To address this issue, you should enable the innodb_file_per_table option in your my.cnf file.		To address this issue, you should enable the innodb_file_per_table option in your my.cnf file.

	==Enabling the innodb_file_per_table option==		==Enabling the innodb_file_per_table option==

Docs admin at 21:18, 22 January 2013

2013-01-22T21:18:23Z

← Older revision		Revision as of 17:18, 22 January 2013
Line 1:		Line 1:
			==Introduction==

	Starting with MySQL 5.5, InnoDB is the default storage engine for MySQL. In earlier versions, you can enable or disable the InnoDB storage engine as you desire. If you are running a default installation of MySQL 5.5 or have explicitly enabled InnoDB, there are some recommended practices you should be aware of before using your system in a production environment.		Starting with MySQL 5.5, InnoDB is the default storage engine for MySQL. In earlier versions, you can enable or disable the InnoDB storage engine as you desire. If you are running a default installation of MySQL 5.5 or have explicitly enabled InnoDB, there are some recommended practices you should be aware of before using your system in a production environment.

Docs admin: /* Converting existing InnoDB tables */

2013-01-22T21:16:27Z

Converting existing InnoDB tables

← Older revision		Revision as of 17:16, 22 January 2013
Line 38:		Line 38:
	As an example, let's assume I have an existing database using InnoDB tables. This database has the following InnoDB tables in it:		As an example, let's assume I have an existing database using InnoDB tables. This database has the following InnoDB tables in it:

			<pre>
	myexampledatabase.table1		myexampledatabase.table1

	myexampledatabase.table2		myexampledatabase.table2

	myexampledatabase.table3		myexampledatabase.table3
			</pre>

	In this example, I've just enabled innodb_file_per_table and I want to convert table1, table2, and table3 over to the per-table tablespace. When I run the above SQL query, I will receive this output:		In this example, I've just enabled innodb_file_per_table and I want to convert table1, table2, and table3 over to the per-table tablespace. When I run the above SQL query, I will receive this output: