How to take physical backup of MYSQL database

Question

I am trying to find out some tool which can take physical backup of MYSQL database. Like mysqlbackup but it is not open source. There is mysqldump but i want to take physical backup. Can someone please suggest me some tool to take backup of MYSQL db on windows. And i am using Innodb as my database engine.

Answer 1

You literally do not need a third party tool.

STEP 01

Find out the path to the data by running

mysql> SHOW GLOBAL VARIABLES LIKE 'datadir';

For this example, let's suppose it is C:\Program Files\MySQL\MySQL 5.6\data

STEP 02

Pick a destination folder to copy the data directory

For this example, let's pick D:\MySQLBackup

STEP 03

Create batch file called C:\RunMySQLBackup.bat with the the following commands

@echo off
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
set MYSQL_DATA=C:\Program Files\MySQL\MySQL 5.6\data
set MYSQL_BACK=D:\MySQLBackup
mysql %MYSQL_CONN% -ANe"FLUSH TABLES; SET GLOBAL innodb_fast_shutdown=0;"
net stop mysql
xcopy /s %MYSQL_DATA%\*.* %MYSQL_BACK%\.
net start mysql

CONCLUSION

Just run C:\RunMySQLBackup.bat on demand or add it to the Windows Scheduler. This is just a rough backup script and it only keeps one copy.

This is not the first time I discussed this. I have another post MySQL backing up MyISAM tables by copying the physical files - and automating the process for copying an all-MyISAM database.

You should think about doing mysqldumps since you can get the data at a point-in-time. Should you want to go back to mysqldumps, I have another post I wrote on how to do mysqldumps in Windows with named backup filenames based on the data and time (mysqldump with automatically generated export name).