CODEWITHSUNDEEP
mysqldatabasedatabase-backups
clarkk
clarkk

Reputation: 27689

backup mysql binary files

I need to know if this is the correct way (not losing written data while backing up) to backup the binary files?

1.

LOCK TABLES
    tbl_0 READ,
    tbl_1 READ,
    tbl_2 READ

FLUSH TABLES WITH READ LOCK

2. 

copy *.frm, *.myd, *.myi files

3. 

UNLOCK TABLES

Upvotes: 1

Views: 1496

Answers (1)

MarkR
MarkR

Reputation: 63538

It is not sufficient to do this.

The best is to shut the database down completely, and copy the entire contents of the data directory.

Alternatively, you may leave the database running and take an atomic filesystem snapshot.

If you are sure that you only use MyISAM tables (and InnoDB is completely disabled in your system, as are other engines), you CAN potentially take a dump under a global lock taken with "FLUSH TABLES WITH READ LOCK". This does impact the system but it's not as bad as a shutdown.

Note that if you have any InnoDB tables - any at all, present anywhere in the server - then you can't take a backup this way. "FLUSH TABLES WITH READ LOCK" is not sufficient to backup innodb.

Upvotes: 3

Related Questions