MySQL backing up MyISAM tables by copying the physical files - and automating the process I have 2 databases with MyISAM tables running on a Windows 2008 Server. These databases are about 20GB in size with a few tables with millions of rows. My question regards backing them up on a weekly basis. Currently I do some updates once a week, and then I go to the data folder and copy the physical folders representing the databases to another drive on the server, and then rar everything up.

This process takes about 45 minutes and during that time certain functionality of my website cannot be used as during the copying, the tables get blocked. I have seen that you can LOCK and FLUSH tables so that they can still be used while they are being copied. So does LOCKing the tables allow concurrent SELECTs?

I do not know exactly how to go about this and I would greatly appreciate if anyone could help me with how I could synchronize the lock/flush statements with the copying of the physical data and then the subsequent unlocking, and how I could possibly automate (possibly script to a dos batch file) this process?

Thanks in advance, Tim
Basically, you need the following paradigm


Session #1 (MySQL)

Lock all tables
Read Lock
Sleep for a Long Time

Session #2 (Windows) : This you are already doing

Copy the .frm, .MYD, .MYI files for every MyISAM table

Session #3 (After Session #2 is Complete)

Look for Sleeping Connection of Session #1
Kill the SLEEP Query (Session #1 Will Terminate)



Create a File Called C:\KillLock.bat with these lines

@echo off
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
set KILL_SCRIPT=C:\KillSleepLock.sql
mysql %MYSQL_CONN% &lt; %KILL_SCRIPT%


Create a File Called C:\FlushTablesWithReadLock.bat with these lines

@echo off
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
set KILL_SCRIPT=C:\KillSleepLock.sql
start mysql %MYSQL_CONN% -ANe"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)"
mysql %MYSQL_CONN% -ANe"SET @x=SLEEP(180); SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE state='User sleep' AND info='SELECT SLEEP(86400)'" &gt; %KILL_SCRIPT%


This will lock all tables, pause for 3 minutes, then create the file C:\KillSleepLock.sql

Backup Process

Here are your steps to automate


STEP 01 : Run C:\FlushTablesWithReadLock.bat
STEP 02 : Run your process to backup the .frm, .MYD, .MYI files
STEP 03 : Run C:\KillLock.bat


I will leave it to you to implement this paradigm

Give it a Try !!!