{"id":316,"date":"2014-02-05T10:45:38","date_gmt":"2014-02-05T10:45:38","guid":{"rendered":"http:\/\/it.tuxie.eu\/?p=316"},"modified":"2014-02-05T12:34:37","modified_gmt":"2014-02-05T12:34:37","slug":"how-mysql-mariadbtokudb-stores-data","status":"publish","type":"post","link":"http:\/\/it.tuxie.eu\/?p=316","title":{"rendered":"How MySQL (MariaDB,TokuDB) stores data"},"content":{"rendered":"

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<\/strong>. Let\u00b4s see opinion from most spreaded community database.<\/p>\n

<\/p>\n

I will be using TokuDB installed few hours ago from Windows host.<\/p>\n

I will use my favourite AquaData Studio, of course it will be very similar to any other GUI:<\/p>\n

\"\"<\/p>\n

(I didn\u00b4t run recommended script after installation, one part is setting password for root \ud83d\ude09 ). First I need to fix connection:<\/p>\n

\"\"<\/p>\n

j@debian:~$ \/opt\/tokutek\/mysql\/bin\/mysql -u root<\/strong><\/span><\/span>
\nWelcome to the MariaDB monitor.\u00a0 Commands end with ; or \\g.<\/span>
\nYour MariaDB connection id is 7<\/span>
\nServer version: 5.5.30-tokudb-7.1.0-MariaDB MariaDB Server<\/span><\/p>\n

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.<\/span><\/p>\n

Type ‚help;‘ or ‚\\h‘ for help. Type ‚\\c‘ to clear the current input statement.<\/span><\/p>\n

MariaDB [(none)]> create database tst;<\/strong><\/span><\/span>
\nQuery OK, 1 row affected (0.00 sec)<\/span><\/p>\n

MariaDB [(none)]> GRANT ALL ON tst.* TO root@’xxx.local‘ IDENTIFIED BY “;<\/strong><\/span><\/span>
\nQuery OK, 0 rows affected (0.00 sec)<\/span><\/p>\n

All looks fine:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

1) TokuDB engine<\/strong><\/p>\n

So again we created table looking like this:<\/p>\n

\"\"<\/p>\n

Let\u00b4s see where MySQL stores data:<\/p>\n

\u00a0j@debian:~$ cat \/etc\/my.cnf | grep datadir<\/strong><\/span><\/span>
\ndatadir = \/var\/lib\/mysql<\/span><\/p>\n

In this directory are located few sub-directories (1 directory = 1 database, same name). Our database is „tst“ =<\/p>\n

root@debian:\/var\/lib\/mysql\/tst# ls -la<\/strong><\/span><\/span>
\ntotal 24<\/span>
\ndrwx—— 2 mysql mysql 4096 Feb\u00a0 5 11:06 .<\/span>
\ndrwx—— 6 mysql root\u00a0 4096 Feb\u00a0 5 11:05 ..<\/span>
\n-rw-rw—- 1 mysql mysql 8588 Feb\u00a0 5 11:06 acc_test.frm<\/span>
\n-rw-rw—- 1 mysql mysql\u00a0\u00a0 65 Feb\u00a0 5 10:54 db.opt<\/span><\/p>\n

Frm file<\/a> covers structure of database, we can check any senseful information by:<\/p>\n

root@debian:\/var\/lib\/mysql\/tst# hexdump -v -C acc_test.frm<\/strong><\/span><\/span><\/p>\n

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\u00b4m 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 \ud83d\ude42 No surprises for name of file:<\/p>\n

root@debian:\/var\/lib\/mysql# ls -la | grep tokudb<\/strong><\/span>
\n-rwxrwx–x\u00a0 1 mysql mysql\u00a0\u00a0\u00a0 32768 Feb\u00a0 5 11:06 tokudb.directory<\/span>
\n-rwxrwx–x\u00a0 1 mysql mysql\u00a0\u00a0\u00a0 16384 Feb\u00a0 4 16:25 tokudb.environment<\/span>
\n-rw——-\u00a0 1 mysql mysql\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 Feb\u00a0 4 16:25 __tokudb_lock_dont_delete_me_data<\/span>
\n-rw——-\u00a0 1 mysql mysql\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 Feb\u00a0 4 16:25 __tokudb_lock_dont_delete_me_environment<\/span>
\n-rw——-\u00a0 1 mysql mysql\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 Feb\u00a0 4 16:25 __tokudb_lock_dont_delete_me_logs<\/span>
\n-rw——-\u00a0 1 mysql mysql\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 Feb\u00a0 4 16:25 __tokudb_lock_dont_delete_me_recovery<\/span>
\n-rw——-\u00a0 1 mysql mysql\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 Feb\u00a0 4 16:25 __tokudb_lock_dont_delete_me_temp<\/span>
\n-rwxrwx–x\u00a0 1 mysql mysql\u00a0\u00a0\u00a0 32768 Feb\u00a0 5 11:06 tokudb_meta_3_0_18.tokudb<\/span>
\n-rwxrwx–x\u00a0 1 mysql mysql\u00a0\u00a0\u00a0 32768 Feb\u00a0 5 11:25 tokudb.rollback<\/span>
\n-rwxrwx–x\u00a0 1 mysql mysql\u00a0\u00a0\u00a0 32768 Feb\u00a0 5 11:25 _tst_acc_test_main_7_2_18.tokudb<\/span><\/strong>
\n-rwxrwx–x\u00a0 1 mysql mysql\u00a0\u00a0\u00a0 65536 Feb\u00a0 5 11:06 _tst_acc_test_status_7_1_18.tokudb<\/span><\/p>\n

Ok, let\u00b4s try directly command used in previous cases:<\/p>\n

root@debian:\/var\/lib\/mysql# time strings -a _tst_acc_test_main_7_2_18.tokudb | grep \u201e[0-9]\\{4\\}\\-[0-9]\\{4\\}\\-[0-9]\\{4\\}\\-[0-9]\\{4\\}\u201c<\/strong><\/span><\/span><\/p>\n

real\u00a0\u00a0 \u00a00m0.420s<\/span>
\nuser\u00a0\u00a0 \u00a00m0.004s<\/span>
\nsys\u00a0\u00a0 \u00a00m0.000s<\/span><\/p>\n

No results!!! Wonderful!!! \ud83d\ude42<\/p>\n

root@debian:\/var\/lib\/mysql# head _tst_acc_test_main_7_2_18.tokudb<\/strong><\/span><\/span>
\ntokudata\u0018<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \ufffdR\ufffd\u0006\u0004\u0003\u0002\u0001\u0003\ufffd\u0003@@T\u0003\u0018\ufffdR\ufffd<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \ufffd<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \ufffdR\ufffd\u0011\ufffdR\u0001\u0016\ufffd\u0002<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \ufffd\ufffd\ufffd?\ufffd\ufffd\ufffd\ufffd\u0016\ufffd\u00103\ufffdtokudata\u0018<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \ufffdR\ufffd\u0006\u0004\u0003\u0002\u0001\u0002\u0016\u0003@`T\u0003\u0018\ufffdR\ufffd<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \ufffd<\/span>
\n\ufffdR\u0001\u0014l\u0002\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \ufffdRQ<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 \ufffd\ufffd\ufffd?\ufffd\ufffd\ufffd\ufffd\u0014\u0649\ufffd|\u0001\u0004<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \ufffdw\ufffdtokuleaf\u0018\u0018\ufffdR\ufffd<\/span><\/p>\n

Well, well, after running hexdump, it looks similar, very similar:<\/p>\n

\"\"<\/p>\n

I must admit, I\u00b4m completely excited! At least someone is securing data file on system level.<\/p>\n

 <\/p>\n

2) InnoDB engine<\/strong><\/p>\n

I have created table acc_test_inno, containing same structure + values, the only change = InnoDB engine.<\/p>\n

\"\"<\/p>\n

Except frm file there are 2 log files and 1 data file for InnoDB in this installation (default values of variables inconfiguration):<\/p>\n

-rw-rw—-\u00a0 1 mysql mysql 18874368 Feb\u00a0 5 11:53 ibdata1<\/span>
\n-rw-rw—-\u00a0 1 mysql mysql\u00a0 5242880 Feb\u00a0 5 11:53 ib_logfile0<\/span>
\n-rw-rw—-\u00a0 1 mysql mysql\u00a0 5242880 Feb\u00a0 4 16:25 ib_logfile1<\/span><\/p>\n

Let\u00b4s 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):<\/p>\n

root@debian:\/var\/lib\/mysql# time strings -a ibdata1 | grep \u201e[0-9]\\{4\\}\\-[0-9]\\{4\\}\\-[0-9]\\{4\\}\\-[0-9]\\{4\\}\u201c<\/strong><\/span><\/span><\/p>\n

real\u00a0\u00a0 \u00a00m0.175s<\/span>
\nuser\u00a0\u00a0 \u00a00m0.164s<\/span>
\nsys\u00a0\u00a0 \u00a00m0.000s<\/span><\/p>\n

Yes! InnoDB passed also.<\/p>\n

 <\/p>\n

3) Aria engine<\/strong><\/p>\n

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:<\/p>\n

-rw-rw—- 1 mysql mysql 8588 Feb\u00a0 5 12:04 acc_test_aria.frm<\/span>
\n-rw-rw—- 1 mysql mysql 8192 Feb\u00a0 5 12:04 acc_test_aria.MAD<\/span>
\n-rw-rw—- 1 mysql mysql 8192 Feb\u00a0 5 12:04 acc_test_aria.MAI<\/span><\/p>\n

Frm file = table definition, MAI file = index content, MAD = data. MAI and MAD files are binary containers = MariaDB also passed \ud83d\ude42<\/p>\n

 <\/p>\n

Overall overview<\/strong><\/p>\n

MariaDB, MySQL, TokuDB – all these storage engines are storing data from tables (and other database objects) in much safer way (in default) than Oracle<\/a> or DB2<\/a>.<\/p>\n

-a-<\/p>\n

 <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

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\u00b4s see opinion from most spreaded community database.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,13],"tags":[],"_links":{"self":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/316"}],"collection":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=316"}],"version-history":[{"count":0,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/316\/revisions"}],"wp:attachment":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=316"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=316"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=316"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}