Hello,

I am evaluating mySQL as an alternative to SQL Server...Other guy wants to
go SQL Server, I kinda like mySQL...

What steps can be taken for mySQL so that I can be guaranteed to be ;
a) ideally corruption free
b) 100% safe if corruption does occur

Can anyone recommend any transaction logging tools, hot backup tools. Should
I be worried about mySQL's atomicity? Im still fairly new to databases...I
come from msaccess background...

I have read a fair bit on it but most information I find is dated pre 2004,
I see less people bagging out mySQL after that date....but still a few
things concern me...things i cant articulate at this moment

Thank you
John Sheppard

John Sheppard wrote:
> Hello,
>
> I am evaluating mySQL as an alternative to SQL Server...Other guy wants
> to go SQL Server, I kinda like mySQL...
>
> What steps can be taken for mySQL so that I can be guaranteed to be ;
> a) ideally corruption free
> b) 100% safe if corruption does occur
>
> Can anyone recommend any transaction logging tools, hot backup tools.
> Should I be worried about mySQL's atomicity? Im still fairly new to
> databases...I come from msaccess background...
>
> I have read a fair bit on it but most information I find is dated pre
> 2004, I see less people bagging out mySQL after that date....but still a
> few things concern me...things i cant articulate at this moment
>
> Thank you
> John Sheppard

given the choice of MySQL, SQLServer or Oracle Express(10g)for ANYTHING
production where I bet my business on the data, I would rank them OE,
SQLServer, MySQL. MySQL is good, but as a long time Database
consultant, would not bet my bottom line on it. It can be a good
"front-end" database where you upload data to a real database engine
frequently. OE and MySQL run on something other than Windows - like
Linux, which only slightly more secure.

Bottom line would be the use - and only you can decide whether or not to
chance losing your data. We all have our preferences, but it comes down
to risk - usually $$$$. SQLServer ain't cheap. PostgreSQL is also a
good alternative.

I have never been a fan of the methods used for backing up MySQL. On my
OpenVMS server, I shut down the database and backup all the data
files. I actually restored just the data files from one server on
another and restarted the database(s) without a single hiccup. My
system backup tool does an "image" backup of the database and since
MySQL is down at the time, and I have no doubt about recovery.

With InnoDB storage engine, I never had any problem. I never had to
repair any database. Even with a dirty shutdown (server disconnected)
when changing database schema (which is VERY slow on InnoDB).

There's one good and simple way to make hot backup. You connect and a
MySQL slave server to your master server or an other master server to
your master (in this case you must specify an increment of 2 and a
different offset for each server). Then you shutdown it, save data, and
resume replication when your have finished. It works perfectly fine.
Replication can be resumed using replication binary logs.

You can also use "InnoDB hot backup" - http://www.innodb.com/hot-backup/

I made some tests, if you rsync table while the database is still
running, and you open it on an other host, it always work fine. That's
not a sure way to backup data (I gave you one), but it always worked
when I tried it. I use it to make frequent (but not a 100% sure) backups.

Transactions work perfectly good. I even made a bogus program (by
mistake) where I was in a big transaction that wasn't always closed. And
I could see it running for hours pretty fine and when it closed it,
sometimes, nothing was saved on the database. The transaction must have
been 6 hours and made 4k queries, and was rollbacked or commited without
noticing any performance problem.

You should look as InnoDB storage engine features and different user
feedbacks on MySQL forums. For me it's the perfect database, you never
have to repair anything, it just always works. I have some tables with 4
millions of rows stored and never had any problem.
* Features : http://www.innodb.com/innodb/features/
* official "Feedbacks" : http://www.innodb.com/innodb/users/ - The most
interesting is PayCom I think. Because it speaks about transaction and
money.

But there some little weird things with it, like the fact that the
database files always grow. It you delete the whole table, the database
file won't be smaller. There will be just more space free to be used inside.
You also cannot determine quickly the number of rows in the database, it
seems to count everything each time you ask him (but usually, you put
this information in a cache table).

More generally, InnoDB isn't really efficient for frequent modifications
(lots of DELETE and UPDATE), but it is for frequent insertions (INSERT
INTO). It has a smart way of managing index reconstruction.

MyISAM is pretty crappy. It is faster and consummes less memory, but it
can fail and it does it as soon as it can.

Unless you have very specific needs, I don't see how MsSQL or Oracle
will be better. Maybe because MsSQL and Oracle have a perfect Visual
Studio integration with LinQ/C#3.5 but MySQL should have it sooner or later.

Florent

In article <43hvj.11867$J41.2093@newssvr14.news.prodigy.net >,
Michael Austin <maustin@firstdbasource.com> wrote:
> given the choice of MySQL, SQLServer or Oracle Express(10g)for ANYTHING
> production where I bet my business on the data, I would rank them OE,
> SQLServer, MySQL. MySQL is good, but as a long time Database
> consultant, would not bet my bottom line on it. It can be a good
> "front-end" database where you upload data to a real database engine
> frequently. OE and MySQL run on something other than Windows - like
> Linux, which only slightly more secure.

MySQL seems to be doing pretty good as as the database for YouTube,
Flickr, and Digg. People have successfully bet some pretty big bottom
lines on it.

--
--Tim Smith

> given the choice of MySQL, SQLServer or Oracle Express(10g)for ANYTHING
> production where I bet my business on the data, I would rank them OE,
> SQLServer, MySQL.

Funny. I never worked with OE, but I'd definitely reverse the last two.
Just try to restore a few backups with SQL Server and see how it treats
your valuable business data.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Hi John,

>
> I am evaluating mySQL as an alternative to SQL Server...Other guy wants to
> go SQL Server, I kinda like mySQL...

Everybody has a prefered horse in the stable.
Their opinion is very subjective.


> What steps can be taken for mySQL so that I can be guaranteed to be ;
> a) ideally corruption free

mysql innodb is really enterprise level stable

> b) 100% safe if corruption does occur

use a hardware raid

Make regular backups like every professional does.
>
> Can anyone recommend any transaction logging tools, hot backup tools. Should
> I be worried about mySQL's atomicity? Im still fairly new to databases...I
> come from msaccess background...
>
Don't worry. MS is building playstations, mysql is building databases

kind regards,

Toni

--
Posen fuer Anfaenger: http://www.w-klch.med.uni-muenchen.de/dischner

A. Dischner schreef:
> Don't worry. MS is building playstations, mysql is building databases
>

playstations are build by another company ;-)

--
Luuk