1

I have a Windows Server 2008 R2 machine and on it I have 2 MySQL MyISAM databases. These databases are quite large as they are over 20GB in combined size.

I update these databases once a week. (I disable my web site in the process - but this is OK for me). I then switch off the MySQL server instance, and copy over the physical folders to another backup folder (datestamped - eg: CB1_17_03_2016) on the same server.

What I noticed lately is, though, that in the backup folders, the individual files (or most of them) - .MYD, .MYI, etc. have a different (older) modified date/time than the files in the actual MySQL data folder. It's like the files were copied WITHOUT the changes just made. I am using the following code in a .bat file to copy the data files:

REM Switch off MySQL service, backup the newly updated folders, and switch on MySQL service again.

SET BackupFolder=C:\Backups\Data

FOR /f "tokens=1-5 delims=/ " %%d in ("%date%") DO SET Folder1=%BackupFolder%\CB1_%%d_%%e_%%f
FOR /f "tokens=1-5 delims=/ " %%d in ("%date%") DO SET Folder2=%BackupFolder%\CB2_%%d_%%e_%%f

SET MySqlFolder=...the folder where the MySQL data files are...
robocopy "%MySqlFolder%\cb1" "%Folder1%"
robocopy "%MySqlFolder%\cb2" "%Folder2%"

cd "%MySQLPath%" (set to MySql.exe location)
NET START MySQL

This copying occurs immediately after updating the tables. Do I need to issue a FLUSH TABLES or something like that before switching off the MySQL instance and copying the files?

Thanks in advance, Tim

Improve this question
2

3 Answers 3

Reset to default
1

maybe, use following method instead:

MySQL :: MySQL 5.7 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program

Improve this answer
5
  • I've tried using MySQLDump but some tables contain hundreds of millions of rows and it takes several hours to complete. Also, restoring would take the order of days.
    – user3480610
    Apr 1, 2016 at 17:00
  • mysqldump is slower then simply copying the files, but if you have so much data that restoring the mysqldump image takes an order of days, then consider that restoring the MyISAM files directly might also take a day or more.
    – Stefan Lasiewski
    Apr 1, 2016 at 18:29
  • Not really as it is just a matter of copying the folders - only takes about 10 minutes.
    – user3480610
    Apr 1, 2016 at 18:32
  • Oh I see-- you're backing up 20GB. Restoring a 20GB MySQL database shouldn't take an order of days. For me, backups took about 10 minutes including the overhead of compression, and restoring took 10-20 minutes. This was on fast SSDs, but even on a slower array, mysqldump shouldn't be very slow.
    – Stefan Lasiewski
    Apr 1, 2016 at 18:36
  • I just moved 100G database from one host to another, it took 2 hours for mysqldump to complete, then another hour for transfer to another host and 2 more hours for restore... sure this is not ideal, but I feel more comfortable then coping files)
    – alexus
    Apr 1, 2016 at 18:58
1

You cannot simply copy MySQL file to take a backup. It will be inconsistent and most likely corrupt.

On Windows with MyISAM table (I assume there are no InnoDB tables) you have following backup options:

  • mysqlhotcopy (it copies files but wraps it with FLUSH TABLES WITH READ LOCK)
  • mysqldump/mysqlpump
  • a slave for cold backups
  • Oracle's MySQL Enterprise Backup ($$$)
Improve this answer
6
  • 2
    You can back up this way, one just needs to stop mysql before copying, which is obviously not ideal in most circumstances.
    – EEAA
    Apr 1, 2016 at 16:04
  • Sure, if you can afford master downtime you can do cold backups. I suggested a slave in option #3. This way the master will be up and you can benefit from file copy backups.
    – akuzminsky
    Apr 1, 2016 at 16:07
  • Can you use mysqlhotcopy on Windows? I thought it was only for UNIX based systems...
    – user3480610
    Apr 1, 2016 at 17:03
  • It's a perl script, so if you have it on windows it should work. at least I don't see why not. All it does is locks tables and copies files. I don't have windows handy, so if you can test it and give us feedback i'd appreciate.
    – akuzminsky
    Apr 1, 2016 at 17:06
  • 1
    You don't even need cold backups for this, and the database can be available in read-only mode for the duration of the backup. You can simply lock the database with FLUSH TABLES WITH READ LOCK, copy the MyISAM files, then unlock the tables: dev.mysql.com/doc/refman/5.7/en/backup-methods.html . This is one reason why many folks still like MyISAM over InnoDB.
    – Stefan Lasiewski
    Apr 1, 2016 at 18:19
0

What you want to do is called a physical backup, in the sense you are copying the raw database files.

The safer bet is to stop mysql and copy the entire directory - in other words, what you are already doing.

You see an older modification time on your backup probably because when you restart the mysql service, the table files are touched, and their last modification time is updated.

To be sure that the files are the same you can run a MD5 comparator on both files, before restarting the mysql service. After the comparison, restart the service and check if file's last modification time is updated.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .