3rd part of my investigation how databases stores data in data file / container on disk. Oracle Databse and DB2 proved that plain text for saving tables is common. Let´s see opinion from most spreaded community database.
I will be using TokuDB installed few hours ago from Windows host.
I will use my favourite AquaData Studio, of course it will be very similar to any other GUI:
(I didn´t run recommended script after installation, one part is setting password for root 😉 ). First I need to fix connection:
j@debian:~$ /opt/tokutek/mysql/bin/mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.30-tokudb-7.1.0-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type ‚help;‘ or ‚\h‘ for help. Type ‚\c‘ to clear the current input statement.
MariaDB [(none)]> create database tst;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> GRANT ALL ON tst.* TO root@’xxx.local‘ IDENTIFIED BY “;
Query OK, 0 rows affected (0.00 sec)
All looks fine:
1) TokuDB engine
So again we created table looking like this:
Let´s see where MySQL stores data:
j@debian:~$ cat /etc/my.cnf | grep datadir
datadir = /var/lib/mysql
In this directory are located few sub-directories (1 directory = 1 database, same name). Our database is „tst“ =
root@debian:/var/lib/mysql/tst# ls -la
total 24
drwx—— 2 mysql mysql 4096 Feb 5 11:06 .
drwx—— 6 mysql root 4096 Feb 5 11:05 ..
-rw-rw—- 1 mysql mysql 8588 Feb 5 11:06 acc_test.frm
-rw-rw—- 1 mysql mysql 65 Feb 5 10:54 db.opt
Frm file covers structure of database, we can check any senseful information by:
root@debian:/var/lib/mysql/tst# hexdump -v -C acc_test.frm
Ok, no data apparently :-)Data are stored depending on their storage engine. There are also option to set if 1 table = 1 file or all tables into one big file (InnoDB). As I´m not familiar with TokuDB save format, I put antoher 2 records into tables (11:24) and checking time of modification. The first one was log file and finally it appeared 🙂 No surprises for name of file:
root@debian:/var/lib/mysql# ls -la | grep tokudb
-rwxrwx–x 1 mysql mysql 32768 Feb 5 11:06 tokudb.directory
-rwxrwx–x 1 mysql mysql 16384 Feb 4 16:25 tokudb.environment
-rw——- 1 mysql mysql 0 Feb 4 16:25 __tokudb_lock_dont_delete_me_data
-rw——- 1 mysql mysql 0 Feb 4 16:25 __tokudb_lock_dont_delete_me_environment
-rw——- 1 mysql mysql 0 Feb 4 16:25 __tokudb_lock_dont_delete_me_logs
-rw——- 1 mysql mysql 0 Feb 4 16:25 __tokudb_lock_dont_delete_me_recovery
-rw——- 1 mysql mysql 0 Feb 4 16:25 __tokudb_lock_dont_delete_me_temp
-rwxrwx–x 1 mysql mysql 32768 Feb 5 11:06 tokudb_meta_3_0_18.tokudb
-rwxrwx–x 1 mysql mysql 32768 Feb 5 11:25 tokudb.rollback
-rwxrwx–x 1 mysql mysql 32768 Feb 5 11:25 _tst_acc_test_main_7_2_18.tokudb
-rwxrwx–x 1 mysql mysql 65536 Feb 5 11:06 _tst_acc_test_status_7_1_18.tokudb
Ok, let´s try directly command used in previous cases:
root@debian:/var/lib/mysql# time strings -a _tst_acc_test_main_7_2_18.tokudb | grep „[0-9]\{4\}\-[0-9]\{4\}\-[0-9]\{4\}\-[0-9]\{4\}“
real 0m0.420s
user 0m0.004s
sys 0m0.000s
No results!!! Wonderful!!! 🙂
root@debian:/var/lib/mysql# head _tst_acc_test_main_7_2_18.tokudb
tokudata
�R��@@T�R�
�
�R��R�
���?�����3�tokudata
�R�@`T�R�
�
�Rl �RQ
���?����ى�|
�w�tokuleaf�R�
Well, well, after running hexdump, it looks similar, very similar:
I must admit, I´m completely excited! At least someone is securing data file on system level.
2) InnoDB engine
I have created table acc_test_inno, containing same structure + values, the only change = InnoDB engine.
Except frm file there are 2 log files and 1 data file for InnoDB in this installation (default values of variables inconfiguration):
-rw-rw—- 1 mysql mysql 18874368 Feb 5 11:53 ibdata1
-rw-rw—- 1 mysql mysql 5242880 Feb 5 11:53 ib_logfile0
-rw-rw—- 1 mysql mysql 5242880 Feb 4 16:25 ib_logfile1
Let´s test if InnoDB saves it also very secure way (at least from size of file we can expect it is so – container in a container):
root@debian:/var/lib/mysql# time strings -a ibdata1 | grep „[0-9]\{4\}\-[0-9]\{4\}\-[0-9]\{4\}\-[0-9]\{4\}“
real 0m0.175s
user 0m0.164s
sys 0m0.000s
Yes! InnoDB passed also.
3) Aria engine
I have created table acc_test_aria, containing same structure + values, the only change = Aria engine. In this case, storage is similar to good old ISAM, MyISAM, so in non-partitioned environment should look:
-rw-rw—- 1 mysql mysql 8588 Feb 5 12:04 acc_test_aria.frm
-rw-rw—- 1 mysql mysql 8192 Feb 5 12:04 acc_test_aria.MAD
-rw-rw—- 1 mysql mysql 8192 Feb 5 12:04 acc_test_aria.MAI
Frm file = table definition, MAI file = index content, MAD = data. MAI and MAD files are binary containers = MariaDB also passed 🙂
Overall overview
MariaDB, MySQL, TokuDB – all these storage engines are storing data from tables (and other database objects) in much safer way (in default) than Oracle or DB2.
-a-