How to backup your MYSQL Database

[Northern Monkey]

Evening All,

My database has grown to about 7MB on a table. I used to backup and restore with no problems but I'm doing this manually and as the tables have grown I've noticed the backups don't include all the rows! Schwepps! How do you lot backup your data everynight and restore it? fcron jobs??

My info is I'm using MYSQL held on an ISP server.

Regs,

NM.

[Donk]

If your server has cpanel you can do it through the backup option on the menu then "Download a MySQL Database Backup" alternatively you should be able to do it with phpmyadmin.

Regards

[Northern Monkey]

Yeah I use phpmyadmin but that's when I started seeing lines missed off. What I'm after is something automatic how do you do it?

NM.

[monkeyboy]

Check out mysqldump

http://www.unixcities.com/mysql/mysqldump.html

[Donk]

I work the other way round. ie my main data files are kept and updated on my computer then I upload them to my site.

I've written a script for one of my customer that enables him to download his database in an xml file but his database is not all that big.

One way you could do it is to write a script to create a csv file on your server. something along these lines.

PHP Code:

open  database etc
<?php
$fp=fopen("backup.csv","w");
$sql="SELECT * FROM table";
$result = mysql_query($sql) or die("Query failed : " . mysql_error());
while($data= mysql_fetch_array($result,MYSQL_NUM))
{
$csv=implode("'",$data);
fwrite($fp,$csv);
}
fclose($fp);
?>

You will need to change the permissions of the directory to which you want to write the file.

Regards

[Lynx]

You could automate a daily backup using a cronjob

mysqldump --user=DB_USER --password=PASSWORD --quick --add-drop-table --add-locks --extended-insert --lock-tables --all DB_NAME | gzip > /home/directory/to_store_backup/bkup-`date -I`.gz

Same as above: You will need to change the permissions of the directory to which you want to write the file.


L.

[andylink]

I too use a cronjob and mysqldump to a backup server.

Andy

[Northern Monkey]

Ok,

I like the sound of the backup by cron job (as my users add data to the database so it can't be on my computer). Only problem is when I try to import the dumped file through mysql\Apache running on Windows XP it comes up with "No SQL Query"?? WTF!

I have changed php.ini to accept larger files etc etc. Any ideas?

Regs,

NM.

[Sandis]

Great tips guys! I like that CSV approach a lot uh.. these programmers

[OnlineMerchants]

Hi,

Well I have to backup a few databases and it may be more than
you need but I use this - and it's brilliant to be honest

http://sourceforge.net/projects/automysqlbackup/

I run the above on a daily cron job and then once the above is complete
I have added another script that tar's up all the backups and copies them
to another server. The second script called world wide backup can be
found here: http://www.worldwidecreations.com/freescripts5.htm

You may be able to just use the second one for your needs??

HTH

Cheers

Steve

[Northern Monkey]

Many thanks, just what I'm looking for.

Ta,

NM.