Solved

MySQL DUMP

Posted on 2014-04-04
23
406 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
  • 9
  • 7
  • 5
  • +1
23 Comments
 
LVL 142

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 50

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 76

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
 

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 50

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 76

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 76

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 50

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

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 76

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 76

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 76

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 50

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 50

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now