Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

mySQL backup all databases in own dump

Posted on 2011-03-18
11
Medium Priority
?
569 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 11

Expert Comment

by:Pieter Jordaan
ID: 35163976
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
ID: 35164919
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
ID: 35171226
-- 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:axega
ID: 35190640
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
ID: 35192856
try without redirection: mysqldump -u root -pYOUPASS you_database_name


0
 

Author Comment

by:axega
ID: 35196430
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
ID: 35198186
SHOW DATABASES +above > .bat-file ? ;)
0
 

Author Comment

by:axega
ID: 35230436
Any other solution statement?

Thanks very much for any help!
0
 
LVL 2

Accepted Solution

by:
ASta earned 2000 total points
ID: 35230915
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
ID: 36903275
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
ID: 36912575
All good. I hope you find something helpful and solve problem. :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 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