How to automate database backup using phpmyadmin

I am currently taking database backup manually using phpmyadmin export as a sql dump,the resulted file name wi开发者_运维百科ll 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.

---

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)

---

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

---

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.

---

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"