Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

mySQL backup all databases in own dump

Posted on 2011-03-18
11
Medium Priority
?
570 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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…
Suggested Courses

971 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