backing up a mysql db via ftp

[Bongeh]

Hi there, im trying to backup a mysql db from a site, but i only have ftp access, i have the username and password for the db and the ftp but dont have sufficient knowledge to do it in php.

 

I spoke to a friend of mine who said you should be able to get the db to write to a txt file, and then write the txt back to a mysql.

 

Any ideas?

 

Thanks in advance

[cooldude832]

I can see you doing something like this

 

1) Open User Connectin

2) Run mysql_list_dbs

3) do a "foreach database found" retrive all the table names in that database

4) do a "Foreach each table found" retrive field names + field data and store it in a file called DBNAME_TABLE_NAME_TIMESTAMP

 

Take all those files and compress them to a zip file

 

send the zip file via header download initalization

 

 

That would back up every table you have as a user, a bit of coding but pretty straightforawrd.

[Bongeh]

hmm thanks, its given me something to think about, now just need to work out how to do all that lol..

 

i know the db name.

[trq]

You can run php scripts on the same server as the db server?

 

If so, you can simply use the exec function to execute the mysql command and dump the database to a file.

[cooldude832]

hmm thanks, its given me something to think about, now just need to work out how to do all that lol..

 

i know the db name.

 

Its just a bunch of foreach loops and creating a file

[Bongeh]

so far ive got

 

<?php

  $link = mysql_connect("localhost", "azahar_user", "*******")

    or die ("Could not connect to database server");

  $result = mysql_select_db("azahar_db", $link)

    or die ("Could not find azahar_db");

?>

 

 

Password asterix'd out for obvious reasons

 

So thats me connected to the mysql server and to the database?

[cooldude832]

add print_r($result); afterward and see what you get, should be an array or a text list.

 

[Bongeh]

<?php

  $link = mysql_connect("localhost", "azahar_user", "ii9Echi4")

    or die ("Could not connect to database server");

  $result = mysql_select_db("azahar_db", $link)

    or die ("Could not find azahar_db");

    print_r($result);

?>

 

All the page displayed was

 

1

[cooldude832]

what are the table names in that db?

[KrisNz]

get a copy of phpmyadmin, then upload it to your server. You'll need to set up the config file with the appropriate username and password but once you get that going, you'll be able to use that to backup all your databases.

[Nhoj]

Wrote this a long time ago, works like a charm on linux servers:

 

<?
$time = $_SERVER['REQUEST_TIME'];

$localdir = '/home/backups'; // Starting slash, no ending slash
$ftpdir = 'backups/sql'; // No starting slash, no ending slash
$ftpuser = ''; // FTP user
$ftppass = ''; // FTP Password
$ftphost = ''; // FTP Host
$owner = 'root'; // The user who is given ownership permissions once the backup is made, it defaults to root so lets change it....
$dbuser = ''; // Database User
$dbpass = ''; // Database Pass
$dbname = ''; // Database Name
$dbhost = ''; // Database Host

// Backup the DB
if (!file_exists($localdir)) {
mkdir($localdir, 0755);
}
$backup_name = date('m-d-Y H_i A', $time);
exec('/usr/bin/mysqldump --host="'.$dbhost.'" --user="'.$dbuser.'" --password="'.$dbpass.'" --opt "'.$dbname.'" 2>&1 >"'.$localdir.'/'.$backup_name.'.sql"');
exec('nice -n 19 bzip2 "'.$localdir.'/'.$backup_name.'.sql"');
exec('chown -R '.$owner.':'.$owner.' "'.$localdir.'"');

// FTP Connect, home server
$ftp_id = ftp_connect($ftphost, 21, 20);
$ftp_login = @ftp_login($ftp_id, $ftpuser, $ftppass);

// Create the backup folder
@ftp_mkdir($ftp_id, $ftpdir);

if ($ftp_id) {
// Read the backup directory and FTP the contents to home server
if ($handle = opendir($localdir)) {
	while (false !== ($file = readdir($handle))) {
		if ($file != '.' && $file != '..') {
			if (ftp_put($ftp_id, ftpdir.'/'.$file, $localdir.'/'.$file, FTP_BINARY)) {
				unlink($localdir.'/'.$file);
			}
		}
	}
	@closedir($handle);
}

@rmdir($localdir);
}

// Close the FTP connection
@ftp_close($ftp_id);
?>

 

This will create an sql backup, archive it, put it in the local backup dir then, it will look thru the backup dir and upload all backups to the ftp server, then delete the local copy.

 

Especially useful if the backup server is down at the time of the backup as it will save it locally and keep it until it can upload all of them.

[Bongeh]

wow thanks for that, before i run it, the $owner field, what should i set that as? or leave it as root?

[Bongeh]

i ran the script with

 

$localdir = '/home/backups'; // Starting slash, no ending slash

$ftpdir = 'backups/sql'; // No starting slash, no ending slash

$ftpuser = 'azahar'; // FTP user

$ftppass = '******'; // FTP Password

$ftphost = 'ftp.*****.com'; // FTP Host

$owner = 'root'; // The user who is given ownership permissions once the backup is made, it defaults to root so lets change it....

$dbuser = 'azahar_user'; // Database User

$dbpass = '******'; // Database Pass

$dbname = 'azahar_db'; // Database Name

$dbhost = 'localhost'; // Database Host

 

Nothing happened, i just got a blank page with no errors, and ive looked on my ftp and cant seem to find the backup file

[Bongeh]

im really sorry for tripple posting, but i found all the table names;

 

 

Table: addresses

Table: bottomimgs

Table: categories

Table: catkwds

Table: copy2_propimgs

Table: copy3_properties

Table: copy3_propimgs

Table: copy3_sectionimgs

Table: copy_propimgs

Table: copy_sectionimgs

Table: currencies

Table: enquiries

Table: keywords

Table: kyerotypes

Table: pagekwds

Table: pages

Table: pagesections

Table: prodcats

Table: prodimgs

Table: prodkwds

Table: products

Table: propcatids

Table: propcats

Table: properties

Table: propimgs

Table: propmeta

Table: proppgs

Table: provinces

Table: sectionimgs

Table: supcats

Table: supkwds

Table: suppliers

Table: users

Table: usertypes

[Nhoj]

Owner should be whatever user you are running as, if its a cpanel server then it's the cpanel username ur using, otherwise root should do.

 

Also, you need to have full privilages with the mysql user i do believe, you also need to have exec enabled on your server and bz archiving installed.

 

In addition, check your local backup folder to see if the backup was saved there, it might simply be an ftp issue... And as for a blank page, it's not going to output anything on the page as it's intended for cron job usage