A user sent the following:

>John:
>
>The phpMyAdmin you use through the Admin Panel DOES NOT offer the >read dump from file.

Is this true, and if so, is there a way round it?.


Thanks,

John

Import the dump file from the mysql command line using SSH is the easiest way to do it.

Thanks for the reply. Is there anyway for a user to do it if the don't have shell access?.

Thanks,

John

Yes, if you do it for him/her.

You can load dumps for the user through phpMyAdmin if you login as root as well. You might also be able to assign the 'rights' to the user, but I've not experimented with that.

Import the dump file from the mysql command line using SSH is the easiest way to do it.

How do I do this.


a Customer sent me this I'm assuming I can do it the above way.

//////////////////////////////////////////////////////////////////////

I am connecting to the MySQL database on mydoamin.com that I setup via EMS MySQL Manager, and am able to access it create tables, and do almost anything from here. BUT, if I try to RESTORE a backup of the tables that I have, I am getting an error:

Operation started!
Restore in progress...
Failed: Access denied for user: 'root@CPE00606773bd06.cpe.net.cable.rogers.com' (Using password: YES)
End of operation!

Any idea why?? Is it because you only allow this operation from LOCALHOST???

//////////////////////////////////////////////////////////////////////

Login via SSH



Note that there is no root user on plesk, it is admin.

Not: there may be no need to specify the database as it may be in the sql file so in that case just omit the database name.

If it is a small database, just copy and paste the dump file text into phpMyAdmin's Query box and click GO.

If it is a large database you will need to import it for the client as stated above.

mysql -h localhost -u admin -p databasename < dumpfile.sql

When I try the above I get
bash: mysql: command not found

change database to the database name.
change dumpfile.sql to name of dump file.

try:
/usr/bin/mysql -h localhost -u admin -p databasename < dumpfile.sql

Actually, users CAN do this via PHPMyAdmin WITHOUT root permissions. Simply have your users log into PHPMyAdmin, then click the DATABASE they need things imported into (not a table, but the actual DB)

Now scroll down until you find "Or Location of the textfile :" And then click browse. Locate the file on the DESKTOP system, and it'll upload everything to the DB .

This works GREAT for smaller dbs. Anything over 20 MB should be done by the administrator via the command line, and this is due to the fact that PHPMyAdmin will fail the request if it takes longer than 30-60 seconds to upload and run.

/usr/bin/mysql -h localhost -u admin -p databasename < dumpfile.sql
I get bash: cd: /usr/bin/mysql: No such file or directory

Is there another way to login to PHPMyAdmin other then the contol panel?

When I use the control panel click on PHPMyAdmin
When it opens I click on the data base name in top right.
I get
Database test running on localhost
No tables found in database
Show this query here again

Create new table on database test :
Name :
Fields :

That is all.

I also noticed that when I create a database and delete it it is removed from the control panel but not out of /usr/local/psa/mysql/var//usr/local/psa/mysql/var/

Are you sure you have the latest version of PHPMyAdmin. I have version 2.2.0, and the latest is 2.2.6. Maybe you need to upgrade?

phpMyAdmin 2.2.2

How do I upgrade.

I searched the forum but only say ensim or upgrades to php.

mysql is located at /usr/local/psa/mysql/mysql in plesk so you'll need to call it from there

I tried that.

this is from inside /usr/local/psa/mysql/var/test

/usr/local/psa/mysql/mysql -h localhost -u admin -p test < test.sql
bash: /usr/local/psa/mysql/mysql: No such file or directory

/usr/local/psa/mysql -h localhost -u admin -p test < test.sql
bash: /usr/local/psa/mysql: is a directory

/usr/bin/mysql -h localhost -u admin -p test < test.sql
bash: /usr/bin/mysql: No such file or directory

Your user can do it itself whitout any SSh needed...

Here a script to do it :

Put this on a file called back.pl

# -start -----------------------
#!/usr/bin/perl

require "./back.pm";

$|++;
&ReadParameters();

if ($P{'todo'} eq 'makeanddownload')
{
print "Expires: Mon, 06 May 1996 04:57:00 GMTnContent-Disposition: attachment; filename=".&TextDate(time).".sqlnContent-type: application/binarynn";
binmode('STDOUT');
system "$DumpDBCommand";
}
elsif (($P{'todo'} eq 'downloadfile') and $P{'file'})
{
print "Expires: Mon, 06 May 1996 04:57:00 GMTnContent-Disposition: attachment; filename=$P{'file'}nContent-type: application/binarynn";
my $CHUNK_SIZE = 4096;
open(IN, "$DBDumpsDir$DirSeparator$P{'file'}");
binmode('IN');
binmode('STDOUT');
while ( read( 'IN', $data, $CHUNK_SIZE ) ) { print $data };
close IN;
}
elsif ($P{'todo'} eq 'save')
{
print &TopOfPage();
if (!$P{'filename'}) { $filename = &TextDate(time).'.sql' } else { $filename = $P{'filename'} };
print "

Operation: saving DB dump with name '$filename'

";
$code = system "$DumpDBCommand >"$DBDumpsDir$DirSeparator$filename"";
&HoldError("Command $DumpDBCommand >"$DBDumpsDir$DirSeparator$filename" executed with error") if ($code);
print '

---

';
print &Form();
print &BottomOfPage();
}
elsif (($P{'todo'} eq 'restore') and $P{'filename'})
{
print &TopOfPage();
print "

Operation: restoring DB with dump '$P{'filename'}'

";
$code = system "$RestoreDBCommand <"$DBDumpsDir$DirSeparator$P{'filename'}"";
&HoldError("Command $RestoreDBCommand <"$DBDumpsDir$DirSeparator$P{'filename'}" executed with error") if ($code);
print '

---

';
print &Form();
print &BottomOfPage();
}
elsif (($P{'todo'} eq 'delete') and $P{'files'})
{
print &TopOfPage();
print "

Operation: deleting files.

";
foreach $file(split(/0/, $P{'files'}))
{
unlink ("$DBDumpsDir$DirSeparator$file") || &HoldError("Can not delete file'$DBDumpsDir$DirSeparator$file'");
}
print '

---

';
print &Form();
print &BottomOfPage();
}
elsif (($P{'todo'} eq 'upload') and $P{'uploadfilename'})
{
print &TopOfPage();
$filehandle = $MULTIPARTQUERY->param('uploadfilename');
$filename = lc($P{'uploadfilename'});
$filename =~ s!^.*(|/)!!;
print "

Operation: uploading file '$filename'

";
open(OUT, ">$DBDumpsDir$DirSeparator$filename") || &HoldError("Can not save to file '$DBDumpsDir$DirSeparator$filename'");
binmode OUT;
while (read($filehandle, $buffer, 1024))
{
print OUT $buffer;
}
close OUT;
print '

---

';
print &Form();
print &BottomOfPage();
}
else
{
print &TopOfPage();
print &Form();
print &BottomOfPage();
}

################################################################################
#
sub TopOfPage
{
return qq{Expires: Mon, 06 May 1996 04:57:00 GMTnContent-type: text/htmlnn

Backup and restore

---

};
}

sub Form
{
my $filename = &TextDate(time).'.sql';
my $result = '';
$result = qq{

Make DB dump

• make dump and download it
  
  
• make dump and save it with name:

---

Select DB dump for delete or restore

};
opendir (DIR, $DBDumpsDir);
while ($file = readdir(DIR))
{
($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,$atime,$mtime,$ctime,$blksize,$blo
cks) = stat("$DBDumpsDir$DirSeparator$file");
$mtime = TextDateTimeSize($mtime);
$result .= qq{
• | | $file [i]($mtime; size: $size bytes)} if ($file ne '.' and $file ne '..')
  }
  closedir(DIR);
  $result .= qq{

---

Upload DB dump

};
return $result;
}

sub BottomOfPage
{
return qq{

---

};
}

sub HoldError
{
print "

Error: @_[0]

n";
}

sub TextDate
{
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($_[0]);
$year = 1900+$year;
++$mon;
if (length($mon)==1) {$mon="0$mon"};
if (length($mday)==1) {$mday="0$mday"};
$_ = "$year-$mon-$mday";
}

sub TextDateTimeSize
{
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($_[0]);
my(@mons) = ('January', 'February', 'March', 'April', 'May', 'June', 'Jule', 'August', 'September', 'October', 'November', 'December');
$year = 1900+$year;
$_ = "$mday $mons[$mon] $year; $hour:$min:$sec";
}

sub ReadParameters
{
use CGI;
$MULTIPARTQUERY = new CGI;
my @params = $MULTIPARTQUERY->param;
my ($line);
foreach $line(@params) { %P -> {"$line"} = join("0", $MULTIPARTQUERY->param("$line")); }
}

# -end------------------------



second file is the config file call it back.pm

# -start------------------------

#############################################################################
# Change the pass / database name / username to match your database
# that you have created before then also the path to your domain
#####################################################

{
$mySQLDBName = 'databasename';
$mySQLHost = 'localhost';
$mySQLUser = 'username';
$mySQLPassword = 'password';
$DBDumpsDir = '/usr/local/psa/home/vhosts/yourdomain.com/cgi-bin/backup';
$DumpDBCommand = "/usr/local/psa/mysql/bin/mysqldump --host=$mySQLHost --user=$mySQLUser --password=$mySQLPassword --add-drop-table $mySQLDBName";
$RestoreDBCommand = "/usr/local/psa/mysql/bin/mysql --host=$mySQLHost --user=$mySQLUser --password=$mySQLPassword $mySQLDBName";
$DirSeparator = '/';
}

# end
#############################################################################


Save this 2 files somewhere on your cgi-bin then chmod 755 the .pl create the backup directory then run it.. If you want to restore a file put that file in the backup directory then run the script !!

This was awesome script usable by your customer installed in 2 mins ;-)