Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Storage Engines: MyISAM, InnoDB and BDB

By: FYIcenter.com

Part:   1  2  3  4   5  6 

(Continued from previous part...)

How To Create a New Table Using the BDB Storage Engine?

BDB (BerkeleyDB) storage engine was originally developed at U.C. Berkeley. It is now maintained by Sleepycat Software, Inc., which is an Oracle company now. BDB is transaction safe, and has been used in products from many companies, like Cisco Systems.

BDB is not the default storage engine. You need to specify "ENGINE = BDB" at the end of the "CREATE TABLE" statement to create new tables with the BDB storage engine. The tutorial exercise below shows you a good example:

>cd \mysql\bin
>mysql -u dev -piyf fyi

mysql> CREATE TABLE fyi_bdb (
  id INTEGER PRIMARY KEY,
  title VARCHAR(80), 
  count INTEGER )
  ENGINE = BDB;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> SHOW CREATE TABLE fyi_bdb;
  CREATE TABLE `fyi_bdb` (
  `id` int(11) NOT NULL,
  `title` varchar(80) default NULL,
  `count` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
1 row in set (0.02 sec)

mysql> drop table fyi_bdb;
Query OK, 0 rows affected (0.00 sec)

The "SHOW CREATE TABLE" command reports that table "fyi_bdb" was actually created with the MyISAM storage engine. This is because the BDB storage engine is not included in the default "mysqld" configuration.

How To Start mysqld to Support the BDB Storage Engine?

The default "mysqld" program does not support the BDB storage engine. If you want to use the BDB storage engine, you can start MySQL server with the "mysqld-max" program.

The tutorial exercise below shows you how to start "mysqld-max" and create a table with the BDB storage engine:

>\mysql\bin\mysqladmin -u root -pretneciyf shutdown

>\mysql\bin\mysqld-max

>\mysql\bin\mysql -u dev -piyf fyi

mysql> CREATE TABLE fyi_bdb (
  id INTEGER PRIMARY KEY,
  title VARCHAR(80), 
  count INTEGER )
  ENGINE = BDB;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> SHOW CREATE TABLE fyi_bdb;
  CREATE TABLE `fyi_bdb` (
  `id` int(11) NOT NULL,
  `title` varchar(80) default NULL,
  `count` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 
1 row in set (0.02 sec)

mysql> INSERT INTO fyi_bdb (id) VALUES(-1);
Query OK, 1 row affected (0.05 sec)

Where Table Data Is Stored by the BDB Storage Engine?

By default, MySQL provides \mysql\data directory for all storage engines to store table data. Under \mysql\data directory, the BDB storage engine will create one file for each table to store table data and index data.

If a new table is created with the BDB storage engine, one format file named as databaseName\tableName.frm will be created. Another file named as databaseName\tableName.db to store table data and index data. For example, the new table "fyi_bdb" created in the previous tutorial exercise will result the following files:

>cd \mysql\data

>dir fyi\fyi_bdb.*
   49,152 fyi_bdb.db
    8,620 fyi_bdb.frm

(Continued on next part...)

Part:   1  2  3  4   5  6