HOWTO: Automatically backup MySQL (On Windows)

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
sdm
New user
New user
Posts: 3
Joined: 2007-05-09 21:31

HOWTO: Automatically backup MySQL (On Windows)

Post by sdm » 2007-05-09 21:46

This how-to describes a backupprocedure which automatically stops the hmailserver-service.
So it's only suitable if you're running hMailServer in a home-environment or company and that you're sure that when you're running this script nobody's logged on!


Requirements:

- PSService (downloadable from http://www.microsoft.com/technet/sysint ... rvice.mspx)
- mysqldump (I got mine from another MySQL-installation)

Put PSService.exe in a map that's in your path.
Put mysqldump.exe in <root_to_hmailserver>\mysql\bin

Make the following batchfile: backup_mysql.bat
with the following content

=============================
@ECHO OFF
echo Stopping hmailserver
psservice.exe stop hmailserver
echo hmailserver stopped

echo Running dump...
"C:\Program Files\hMailServer\MySQL\Bin\mysqldump" --add-drop-table --port=3307 -uroot -pPASSWORD --result-file="f:\backup\mysql\backup.%DATE:~0,3%.sql" hmailserver
echo Done!

echo starting hmailserver
psservice.exe start hmailserver
echo hmailserver started
=============================

Change the paths (red) in the script.

You can recover the rootpassword (blue) for MySQL as follows:

1. Run the script hMailServer/Addons/Utilities/DecryptBlowfish.vbs.
2. Enter your main hMailServer Administrator password. This is the password you specified during installation. Click OK.
3. Open up hMailserver.ini, located in your Windows directory or hMailServer bin directory.
4. Copy the password from hMailServer.ini to the "Enter encrypted password" dialog.
5. Click OK in DecryptBlowfish to view your password

Change the portnumber (orange) if you don't use the internal MySQL-server.

This batchfile does the following:
1. Stops the hmailserver-service (so no new mails will be downloaded, and nobody can login.) (if anybody is logged on, their connection will be dropped, so be carefull)
2. Creates a backup.Mon.sql. (assuming it's Monday). The advantage (or disadvantage, depending on your needs) of using the week day is that you will eventually have only seven backup files... you can go back, at most, seven days. Eventually each day will be overwritten.
3. Starts the hmailserver-service.

The only thing you should do now, is schedule this batchfile to automatically backup your MySQL-database.

I hope this helpes.

User avatar
danny6167
Senior user
Senior user
Posts: 472
Joined: 2007-02-07 15:24
Location: Western Australia
Contact:

Post by danny6167 » 2007-05-10 04:31

Code: Select all

psservice.exe stop hmailserver
Is there a reason we cant use the built in functionality of windows

Code: Select all

NET STOP hmailserver
will also stop the server and requires no extra programs

sdm
New user
New user
Posts: 3
Joined: 2007-05-09 21:31

Post by sdm » 2007-05-14 18:59

I forgot about it.

Yes, of course it's a possibility.

neilkilbride
New user
New user
Posts: 10
Joined: 2008-05-13 16:25

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by neilkilbride » 2008-05-13 16:28

I know this post is old but I was wondering why you have to stop the hMailServer service?

Is this to avoid loss in the integrity of the data?

...Just thought I would ask as I don't really like the thought of my mail server offline :S

sdm
New user
New user
Posts: 3
Joined: 2007-05-09 21:31

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by sdm » 2008-05-13 17:52

If you don't stop the service, it will continue polling for new mail.

Because the backup of the files and the db probably won't run exactly simultaneous, it's better to stop it.

In this way you're sure that the files and the db match.

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by ^DooM^ » 2008-05-13 19:55

If you have a small installation then you can pretty safely run without stopping the server but for larger busy servers it is always better to stop your server. Incoming messages should then be handled by your backup mail server if you have one else sending servers will just retry later on.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

neilkilbride
New user
New user
Posts: 10
Joined: 2008-05-13 16:25

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by neilkilbride » 2008-05-13 21:02

Thanks for your thoughts.

Probably best to stop it I suppose. My DB is only small anyway so it performs the backup in seconds.

Is this method of backup better than the built in backup functionality (in hMailServer admin options). Currently I do both - perhaps I'm being too cautious?!

andipfaff
New user
New user
Posts: 3
Joined: 2008-07-07 10:16

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by andipfaff » 2008-07-07 11:35

You should never shut down the mail server. Especially a busy one!
MySQL database server keeps track of data written to the database and read from it for backup. So it should never happen that you have inconsitencies.

Of cause this depends on how multiple data sets -which should be consitent- is being written to different tables. If tts is used correctly there will be no problem with it. As long as tts is not closed again the data will not be backed up.

Olek
New user
New user
Posts: 1
Joined: 2016-07-13 12:06

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by Olek » 2016-07-13 12:11

Nice script, thanks! But you can use this tiny tool MySQLBackupFTP (http://mysqlbackupftp.com/) for making scheduled MySQL database backups.

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by percepts » 2016-07-13 12:25

it amazes me that since windows has been around for several decades that people always resort to third party solution instead of writing a very simple bat file which can either execute the mysqldbexport utility or simply just copy the mysql hmail data folder to another location and trigger it with windows own task scheduler.

User avatar
jimimaseye
Moderator
Moderator
Posts: 8118
Joined: 2011-09-08 17:48

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by jimimaseye » 2016-07-13 12:31

I agree. In fairness, the original HOW TO was written in 2007 and this guy us coming on to advertise some 3rd party nonsense 9 years later. I cant see why anyone would need this nonsense if they are windows administrators of a MySQL database. They would already know windows task scheduler, mysql administration and be aware of how to stop and start HMS service.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

qvex
New user
New user
Posts: 1
Joined: 2016-07-27 15:27

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by qvex » 2016-07-27 16:39

sdm wrote:This how-to describes a backupprocedure which automatically stops the hmailserver-service.
So it's only suitable if you're running hMailServer in a home-environment or company and that you're sure that when you're running this script nobody's logged on!
What is meant by "nobody's logged on" ?
1) Nobody is logged at the hMailserver Administrator or
2) Nobody is logged on with a mail client ?

In case of 2): why would this be important? Because
a) User are interrupted in their work sending and receiving mail (i wouldn't care) or
b) mail clients logged on tho the server when stopping the server could mess things up?

Thanks in Advance
Dirk

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by percepts » 2016-07-27 17:07

In the com api you will find the "Application" Class

this class has two methods that you can use, Start and Stop.

Start and Stop use slightly misleading naming as they are really Pause and Resume.

They provide the functionality for the Pause/Resume button in the admin settings gui Status panel. They don't start and stop the hmailserver windows service by loading and unloading to memory. They just pause and resume processing. So be clear on the differences between windows service stop/start and hmailserver application.stop/start.

I have no idea what just stopping the hMailserver service in the windows services panel will do in terms of completing current message delivery so for peace of mind it is probably best to issue a com api application.stop which will pause hmail processing message deliveries at a sensible point (no half finished deliveries but there may still be messages in the message queue waiting to be delivered) and blocks all user access and also stops any external deliveries to hmailserver.

It is then safe to do your backup. You can also stop the windows hmailserver service which takes it out of memory but isn't actually required to be done if you have issued an application.stop (pause) using the com api.

When backup is finished you can start hmailserver service (if it was stopped) and issue a Com Api Application.Start

That is how it should be done (IMO) leaving no possibility of a partially processed mail being corrupted in the subsequent backup but I don't know enough about hmailserver internals to know if stopping windows service issues a pause before stopping and unloading itself.

backup should always inlude both the database and the data folder which should be in sync and need to be kept in sync. i.e. you can't just restore the data folder or just the db and expect it to be in sync with the other. They should always be backed up/restored as a matching pair.

If users can't login or process mail during backup then tough. But this is why we usually schedule backup to run in the middle of the night.

User avatar
mattg
Moderator
Moderator
Posts: 20104
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by mattg » 2016-07-28 01:09

To be honest, I don't do either when I backup.

My backup script robocopys the data directory, copies the ini file, the eventhandlers.vbs and ./PHPWebAdmin/include_versioncheck.php (which tells me the version of hMailserver) all to a known directory then does a live MySQLdump and then 7zips them into backups.

(I actually zip each domain separately to make it easier to get a single message out of the zip later).

There is a possibility that the database has some messages recorded that aren't in the data folder backup (and that are deleted before next night's backup) but that is unlikely to be anything important at 2:45 am. Most of my overnight mail that is real mail is logs of some description, which are simply backed up the next night. The original log is probably still available if my mail server dies today.
percepts wrote:... about hmailserver internals to know if stopping windows service issues a pause before stopping and unloading itself.
I believe that it does. It seems to unload all client connections and complete current delivery threads.

Also it should be noted that the builtin backup works on much larger data sets now. It is at least 16 GB and may well be unlimited (I've not checked in a while). I don't believe that the builtin backup stops or pauses the server
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by percepts » 2016-07-28 20:04

mattg wrote: Also it should be noted that the builtin backup works on much larger data sets now. It is at least 16 GB and may well be unlimited (I've not checked in a while). I don't believe that the builtin backup stops or pauses the server
I think the server needs to be running for it to work. I was thinking of other backup means. The internal backup utility will resume hmail if it was stopped and then do its stuff. Its potentially dangerous (in a very small way) as you could get backup xml file out of sync with datafolder for a small number of emails. Just depends on whats happening at time of backup. For small setups and backup run at low peak its unlikely. For larger data volumes not run at low peak it would be more likely.

User avatar
jimimaseye
Moderator
Moderator
Posts: 8118
Joined: 2011-09-08 17:48

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by jimimaseye » 2016-07-28 20:11

percepts wrote: The internal backup utility will resume hmail if it was stopped and then do its stuff.
If you PAUSE hms (in the 'Server' tab) first, then run the INTERNAL BACKUP, it all works fine and the server remains on PAUSE. A manual RESUME is required. (Of course, if the windows SERVICE is stopped then nothing will work and the internal backup will not be available - least alone because you wont get direct access to the Admin screen in the first place).
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by percepts » 2016-07-28 20:17

jimimaseye wrote:
percepts wrote: The internal backup utility will resume hmail if it was stopped and then do its stuff.
If you PAUSE hms (in the 'Server' tab) first, then run the INTERNAL BACKUP, it all works fine and the server remains on PAUSE. A manual RESUME is required. (Of course, if the windows SERVICE is stopped then nothing will work and the internal backup will not be available - least alone because you wont get direct access to the Admin screen in the first place).
Bollocks. If you pause it the button will say resume. If you then run backup and then go back you will find the button already says says Pause which means the backup resumed it already. Which is what I said. So you can't "manually" resume it from the status panel after internal backup becasue its already running.

User avatar
jimimaseye
Moderator
Moderator
Posts: 8118
Joined: 2011-09-08 17:48

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by jimimaseye » 2016-07-28 20:19

Not on my 2 systems, it doesnt. (Its as I said above).
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by percepts » 2016-07-28 20:44

your system must be screwed then. Did it actually do the backup

User avatar
jimimaseye
Moderator
Moderator
Posts: 8118
Joined: 2011-09-08 17:48

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by jimimaseye » 2016-07-28 20:52

percepts wrote:your system must be screwed then.
Yeah, both of them. 2 different countries, 2 different versions on 2 different operating systems. What are the chances, ai. (As if. :roll: )

[quote]
2016-07-28 20:18:58.337 Loading backup settings....
2016-07-28 20:18:58.340 Backing up domains...
2016-07-28 20:18:58.358 Backing up account user1@jim.com...
2016-07-28 20:18:58.459 Backing up account user2@jim.com...
2016-07-28 20:18:58.469 Backing up account user3@jim.com...
2016-07-28 20:18:58.477 Backing up account user4@jim.com...
2016-07-28 20:18:58.493 Backing up data directory...
2016-07-28 20:18:58.874 Backing up settings...
2016-07-28 20:18:58.927 Writing XML file...
2016-07-28 20:18:58.974 Compressing message files...
2016-07-28 20:18:59.472 Backup completed successfully[/quote]


And after manually stopping the server first before calling the backup, no evidence of the backup starting it before commencing:
[quote]
"APPLICATION" 15584 "2016-07-28 20:18:54.126" "Servers stopped." <----- manual stop
.
.
"DEBUG" 15520 "2016-07-28 20:18:58.333" "BackupManager::StartBackup()"
"DEBUG" 15520 "2016-07-28 20:18:58.334" "Adding task BackupTask to work queue Maintenance queue"
"DEBUG" 15520 "2016-07-28 20:18:58.335" "BackupManager::~StartBackup() - E3"
"DEBUG" 1920 "2016-07-28 20:18:58.335" "Executing task BackupTask in work queue Maintenance queue"
"DEBUG" 1920 "2016-07-28 20:18:58.352" "Reading messages from database."
"DEBUG" 1920 "2016-07-28 20:18:58.436" "Reading messages from database."
"DEBUG" 1920 "2016-07-28 20:18:58.441" "Reading messages from database."
"DEBUG" 1920 "2016-07-28 20:18:58.448" "Reading messages from database."
"DEBUG" 1920 "2016-07-28 20:18:58.465" "Reading messages from database."
"DEBUG" 1920 "2016-07-28 20:18:58.897" "Reading messages from database."
"DEBUG" 1920 "2016-07-28 20:18:59.471" "BackupManager::OnBackupCompleted()"
"DEBUG" 1920 "2016-07-28 20:18:59.472" "BackupManager::~OnBackupCompleted()"
"APPLICATION" 11400 "2016-07-28 20:50:23.692" "Starting servers..." <----- manual restart[/quote]

(not surprised either, as that is what the code says)

So Bollocks it might be, but at least its working correctly bollocks.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by percepts » 2016-07-28 22:30

my setup Resumes automatically after the backup completes. So it backup actually runs with the system paused which isn't what I thought.
Why yours doesn't auto restart I have no idea. You can fix the code since you have it.

User avatar
jimimaseye
Moderator
Moderator
Posts: 8118
Joined: 2011-09-08 17:48

Re: HOWTO: Automatically backup MySQL (On Windows)

Post by jimimaseye » 2016-07-28 22:37

If one has chosen to PAUSE the system at some point, why would one then want it automatically restarting just because the backup has been run? It might be against the purpose of initially wanting it paused in the first place (going on to cause problems). For example, the admin may have paused it earlier in the day for some sort of maintenance and to prevent external downloads from coming in during that time. Meantime, the scheduled/scripted internal backup kicks in and then restarts his server afterwards and ruins the integrity of his system/work.

As it is at the moment, though, the control remains completely with the administrator leaving him to decide whether he wants the system to be, or remain, paused or not (he can always click RESUME himself, or via a script if that is how he is launching the backup in the first place). ie, the initiating method (whether it be manually or by script) will leave the system in the state it was before backup was started.

I suspect this is the reasoning that the code is the way it is and consequently isnt broken. And if it isnt broken, there is nothing to fix.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

Post Reply