How to schedule a MySQL database backup of one table per file in Windows?

Question

I'm using MySQL Server 5.5 and need to schedule a daily database backup. Am currently doing the following in a batch file:

set currdate=%date:~4%
Set FileDate=%currdate:/=_%

mysqldump -u root-proot db > "C:\backup\database\db%FileDate%.sql"

It exports all tables in a single file. I want to export one file per table.

Isn't mysqldump -T [dir_name] option working for you? You get separate structure (.sql) and data (.txt) files, but still easy to import... — Bart
– Bart, Commented May 19, 2018 at 21:16

Steve Chambers · Accepted Answer · 2020-03-19 09:20:36Z

2

+50

The following outputs all table names to a temporary file first and then iterates through them, dumping each one to an appropriately named file:

@echo off
set currdate=%date:~4%
set filedate=%currdate:/=_%
mysql --skip-column-names -u root -proot db -e "show tables;" > tables.tmp
for /f "skip=3 delims=|" %%t in (tables.tmp) do (
  mysqldump -u root -proot db %%t > "C:\backup\database\db_%%table_%filedate%.sql"
)
del tables.tmp

edited Mar 19, 2020 at 9:20

answered May 19, 2018 at 22:34

Steve Chambers

39.8k29 gold badges179 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rick James Over a year ago

Neha -- is this not adequate? (I'm wondering why the belated Bounty.)

Vincent Over a year ago

I would like to point out that the index parameter name of %%table is invalid in a loop because the loop parameter name must be a single character. Therefore use %%t instead of %%table. I confirmed this on Windows Server 2016. Perhaps other versions of windows allow longer parameter names?

Steve Chambers Over a year ago

@Vincent Thanks for pointing it out - haven't come across this before and not sure which Windows versions it applies to (was working for me in 2018, guessing that was on Windows 8.1). Have now updated the answer to use %%t instead.

Compo · Accepted Answer · 2018-05-23 12:13:55Z

As you have not indicated what is wrong with the answer provided, here's a similar answer, (please note that this is completely untested):

@Echo Off
Rem modify as necessary
Set "buDest=C:\backup\database"
Set "dbName=db"
Set "dbPass=root"
Set "dbUser=root"
Rem If binaries are not in %CD% or %PATH% modify the Set line below
Rem     Example: Set "sqlBin=C:\Program Files\MySQL\MySQL Server 5.5\bin"
Set "sqlBin=."

Set "dStamp="
For /F "Tokens=1-3 Delims=/ " %%A In ('RoboCopy/NJH /L "\|" Null') Do If Not Defined dStamp Set "dStamp=%%A_%%B_%%C"

If Not Exist "%buDest%\" MD "%buDest%" 2>Nul || Exit /B

"%sqlBin%\mysql" --user=%dbUser% --password=%dbPass% -D %dbName% -Bse "Show Tables";|^
 For /F "Skip=3 Delims=| " %%A In ('FindStr "^|"') Do (
    mysqldump --user=%dbuser% --password=%dbpass% %%A >"%buDest%\%dbName%-%%A-%dStamp%.sql")

Just ensure that the value data on lines 3, 4, 5, 6 and possibly 9 is modified as necessary

Collectives™ on Stack Overflow

How to schedule a MySQL database backup of one table per file in Windows?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related