5

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.

0

1 Answer 1

6

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).

1
  • 1
    The 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.
    – MarcoZen
    Oct 26, 2019 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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