MySQL backing up MyISAM tables by copying the physical files – and automating the process

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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% < %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)'" > %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 !!!

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply