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:
-
What are the three files doing exactly?
-
If I create a new directory under path
/var/lib/mysql/
saydb_test_2
, and copy every file fromdb_test_1
directory todb_test_2
, will it also create a new databasedb_test_2
which has exactly the same contents(tables) asdb_test_1
's ?
Does this physically database files moving action create the same result as following command-line actions:
-
dump the database
db_test_1
out -
create a new database
db_test_2
-
then dump the
db_test_1
database back into the new databasedb_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?
Best Answer
AFAIR, .frm is description-file (where database table stucture described), .MYD is file with data, .MYI is file with indexes.
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.