Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to add date and time with backupfile name using mysqldump from command prompt and to define the path of backupfile

Im using this command for backup from mysqldump

mysqldump -uroot -ptrackerdb) --alldatabases >test.sql

Now i want to add date-time with my backup file like current date and time e.g test_25July2013_13:00

For this i add test_date +%Y-%m-%d_%H-%M-%S.sql in file name but it gives error

'Couldn't find table': date +%Y-%m-%d_%H-%M-%S`

What I'm doing wrong here?

like image 613
Shauzab Ali Rawjani Avatar asked Jul 25 '13 09:07

Shauzab Ali Rawjani


People also ask

What does Mysqldump command do?

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server.

Which of the following commands can be used to dump the first 5 rows of a table from your database in MySQL command-line interface of Cloud IDE?

The 'mysqldump' command is used to dump databases managed by MySQL.


3 Answers

I'm using that:

LINUX

mysqldump -u <user> -p <database> | bzip2 -c > <backup>$(date +%Y-%m-%d-%H.%M.%S).sql.bz2

WINDOWS (googled it, because i have been using LIN only)

@echo off
cls
echo Date format = %date%
echo dd = %date:~0,2%
echo mm = %date:~3,2%
echo yyyy = %date:~6,4%
echo.
echo Time format = %time%
echo hh = %time:~0,2%
echo mm = %time:~3,2%
echo ss = %time:~6,2%
echo.
echo Timestamp = %date:~6,4%-%date:~3,2%-%date:~0,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%

%mysqldir%\mysqldump -u %mysqluser% -p%mysqlpassword% -h %mysqlhost% -P %mysqlport% --databases --routines --verbose gnucash_shockwave > %BackupDir%\gnucash_shockwave-%timestamp%.sql

here more info

like image 189
jaczes Avatar answered Oct 08 '22 11:10

jaczes


In Microsoft Windows, run below command in CMD

mysqldump -u USERNAME -pYOURPASSWORD --all-databases > "C:/mysql_backup_%date:~-10,2%-%date:~-7,2%-%date:~-4,4%-%time:~0,2%_%time:~3,2%_%time:~6,2%.sql"

Output file will look like,

mysql_backup_21-02-2015-13_07_18.sql

If you want to automate the backup process, then you can use Windows Task Scheduler, and put above command in .bat file - task scheduler will run the .bat file at specified interval.

like image 41
Piyush Patel Avatar answered Oct 08 '22 12:10

Piyush Patel


mysqldump -u Database Username --password=Database password --all-databases |gzip > /home/username/MySQLDBBK$(date +%Y_%m_%d__%H_%M_%S$%M%S).sql.gz

this Cmd is Working.

like image 24
Aditya Gosavi Avatar answered Oct 08 '22 12:10

Aditya Gosavi