Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I am using MySQL database and using an Ubuntu Linux machine.

My database named db_test , I notice that under path /var/lib/mysql/db_test ,there are files suffix with .frm, .MYD, .MYI like following:

/var/lib/mysql/db_test# ls

cars.frm 
cars.MYD 
cars.MYI

customers.frm
customers.MYD
customers.MYI

departments.frm
departments.MYD
departments.MYI

... 

Seems each .frm, .MYD, .MYI files group mapped with one table in the database.

I have following two questions to ask:

  1. What are the three files doing exactly?

  2. If I create a new directory under path /var/lib/mysql/ say db_test_2 , and copy every file from db_test_1 directory to db_test_2 , will it also create a new database db_test_2 which has exactly the same contents(tables) as db_test_1's ?

Does this physically database files moving action create the same result as following command-line actions:

  1. dump the database db_test_1 out

  2. create a new database db_test_2

  3. then dump the db_test_1 database back into the new database db_test_2?

If so, it seems moving files are much faster then using mysqldump to copy databases(or to importing data from one DB to another DB in MySQL). Any opinions on this?

share|improve this question

2 Answers 2

You have a cmd-line tool which does exactly this: mysqlhotcopy

It works fine wy myisam tables, but not with InnoDb tables.

If you have configured your server with lvm, and put your /var/lib/mysql on a dedicated volume here is the way I recommend to backup very fast and in a non-blocking way all your databases:

mysql -U root -p
  > flush tables with read lock;

This flushes all your tables to disk and blocks any r/w operation

  > system "lvcreate -s -L 1G -n lvMysql_snap /dev/vg_myserver/lv_mysql" ;

Needs to be adapted to your configuration, this creates a snapshot of the filesystem of your database. It takes no time

  > unlock tables;

This is done, R/W operation are resumed.

Now you can mount /dev/vg_myserver/lvMysql_snap and make a tar archive of your database!

share|improve this answer
  1. AFAIR, .frm is description-file (where database table stucture described), .MYD is file with data, .MYI is file with indexes.

  2. Yes, copying will be much faster. But there's one problem: it's not atomic. Under high load copied files will be inconsistent and maybe even corrupted at all. Especially if you are using some more 'smart' engine like InnoDB.

Edit: p.s. You can safely copy these files, but before you should stop mysql server.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.