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.
1 Answer
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).
-
1The InnoDB shutdown mode. If the value is 0, InnoDB does a slow shutdown, a full purge and a change buffer merge before shutting down. If the value is 1 (the default), InnoDB skips these operations at shutdown, a process known as a fast shutdown. If the value is 2, InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.– MarcoZenOct 26, 2019 at 11:19