Solved

mySQL backup all databases in own dump

Posted on 2011-03-18
11
568 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 500 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
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, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

631 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