Backup with mysqldump

[damezumari]

I use Windows XP and Apache.

I am trying to backup a database on my local computer with the
following php code:

$dbname = 'ol';
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$backupFile = $dbname .'-' .date("Y-m-d-H-i-s") . '.sql';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname >
$backupFile";
if (!system($command, $retval)){die('Backup failed.);}

It does produce a file like C:\Program Files\xampp\htdocs
\onlinelearning\ol-2007-06-20-13-31-09.sql, but it is empty.

mysqldump.exe is at C:\Program Files\xampp\mysql\bin\mysqldump.exe and
the script is sitting in the folder C:\Program Files\xampp\htdocs
\onlinelearning.

When I put a copy of mysqldump.exe in the folder C:\Program Files\xampp
\htdocs\onlinelearning I get a black sreen saying 'Enter password:'.
When I give the password I get a file like this:

-- MySQL dump 10.10
--
-- Host: localhost Database: ol
-- ------------------------------------------------------
-- Server version 5.0.18

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,
SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

---

Any help is greatly appreciated.

Regards,

Jan Nordgreen

[damezumari]

I got it to work by changing "-p $dbpass" to "--password=$dbpass"!

This works:

$dbname = 'ol';
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$backupFile = $dbname .'-' .date("Y-m-d-H-i-s") . '.sql';
$command = "mysqldump --opt -h $dbhost -u $dbuser --password=$dbpass
$dbname $backupFile";
$dummy = system($command, $retval);
die($retval);

What remains is not to be dependent on mysqldump.exe in the script's
folder.

Regards,

Jan Nordgreen

[damezumari]

This did it: $command = "..\..\mysql\bin\mysqldump ...

Final question in this conversation with myself :), why didn't php
find mysqldump.exe on its own. Do I set this in php.ini?

Regards,

Jan Nordgreen

[Anonymous]

damezumari wrote:

$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname >
$backupFile";

Any help is greatly appreciated.

You should read the mysqldump manual more carefully.

--password[=password], -p[password]
The password to use when connecting to the server. If you use the short
option form (-p), you cannot have a space between the option and the
password. If you omit the password value following the --password or -p
option on the command line, you are prompted for one.

[shimmyshack]

On Jun 20, 7:58 pm, damezumari <[e-mail]:

This did it: $command = "..\..\mysql\bin\mysqldump ...

Final question in this conversation with myself :), why didn't php
find mysqldump.exe on its own. Do I set this in php.ini?

Regards,

Jan Nordgreen

it has to be in the path, use the system properties window, advanced
environmental vars, to set the system path to include the path to
executables you wish apache to be able to use.
It does sound though like you have your apache being run as SYSTEM -
and so it is able to range throughout the filesystem, you might
consider locking that down a bit (if you are running xp pro) but
creating and apache user.
Also when backing up your database you dont need root permissions,
create a user which has only the right to select and lock tables for
that database, and use that to backup, then should your script leak
out, they can read but not change your database. Each database could
have a "web user" which ahs only the rights your app needs it to have
a "backup user";select, lock tables, and of course there may well be a
root account with a password (which is not the default) which is used
to administer the whols DBMS.
my $0.02 (it doesnt take much to get into the habit of this, consider
where a backup user password will be, scripts marked db_backup and
cron jobs - which might result in password being sent via email, so
its best to have users specific for the task they will perform IMHO.

[damezumari]

Anonymous:
Thanks for the tips on the extra space. You were right.

shimmyshack:
1. I did as you suggested in Control Panel and edit the system
variable Path to include the folder where mysqldump.exe sits. Then I
could replace "..\..\mysql\bin\mysqldump..." with "mysqldump...".

2. " It does sound though like you have your apache being run as
SYSTEM -
and so it is able to range throughout the filesystem, you might
consider locking that down a bit (if you are running xp pro) but
creating and apache user."
How do I create an apache user?

3. I created a backup user like you suggested and it worked fine!

Thank you for your help!

Regards,

Jan Nordgreen

[shimmyshack]

On Jun 21, 4:19 pm, damezumari <[e-mail]:

Anonymous:
Thanks for the tips on the extra space. You were right.

shimmyshack:
1. I did as you suggested in Control Panel and edit the system
variable Path to include the folder where mysqldump.exe sits. Then I
could replace "..\..\mysql\bin\mysqldump..." with "mysqldump...".

2. " It does sound though like you have your apache being run as
SYSTEM -
and so it is able to range throughout the filesystem, you might
consider locking that down a bit (if you are running xp pro) but
creating and apache user."
How do I create an apache user?

3. I created a backup user like you suggested and it worked fine!

Thank you for your help!

Regards,

Jan Nordgreen

if you have windows xp PRO, you can goto "my computer" (or explorer)
tools->folder options->view->uncheck "use simple file sharing
(recommended)"
now you will have more config options to create users.
start->run->compmgmt.msc->local users and groups->users
(right click users on left) -new user
username:apache
full name: apache user
description: this is the user apache uses
set some strong password
[] user must change password at next logon
[x] user cannot change password
[x] password never expires
[] account is disabled
ok it
double click your new apache user, member of tab
get rid of everything, so it is a member of nothing.

now you have your apache user, find the folders which you would like
apache to be able to access
each one - think where your config files are, the exes, the tmp
folder, the doc root, everything that apache needs to function, if you
run php as a module then include where php is.
You might find that all this is covered by something like
c:\xampp\
because everything lives under there, otherwise you will have to do
each manually, these days your config should not be in c:\windows or c:
\windows\system32 if it is move it out of there and next to the apache
executable or somewhere less "important"
right click that folder and select
protperites->security tab
add->advanced->find
find your apache user, click on it and say ok, and ok it again
once you have your apache user added, you will need to set its rights
useing the checkboxes.
the apache user will need write access to some parts but read only on
others, think carefully and make your decisions.
now that you have everything set up goto
start->run->services.msc
double click the apache service (I am assuming you have apache running
aas a service)
logo on tab
under "this account" type the user "apache" and its strong password,
apply, and ok the warning, restart the service
apache will start if it has enough rights to do so.
Now if php is running as a module, write a short script that tries to
read files from outside the folders for which apache has permission.
It shouldnt be able to.
Same goes for write permissions.
try to start a session with php, it will use the tmp directory and you
should check it can read/write to the session files.
If everything is ok, you now have a more secure setup.
hope it helps.

[damezumari]

Thanks for the answer!

Unfortunately I have only Windows XP Home, but the day i upgrade to
Pro I will use your notes.

Regards,

Jan Nordgreen