1

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

1
  • Have you checked the related questions recommended to take a look when you created the question? There are couple of them that I think will answer your question, with plenty of details and scripts to test. Copying database files from one location to another seems not the right thing to do. – Yaroslav Sep 30 '14 at 10:38
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 !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.