473,372 Members | 805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

sheduled mysqldump via windows bat file

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?
Mar 22 '06 #1
6 30050
"Robert Blackwell" <me@email.com> 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.
Mar 22 '06 #2
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.
Mar 22 '06 #3
"Robert Blackwell" <me@email.com> 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.
Mar 25 '06 #4
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.
Mar 27 '06 #5
"Robert Blackwell" <me@email.com> 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.
Mar 28 '06 #6
Everything seems to hummin a long just fine now. Thanks a lot!
Mar 28 '06 #7

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

Similar topics

4
by: Mangina | last post by:
Hello all. Can anyone see why this snippet of code isnt dumping the databases using mysqldump ? All the credentials are correct (changed for the purpose of this post). $host = "localhost";...
3
by: bobb | last post by:
Here's the syntax, I've never been able to do this. I'm SURE it'll help someone.!!! mysqldump -hhost -uuser -ppassword -c -t -wfield=value (don't forget quotes if string) database table >...
0
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...
7
by: Adam Smith | last post by:
Hope this is the right news group!! I did a server upgrade and at the same time did a mysql update from 4.0.12 to Ver 12.22 Distrib 4.0.16 mach1# mysql --version mysql Ver 12.22 Distrib...
0
by: mcstayinskool | last post by:
I'm trying to dump a bugzilla installation to a file, then restore it to a different database name (so as to test my db backup system). I use mysqldump to dump it to a file: # mysqldump -u root...
1
by: Dave Crypto | last post by:
Hi there, SUMMARY: 1) When I use phpadmin to export a large database, the file created on my local PC never containes all the tables and information. It seems to max out at about 10mb. 2)...
1
by: Greg.Harabedian | last post by:
I'll start off by saying I am using MySQL v4.0 and my question is...how do I get mysqldump to dump the actual binary values store in a blob? Here is an example: -- Create a test table create...
6
by: Antoni | last post by:
Hello, I'm trying to make a daily database backup. When executing "mysqldump - uxxx -pxxx database /home/backup/ddbb.sql -q &" the httpd server gets blocked and my site is "offline" for 30...
7
by: damezumari | last post by:
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 =...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.