I am currently taking database backup manually using phpmyadmin export as a sql dump,the resulted file name will be spbkYYMMDD(Y;year m:month D:day).Is there any way to automate db backup so that i get sql dump for regular intervals and the file name should automatically generated correspondingly .can you explain me the logic.
5
-
do u need it as a web interface to do ?Sudantha– Sudantha2011-07-11 04:49:12 +00:00Commented Jul 11, 2011 at 4:49
-
What do you mean by needing a web interface? What will you be having in the interface?Balanivash– Balanivash2011-07-11 05:09:02 +00:00Commented Jul 11, 2011 at 5:09
-
we can give the time backup interval text box to capture the time intervaln92– n922011-07-11 05:15:43 +00:00Commented Jul 11, 2011 at 5:15
-
@Vinay: check the answer. Have Updated itBalanivash– Balanivash2011-07-11 05:36:25 +00:00Commented Jul 11, 2011 at 5:36
-
1You can simply use mysqlbackupftp.com. Connect MySQLBackupFTP tool to your phpMyAdmin and create a schedule to backup your databases.Alexandr Omelchenko– Alexandr Omelchenko2017-01-10 09:02:29 +00:00Commented Jan 10, 2017 at 9:02
Add a comment
|
5 Answers
Run crontab in unix shell and create the rule to launch process for creating database backup
0 0 * * * /usr/local/bin/mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date “+\%Y-\%m-\%d”`.gz
Also check this
EDIT
The web interface you only have to write, dont think you can find a readymade code for that. But You need to use cron job, to automate a function to run at regular intervals in a unix machine. You can find more info on how to write a cron-job here. So you now, just need to write a web interface, which gets data from user and changes the rule according to the input(Which I think you can do it yourselves)
4 Comments
Erik Cederstrand
mysqldump doesn't work from the outside if your DB server is only available on localhost or some RFC 1918 address.
Erik Cederstrand
See my answer to stackoverflow.com/a/11031535/219640 if you REALLY need to do this with phpMyAdmin.
Benjamin Crouzier
Remove those wierd quotes if date complains about the format:
“ : date “+\%Y-\%m-\%d” => date +\%Y-\%m-\%d
Christhofer Natalius
Your "also check this" link is invalid or no longer exists.
The Code Will be Like This :
@echo off
for /f "tokens=1" %%i in ('date /t') do set DATE_DOW=%%i
for /f "tokens=2" %%i in ('date /t') do set DATE_DAY=%%i
for /f %%i in ('echo %date_day:/=-%') do set DATE_DAY=%%i
for /f %%i in ('time /t') do set DATE_TIME=%%i
for /f %%i in ('echo %date_time::=-%') do set DATE_TIME=%%i
"j:\xamppp\bin\mysqldump" -u root -p --all-databases>j:\backupmysql\%DATE_DAY%_%DATE_TIME%_database.sql
and save it as .bat and u can run it from task scheduler
Comments
I wrote a quick script for this exact use-case, since I had no access to the console for mysqldump and therefore needed it myself:
Downloads: Github: phpmyadmin_sql_backup.py
Usage in your case:
./phpmyadmin_sql_backup.py "https://www.example.com/phpmyadmin_login_page" USERNAME PASSWORD --basename "" --prepend-date --prefix-format "spbk%y%m%d" --overwrite-existing -o OUTPUT_DIRECTORY
Hopefully it proves to be useful for others.
The best way to automate MySQL DB backup is to use some backup software in conjunction with phpmyadmin utility. The second way, often having an advantage of no extra payment and a disadvantage of uncontrolled security, is implementing some script with cron.
Personally, I prefer Handy Backup in addition to my phpMyAdmin. See the link to an article as an example. It is not hottest backup solution, but relatively cheap and very stable.
Comments
Create .bat file in a folder when you have admin rights. down here is a simple .bat file
cd "C:\XAMPP\mysql\bin"
mysqldump -hlocalhost -uroot ca > "D:\cash\bkcash\ca\db_%date:~-4,4%%date:~-10,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%.sql"
