[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    RE: the best way to back up...
From:       Van <vanboers () server ! dedserius ! com>
Date:       1999-07-30 21:27:52
[Download RAW message or body]

James,
This is getting pretty involved, so, I'd like to bring the group back in
on this discussion.


On Fri, 30 Jul 1999, James wrote:

> Van,
> 
> We (Chad and I) are giving this a shot but we haven't found much in the 
> way of docs (except for the small amount that came with it from
> OpenLink) for iODBC.  Chad is downloading MyODBC right now.  Can you
> outline the procedure for us?
1.	On UNIX/Linux, download and build iODBC;
2.	On NT, download and install the binary MyODBC driver;
3.	On NT, open control panels ODBC and create a new MySQL DSN to the
UNIX/Linux box;
4.	In MSSQL, create the link to that database;
5.	Test it out with some queries;
6.	Create whatever queries and stored procedures you wish to use;
7.	Use this DSN in the web-based queries in your www-server;
8.	Set up some replication procedures to back up the MySQL database
at reasonable intervals;

That should do it.

> 
> If you are curious, we are outlining these possibilities for this project:
> 
> 1. SQLServer for transactions and MySQL for queries and reference
> lookups.  We could use your methods to do something like this (in 
> theory).  Client (browser) to Web server (Apache) to file (via perl).
One way to do it; should work;

> From there NT via Samba uses the file to update both SQLServer
> (transaction processing) and, upon the successful completion of the
> transaction, MySQL gets updated.  Lets see if I can draw a picture and
> we will fill in the blanks as we go.  Browser --> Apache(mod-perl) -->
> file --> through Samba --> NT --> ActivePerl --> ODBC --> SqlServer -->
> MyODBC --> Linux --> iODBC --> MySql.  I think this is how you outlined
> some of what you do.  Did you mean there is a way for SQLServer to use
> ODBC to connect to other databases directly or are you using some kind
> of front end (VB or perl) to update both?
Browser --> Apache(mod-perl or mod-php) --> MySQL; </done>
(Harder, but, faster than lightning)
--or--
Browser --> NT WWW/IIS --> ODBC --> MySQL; </done>
(Easier, but, overhead on may cause performance loss under heavy volume)

Samba?  Not unless you want to just back up the /usr/local/var directory.
I think Samba on a MySQL server with that directory as a share is a bad
idea.  I'd favor leaving the dbase alone and keeping the nmb and smb ports
closed (139).
I think the following would be better;
MSSQL --> ODBC --> MySQL --> replicate dbase --> backup to tape;

> 
> 2. MySQL for everything.  This would mean writing our own transaction 
> system.  I have thought of something along these lines and I think it
> would be fun but, well you know, it always gets to be more than you
> thought at first.  Browser --> Apache(mod-perl) --> file(T1) (this has 
> all the sql code for the entire transaction.  E.g. 'insert 'parent';
> insert 'child(ren)'').  The program responsible for executing these
> transactions would move the files from the T1 directory to the T2
> directory when the transaction is successfully completed.  T2 holds 
> all transactions completed since the last backup.  When a backup is
> performed, T2 files are moved to T3.  If a transaction fails it will be
> moved to the E1 directory if the trans programs detected the error.  If
> not it will still be in the T1 directory.  Cleanup programs have another
> shot to spot problems either way.  Some imagination needs to be applied
> here for the different ways the transaction could fail.  For instance,
> if the entire server was lost the last backup could be restored and
> rolled forward if T1 and T2 were intact etc., etc.
I'm discussing this possibility with someone right now (NT IIS guru).  It
does sound fun, but, I'm waiting for him to take a crack at it.
Basically, it's a home-rolled way to implement stored procedures in MysQL
by storing the procedures in a dbase and using some perl scripts to parse
this out to make the actual query.  For me, too much work at this point.
I think you're looking at the same dilemma.
Redundancy is great, but I favor implementing a mechanism with the fewest
points of failure possible.
So, if you don't mind a little work, the mysql --> perl/php route may be
the quickest way to put the application together if you're good at
perl/php.  Otherwise, put the dbase on the MySQL, hook via ODBC from the
SQL server and use the built-in query wizards that make NT a little easier
in the middles-ware arena.  Downsides to this are performance issues and
potential incompatibility problems due to MSSQL not really adhearing to
the ANSI SQL 92 standard in many functions that you'll undoubtedly need.
But, you may be able to get around them.

Sounds fun.  Wish I had more time to dink with it, but, I have a VFoxPro
to Access to MySQL project to finish by Monday.  >:(  Work, work, work.
Regards,
Van

> 
> (2) seems the most interesting but (1) is probably more practical.  With (1) the \
> load on the transaction server is kept to a minimum because all the lookups, etc. \
> that would need to be done to build a complete transaction would be hitting MySQL \
> so only a fully prepared transaction would hit SQLServer.  If MySQL is fast enough \
> to build temp tables we could run even the queries needing sub-queries \
> (sub-selects) there.  
> Anyway, let me know what you think and, if there is anything you can send that will \
> even help us get, say, Access talking to MySQL, it would be greatly appreciated. 
> -----Original Message-----
> From:	Van [SMTP:vanboers@server.dedserius.com]
> Sent:	Thursday, July 29, 1999 4:46 PM
> To:	James
> Subject:	Re: the best way to back up...
> 
> James wrote:
> > 
> > Woe there big guy, I think your are on to something but I totally missed this \
> > somehow.  Can you outline for me exactly how to do this or point me to the right \
> > documentation?  Frankly I am not sure I have a clear mental picture of what you \
> > are doing but it sounds very interesting. 
> James,
> Check out MyODBC for the NT (a client app) and, iODBC on the UNIX side
> that allows WinXX clients to attach to the MySQL server via a DSN.  This
> allows them to use any MS application that is ODBC wise to attach to the
> database on MySQL.  The code for all this is on the tcx.se site.  
> 
> Today I performed the operation on 3 servers:  my workstation running
> RedHat 5.2 at a client site over a 100 base T LAN, a web server running
> Slackware over a 56K analog modem, and another Slackware box on a T-1. 
> I created MySQL DSN entries for each of these boxes using the MyODBC for
> Windows driver and was able to look at MySQL data using both Access97,
> and Excel97 (I created a little chart to check web stats).
> 
> It's very simple.  So, what I'm suggesting is to have MSSQL attach to
> the MySQL database via this ODBC connection and  creating queries in the
> SQL environment, and take advantage of stored procedures, and triggers,
> and, house the data on the MySQL UNIX machine.  Unfortunately, I will
> confess that the ODBC layer offers a little overhead, but, you may be
> able to tweak it a little bit for speed.
> 
> You can also replicate the database using some scripts on your MSSQL
> server daily, or whatever to provide a little redundancy.  That make any
> sense?
> Van
> -- 
> =========================================================================
> Linux rocks!!!   http://www.dedserius.com
> =========================================================================
> 



---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread8930@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail mysql-unsubscribe@lists.mysql.com instead.


[prev in list] [next in list] [prev in thread] [next in thread]