473,695 Members | 2,313 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Backup with mysqldump

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($comma nd, $retval)){die(' Backup failed.);}

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

mysqldump.exe is at C:\Program Files\xampp\mys ql\bin\mysqldum p.exe and
the script is sitting in the folder C:\Program Files\xampp\htd ocs
\onlinelearning .

When I put a copy of mysqldump.exe in the folder C:\Program Files\xampp
\htdocs\onlinel earning 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=@@CH ARACTER_SET_CLI ENT */;
/*!40101 SET @OLD_CHARACTER_ SET_RESULTS=@@C HARACTER_SET_RE SULTS */;
/*!40101 SET @OLD_COLLATION_ CONNECTION=@@CO LLATION_CONNECT ION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE= @@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00: 00' */;
/*!40014 SET @OLD_UNIQUE_CHE CKS=@@UNIQUE_CH ECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KE Y_CHECKS=@@FORE IGN_KEY_CHECKS,
FOREIGN_KEY_CHE CKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@ @SQL_MODE,
SQL_MODE='NO_AU TO_VALUE_ON_ZER O' */;
/*!40111 SET @OLD_SQL_NOTES= @@SQL_NOTES, SQL_NOTES=0 */;

---

Any help is greatly appreciated.

Regards,

Jan Nordgreen

Jun 20 '07 #1
7 4714
I got it to work by changing "-p $dbpass" to "--password=$dbpas s"!

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=$dbpas s
$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
Jun 20 '07 #2
This did it: $command = "..\..\mysql\bi n\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

Jun 20 '07 #3
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.
Jun 20 '07 #4
On Jun 20, 7:58 pm, damezumari <jannordgr...@g mail.comwrote:
This did it: $command = "..\..\mysql\bi n\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.

Jun 20 '07 #5
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\bi n\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

Jun 21 '07 #6
On Jun 21, 4:19 pm, damezumari <jannordgr...@g mail.comwrote:
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\bi n\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\system 32 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.

Jun 21 '07 #7
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

Jun 21 '07 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
15463
by: James | last post by:
HI, I'm looking for a script that will allow users/admins to have a one click backup solution for a MYSQL Database.. 'BACK DATABASE' button, click and its done... The a restore option, that shows all current backups, and restores the selected one with one click...
4
2570
by: Dave Moore | last post by:
Hi All, I'm looking at using PHP and MySQL for a particular website application. However, I was a bit concerned that if a PHP script went wrong then it would be possible to accidentally delete or overwrite large chunks of the data in the database which presumably would then be lost. What is the usual procedure for these situations?. Is it possible to back up a database somehow so that data from a previous time can be retrieved?. Ta,...
0
1658
by: xabaras | last post by:
Doubt. Backup of the database. Example: CREATE DATABASE prova; CREATE TABLE `test` (`progressivo` tinyint(3) unsigned NOT NULL auto_increment,`testo` char(50), PRIMARY KEY (`progressivo`)) TYPE=MyISAM;
1
3623
by: Thiko | last post by:
Hi We currently use mysqldump to dump all our db's in quick mode to one file, backup.sql. This file has come quite large. mysqldump -Aq -u root > /MySQL/backup/backup.sql I would now like to split the mysqldump so that the output comprises of several files of less than 2 gigabytes each. Does anyone know the mysqldump options that could be used to do this? Or with using the >
3
11416
by: Reuben Pearse | last post by:
Hi all, I've just converted the tables in a big database (approx 27 million records) from MyISAM to InnoDB. When I was using MyISAM I backed up the database by stopping MySQL and then copying the MYI, MYD, frm files in the database directory, and restored the database in the same way. Is there a similar way to backup and restore InnoDB tables, or do I have to use the InnoDB Hot Backup tool?
2
3242
by: Raffi | last post by:
I use the --all-databases switch to backup my entire database. Sometimes there's a need to restore individual databases or tables form the backup file. What command should I use for this? Thanks, Raffi
0
1891
by: newman | last post by:
Dear all, I have mysql 4.1.11 on my current server, i need my database restore another server.. (another server mysql version is 4.1.11 same.) And now... I just created new my database to new server following command: CREATE DATABASE dbname DEFAULT CHARACTER SET latin5 COLLATE
4
4062
by: Bootstrap Bill | last post by:
I'm looking for a PHP program to backup and restore a mysql database. I'm using Godaddy to host a forum. Their mysql control panel will only restore a database of two megabytes or less. My database is over 13 megabytes in size.
15
2404
by: Jerry Yang | last post by:
Hi I'm having issues with mysqldump so need to create backups of my mysql databases using PHP. Can any one recommend a way to do this with out calling mysqldump ? I did find one script and it worked on a small sample DB (1MB), but fails on my db (75MB) Thanks :)
0
8574
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9119
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8994
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8830
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7664
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6493
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4342
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3008
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2276
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.