I'm looking for a way to script the process of creating a snapshot of
a database as a backup in Windows XP.

I currently create snapshots by exporting the database with
phpMyAdmin. This involves running phpMyAdmin in the browser, entering
the password, clicking Export, setting a couple of fields in the
export page, and clicking Go. Then I have to respond to a couple of
dialog boxes from the browser to save the export to a file.

I'd like to be able to run something from a batch file to create a
snapshot in a single step. Any suggestions?

Jonathan Sachs <js070717@sbcglobal.net> wrote in
news:05m60495qsho2tt86poku4avgjbqgku0un@4ax.com:

> I'd like to be able to run something from a batch file to create a
> snapshot in a single step. Any suggestions?
>

Those who live by the GUI, die by he GUI.

use mysqldump instead

On 2008-04-14, Jonathan Sachs <js070717@sbcglobal.net> wrote:
> I'm looking for a way to script the process of creating a snapshot of
> a database as a backup in Windows XP.
>
> I currently create snapshots by exporting the database with
> phpMyAdmin. This involves running phpMyAdmin in the browser, entering
> the password, clicking Export, setting a couple of fields in the
> export page, and clicking Go. Then I have to respond to a couple of
> dialog boxes from the browser to save the export to a file.
>
> I'd like to be able to run something from a batch file to create a
> snapshot in a single step. Any suggestions?

I'm running this kind of stuff in cron:


#!/bin/sh
#This script is for backing up my Gallery2 database by using mysqldump:

# Delete backups older than 7 days (alter the age here):
find /path/to/backupfiles/* -mtime +7 -exec rm {} \;
echo old backups processed

#Set gallery2 to maintenance mode:

/usr/bin/replace "\$gallery->setConfig('mode.maintenance', false);"
"\$gallery->setConfig('mode.maintenance', true);" --
/srv/www/htdocs/gallery/config.php

#Backup Gallery2

datetime=`date '+%y-%m-%d-%A-%H-%M-%S'`
echo datetime set

mysqldump --opt -Q -uroot -pYourPasswordComesHere gallery2 > /path/to/backupfiles/$datetime.gallery2.sql
echo database gallery2 dumped

#Set gallery back to normal mode:

/usr/bin/replace "\$gallery->setConfig('mode.maintenance', true);"
"\$gallery->setConfig('mode.maintenance', false);" --
/srv/www/htdocs/gallery/config.php



The above works for me, make your own modifications.
I hope I didn't make too many mistakes while editing it here on the
fly...

Vahis
--
Training new things here:
http://waxborg.servepics.com
"The only thing more expensive than training is the lack of it"
Henry Ford

On Mon, 14 Apr 2008 13:53:47 GMT, "Ana C. Dent"
<anacedent@hotmail.com> wrote:

>Those who live by the GUI, die by he GUI.
>
>use mysqldump instead

I tried that technique, but I don't think it's going to work for me.

I found mysqldump, which turned out to be a utility in MySQL's own
private environment rather than an application that I can put directly
in a batch file.

I configured the MySQL monitor (which I otherwise have had no reason
to use).

I found documentation which explains how to run mysqldump, but which
requires me to dig out details of several technical things that I've
not encountered before. (When is an option preceded by a double
hyphen? What does '-p' followed by no password mean?)

Then I'll have to figure out how to run the monitor from a script, and
then how to run mysqldump from a script inside the monitor.

This is supposed to SAVE me time, not consume it.

I'd love to become an expert user of MySQL, but I don't have that
luxury. I'm working on a project of which MySQL is a necessary but
incidental part. I need to spend my time on the project.

If this is the only way I can automate a backup, I'll have to continue
doing it through the GUI until I have time to learn all that stuff...
maybe next Christmas.

I hope someone can suggest a simpler way.

Jonathan Sachs wrote:
> I found mysqldump, which turned out to be a utility in MySQL's own
> private environment rather than an application that I can put directly
> in a batch file.

You can put it in an batch, only you have to specify its full path, or
you have to add its path to the %PATH environment variable.

> I found documentation which explains how to run mysqldump, but which
> requires me to dig out details of several technical things that I've
> not encountered before. (When is an option preceded by a double
> hyphen? What does '-p' followed by no password mean?)

It means that it will prompt for the password. If your database is only
accessible with password you need your script to know the password. In
this case you can write

mysqldump database --user=the_username --password=the_password > out.sql

where "the_username" and "the_password" are the actual credential to
access the database and "database" is the name of the database.
"out.sql" is the file where the dump is written to.

> Then I'll have to figure out how to run the monitor from a script, and
> then how to run mysqldump from a script inside the monitor.

I don't know what is the monitor. I suppose you can run mysqldump
directly. Try it first from the command line: "start->exec... cmd"

E.

On 2008-04-18, Jonathan Sachs <js070717@sbcglobal.net> wrote:
> On Mon, 14 Apr 2008 13:53:47 GMT, "Ana C. Dent"
><anacedent@hotmail.com> wrote:
>
>>Those who live by the GUI, die by he GUI.
>>
>>use mysqldump instead
>
> I tried that technique, but I don't think it's going to work for me.
>
> I found mysqldump, which turned out to be a utility in MySQL's own
> private environment rather than an application that I can put directly
> in a batch file.
>
> I configured the MySQL monitor (which I otherwise have had no reason
> to use).
>
> I found documentation which explains how to run mysqldump, but which
> requires me to dig out details of several technical things that I've
> not encountered before. (When is an option preceded by a double
> hyphen? What does '-p' followed by no password mean?)
>
> Then I'll have to figure out how to run the monitor from a script, and
> then how to run mysqldump from a script inside the monitor.
>
> This is supposed to SAVE me time, not consume it.
>
> I'd love to become an expert user of MySQL, but I don't have that
> luxury. I'm working on a project of which MySQL is a necessary but
> incidental part. I need to spend my time on the project.
>
> If this is the only way I can automate a backup, I'll have to continue
> doing it through the GUI until I have time to learn all that stuff...
> maybe next Christmas.
>
> I hope someone can suggest a simpler way.

I did already. I posted a shell script that can be run in cron.
You can edit that script to your needs.
Maybe you didn't follow. OK.

There was stuff you don't need, (gallery2 part) so here's just the
backup command:

mysqldump --opt -Q -uroot -pYourPasswordComesHere nameofyourdatabase >
/path/to/backupfiles/nameofyourdatabase.sql

Put everything in one line and run that in cron if you want automation.

In my earlier post there was datetime for making a new backup file each
time, see my earlier post. The command above overwrites the backup file
each time.

See that post and come back with the parts you don't understand.

And here's yet the restore command:

mysql -uroot -pYourPasswordHere yourdatabase < /path/to/backupfile.sql

The commands here are run as database root. You may run them as any
other user that has privileges.

Vahis
--
Training new things here:
http://waxborg.servepics.com
"The only thing more expensive than training is the lack of it"
Henry Ford

On Fri, 18 Apr 2008 16:01:15 GMT, Vahis <waxborg@gmail.com.invalid>
wrote:

>> I hope someone can suggest a simpler way.
>
>I did already. I posted a shell script that can be run in cron.

In fact, I didn't read any further than "shell script." Will the same
command work in a Windows batch file? If so, it should do what I
need.

When I looked the documentation for this stuff I was very tired, and I
forgot that MySQL documentation is entirely Unix-centric. I saw
examples that begin with the "shell>" prompt, and they only registered
as "this ain't a real command." Sorry about the confusion.