Solved

MySQL DUMP

Posted on 2014-04-04
23
419 Views
Last Modified: 2014-04-14
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?

thanks,
Aleks
0
Comment
Question by:APD_Toronto
[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
  • 9
  • 7
  • 5
  • +1
23 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39979906
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 ...
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 39980066
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.
0
 
LVL 79

Expert Comment

by:arnold
ID: 39980386
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.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:APD_Toronto
ID: 39983681
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

0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 39984145
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.
0
 

Author Comment

by:APD_Toronto
ID: 39986386
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?
0
 
LVL 79

Expert Comment

by:arnold
ID: 39986446
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.
0
 

Author Comment

by:APD_Toronto
ID: 39986467
so, how can I dump individual dbs, but without changing my scripts every time I add/drop a db?
0
 
LVL 79

Expert Comment

by:arnold
ID: 39986502
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.
0
 

Author Comment

by:APD_Toronto
ID: 39987073
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 @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR FOR  
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   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  
 
       FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

Open in new window


Can I do something similar in MySQL?
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 39987200
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: http://dev.mensfeld.pl/2013/04/backup-mysql-dump-all-your-mysql-databases-in-separate-files/
0
 
LVL 79

Expert Comment

by:arnold
ID: 39987249
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)
0
 

Author Comment

by:APD_Toronto
ID: 39987412
routinet, the link to the script you provided, how can it be a part of a batch file?
0
 
LVL 79

Expert Comment

by:arnold
ID: 39987431
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?
0
 

Author Comment

by:APD_Toronto
ID: 39987520
Ok, so how would I do this for Windows?
0
 

Author Comment

by:APD_Toronto
ID: 39987542
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?
0
 
LVL 79

Expert Comment

by:arnold
ID: 39987541
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
0
 

Author Comment

by:APD_Toronto
ID: 39987544
I'm comfortable with vbs, followed by php, and bat

vbs I-s my strongest
0
 
LVL 79

Expert Comment

by:arnold
ID: 39987567
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 .
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 39987845
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 newtover.tumblr.com.
0
 

Author Comment

by:APD_Toronto
ID: 39999454
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" 
"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

0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 40000193
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.
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

615 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