sheduled mysqldump via windows bat file

[Robert Blackwell]

I want to make a scheduled task in windows to do a mysqldump. Someone gave
me this .bat to run but I'm not able to get it to work.

REM @echo off

for /f "tokens=1" %%i in ('date /t') do set DATE_DOW=%%i

for /f "tokens=2" %%i in ('date /t') do set DATE_DAY=%%i
for /f %%i in ('echo %date_day:/=-%') do set DATE_DAY=%%i
for /f %%i in ('time /t') do set DATE_TIME=%%i
for /f %%i in ('echo %date_time::=-%') do set DATE_TIME=%%i
C:\Program Files\MySQL\MySQL Server 4.1\bin
mysqldump -u user -p'pass' dbname >C:\DATE_DAY%_%DATE_TIME%_database.sql

When I run this script it doesn't work. All I get is a blank .sql file in
the output directory.
Can someone help me with this?

[Bill Karwin]

"Robert Blackwell" <[e-mail]> wrote in message
news:CGiUf.11808$wD1.3719@trnddc02...

C:\Program Files\MySQL\MySQL Server 4.1\bin
mysqldump -u user -p'pass' dbname >C:\DATE_DAY%_%DATE_TIME%_database.sql

I see the following four errors in the above line.

1. Missing a beginning % character before DATE_DAY. Variables should have a
% before and after the variable name.
2. Must enclose the path to the mysqldump program in double-quotes to ensure
it's treated as one argument.
3. Must use double-quotes around "pass", single-quotes don't seem to work
when I try it.
4. Missing a slash between bin and mysqldump.

So this line should be:

"C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqldump" -u user -p"pass"
dbname >C:\%DATE_DAY%_%DATE_TIME%_database.sql

(that's all one line, no linebreak in the middle)

Regards,
Bill K.

[Robert Blackwell]

Okay, I didn't have the slash because I thought the next part being on a
second line would be okay.

I pasted the line you fixed and changed the un/pass obviously. The command
prompt flashed briefly when it ran and it did create a .sql file using the
name formatting, however, it is still an empty file. I've tried waiting a
couple of minutes refreshing now and then but the file does not increase in
size as it would if I was dumping from a program such as mysqlfront.

I'm certain my login information is correct too.

[Bill Karwin]

"Robert Blackwell" <[e-mail]> wrote in message
news:_clUf.8676$I7.3143@trnddc03...

The command prompt flashed briefly when it ran and it did create a .sql
file using the name formatting, however, it is still an empty file.
You should open a "cmd" shell window and run the bat script by typing its
name. Then if it has any error messages in its output, you'll see them. If
you run the bat script by double-clicking the bat file, the command window
disappears too quickly; you may not see any errors it prints.

So do you actually have a database called "dbname" or is that something you
need to change, just like the username/password?
I've tried waiting a couple of minutes refreshing now and then but the
file does not increase in size as it would if I was dumping from a program
such as mysqlfront.

Once you get a prompt back (or the window closes), the bat script is
finished. There will not be any further output going into that file.

Regards,
Bill K.

[Robert Blackwell]

I've been typing a substitue dummy name.
"C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqldump" -u
user-p"mypassword" test >"C:\Documents and Settings\Administrator\My
Documents\backup db's\"%DATE_DAY%_%DATE_TIME%_test.sql

I think what I was missing is the quotes around the password. However, with
that fixed, this is what's dumped.
- MySQL dump 10.9
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 4.1.14-nt

/*!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 */;
/*!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 */;
I was able to keep the command prompt open so I could see what exactly was
goign on and if there was an error at the end, and there was.
mysqldump: Got error: 1044: Access denied for user 'user'@'%' to database
'test' when using LOCK TABLES.
I didn't know I locked the tables... or how to for that matter. I think if
you can walk me through unlocking them the script should hopefully work.

Then I tried using the root user account and it did dump the file... but i'm
not sure if it dumped the whole thing.
I just created a brand new db from a dump that I had made a while ago.
Thedump I imported happend to be 46.5MB. Now, when I ran this batch file the
dump it made is only 21.2MB. Can there be that much fluctuation in a dump?

If I zip up the original dump it compresses to 6.73MB
If I zip the new dump, it compresses to 6.25MB
I'd rather not use the root user in my bat so it would be nice to figure out
what permission/locking is preventing me from dumping with my other users.

[Bill Karwin]

"Robert Blackwell" <[e-mail]> wrote in message
news:kiZVf.5145$VL2.5013@trnddc04...

mysqldump: Got error: 1044: Access denied for user 'user'@'%' to database
'test' when using LOCK TABLES.
I didn't know I locked the tables... or how to for that matter. I think if
you can walk me through unlocking them the script should hopefully work.
Locking tables is a normal part of the work done by mysqldump. The locking
is enabled by the "--lock-tables" option, which is implicit when using the
"--opt" option, which is the default. (How's that for convoluted?)

A user can lock tables only if he/she has been granted the LOCK TABLES
privilege. Apparently, the user you're using to do the mysqldump lacks this
privilege, if you're seeing an Access Denied error.
Then I tried using the root user account and it did dump the file...
The root user is has all privileges, so it won't get the Access Denied
error.
I just created a brand new db from a dump that I had made a while ago.
Thedump I imported happend to be 46.5MB. Now, when I ran this batch file
the dump it made is only 21.2MB. Can there be that much fluctuation in a
dump?
Yes, it is possible for the output of two mysqldumps of the same database to
vary greatly in size, depending on the options used when running the
commands (e.g. --complete-insert, --extended-insert, etc.).

But if you want to double-check the databases, restore the dump file to some
new test database, and do some SELECT COUNT(*) queries on the tables.
Compare these to similar queries on your original table. If the results are
different, you know something is wrong. If the results are the same, you
know at least that the same number of records were backed up and restored
(but not necessarily that the content is intact).
I'd rather not use the root user in my bat so it would be nice to figure
out what permission/locking is preventing me from dumping with my other
users.

Yes, you can grant the right privileges as root:

mysql> use mysql;
mysql> GRANT SELECT, LOCK TABLES ON *.* TO 'user'@'%' IDENTIFIED BY
'password';

Regards,
Bill K.

[Robert Blackwell]

Everything seems to hummin a long just fine now. Thanks a lot!