Automaticaly Backup Database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
amplifire
Forum Newbie
Posts: 23
Joined: Thu Jul 22, 2010 1:09 am
Location: India
Contact:

Automaticaly Backup Database

Post by amplifire »

Hello everyone!!
I want to refer for this problem. I want to backup my database after particular interval of time. Please guide me on concept of how to do this. I have something going on in mind but still need some advice!!

Suggestions Appreciated.
Regards!!
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Automaticaly Backup Database

Post by AbraCadaver »

amplifire wrote:Hello everyone!!
I want to refer for this problem. I want to backup my database after particular interval of time. Please guide me on concept of how to do this. I have something going on in mind but still need some advice!!

Suggestions Appreciated.
Regards!!
Assuming mysql, put mysqldump in a cron job.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Automaticaly Backup Database

Post by califdon »

As an alternative strategy, and in case your database is hosted somewhere that you don't have access to system functions like cron, here's another possibility to consider:

What many people don't think about, with respect to database backups, is that no purpose is served by backing up data that hasn't been modified since the previous backup, so if you design your application so that every transaction resets a timestamp in a table, then each time your application starts, it can compare the stored timestamp with the current date-time, and if more than a certain time has passed since the most recent transaction was processed, you can initiate the mysqldump, or similar function if it is not MySQL. This approach has limitations, of course. If the database is very active (transactions almost continuously) or if the database is extremely large (many gigabytes), this approach might cause slowdowns or interruptions. But for smaller and less active databases, I would prefer this to forcing backups that only replace the same data.
amplifire
Forum Newbie
Posts: 23
Joined: Thu Jul 22, 2010 1:09 am
Location: India
Contact:

Re: Automaticaly Backup Database

Post by amplifire »

Yes my Database is updated regularly. New entries are made every day. I read somewhere i can use system() function and then mysqldump to backup after some times. Although time stamp is not required but i will try to use it too.

Can Anyone tell me how system() function works?

Thanx!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Automaticaly Backup Database

Post by califdon »

boyter
Forum Newbie
Posts: 3
Joined: Sun Dec 05, 2010 6:44 pm
Location: Sydney

Re: Automaticaly Backup Database

Post by boyter »

Code: Select all

#!/bin/sh
date=`date -I`

for I in $(mysql -u root -pPASSWORD -e 'show databases' -s --skip-column-names);
do
  mysqldump -u root -pPASSWORD $I | gzip > "$date-$I.sql.gz";
done
The above is what I use. It queries for every database in the system and then dumps them into a seperate gzipped file. Works well.
amplifire
Forum Newbie
Posts: 23
Joined: Thu Jul 22, 2010 1:09 am
Location: India
Contact:

Re: Automaticaly Backup Database

Post by amplifire »

Hello everyone
I referred to you for database backup using mysqldump ans system() function to execute it.
So far iam able to create backup file, but my backup file is empty. When i use mysqldump from command line, it works.
But from php it doesnt work what could be the problem?

Here's my code:

Code: Select all

if($_POST['backup'])
	{	
		 $command="mysqldump -h localhost -u root nigamkota > database/backup.sql";
		 system($command); 
		
		 echo "<script>alert('table has been copied');</script>";
	}
Regards
Post Reply