Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

mySQL backup all databases in own dump

Posted on 2011-03-18
11
Medium Priority
?
571 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V 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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

577 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