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 FilesMariaDB 10.4databackup%_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.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 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:xamppmysqlbin>set _my_datetime=%date:-=% C:xamppmysqlbin>echo %_my_datetime% Tue 10/19/2021
C:Program FilesMariaDB 10.4databackup%_my_datetime%.sql => Will be: C:Program FilesMariaDB 10.4databackupTue 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 FilesMariaDB 10.4databackupmysql_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
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0