Backup Your SQLS each day

[Uridium]

This small mod will allow you to backup your SQLS to a folder on your FTP

create a new folder on your FTP called sqlbackups Or what ever you want to name the folder but remember to edit the name on the following piece

folder needs to be CHMODDED to 755 and make sure you place a index.php or index.html file into your newly created folder...

$mysql_host = "localhost";

$mysql_user = "username";

$mysql_pass = " -p\"password\""; // LEAVE the Final \ where it is this is incase your password uses amps or @'s

$mysql_db = "databasename";

$backupbaselocation = "/home/xxxxxx/public_html/sqlbackups"; // change the xxxxxx to your ftp username if you changed the folder name as above then change this to match

$backuplocation = $backupbaselocation . "/" . date("d-m-Y");

mkdir($backuplocation, 0777);

system("mysqldump --user=$mysql_user --password=$mysql_pass $mysql_db > $backuplocation/database.sql");

now copy and paste the whole part before the ?> on your cron_day

And your done your SQLS will be backed up each day

[Danny696]

Love it!

[Sim]

interesting. I never used system() before. Work on all web servers or is this like a shell? run? command?

[Danny696]

Sim, try This!

[Uridium]

It will run on all Server sometimes getting the $backupbaselocation = "/home/xxxxxx/public_html/sqlbackups"; location correct can be a pain as some use different methods but on mine its as ive shown...

some have example >> $backupbaselocation = "usr/www/home/xxxxxx/public_html/sqlbackups"; so edit this if it fails

[Equinox]

You may want to look into compressing this, otherwise it could be huge.

$backupbaselocation = "/home/xxxxxx/public_html/sqlbackups"; // change the xxxxxx to your ftp username if you changed the folder name as above then change this to match

$backuplocation = $backupbaselocation . "/" . date("d-m-Y");

mkdir($backuplocation, 0777);

system("mysqldump --user=$mysql_user --password=$mysql_pass $mysql_db | gzip > $backuplocation/database.gz");

Should do the trick

[Djkanna]

Not the best idea to store your sql backups where others can access them. :)

But other than that, nice work Illusions.

[Dave]

Should ideally store them out of the public_html directory somewhere in your home folder is always a good bet.

[Equinox]

Should ideally store them out of the public_html directory somewhere in your home folder is always a good bet.

[iseeyou94056]

is there a way to make it so that a full backup gets sent to your email everyday?

[Djkanna]

is there a way to make it so that a full backup gets sent to your email everyday?

In short yes, getting it to send as an attachment is flippin' tricky but possible.

[Uridium]

ive been tinkering with the script and its possible for the script to just pull 1 table from the database and put that to the folder instead of the whole lot..

[rulerofzu]

Specific tables

mysqldump -uusername -ppassword database-name table1,table2,table3 | gzip > db.sql.gz

You can send the files using PHP ftp_connect and ftp_put

http://php.net/manual/en/function.ftp-put.php

I prefer to use linux shell for backups kept out of root backed up to a directory out of root and a copy is also passive ftp'd to a remote backup server which is only accessible on the network not from the net itself. But I also run a ecommerce business so keeping backups is essential.

[iseeyou94056]

is there a way to make this work for people that dont use ftp

[Uridium]

is there a way to make this work for people that dont use ftp

 

Is this a trick question :)

even for those who have cpanel you can use te file manager to locate the sqls files in the folder you created

[peterisgb]

Warning: system() has been disabled for security reasons in /home/ividenco/public_html/*********.php on line 24

any help with this?

[a_bertrand]

No we cannot really help as your host has simply blocked the function system. Check if there is other ways to call the mysql_dump maybe from cpanel or equivalent.

[Dave]

is there a way to make it so that a full backup gets sent to your email everyday?

You could do this indeed but maybe hard on a shared host due to most hosts blocking the system, and many other, functions due to the security reasons.