MySQL – How to Flush InnoDB Table, Copy Files, and Unlock Tables from Windows Bat File

backupinnodbMySQLmysql-5.6windows

I can successfully execute "FLUSH TABLES myTable FOR EXPORT;" in MySql Workbench. Then I can copy the cfg and ibd files I need, and then I can go back to workbench and run "UNLOCK TABLES" and all is well. But I would like to do this from a Windows batch file.
However, if I run the following bat file: 

mysql -username u -password < prepareTableForCopy.txt

and prepareTableForCopy.txt contains the following:

use mydata;
FLUSH TABLES myTable FOR EXPORT;

I get no errors, but when I look in the data folder the files I need to copy are not there. It seems that as soon as the flush is run and the script returns the files are gone.

How can I fix this so that I can achieve my goal which is to have a .bat file that prepares the table for export, copied the correct files to another directory. zips the files and then unlocks the table?

Best Answer

Your problem is to maintain two sessions

  • Session #1 : Lock the table
  • Session #2 : Copy .ibd file

Note the Following

  • Session #1 must remain alive in order to hold the lock
  • Session #1 must remain alive long enough for Session #2 to copy the .ibd file

Steps to Perform

  • Session #1 sleeps for 30 seconds after issuing the lock
  • Pause 5 seconds to give Session #1 time to lock the table
  • Session #2 copies the .ibd file
  • When Session #1 terminates, the lock is implicitly released (unlocked)

This gives Session #2 25 seconds to copy the .ibd file

Here is the Windows BAT file

@echo off
rem
rem Setup Path to MySQL Folders
rem
set BASE_FOLDER="C:\Program Files\MySQL\MySQL Server 5.6"
set DATADIR=%BASE_FOLDER%\data
rem
rem Setup Path to .ibd file and Destination Folder
rem
set COPY_FROM_DB=mydata
set TABLE_TO_COPY=mytable
set IBD_TO_COPY=%DATADIR%\%COPY_FROM_DB%\%TABLE_TO_COPY%.ibd
rem
rem Setup Destination Folder
rem
set TARGET_FOLDER=D:\BackupFolder
rem
rem Setup MySQL Username and Password
rem
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
rem
rem Setup Session to Lock Table and Hold Lock for 30 seconds in the Background
rem Pause 5 Seconds
rem Perform Physical Copy
rem
set TIME_TO_SLEEP=30
set TIME_TO_PAUSE=5
set SQL=FLUSH TABLES %TABLE_TO_COPY% FOR EXPORT ; DO SLEEP(%TIME_TO_SLEEP%)
start mysql %MYSQL_CONN% -D%COPY_FROM_DB% -ANe"%SQL%"
mysql %MYSQL_CONN% -D%COPY_FROM_DB% -ANe"DO SLEEP(%TIME_TO_PAUSE%)"
copy %IBD_TO_COPY% %TARGET_FOLDER%\.

GIVE IT A TRY !!!

Note : You can change TIME_TO_SLEEP and TIME_TO_PAUSE if it takes more than 30 Seconds to copy

Related Question