Backup your database (easy)

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Backup your database (easy)

Post by onoehring » 2019-11-30 09:32

Hi,

I had the urge to backup the database as there are many, many changes in one day and it would be catastrophic to loose even one day.
After searching the net I found a nice, easy to implement script to backup a database:
  • Open the script in a browser and the database will be backed up.
  • The backup will be created in a user defined directory
  • with a unique name (databasename and timestamp) and
  • if the user likes it will be gzipped.
My additions changed the script to
  • make it possible to password protect it and
  • take database credentials directly from your AggGini application, so that it's easy to transfer to some other site.
(no magic done here by me).
The only thing one has to do is call that script - which - of course can be done by hand or using a cron job.
If you do not have a cronjob availabe you could use websites that offer such services (https://www.easycron.com/ or https://www.cronjob.de or ... does anyone else know free web cron services? Please add them in the thread for all of us to learn about them).

Source of the script: https://www.daniloaz.com/en/using-php-t ... databases/ and the download page https://github.com/daniloaz/myphp-backup

How to use in AppGini?
Download the attached ZIP and extract it to your hooks folder. It will create a new subdirectory dbbackup. In this you will find a myphp-backup.php and another subdirectory sql_backup. In that sql_backup the backupfiles will be placed. The backups (hooks\dbbackup\sql_backup) can not be accessed through the browser as I protected the folder with a .htaccess file from any access.

You can call your database backup now yourdomain.com/hooks/dbbackup/myphp-backup.php and the backup will be created.

Additional safety
If you want to set an access password (recommended), open the myphp-backup.php file. Search for (around line 18)

Code: Select all

$run_pass ='';
Change this to the access code you like. Example:

Code: Select all

$run_pass ='mySecret';
You can call your database backup now yourdomain.com/hooks/dbbackup/myphp-backup.php?pw=mySecret and the backup will be created. If the password is wrong, you will get a specific message.

Download
Database_Backup_v1.0.zip
(4.67 KiB) Downloaded 307 times
Comments? Suggestions? Please post them here.

Olaf

jlarmarange
Posts: 16
Joined: 2019-02-21 19:03

Re: Backup your database (easy)

Post by jlarmarange » 2020-01-06 10:03

Just to mention a small bug in your script.

At the beginning on the script, it should be "$dbServer" instead of "$dbServe".

Thanks a lot for your script.

Regards

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Backup your database (easy)

Post by onoehring » 2020-01-06 10:31

Hi,

thanks for reporting. I found this on my own site already and thought, I had updated the script here as well.

So here is version 1.1. without that bug.

Download 1.1
Database_Backup_v1.1.zip
(5.03 KiB) Downloaded 295 times
Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Backup your database (easy)

Post by onoehring » 2020-02-14 18:17

Hi,

just created a new version. Please download below.
I noticed, that when using not a timestamp, but e.g. BACKUP_CYCLE = 'wH' to create the backup filename, the files grew larger and larger ... My 20 MB database suddenly had a 1 GB backup file as each backup added another 20 MB. This needed to be fixed fast - so, here you go. Please check your database backup folder for strangely large files if you used the previous version.

Download v1.2
Database_Backup_v1.2.zip
(5.82 KiB) Downloaded 279 times
Comments?
Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Backup your database (easy)

Post by onoehring » 2020-02-15 12:53

Hi,

just me again.
I decided to try another php backup solution. To my amusement this turned out to be a very good decision.
I changed my script now to use https://github.com/ifsnop/mysqldump-php - and it works great.
The (old) script used before (https://github.com/daniloaz/myphp-backup) failed completely when I had old views in the database that uses tables/fields which actually did not exist anymore. Furthermore, the new script even allows to use BZIP2 compression and is much faster than the one before. Please read the notes below.

Only changes between START SETTINGS and END SETTINGS are needed.
* @version 2.0
* backup code was changed to use https://github.com/ifsnop/mysqldump-php (instead of https://github.com/daniloaz/myphp-backup)
* this results in a speed change:
* testbackup
* php myadmin 17 MB uncompressed
* daniloaz = 24 MB uncompressed in about 3 minutes (fails on defect VIEWS, and outputs VIEWS as table data with INSERT INTO...),
* new version (mysqldump-php): 3 seconds, 13 MB uncompressed (GZIP 1.2 MB, BZIP2: 0.8 MB).
* Note: ALL tables and data are exiting in the backup
* changed structure of code: Settings are on top now
* new compression method possible. Now: NONE (outputs text file), GZIP and BZIP2
*
* Define your settings in between START SETTINGS and END SETTINGS. No other changes are needed.
*
* The script will delete an existing backup file before creating a new one. This is done to prevent growing and growing backup files.

Download v2.0
Database_Backup_v2.0.zip
(16.3 KiB) Downloaded 365 times
Comments? Ideas?
Olaf

Post Reply