Georgi Mitev's blog for scripts, tutorials and something else

The MySql database backup website backup is very similar to the previous backup that I showed you 2 days ago. We are going to create a php file that is going to dump our database into filename with the current date and time.

Ok so let’s get going:
1. Create the file “backup_database.php” and input the following code into it:
<?php exec(‘cd /home/your_user/backup/db/;mysqldump –add-drop-table -h localhost –user=your_user –password=your_pass database_name > `date +%y-%m-%d_%H-%M`.sql’); ?>

Sometimes the path to your home dir looks different than ‘/home/your_user/’, so execute the ‘pwd’ command like explained in my previous post

Here we use the command mysqldump in a tutorial that I wrote previously about the dumping the sql database from one host to another. Now the database will be saved directly on the server that is hosting your website

2. Create /backup/ and /backup/db/ directories via SSH or FTP if not created.

Of course you can use any names. But better do not create the /backup/ directory in the /public_html/ directory for security reasons

3. Upload “backup_database.php” to the /home/your_user/backup  directtory

If you upload backup_database.php to www.yoursite.com/backup_database.php by accessing it directly in your browser you will create a backup of your database.  However I do not recommend doing this First because someone can overload your space quota by accessing the backup_database.php many times and second because you probably don’t want to enter the URL in your browser every time you want to perform database backup.

4. Go to CPanel -> Cron Jobs and input the path to backup_database.php:

Click on the `Add new Cron Job` button…     and you are pimped out. In my case I added the option this cron job to be executed twice a day. In case you database is updated more often/rarely you can modify this setting to suit your needs.
You can also add option to save the dump of the database in tar format to save some space in your hdd quota by changing the script a bit.
Full database backup in tar format (save more space):
backup_database.php:
&lt;?php exec(‘FILE=`date +%y-%m-%d_%H-%M`;cd /home/ps3igri/backup/db/;mysqldump –add-drop-table -h localhost –user=your_mysql_user –password=your_pass db_name > $FILE.sql;tar czpf $FILE.tar $FILE.sql;rm -f $FILE.sql’); ?>
As you see here we use the ‘FILE’ variable to store information about the filename. We create a SQL Dump, then we archive it, and after that we delete the original file to save some disk space.
Good luck with the backup and see you next time