InnoDB

by

InnoDB. Provides MySQL with a transactional storage engine ( ACID compliant ) with commit (commit), rollback (cancel), and crash recovery capabilities.

Summary

[ hide ]

  • 1 Features
  • 2 Configuration
    • 1 Windows
    • 2 Unix
    • 3 Global options
    • 4 User specific options
  • 3 Create the table space
  • 4 Troubleshooting Initialization
  • 5 Back up and recover a database
  • 6 Source

characteristics

InnoDB performs row level locks and also provides consistent, Oracle- style non-locking read functions on SELECT statements. These features increase performance and the ability to manage multiple simultaneous users. No scaled lock is needed in InnoDB because row-level locks take up very little space.

InnoDB also supports FOREIGN KEY constraints. In SQL queries , even within the same query, tables of the InnoDB type can be freely included with tables of other types.

InnoDB was designed for maximum performance when processing large volumes of data. Probably no other disk relational database engine matches its efficiency in CPU usage .

Despite being fully integrated with the MySQL server, the InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a table space, which can consist of several files (or disk partitions). This differs, for example, in the MyISAM engine, where each table is stored using separate files, InnoDB tables can be of any size, even on operating systems where the file size is limited to 2GB.

In MySQL 5.0, InnoDB is included by default in binary distributions. The Windows Essentials installer sets InnoDB as the default MySQL database type on Windows .

InnoDB is used in many large database sites that need high performance. The famous internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. stores more than 1TB of data in InnoDB, and other sites handle an average load of 800 inserts and updates per second in InnoDB.

Setting

In MySQL 5.0 , the InnoDB storage engine is enabled by default. If you don’t want to use InnoDB tables, you can add the skip-innodb option to the MySQL options file.

Two very important disk-based resources that the InnoDB storage engine manages are its table space data files and its log files. If no configuration options are specified for InnoDB, MySQL 5.0 creates in the MySQL data directory a 10MB (self-extending) data file called ibdata1 and two 5MB log files called ib_logfile0 and ib_logfile1.

To configure InnoDB table space files, the innodb_data_file_path option must be used in the MySQL section of the my.cnf options file. In Windows, my.ini can be used instead. The value of innodb_data_file_path should be a list of one or more file specifications. If more than one data file will be included, they must be separated with a semicolon (‘;’):

innodb_data_file_path = data_file_spec1 [; data_file_spec2] …

When creating an InnoDB table space for the first time, it is best to start the MySQL server from the command line. Then, InnoDB will print the information about the creation of databases on the screen, so that you can see what is happening.

Windows

The rules for options files in Windows are as follows:

  • Only the my.cnf or my.ini file should be created, but not both.
  • The my.cnf file must be placed in the root directory of drive C.
  • The my.ini file should be placed in the WINDIR directory; for example, C: \ WINDOWS or C: \ WINNT. The SET command can be used in a console window to display the value of WINDIR:

C: \> SET WINDIR

windir = C: \ WINNT

  • If the computer uses a bootloader where the C: drive is not the boot drive, it is only possible to use the my.ini file.
  • If MySQL was installed using the installation and configuration wizards, the my.ini file is located in the MySQL installation directory.

Unix

On Unix, MysQL reads the options in the following files, if they exist, in the following order:

/etc/my.cnf

Global options

  • $ MYSQL_HOME / my.cnf
  • Server specific options.

defaults-extra-file

  • The file specified with the –defaults-extra-file option.

/.my.cnf

User-specific options

  • MYSQL_HOME represents an environment variable which contains the path to the directory that hosts the server-specific my.cnf file.
  • If you want to make sure that mysqld reads your options only from a certain file, you can use –defaults-option as the first option on the command line when the server starts:

mysqld –defaults-file = path_to_my_cnf

Create the tablespace

Assuming that MySQL has been installed and the options file has been edited to contain the necessary InnoDB parameters, before starting MySQL it should be verified that the directories indicated for the InnoDB data and log files exist and that the server MySQL has access permissions to these directories. InnoDB cannot create directories, only files. You should also verify that you have enough disk space for the data and log files.

When creating an InnoDB database, it is better to run the MySQL server, mysqld from the command line, not from the mysqld_safe wrapper or as a Windows service. When run from the command line, you can see what mysqld prints and what is happening. On Unix, mysqld simply needs to be invoked. In Windows, you have to use the console option.

Troubleshooting initialization

If InnoDB prints an operating system error on a file operation, the problem is usually one of the following:

  • The directory for the InnoDB data or log files was not created.
  • MysQLd does not have access permissions to create files in those directories.
  • MysQLd cannot read the proper my.cnf or my.ini options file, and therefore does not see the specified options.
  • The disk is full or the disk quota is exceeded.
  • A subdirectory has been created that has the same name as one of the specified data files.
  • There is a syntax error in innodb_data_home_dir or innodb_data_file_path.

If something goes wrong during InnoDB’s attempt to initialize the table space or log files, all files created by InnoDB should be deleted. This includes all ibdata files and all ib_logfile. If you have created any InnoDB table, you will have to delete the corresponding .frm files from these tables from the MySQL data directory (and any ibd file if you are using multiple table spaces). Then creating the InnoDB database can be tried again. It is better to start the MySQL server from a command line so that what is happening can be seen.

Back up and recover a database

InnoDB Hot Backup is an online backup tool that can be used to back up the InnoDB database while it is running. InnoDB Hot Backup does not require the database to stop and does not set any locks or hinder normal database processing.

If you are able to stop the MySQL server, a binary backup can be made, consisting of all the files used by InnoDB to manage your tables. The following procedure is used:

  • Stop the MySQL server and make sure it does it without errors.
  • Copy all data files (ibdata and .ibd files) to a safe place.
  • Copy all ib_logfile files to a safe place.
  • Copy the my.cnf configuration file (s) to a safe place.
  • Copy all the .frm files from the InnoDB tables to a safe place.

Replication works with InnoDB tables, so it can be used to maintain a copy of the database at database sites that need high availability.

 

by Abdullah Sam
I’m a teacher, researcher and writer. I write about study subjects to improve the learning of college and university students. I write top Quality study notes Mostly, Tech, Games, Education, And Solutions/Tips and Tricks. I am a person who helps students to acquire knowledge, competence or virtue.

Leave a Comment