« Previous | Next»

Enabling InnoDB support in debian (MySQL)

Posted by coldtobi | 17 Mar, 2011, 10:17

InnoDB as storage engine has some advantages: Just one example: it is the backend to use when foreign keys are required.

For a complete picute, read the documenation:  http://dev.mysql.com/doc/refman/5.1/en/innodb-storage-engine.html

 However, on Debian InnoDB is disabled by default: (take a look at /etc/mysql/my.cnf)

 skip-innodb

However, it's not just uncommeting to enable it: Your mysqldaemon will fail.  This article will explain how you can enable it with Debian

Of course, this assumes that you already setup mysql on your Debian server.

1.) To check which storage engines are available, so to determine if you have it already enabled, you can execute the following SQL statement (for example, from a shell using the command "mysql -u root -p"):

thecus:/home/tobi# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> SHOW ENGINES;
+------------+---------+--------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                      | Transactions | XA   | Savepoints |
+------------+---------+--------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                        | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappear | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                           | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables    | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                               | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                       | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance       | NO           | NO   | NO         |
+------------+---------+--------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

Anzeige
In this example, InnoDB is missing ;-) But how to enable it?

Here are the three steps required:

1) Edit /etc/mysql/my.cnf and comment out the skip-inno-db line. Add the following lines

#skip-innodb
innodb_data_home_dir =
innodb_data_file_path = /var/lib/mysql/ibdata/ibdata1:100M:autoextend

This will create a initally 100MByte big database file at the given location -- which does not exist yet: 

2) Create the database location and set ownership -- of course you need to do that as root.

mkdir  /var/lib/mysql/ibdata/
chown -R mysql:mysql /var/lib/mysql/ibdata

3) Let mysql initialize the database files.

On my (slow) database server I figured out that a /etc/init.d/mysql restart did not wait long enough for mysql until it gets ready. So I suggest to manually start the first time. 

Note the "&" at the end -- I start mysql in the background.    The reason for starting mysqld in the background is, that I want to conveniently stop the daemon after it initalized the database -- if run in the foreground, Ctrl+C and Ctrl+Z seems to be trapped.

 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --port=3306 --socket=/var/run/mysqld/mysqld.sock &
 [1] 9724
 thecus:/var/lib/mysql#
InnoDB: The first specified data file /var/lib/mysql/ibdata/ibdata1 did not exist:
 InnoDB: a new database to be created!
 110317 10:58:52  InnoDB: Setting file /var/lib/mysql/ibdata/ibdata1 size to 100 MB
 InnoDB: Database physically writes the file full: wait...
 InnoDB: Progress in MB: 100
 110317 10:58:57  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
 InnoDB: Setting log file ./ib_logfile0 size to 5 MB
 InnoDB: Database physically writes the file full: wait...
 110317 10:58:58  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
 InnoDB: Setting log file ./ib_logfile1 size to 5 MB
 InnoDB: Database physically writes the file full: wait...
 InnoDB: Doublewrite buffer not found: creating new
 InnoDB: Doublewrite buffer created
 InnoDB: Creating foreign key constraint system tables
 InnoDB: Foreign key constraint system tables created
 110317 10:59:00  InnoDB: Started; log sequence number 0 0
 110317 10:59:00 [Note] Event Scheduler: Loaded 0 events
 110317 10:59:00 [Note] /usr/sbin/mysqld: ready for connections.
 Version: '5.1.49-3'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)

After that, the files are created -- you can check it with ls /var/lib/mysql/ibdata/

To stop mysql after it did create the database file, get its pid with jobs -p and ask it to terminate. (Note: Make sure to use your pid.... below )

thecus:/var/lib/mysql# jobs -p
9724
thecus:/var/lib/mysql# kill <your pid> # in my example it would be 9724
thecus:/var/lib/mysql# 110317 11:03:50 [Note] /usr/sbin/mysqld: Normal shutdown

110317 11:03:50 [Note] Event Scheduler: Purging the queue. 0 events
110317 11:03:50  InnoDB: Starting shutdown...
110317 11:03:51  InnoDB: Shutdown completed; log sequence number 0 44233
110317 11:03:51 [Note] /usr/sbin/mysqld: Shutdown complete

Now you can resume "normal" operation by /etc/init.d/mysql start, and it should work:

thecus:/var/lib/mysql# /etc/init.d/mysql start
Starting MySQL database server: mysqld . ..
Checking for corrupt, not cleanly closed and upgrade needing tables..
thecus:/var/lib/mysql# mysql -u root -p
Enter password:

mysql> SHOW ENGINES;
+------------+---------+--------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                      | Transactions | XA   | Savepoints |
+------------+---------+--------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys   | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                        | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappea) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                           | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables    | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                               | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                       | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance       | NO           | NO   | NO         |
+------------+---------+--------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

 

Anzeige


<—&mdash Showing ERROR? Click here!


Blog and Website | Comments (0) | Trackbacks (0)

Related Articles:

0 Comments | "Enabling InnoDB support in debian (MySQL)" »

Add comment

 

 This is the ReCaptcha Plugin for Lifetype

Due to German legislation, all comments are moderated. If you get NO error message, your comment is accepted by the system and will be released at the earliest opportunity. Sorry for the inconvenience this might cause.

Inappropiate comments might be edited or not accepted.