Solved

mySQL backup all databases in own dump

Posted on 2011-03-18
11
562 Views
Last Modified: 2012-05-11
Hy everybody

We use a mySQL Server on a Windows Server 2008 with 1 application DB and the normal system DBs. Now I want to run a script every night, which dump every single DB in a separate File.sql

I designed a script. But my Know How about scripting is not so advanced. And the script fails.

First I write all DB information in a separate file:

mysqlshow -u root -p "password" > c:\temp\test1.txt
PAUSE

The information in the new file are something like that:

+--------------------+
|     Databases  |
+--------------------+
| information_schema |
| cdcol              |
| app_db               |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+

now I want to use a script which ignores the characters "+|-" maybe like that?
{
for %singleDB% IN c:\temp\test1.txt do
  if NOT %singleDB%:~0,1% ==  "|" && NOT %singleDB%:~0,1% == "+" && NOT %singleDB% == "Databases" then
                mysqldump -u root -p "password" %singleDB% > c:\TEMP\%singleDB%.txt
}

Thank you for your help

0
Comment
Question by:axega
  • 5
  • 5
11 Comments
 
LVL 11

Expert Comment

by:Pieter Jordaan
Comment Utility
Hi

Running MySQL on Windows is a bad idea.

Yes, it works, but Windows will never have the scripting and automation features of UNIX.
Even if they get to the point where it can work like UNIX, you will still struggle with viruses and patches, and reboot constantly.

It will take a lot of patching up to get it to almost work like it does on UNIX.

Save yourself the trouble, and install it on nix.

Ubuntu server is easy enough to configure.
0
 

Author Comment

by:axega
Comment Utility
Hi Bitfreeze

I know there are a few drawbacks to run a mySQL solution on a Microsoft OS. Butt in this environment, there are no possibilities to use it on a UNIX platform.
0
 
LVL 2

Expert Comment

by:ASta
Comment Utility
-- Backup.bat ----
mysqldump -u root -p "password" <db_name> > c:\%Backup_path%\<db_name>_<yyyy_mm_dd>.sql
zip ... <db_name>_<yyyy_mm_dd>.sql
------

Add line to backup.bat when create new db.
0
 

Author Comment

by:axega
Comment Utility
Hy A Sta

Thanks for your script, but it don't works in my environment. When I starting the first part of the Script:
mysqldump -u root -p "password" <db_name> > c:\%Backup_path%\<db_name>_<yyyy_mm_dd>.sql

the following warning occurs:  
> was unexpected at this time.

Thanks for any help.
0
 
LVL 2

Expert Comment

by:ASta
Comment Utility
try without redirection: mysqldump -u root -pYOUPASS you_database_name


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:axega
Comment Utility
A Sta sorry, this is not what i want to do...

"I need a Script which dump every single DB in a separate File.sql"
0
 
LVL 2

Expert Comment

by:ASta
Comment Utility
SHOW DATABASES +above > .bat-file ? ;)
0
 

Author Comment

by:axega
Comment Utility
Any other solution statement?

Thanks very much for any help!
0
 
LVL 2

Accepted Solution

by:
ASta earned 500 total points
Comment Utility
New other solution: "mysqldump --help" and read about key  "--all-databases" :)

May be command like "mysqldump.exe --user=root --password=<You_pass> --all-databases >dmp.txt" help you?

0
 

Author Closing Comment

by:axega
Comment Utility
sorry I wasn't able to close this earlier since I was away... but really appreciated the help!
0
 
LVL 2

Expert Comment

by:ASta
Comment Utility
All good. I hope you find something helpful and solve problem. :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now