Mysql and the dbmail 2.x

On Tue, 29 Nov 2005, M. J. [Mike] OBrien wrote:

> 1) Yes to DBMail 2.07 (not 2.x for production!!!)
> 2) Yes to InnoDB (for sure)

My question/issue/source of discomfort is the move from
MyISAM to 
InnoDB.  Is there a quick/easy HOW-TO on this?  I read
through the 
docs on Mysql's website, but just don't feel comfortable
doing it 
without someone "watching", to give me a hand to
hold onto if 
needed.

> 3) http://www.dbma.ca
DBMailAdministrator replaces all your PHP user 
> management/

The PHP involves much more than just dbmail.  It is my
provisioning 
system for new users, my ticket tracking mechanism, my
radius 
provisioning system, etc.  I have looked at DBMA, but it is
not 
going to do all that I need to do.

> 4) Don't mess with the running system. Consider
building a parallel system; 
> migrating your existing accounts and mail and then when
fully satisfied, roll 
> out the new system. You need to time the events so that
you can at the time of 
> final mail migration, freeze the database against
writes while the MTA 
> stores/queues mail. As you failover to the new system,
you push out the new 
> database into production; and have the MTA flush and
forward then resume 
> normal mail via dbmail-lmtp / dbmail-smtp (whatever
transports you use) to the 
> new DBMail 2.0.7 system.

This is a good idea.  I will certainly do it this way.

>5) Make it easier... Why not have everything on the same
server? Is 
>your MTA really that busy? You can then use your other
for WebMail, 
>Admin, MX2 etc.

Well, it's the way it is, because my virus/spam scanning
happens on 
the machine that handles the smtp as well as (currently) the
dbmail 
daemons.  This machine is an older machine, and the
combination of a 
busy mysql server (there are other things using the mysql
server) 
and the spam/virus scanning is pretty CPU intensive.

-- 
Butch Evans
BPS Networks  http://www.bpsnetworks.co
m/
Bernie, MO
Mikrotik Certified Consultant
(http://www.m
ikrotik.com/consultants.html)
_______________________________________________
Dbmail mailing list
Dbmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Butch Evans wrote:

> My question/issue/source of discomfort is the move from
MyISAM to
> InnoDB.  Is there a quick/easy HOW-TO on this?  I read
through the docs
> on Mysql's website, but just don't feel comfortable
doing it without
> someone "watching", to give me a hand to hold
onto if needed.

Butch, depending on the size of your database it is a
totally painful
operation. Especially if you data is in good shape.

So:

- make sure your mysql server has innodb enabled and has
room to spare
for the tablespace (the ibdata files).
- shut down your mta and all dbmail daemons.
- run dbmail-maintenance with all checks and fixes enabled.
- dump your data into a backupfile
- load your data into a test database (dbmailtest)

convert your tables in the test database:

--------------<cut>-----------------
# convert dbmail-myisam to dbmail-innodb (1.2 version)
#
alter table aliases type=innodb;
alter table mailboxes type=innodb;
alter table messageblks type=innodb;
alter table messages type=innodb;
alter table users type=innodb;

alter table mailboxes add foreign key (owner_idnr)
references
users(user_idnr) on delete cascade on update cascade;
alter table messages add foreign key (mailbox_idnr)
references
mailboxes(mailbox_idnr) on delete cascade on update cascade;
alter table messageblks add foreign key (message_idnr)
references
messages(message_idnr) on delete cascade on update cascade;
--------------<cut>-----------------

and if that all works, fire this conversion sql at your
production
database, restart the daemons and your mta.

et voila!


-- 
 
____________________________________________________________
____
  Paul Stevens                                      paul at
nfg.nl
  NET FACILITIES GROUP                     GPG/PGP:
1024D/11F8CD31
  The Netherlands________________________________http://www.nfg.nl
_______________________________________________
Dbmail mailing list
Dbmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

On Wed, 30 Nov 2005, Paul J Stevens wrote:

>Butch, depending on the size of your database it is a
totally 
>painful operation. Especially if you data is in good
shape.

Hope you meant "painless".   The
database is ~1GB.  Any ideas on 
how much time to convert this?

> - make sure your mysql server has innodb enabled and
has room to spare
> for the tablespace (the ibdata files).
> - shut down your mta and all dbmail daemons.
> - run dbmail-maintenance with all checks and fixes
enabled.
> - dump your data into a backupfile
> - load your data into a test database (dbmailtest)

The rest of this looks much less painless than I thought it
would be 
reading the docs.  InnoDB vs MyISAM won't be a problem with
the 
earlier version of dbmail?

-- 
Butch Evans
BPS Networks  http://www.bpsnetworks.co
m/
Bernie, MO
Mikrotik Certified Consultant
(http://www.m
ikrotik.com/consultants.html)
_______________________________________________
Dbmail mailing list
Dbmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Butch Evans wrote:
> On Wed, 30 Nov 2005, Paul J Stevens wrote:
> 
>> Butch, depending on the size of your database it is
a totally painful
>> operation. Especially if you data is in good shape.
> 
> 
> Hope you meant "painless".   The
database is ~1GB.  Any ideas on how
> much time to convert this?

No pain . Depends
on your hardward. dual xeon p4, 1gb ram, scsi disks would
probably take about 10 minutes or less.


> The rest of this looks much less painless than I
thought it would be
> reading the docs.  InnoDB vs MyISAM won't be a problem
with the earlier
> version of dbmail?
> 

Dbmail-1.2 or 2.0 doesn't care one way or the other.

-- 
 
____________________________________________________________
____
  Paul Stevens                                 
mailto:paulnfg.nl
  NET FACILITIES GROUP                     PGP: finger
paulnfg.nl
  The Netherlands________________________________http://www.nfg.nl
_______________________________________________
Dbmail mailing list
Dbmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail