Mysql – Database Files-Related Issue

backupMySQLmysqldump

I am using MySQL database and using Ubuntu linux machine.

I noticed that my database, db_test, is under the path /var/lib/mysql/db_test

There are files with the suffix .frm, .MYD, .MYI like the 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:

  • 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 database db_test_2?

If so, it seems moving files are much faster then using mysqldump to copy databases. Any opinions on this?

Best Answer

Answer to your first question: Each file contains info for the table

  • cars.frm has the table structure
  • cars.MYD has the table data
  • cars.MYI has the table indexes

Given this layout, the cars table uses the storage engine MyISAM.

Answer to your second question: Yes, provided the following circumstances

  • every table uses the storage engine MyISAM
  • no INSERTs, UPDATEs, or DELETEs are running
  • DB Connections using TCP/IP are disabled

To check to see if all the tables are MyISAM, run this query:

SELECT COUNT(1) TableCount,engine
FROM information_schema.tables
WHERE table_schema='db_test_1'
GROUP BY engine;

You should only see just MyISAM. If you see other storage engines, you cannot just copy.

If your root@localhost password is mypass, here is all you need to to safely move all the tables regardless of storage engine:

# USERPASS="-uroot -pmypass"
# service mysql restart --skip-networking
# mysql ${USERPASS} -A -e"CREATE DATABASE db_test_2"
# mysqldump ${USERPASS} --routines --triggers db_test_1 | mysql ${USERPASS} -A -Ddb_test_2
# service mysql restart

If the data is huge, do this instead:

# USERPASS="-uroot -pmypass"
# service mysql restart --skip-networking
# mysql ${USERPASS} -A -e"CREATE DATABASE db_test_2"
# mysqldump ${USERPASS} --routines --triggers db_test_1 > /root/MyData.sql
# mysql ${USERPASS} -A -Ddb_test_2 < /root/MyData.sql
# service mysql restart

Give it a Try !!!

Related Question