Backup Database

[skleung]

How can I backup my database?

I just go to mantis/phpmyadmin.php to export the bugtrcaker DB as sql format..is it enough?
So, are all my attachments attached?
And, how about the mantis folder and mysql folder?

[dialexia]

That would depend if the file attachments are uploaded into the database or stored on the hd.

[partymix]

Does anyone have a recommendation / step by step instruction to set up something to automatically back up the database to a diff file location?

I've seen some different things online... but i've attempted them all without success... so any suggestions would be appreciated!

[istvanb]

Actually this is an interesting question since I work on something like that right now.

If you upload your attachments to the database then the proper backup command in your \mysql\bin folder is: mysqldump --user=myusername --password=mypassword --all-databases > MANTIS.SQL

of course if you database is not protected then you wont need a username and a password. At the same time if your database has other thing than Mantis then you should not use the --all-databases switch but you should want to archive only the Bugtracker database.

The restore command is from your \mysql\bin folder: mysql --user=myusername --password=mypassword < MANTIS.SQL

If you store your attachments in a folder then you have to archive that folder as well (pretty much copy the folder to a safe location)


I think this can be done thru the task scheduler of a windows server, but again I am working on this right now. Let me know if you figured out something interesting!

[istvanb]

Our IT is whining about running a script, however they are fine with backing up folders on the server. I think the mantis database can be backed up by copy the \mysql\data\bugtracker folder to a safe location. I am not a database expert but I have found this as an alternative solution on the net and tested manually as well.

[Phileas]

I made a batchfile wich make a SQL Dump described before. This batch runs all night through "planned Tasks" on the Windows Server Maschine.
My IT stores this file (or directory) away on a tape (or similar).
Works fine.

[istvanb]

yeah... that would be the preferred method, if we would have a more helpful IT here

[partymix]

I made a batchfile wich make a SQL Dump described before. This batch runs all night through "planned Tasks" on the Windows Server Maschine.
My IT stores this file (or directory) away on a tape (or similar).
Works fine.

Would you mind sharing the batchfile you used?

[istvanb]

d:\xampp\mysql\bin\mysqldump --user=rootusername --password=rootpassword --all-databases > d:\backups\fixit.sql

[partymix]

It allows me to backup the entire space... but it wont allow me to simply back up my 'bugtracker' database? Anythoughts? There is 100% a database named bugtracker.

C:\webdev\mysql\bin>mysqldump --password=admin --user=admin --databases=bugtracker > C:\temp\backup.sql
Warning: mysqldump: ignoring option '--databases' due to invalid value 'bugtracker'

[istvanb]

C:\webdev\mysql\bin>mysqldump --password=admin --user=admin bugtracker > C:\temp\backup.sql

[partymix]

thanks man! life saver mantis is really cool! I'm diggin it

[partymix]

one last thought- is there anyway to make the logname incorporate datetime stamp are you aware of?

C:\webdev\mysql\bin>mysqldump --password=admin --user=admin bugtracker > C:\temp\backup%date()%.sql

???

[partymix]

Never mind I got it!

For anyone else who wants this.... I figure i'll just set this to run with windows scheduler

file name: backup.bat
---------------------------------------------------------------------------------------
cd C:\webdev\mysql\bin
mysqldump --password=admin --user=admin bugtracker > C:\temp\backup.sql
REN "C:\temp\backup.sql" "* %Date:/= % %Time::=.%.*"
---------------------------------------------------------------------------------------

For example... This creates the file : "backup.sql Thu 11 04 2010 13.53.00.70" located in the C:\temp\ drive.

[Phileas]

You asked for my batchfile. Here is it

Code: Select all

@echo off
set mysqldir="c:\xampp\mysql\bin"
echo Starting Backup of Database: bugtracker

For /f "tokens=1-3 delims=. " %%a in ('date /t') do (set dt=%%c.%%b.%%a)
For /f "tokens=1-4 delims=:." %%a in ('echo %time%') do (set tm=%%a.%%b)
set bkupfilename=bugtracker-%dt%-%tm%.sql
rem echo Backing up to file: %bkupfilename%
%mysqldir%\mysqldump bugtracker -ubackupuser -ppassword > "c:\Backup\%bkupfilename%"
echo Backup Complete!
echo on

So i have a SQL-File with databasename and date-time included.