How do I fix the error that occurs when I enter the file path and date in mysqldump?

Question

How do I fix the error that occurs when I enter the file path and date in mysqldump?

I ran the mysqldump command as follows in cmd:

set _my_datetime=%date:-=%

mysqldump -u root -p --all-databases -pMyPassword > C:\Program Files\MariaDB 10.4\data\backup\%_my_datetime%.sql

But the result is:

Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

I wonder how to modify the above command in Windows server environment.

Answer 1

Because the %date:-=% will be evaluated as Tue 10/19/2021, and this date separator forward slash / conflicts with the path being used in the mysqldump command.

C:\xampp\mysql\bin>set _my_datetime=%date:-=%

C:\xampp\mysql\bin>echo %_my_datetime%
Tue 10/19/2021

C:\Program Files\MariaDB 10.4\data\backup\%_my_datetime%.sql
=> Will be: C:\Program Files\MariaDB 10.4\data\backup\Tue 10/19/2021.sql

which is an incorrect Windows path syntax and throws a generic syntax exception in your case.

Solution
Find a way to format the date with separator as dash - or underscore _ instead.

You can simply use

mysqldump -u root -p --all-databases -pMyPassword > "C:\Program Files\MariaDB 10.4\data\backup\mysql_backup_%date:~-10,2%-%date:~-7,2%-%date:~-4,4%-%time:~0,2%_%time:~3,2%_%time:~6,2%.sql"

will give a filename pattern: mysql_backup_10-19-2021-10_08_12.sql

Date format in Windows command line can be taunting in its way.

Reference:
how to add date and time with backupfile name using mysqldump from command prompt and to define the path of backupfile
Format date and time in a Windows batch script