Hi Experts,

if I have a batch file on a nightly schedule that uses mysqldump with --all-databases, will I eventually run into problems if I have bigger and bigger databases?

APD TorontoAsked:
Who is Participating?
Steve BinkConnect With a Mentor Commented:
mysqldump will only populate the text file in the event it has output, i.e., an error of some kind.  If the file is blank, all is well.  

Alternatively, you can try the --verbose option, but then you'd still have to scan the files for success/failure.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the batch will take longer and longer, and use also more and more space.
other than that, you shall not have any "problems".
you might consider doing a backup per database, and schedule them individually.
depending on how many databases you have, and how fluctuating you create/drop them, you might create a dynamic script that builds up the backup scripts for example on a daily, so you backup only part of the databases every day, and not all ...
Steve BinkCommented:
Another handy tip - pipe the output of mysqldump through `tar -czf some-backup-file.tar.gz`.  That will compress your backups to save more space.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

With the prior comments in hand  what is the concern that you wish to address?

One thing is dealing with backups. Another thing deals with space, yet another item deals with disaster recovery plan, I.e what is your process should this system fail?

Do you want an in time restorability?  Deals with setting up enabling binary logging.
APD TorontoAuthor Commented:
My concern is not with time or space, just a reliable backup if I need to use it.

For example, I have the following line, and in the morning I get a empty text file, I assume everything is ok. Is this the case?

mysqldump --opt --all-databases -r d:\DBs_Backups\mysql_dump.txt -u root -p123   > "C:\Users\Nataliia & aleks\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup\MySQL_Results.txt"

Open in new window

Steve BinkCommented:
Which file is blank?  You are using the -r option, which should write the dump directly to d:\DBs_Backups\mysql_dump.txt.  You are also redirecting the output to "C:\Users\Nataliia & aleks\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup\MySQL_Results.txt".  That file should be blank on a successful dump.
APD TorontoAuthor Commented:
So, If my "C:\Users\Nataliia & aleks\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup\MySQL_Results.txt" is empty, I have nothing else to worry about?
You should always test your backup to make sure your restore will work as you expect it.

Note when you dump all databases into a single file means your backup plan deals with a complete failure.
Dumping each database into its own file provides a quick access should you need to restore one Database.
the dump file is clear text and can have the specific database data extracted.
APD TorontoAuthor Commented:
so, how can I dump individual dbs, but without changing my scripts every time I add/drop a db?
You can not without a small change dealing with first getting the list of the DBs from the server.
Then iterating through the list.

If you have another system, on which you can install mysql, test the restore.
To see what it takes.
APD TorontoAuthor Commented:
so, how would I iterate through my dbs?

For example, in MSSQL I have the following:

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory
SET @path = 'D:\DBs_Backups\'  
-- specify filename format
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  
       FETCH NEXT FROM db_cursor INTO @name   
CLOSE db_cursor   
DEALLOCATE db_cursor

Open in new window

Can I do something similar in MySQL?
Steve BinkCommented:
Not really.  This task would be accomplished through shell scripting.  It should not be very hard to implement - one step to pull the databases, and a loop to backup each one to a separate file.

See here for an example:
yes, you would either use powershell or vbscript to issue the show databases;
you could use batch file as well, but I like the features the other two offer, that ....
which will provide you with a list.

echo "show databases" | mysql -u root -ppassword
assigning the results into an array.
then you would iterate through the array issuing the using a loop
mysldump -u root -ppassword -D <database name from the variable>  > (path to where you want the backup to be and the filename you want)
APD TorontoAuthor Commented:
routinet, the link to the script you provided, how can it be a part of a batch file?
The script routinet provided is a shell script that runs on a Linux/unix platform.

You can use it if you install cygwin (a unix like environment for windows)

The script illustrates the process.

Using show databases it builds a list that can be assigned to a variable.
Then the list is used in a loop to iterate through each element which can be evaluated on whether or not a backup on it is needed. At which point the backup directive is issued with the requisite parameters.

Which windows based scripting languages are you familiar, comfortable with?
APD TorontoAuthor Commented:
Ok, so how would I do this for Windows?
APD TorontoAuthor Commented:
I also understand that I'll need a separate .sql file that:

show databases into dbArray

For each db in dbArray
  mysqldump --opt --all-databases -r d:\DBs_Backups\mysql_%db.txt
End for

...but, what is the translation for this psedo code?
Which scripting languages are you familiar comfortable with?
Set a=echo "show databases" | mysql -u root -p123
For /f %%database in (%%a) do (mysqldump -u root -p123 -D %%database > path_where_you\%%database.sql)

The above is merely a referential non functioning example
APD TorontoAuthor Commented:
I'm comfortable with vbs, followed by php, and bat

vbs I-s my strongest
Vbs using
Define wshell object
Run cmd "echo show databses| mysql -u root -p123 " and assign the value to a variable.

The iterate through the variable to get an element at a time,  

For mysqldump do not use --all-databases --databases databasebname_variable .
Steve BinkCommented:
Well, that's...ummm...yeah...

Try this batch script, totally not copied from some anonymous, unnamed site.  It should do what you are requesting.

This can be run directly in cmd (I wrapped the line but it should not be wrapped):
mysql.exe -uroot -p1234 -s -N -e "SHOW DATABASES" |
  for /F "usebackq" %D in (`findstr /V "information_schema performance_schema"`)
    do mysqldump %D -uroot -p1234 > S:\Backup\MySQL\%D.sql

Open in new window

In a batch file you will need to escape % with an additional %, that is use %%D.

Credit to newtover of
APD TorontoAuthor Commented:
Ok, now I have the following MySQL_backup.bat which works,

:: Creates a backup of all databases in MySQL and puts them in seperate .sql files.

:: Name of the database user
set dbuser=root

:: Password for the database user
set dbpass=123

:: Switch to the data directory to enumerate the folders
pushd "C:\Program Files (x86)\MySQL\MySQL Server 5.0\data"

echo "hello"
:: Loop through the data structure in the data dir to get the database names

FOR /D %%F IN (*) DO (

"mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases %%F > "D:\DBs_Backups\MySQL\%%F.sql"


REM pause

Open in new window

... but my aim is also to do a resulting text file, but If I call the above from a second batch file and amend "> results.txt", I just see my scripts and loops. In MSSQL, however, I'm using something similar and my results show "success" or "fail" for each db. anyway I can do this with MySQL? thoughts?

here is results.txt

D:\DBs_Backups>set dbuser=root 

D:\DBs_Backups>set dbpass=123 

D:\DBs_Backups>pushd "C:\Program Files (x86)\MySQL\MySQL Server 5.0\data" 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>echo "hello" 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>FOR / %F IN (*) DO ("mysqldump.exe" --user=root --password=123 --databases %F  1>"D:\DBs_Backups\MySQL\%F.sql" ) 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>("mysqldump.exe" --user=root --password=123 --databases accounting  1>"D:\DBs_Backups\MySQL\accounting.sql" ) 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>("mysqldump.exe" --user=root --password=123 --databases binary_data  1>"D:\DBs_Backups\MySQL\binary_data.sql" ) 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>("mysqldump.exe" --user=root --password=123 --databases books  1>"D:\DBs_Backups\MySQL\books.sql" ) 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>("mysqldump.exe" --user=root --password=123 --databases ecommerce  1>"D:\DBs_Backups\MySQL\ecommerce.sql" ) 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>("mysqldump.exe" --user=root --password=123 --databases movies  1>"D:\DBs_Backups\MySQL\movies.sql" ) 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>("mysqldump.exe" --user=root --password=123 --databases mysql  1>"D:\DBs_Backups\MySQL\mysql.sql" ) 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>("mysqldump.exe" --user=root --password=123 --databases my_guitar_shop1  1>"D:\DBs_Backups\MySQL\my_guitar_shop1.sql" ) 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>("mysqldump.exe" --user=root --password=123 --databases my_guitar_shop2  1>"D:\DBs_Backups\MySQL\my_guitar_shop2.sql" ) 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>("mysqldump.exe" --user=root --password=123 --databases reservations  1>"D:\DBs_Backups\MySQL\reservations.sql" ) 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>("mysqldump.exe" --user=root --password=123 --databases test  1>"D:\DBs_Backups\MySQL\test.sql" ) 

C:\Program Files (x86)\MySQL\MySQL Server 5.0\data>REM

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.