Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Nov 27 '11 at 21:43

This question came from our site for professional and enthusiast programmers.

    
Schema, data and index, I'd say. Guess which is which. And "no" to number 2. –  Kerrek SB Nov 27 '11 at 12:24
    
Thank you for "no" to question 2, but explanation is more important than "no" answer. –  Mellon Nov 27 '11 at 12:25
    
Well, the DBMS may/will keep additional meta data (e.g. grants) about existing databases elsewhere, so simply copying the files may not tell it that a new database actually exists. I'm not actually sure, that was just a to-be-safe answer; why don't you try it out? Check out the mysql database first though to see if there are references to the existing databases in there. –  Kerrek SB Nov 27 '11 at 12:27
    
The copy will work. As long as you copy all the necessary files, and you duplicate / rename any permissions, replication etc associated with the database name. Well it worked with no innodb in mysql 4, and that proviso indicates the problem with the method. There's no guarantee. –  Tony Hopkinson Nov 27 '11 at 13:16

2 Answers 2

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 !!!

share|improve this answer

These 3 files comes from tables with MyISAM engine. From Mysql Forge on MyISAM

MySQL creates files named Table1.MYD ("MySQL Data"), Table1.MYI ("MySQL Index"), and Table1.frm ("Format").

As to duplicating your database with file copy it should work.

  1. Create a new table as normal mysqladmin create database db_test2
  2. (possibly shut down MySQL, I don't know if this is needed but I would do it)
  3. Copy all files from db_test to db_test2
  4. (start MySQL if you stopped it)

Now you have all data copied but nothing else. Access rights, stored procedures, functions and so on needs to be created again. I guess some clever inserts in the mysql database could do it aswell).

share|improve this answer

Your Answer

 
discard

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