22

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?

Improve this question

4 Answers 4

Reset to default
12

  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.

Improve this answer
5

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!

Improve this answer
3
  • This seems like a fast way to backup the db. But what about switching that snapshot back so it becomes my live database again? That's the part I'm really concerned about. I can mysqldump my db in under 2 seconds. Restoring it is the slow part, taking 5-10 minutes. Commented Jan 21, 2016 at 8:50
  • on recent distros lvm snapshots can be reverted to the origin, but it is probably not what you want for for managing database backups. Commented Jan 28, 2017 at 13:42
  • Regarding mysqlhotcopy:"This utility is deprecated in MySQL 5.6.20 and removed in MySQL 5.7" From: [dev.mysql.com/doc/refman/5.6/en/mysqlhotcopy.html] Commented Mar 6, 2019 at 3:19
3

This'll work for MyISAM, but not for InnoDB. See https://serverfault.com/a/367321/57569

From that answer, about InnoDB:

If you are thinking of just copying the .frm and .ibd file, you are in line for world of hurting. Copying the .frm and .ibd file of an InnoDB table is only good if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.

Improve this answer
0

I find mysqldump | mysql method too slow for cloning 6GB database even on the same machine/mysql-server, so I tried copying entire datadir folder of the db and attempted to paste it on a different folder (same instance) for other db where I can mess with it. turns out, it appears to be corrupted when starting the mysql.. tried to set auto_recover table, but no luck.

I guess it would only work on the same database name. what I did is to create 2 docker container running mysql, 1 as slave of my production's slave, and one for direct replicating via docker mapping files. see my structure below.

 +-----------+ | Master DB | +-----------+ | (Replication) | +-----------------+ | Slave/Master DB | +-----------------+ | | Cloud / Production Server V ====================================================================== | | My Local Computer (Replication) | +-----------+ - Docker Container: AppDBLive.readonly | Slave DB | - DB Name: MyAppDB +-----------+ - READ Only (If I want to test live data) | - Consistently cloning from prod slave/master | (Rsync/Copy datadir of MyAppDB) | V +----------------+ - Docker Container: AppDBLive.readwrite | No Replication | - DB Name: MyAppDB +----------------+ - Can Write and mess with all the data for testing/dev - clone via sh script to trigger rsync from AppDBLive.read

Here's my script for building my docker containers:

/local/path/to/AppDBLive.readonly/run.sh:

_PWD=`pwd` docker run --name=MyAppDB \ --mount type=bind,src=$_PWD/my.cnf,dst=/etc/my.cnf \ --mount type=bind,src=$_PWD/datadir,dst=/var/lib/mysql \ --mount type=bind,src=$_PWD/logs,dst=/var/log/mysql \ -p 3336:3306 \ -d mysql/mysql-server:5.7

/local/path/to/AppDBLive.readwrite/run.sh:

_PWD=`pwd` docker run --name=MyAppDB.dev \ --mount type=bind,src=$_PWD/my.cnf,dst=/etc/my.cnf \ --mount type=bind,src=$_PWD/datadir,dst=/var/lib/mysql \ --mount type=bind,src=$_PWD/logs,dst=/var/log/mysql \ -p 3337:3306 \ -d mysql/mysql-server:5.7

Note the difference on port numbers of both containers. make sure your app is connecting to the right ports.

Also make sure when you run clone script, both containers are off.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.