Simple Machines Community Forum

does anybody have such a script which is propriate for backing up smf database in exactly period of time?
smf 1.1.2
linux server  

After the backup is done, what should it do with the backup? Do you want it emailed to you, stored in your home directory, uploaded to an external server, or something else?

i didnt see similar topic in croatin support board.
http://www.simplemachines.org/community/index.php?topic=137171.0
this would help

Quote from: Bernard T. on December 29, 2006, 12:29:16 PM
http://www.2serveu.net/cpanel-tutorials/standardcron.html

daniel, i want to store it in my home directory

Quotedaniel, i want to store it in my home directory

OK, first, create a new database username and password (in the MySQL section of cPanel, under "Current Users:"). Then, under "Add Users To Your Databases:", add the user to your database (for security reasons, only give it the "SELECT" permission).

Then, in the Cron section of cPanel, create a cron job with the following command:
mysqldump -ucpuser_username -ppassword cpuser_database > /home/cpuser/backup.sql

cpuser_username: Database username (the one you created earlier)
cpuser_password: Database password
cpuser: cPanel username
backup.sql: File to store backup in
As an example:
mysqldump -udaniel15_backup -pOIHsa3dfa23SDGa daniel15_test > /home/daniel15/backup.sql

If you want to compress the backup using gzip, try a command line like this:
mysqldump -ucpuser_username -ppassword cpuser_database | gzip > /home/cpuser/backup.sql.gz

I haven't tried this, but it should work :)

... or you can just download AutoMySQLBackup script and add that to cron.

http://sourceforge.net/project/showfiles.php?group_id=101066

A script to take daily, weekly and monthly backups of your MySQL databases using mysqldump. Features - Backup mutiple databases - Single backup file or to a seperate file for each DB - Compress backup files - Backup remote servers - E-mail logs - More..

I use it and it works great!  :)

how i can add it to cron?

i've got this error

mysqldump: Got error: 1045: Access denied for user 'xxxxxx'@'localhost' (using password: YES) when trying to connect

Quote from: Farmacija on April 12, 2007, 11:25:46 AM
i've got this error

mysqldump: Got error: 1045: Access denied for user 'xxxxxx'@'localhost' (using password: YES) when trying to connect

That means the the username and password you used was incorrect. Double-check the settings you specified.

Quotemysqldump: Got error: 1044: Access denied for user 'farmaceu_blabla'@'localhost' to database 'farmaceu_wrdp2' when using LOCK TABLES

Make sure that user has full permissions on that database. You'll need to grant "ALL" permissions when adding the user to the database.

you said above  that i should give it just select permission!!!!

Sorry about that... I thought SELECT would cover everything, but it looks like it tries to LOCK the tables. SELECT worked for me using MySQL 4.1, but I guess it doesn't work like this for all versions.

No one is perfect :)
and where is saved that back up? i can't find it in home directory.........

The > /home/daniel15/backup.sql at the end tells it where to write the file to. I assume you changed this to suit your setup? You'll need to look in your home directory; it won't be in the "public_html" directory...

it works daniel, thanx. :)

how we can do this for several databases on 1 server?

I haven't tried this, but just came up with a script that should do this properly. First, make a file called "database-backup.php" in your home directory, and put something like this in it:

#!/usr/bin/php -q
<?php
// Where do we want to store the backups?
$output = '/home/username/backups';

// Some MySQL stuff
$host = 'localhost';
$username = 'mysql_username';
$password = 'mysql_password';

$databases = array(
'database1',
'database2',
// ... add more if you need to, like this:
// 'database name',
);

foreach ($databases as $database)
{
exec('mysqldump -u' . $username . ' -p' . $password . ' ' . $database . ' | gzip > ' . $output . '/' . $database . '.sql.gz');
}
?>


Create a "backups" directory to store the backups (make sure $output in the script above is correct!), and then change the cron job to something like:
/home/username/database-backup.php

Hope this works for you :)

I use one similar to this


<?php
// Ok.. Get our vars in here.
$db['host'] = 'localhost';
$db['user'] = 'root';
$db['pass'] = 'access';
$db['goto'] = '[email protected]';
$db['from'] = '[email protected]';
$db['date'] = date("Y-m-d");
$db['temp'] = '/home/cpaneluser/tmp';

// What to call this sucker.
$mailfile = 'FULL-backup-'.$db['date'].'.tar.gz';

// MySQL we need ya..
$link = mysql_connect($db['host'], $db['user'], $db['pass']);
if(!$link)
die ('Unable to connect to mysql server.. Could not Create Database Backup.');

// Result it
$result = mysql_query('show databases') or die('Couldn\'t Show DB:' . mysql_error());

// For each database put it into a nice array for use.
while($row = mysql_fetch_array($result))
$databases[] = $row['Database'];

// Foreach of the databases we dump it
foreach($databases AS $dbs)
passthru("mysqldump -u " . $db['user'] . " --password=" . $db['pass'] . " " . $dbs . " > " . $db['temp'] . '/' . $dbs . ".sql");

// Now we compress the whole temp folder.. wow...
@passthru('tar -czf '.$mailfile.' '.$db['temp'].'  -p > /dev/null 2>&1');

// Deletion time..
foreach($databases AS $dbs)
if(file_exists($db['temp'].'/'.$dbs.'.sql'))
unlink($db['temp'].'/'.$dbs.'.sql');

// The Fun crap.. Mail preperation..
$attachmentname = array_pop(explode("/", $mailfile));   // If a path was included, strip it out for the attachment name
$message = "Compressed database backup file $attachmentname attached.";
$mime_boundary = "<<<:" . md5(time());
$data = chunk_split(base64_encode(implode("", file($mailfile))));
$headers = "From: ".$db['from']."\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-type: multipart/mixed;\r\n";
$headers .= " boundary=\"".$mime_boundary."\"\r\n";
$content = "This is a multi-part message in MIME format.\r\n\r\n";
$content .= "--".$mime_boundary."\r\n";
$content .= "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n";
$content .= "Content-Transfer-Encoding: 7bit\r\n\r\n";
$content .= $message."\r\n";
$content .= "--".$mime_boundary."\r\n";
$content .= "Content-Disposition: attachment;\r\n";
$content .= "Content-Type: Application/Octet-Stream; name=\"$attachmentname\"\r\n";
$content .= "Content-Transfer-Encoding: base64\r\n\r\n";
$content .= $data."\r\n";
$content .= "--" . $mime_boundary . "\r\n";

// Now we finally mail it..
mail($db['goto'], 'MySQL backup file - ALL', $content, $headers, '-f'.$db['from']);

// Delete the big backup we just made..
unlink($mailfile);
?>

hm, which command then i should put in cron jobs
this mysqldump -ucpuser_username -ppassword  cpuser_database > /home/cpuser/backup.sql ?

You could do that if you want to do it for one database or one for each database.
If you are using the php script you do

php -q /home/cpuser/path/to/phpscript.php

SleePy, I suggest you add a shebang line to the top of the file...
Above <?php, put:

#!/usr/bin/php -q


Then, just use the file path (without the php -q) in the cron job :)

you confusing me, which code i should use in cron jobs when i use daniel15's script for more then one databases? :)

If you use my script above, first edit the script to reflect the correct values (username, password, databases), and then change the cron job to something like /home/cpuser/database-backup.php :).

i don't need to put this mysqldump -ucpuser_username -ppassword  cpuser_database > before /home/cpuser/database-backup.php?


I get this error => /bin/sh: /home/farmaceu/database-backup.php: Permission denied

edit2=> now it's ok, it was needed to chmod file database-backup.php to 744.
thank u very much daniel!!!  :)

No problem ;)

Quote from: Farmacija on April 27, 2007, 04:18:52 AM
i don't need to put this

Code Select Expand

mysqldump -ucpuser_username -ppassword  cpuser_database > before

Code Select Expand

/home/cpuser/database-backup.php?

No, you didn't need to do that, as the PHP script does the mysqldump call itself :)

yes, i figure it out :)

--
But, now i have this error
Failed loading /root/milan/ioncube/ioncube_loader_lin_4.4.so:  /root/milan/ioncube/ioncube_loader_lin_4.4.so: cannot open shared object file: Permission denied

Me again.

Now i have problem because back up file is huge, in a last time it was about 8gb???


Does someone know where is the problem?