« 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)

_ASIP_ 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)

 

_ASIP_

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

Related Articles:

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

  1. Tim : hmm

    21/01/2012, at 19:55 [ Reply ]

    Something happens when I try and mysql seems to hang when initializing the DB...any ideas?

  2. Hugo : Errors when I initialize the database files

    03/07/2012, at 06:11 [ Reply ]

    120703 12:11:58 [Note] Plugin 'FEDERATED' is disabled.
    120703 12:11:58 InnoDB: Initializing buffer pool, size = 10.0G
    120703 12:12:06 InnoDB: Completed initialization of buffer pool
    InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
    InnoDB: than specified in the .cnf file 0 268435456 bytes!
    120703 12:12:06 [ERROR] Plugin 'InnoDB' init function returned error.
    120703 12:12:06 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    120703 12:12:06 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
    120703 12:12:06 [ERROR] Do you already have another mysqld server running on por t: 3306 ?
    120703 12:12:06 [ERROR] Aborting

    120703 12:12:06 [Note] /usr/sbin/mysqld: Shutdown complete

  3. coldtobi :

    21/08/2012, at 08:49 [ Reply ]

    Hallo Hugo,
    it seems that you have another instance of mysql running which prevents the start of the mysql-daemon:
    "Can't start server: Bind on TCP/IP port: Address already in use"

Add comment

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.