Moving mySQL database

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
User avatar
PeterK2003
Normal user
Normal user
Posts: 126
Joined: 2005-07-20 17:08
Location: Catawissa, PA

Moving mySQL database

Post by PeterK2003 » 2009-02-20 16:32

i would like to move the hmail mySQL database to my semi-dedicated mySQL server and I have 2 questions.

1) What is the easiest way to do this?

2) in the event that the SQL server is down how will hMail react?


My apologies if this is answered elsewhere.

DeanoX
Senior user
Senior user
Posts: 480
Joined: 2005-11-05 00:07
Location: Michigan

Re: Moving mySQL database

Post by DeanoX » 2009-02-20 17:52

I would shutdown the hmail service, do a mysql backup/dump, then do a restore on the other mysql server, point hmail to that mysql server, and then restart the hmail service. The whole process should only take about 10 minutes.

I am in the process of something similar, and there is a little more to it, but that's the jist of it so far.


Thanks,
-Dean
hMailServer 5.4.2-1964, mysql, ClamAV, SpamAssassin, SquirrelMail, GeoIP.
hMailServer Support Services for US Based Clients.
Low Rates, Quick Service. Send a Private Message for More Information.

User avatar
PeterK2003
Normal user
Normal user
Posts: 126
Joined: 2005-07-20 17:08
Location: Catawissa, PA

Re: Moving mySQL database

Post by PeterK2003 » 2009-02-20 17:57

what needs to be changed in hamil to point it to the new SQL server?

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Moving mySQL database

Post by martin » 2009-02-20 18:38

Run Database Setup and choose to Select a new database server. Then enter the new connection information.

User avatar
PeterK2003
Normal user
Normal user
Posts: 126
Joined: 2005-07-20 17:08
Location: Catawissa, PA

Re: Moving mySQL database

Post by PeterK2003 » 2009-02-20 19:00

ok so my steps are:

1) Shutdown server

2) dump DB

3) restore DB

4) run \hMailServer\Bin\dbsetup.exe

5) restart server


what about my second question?

2) in the event that the SQL server is down how will hMail react?

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Moving mySQL database

Post by martin » 2009-02-20 19:02

hMailServer will try to connect, and after a few attempts it will log an error message to the hMailServer error log in the log directory. You will see the same error message if you try to use hMailServer Administrator after the hMailServer service has failed connecting to the database.

DeanoX
Senior user
Senior user
Posts: 480
Joined: 2005-11-05 00:07
Location: Michigan

Re: Moving mySQL database

Post by DeanoX » 2009-02-20 19:27

Now I have a question. :)

I am pretty much doing the same thing. I am simply replacing the included version of mysql4 port 3307, with a new install of mysql5 port 3306, on the same machine.

The hmailserver service, is currently setup with a dependency of hmailservermysql.

How can I change that dependency to mysql?

Can I simply run the .vbs script to set service dependency, to change it?


Thanks,
-Dean
hMailServer 5.4.2-1964, mysql, ClamAV, SpamAssassin, SquirrelMail, GeoIP.
hMailServer Support Services for US Based Clients.
Low Rates, Quick Service. Send a Private Message for More Information.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Moving mySQL database

Post by martin » 2009-02-20 20:00

Yes, assuming that the name of the MySQL service is "MySQL" because that's what exists in the mysql-external-reg file if I'm not wrong.

(In hMailServer 5, the database setup lets you set service dependency)

DeanoX
Senior user
Senior user
Posts: 480
Joined: 2005-11-05 00:07
Location: Michigan

Re: Moving mySQL database

Post by DeanoX » 2009-02-20 20:36

Martin,

Thanks, and yes, the mysql-external-reg file, in hex, converts to RPCSS MySQL

I will run the database setup program.

Welcome back. :)


Thanks,
-Dean
hMailServer 5.4.2-1964, mysql, ClamAV, SpamAssassin, SquirrelMail, GeoIP.
hMailServer Support Services for US Based Clients.
Low Rates, Quick Service. Send a Private Message for More Information.

Post Reply