Help with daily backup script

Robert_Blackwel

So, you want to automate a daily backup of one of your databases. No problem. Follow these instructions and you can automate a mysql dump with a convenient time stamp.

Create a .bat file with this text in it.

REM @ 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 yourusername -p"yourpassword" yourdatabase >"C:\youroutputdirectory\"%DATE_DAY%%DATE_TIME%yourdatabase.sql

The path to your MySQL folder might be different so you need to check that and change it appropriately.
And obviously, replace where I typed yourusename and yourpassword and yourdatabase with your real information.
The user running the script requires Lock Tables rights.

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

If you dont want to grant lock tables on all databases change . to your data base name

Now, in the windows control panel double click Scheduled Tasks and add a new task. When it asks which program to run, click choose and navigate to the folder with your batch file and click open. Then create a name for the task you are scheduling. I called mine database backup. I selected to run the task daily. You then have to confirm your user login and password. I checked the advanced box when clicking finish so I could have the backup run multiple times each day. Once that window closes out you'll have the properties window of your task open. Click the Schedul tab. From there, check the box that says repeat task. From there you select in what intervals you want it to run and this is all up to you. So, for example you can set it to run every 4 hours. The duration of the task has to be longer then the intervals. So for duration you can just put 4 hours 1 minute. Now, unless you have a gigantic database this script should only a few minutes. Click okay and you're all done.

Thanks everyone for the helped on this forum and others.

P.S. This post is meant to help those who have remote desktop access to their windows server.

gardnc

Schedule the following as a batch script. This will need a liitle wiork for paths, etc.

REM @ 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
mysqldump -u whomever -p'whatever' database > %DATE_DAY%%DATE_TIME%database.sql

Robert_Blackwel

So, make a .bat out of that and just add the directory after the greater than sign?
mysqldump -u whomever -p'whatever' database > c:\backups\DATE_DAY%%DATE_TIME%database.sql

gardnc

Yup, just be sure all the paths work, including the one to mysqlsump.

Robert_Blackwel

Its not working for me, here's a screenshot of the command window when it runs.
http://www.wowcentral.com/random/batfile.gif

gardnc

I don't see the > in front of the path and backup database name

.........\Robbie \My Documents\backup db's ...........

and you need to quote the path since it has spaces and special characters.

Robert_Blackwel

It doesn't seem to be working still even with the quotes or me changing the path to c:. It will create a .sql file in the directories I've tried but the dump isn't actually working and the naming scheme isn't working either; it just creates DATE_DAYDATE_TIME_database.sql

The final line in the command looks like this
c:>mysqldump -u user -p'password' dbname 1>C:\DATE_DAYDATE_TIME_database.sql

I dont think its that I'm being too impatient either, I've tried giving it a minute or so refreshing the directory but the file stays at 0kB

I've also tried changing the script to
C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqldump.exe as I assume that's what you are trying to have me run. But when I do, the command window pops up for just s econd and then it goes away. It creates a .sql but its empy and the naming scheme didn't work.

gardnc

You need to use the variable name (with the %) and can't use C: as part of the output path.

Try this:

C:
CD THEPATHWHEREYOUFINDMYSQLDUMP
mysqldump -u user -p'password' dbname > %DATE_DAY%%DATE_TIME%database.sql

If this doesn't get it post the FULL path to mysqldump, FULL path to the backup folder and the name of the database to backup and I'll give you the correct syntax.

Robert_Blackwel

I've tried

C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqldump.exe -u user -p'pass' mydb >C:\DATE_DAY%%DATE_TIME%database.sql

cd C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqldump.exe -u user -p'pass' mydb >C:\DATE_DAY%%DATE_TIME%database.sql

Bothopen and close the command prompt really fast but the dump isn't working.

Robert_Blackwel

Well, here's the latest update.

REM @ 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 "password"
wowc >"C:\Documents and Settings\Administrator\My Documents\backup db's\"%DATE_DAY%%DATE_TIME%database.sql

It produces a .sql file with the appropriate naming scheme but the file is empty.

gardnc

Keep running and modifying

C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqldump" -u user -p "password"
wowc

in a command prompt until you get out put in the screen. There is a problem with that part of the code. -p should be followed directly with the password - no spaces and SINGLE quotes, i.e.

-u user -p'password'

Larry

Robert_Blackwel

I've tried with single quotes and without quotes at all...when I dont use single quotes or quotes, the command prompt asks me for a password. After typing my password some stuff happens too quickly for me to tell and the screen goes away but its not yet output a file that wasn't empty.

I tried without a space betweein -p"password" and that seemed to work SORT of, this is what it dumped

MySQL dump 10.9
--
-- Host: localhost Database: mydb
-- ------------------------------------------------------
-- 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 /;

So, I modified the bat and added cd:\ just so the command prompt wouldn't go away after it finished and here's what it says.

mysqldump: Got error: 1044: Access denied for user 'user'@'%' to database 'mydb' 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.

gardnc

What is mydb? Did you create it or is it a functioning system? I hate to advise you on your data if I don't know anything about it. Can you manipulate in in phpmyadmin?

I really can't answer about why the mysql is doing that. Start with a known quantity and get it running first then tackel the more complex job.

The key to debugging this is to:

A) create a new database and a simple table with a couple of records,
😎 run a mysqldump in the command prompt with whatever solution works re password,
C) look for output resembling (my data not yours):

CREATE TABLE usertypes (
usertypecode char(1) NOT NULL default 'U',
usertypedescription varchar(22) NOT NULL default 'List User',
PRIMARY KEY (usertypecode)
) TYPE=MyISAM COMMENT='Control Table for User Types';

/Data for the table usertypes /

insert into usertypes (usertypecode,usertypedescription) values ('U','List User');
insert into usertypes (usertypecode,usertypedescription) values ('W','Webmaster');
insert into usertypes (usertypecode,usertypedescription) values ('y','Youth Director');

D) once you are getting a true dump of the data use that exact code in the batch file,

E) then try substituting the name of the failing database and resolve those issues.

This backup method works as long as the SQL part of it is functioning, and you need to actually see that work with a simple test.

Robert_Blackwel

mydb is just a substite name for my real db. Just being extra cautious/paranoid.

Anyways, I tried using different credentials. I tried using root 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. The dump happend to be 46.5MB. The dump 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 zup 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.