Introduction –
Any database in MySQL is stored physically in Data directory of MySQL server. You can find data directory in /etc/my.cnf file or from running MySQL process..
arun@arunbagul:~$ ps aux | grep “/usr/sbin/mysqld” | grep -v grep
mysql 5534 0.0 1.0 127920 21136 ? Sl 21:09 0:00 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/run/mysqld/mysqld.sock
arun@arunbagul:~$
So data directory for MySQL server is normally “/var/lib/mysql“. Format of storing data physically in data directory is depends of the table type or Storage Engine used by MySQL. There are six (6) types of tables (Storage Engine) you can use in MySQL.
root@arunbagul:~# grep “datadir” /etc/mysql/my.cnf
datadir = /var/lib/mysql
root@arunbagul:~# cd /var/lib/mysql
root@arunbagul:/var/lib/mysql# ls -lF
total 8768
drwxr-xr-x 2 mysql mysql 4096 2008-04-01 07:51 mysql/
drwx—— 2 mysql mysql 4096 2008-04-09 12:39 trac/
drwx—— 2 mysql mysql 4096 2008-02-22 23:18 wordpress/
drwx—— 2 mysql mysql 4096 2008-03-07 11:27 zabbix/
root@arunbagul:/var/lib/mysql#
By default MySQL server creates administrative database called “mysql“. Which contains system tables necessary to control user access, provide help related to information, and support time-zone related functionality.
1) BDB – Tranction safe table that the berkeley DB handler manages. InnoDB tables have replaced BDB tables.
2) Memory – A table whose contents are stored in memory. The data stored in the tables is available only as long as the MySQL server is available. If the server crashes or is shutdown, the data disappears.
3) InnoDB – A transaction safe table that the InnoDB handler mamages. As a result, dtata is not stored in .MYD file, but instead is managed in the InnoDB tablespace.
4) ISAM – This table type was the default table type, now it is deprecated. Now a days MyISAM is the default table type used in MySQL.
5) MERGE – This is the virtual table that is made up of multiple MyISAM tables. Data is not stored in the MERGE table, but rather in the underlying MyISAM tables.
6) MyISAM – This is the default table type used in MySQL. Which support extensive indexing and are optimized for compressions and speed.
root@arunbagul:/var/lib/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> show variables like ‘storage_engine’;
+——————-+—————–+
| Variable_name | Value |
+——————+—————–+
| storage_engine | MyISAM |
+—————–+——————-+
1 row in set (0.00 sec)
mysql> quit
Bye
root@arunbagul:/var/lib/mysql#
** How to define table/storage type in MySQL ?
mysql> create table person ( id INT(10) PRIMARY KEY, name VARCHAR(20) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
mysql> quit
Bye
root@arunbagul:~#
Thank you,
Arun Bagul
