Hello,

I looking for some guidance, terminology for a project I have been
made responsible for. I want to ensure I know what is needed prior to
hiring it out.

We have a database driven website from the mother house. Sadly, we
cannot play with it directly in any manner. As such, we want to link,
synchronize, replicate (I don't know the proper term) with the
database in read-only and build another site on another host. Thus
enabling us to do whatever we'd like and allowing us to create new
functionality/reporting tools....


The questions are:
1. Can this type of setup be achieved?
2. What is this called (synchronization, replication, something else)
3. Can it be done in real-time or will it be periodic updates

Any advise, readings, resources are greatly appreciated!

QB

>I looking for some guidance, terminology for a project I have been
>made responsible for. I want to ensure I know what is needed prior to
>hiring it out.
>
>We have a database driven website from the mother house. Sadly, we
>cannot play with it directly in any manner. As such, we want to link,

You need to clarify this. What mechanisms are available to transfer
data? Can you log into the main database as a replication slave?
Can you ftp files? FedEx USB drives?

>synchronize, replicate (I don't know the proper term) with the
>database in read-only and build another site on another host. Thus
>enabling us to do whatever we'd like and allowing us to create new
>functionality/reporting tools....

Replication is real-time. However, it may require more access (such
as exposing port 3306 of the master to the world, even if access
is locked down by IP and password or SSL certificate) than you can
have.

There's also such things as doing a mysqldump on the master, say,
once an hour, transferring the files somehow (even if by US Postal
Service) and re-loading it on the slave.

>
>
>The questions are:
>1. Can this type of setup be achieved?
>2. What is this called (synchronization, replication, something else)
>3. Can it be done in real-time or will it be periodic updates
>
>Any advise, readings, resources are greatly appreciated!
>
>QB

On Dec 15, 3:28 pm, gordonb.v6...@burditt.org (Gordon Burditt) wrote:
> >I looking for some guidance, terminology for a project I have been
> >made responsible for. I want to ensure I know what is needed prior to
> >hiring it out.
>
> >We have a database driven website from the mother house. Sadly, we
> >cannot play with it directly in any manner. As such, we want to link,
>
> You need to clarify this. What mechanisms are available to transfer
> data? Can you log into the main database as a replication slave?
> Can you ftp files? FedEx USB drives?
>
> >synchronize, replicate (I don't know the proper term) with the
> >database in read-only and build another site on another host. Thus
> >enabling us to do whatever we'd like and allowing us to create new
> >functionality/reporting tools....
>
> Replication is real-time. However, it may require more access (such
> as exposing port 3306 of the master to the world, even if access
> is locked down by IP and password or SSL certificate) than you can
> have.
>
> There's also such things as doing a mysqldump on the master, say,
> once an hour, transferring the files somehow (even if by US Postal
> Service) and re-loading it on the slave.
>
>
>
>
>
> >The questions are:
> >1. Can this type of setup be achieved?
> >2. What is this called (synchronization, replication, something else)
> >3. Can it be done in real-time or will it be periodic updates
>
> >Any advise, readings, resources are greatly appreciated!
>
> >QB- Hide quoted text -
>
> - Show quoted text -

We are at the point of trying to figure it out. So limitation have
not been established yet. We will have to request access and see what
we are told. So Replication should be our 1st request?

We are looking to a method that can be completely automated, so no
usb, cd.... but rather a dump perhaps with an automated upload or
replication....

I am truly at the very beginning of this process. What question
should I be asking of the main webmaster to establish what can be
done?

Thank you!

>> >I looking for some guidance, terminology for a project I have been
>> >made responsible for. I want to ensure I know what is needed prior to
>> >hiring it out.
>>
>> >We have a database driven website from the mother house. Sadly, we
>> >cannot play with it directly in any manner. As such, we want to link,
>>
>> You need to clarify this. What mechanisms are available to transfer
>> data? Can you log into the main database as a replication slave?
>> Can you ftp files? FedEx USB drives?
>>
>> >synchronize, replicate (I don't know the proper term) with the
>> >database in read-only and build another site on another host. Thus
>> >enabling us to do whatever we'd like and allowing us to create new
>> >functionality/reporting tools....
>>
>> Replication is real-time. However, it may require more access (such
>> as exposing port 3306 of the master to the world, even if access
>> is locked down by IP and password or SSL certificate) than you can
>> have.
>>
>> There's also such things as doing a mysqldump on the master, say,
>> once an hour, transferring the files somehow (even if by US Postal
>> Service) and re-loading it on the slave.
>>
>>
>>
>>
>>
>> >The questions are:
>> >1. Can this type of setup be achieved?
>> >2. What is this called (synchronization, replication, something else)
>> >3. Can it be done in real-time or will it be periodic updates
>>
>> >Any advise, readings, resources are greatly appreciated!
>>
>> >QB- Hide quoted text -
>>
>> - Show quoted text -
>
>We are at the point of trying to figure it out. So limitation have
>not been established yet. We will have to request access and see what
>we are told. So Replication should be our 1st request?

Replication gives you the most up-to-date data. The changes may
propagate in fractions of a second. If the network goes down, it
will catch up when it reconnects. However, it requires MySQL-to-MySQL
access (usually on port 3306), and an account on the master server
to allow this. Is this over the Internet at large? You may want
to look up SSL encryption over the connection.


>We are looking to a method that can be completely automated, so no
>usb, cd.... but rather a dump perhaps with an automated upload or
>replication....

The next best involves dumping the database on the master end (which
locks it, so you might do this daily at an off-hour), transferring
the file, and loading it into the slave. A cron job on the master
could create the dump file and put it in a non-anonymous ftp directory
(encrypted?). A cron job on the slave, timed to be well after the
master finishes, could fetch the file with ftp, decrypt it, and
load it into the database with the command-line utility 'mysql'.
Or, you could let one end drive the whole process if one end can do
remote execution on the other end.
(For example, on the master:
mysqldump blah, blah, blah ... | ssh slave.domain.com mysql blah, blah, blah

Advantages: No outside access to master. Encryption is done.
Disadvantages: Piping mysqldump direct output over the net may increase the
time the database is locked. (alternative: dump into a file, then send the
file over the net)
)

>I am truly at the very beginning of this process. What question
>should I be asking of the main webmaster to establish what can be
>done?

First off, find out if exporting the data off the main site is even
acceptable *AT ALL*, or your project is dead.

On Dec 15, 5:51 pm, gordonb.ii...@burditt.org (Gordon Burditt) wrote:
> >> >I looking for some guidance, terminology for a project I have been
> >> >made responsible for. I want to ensure I know what is needed prior to
> >> >hiring it out.
>
> >> >We have a database driven website from the mother house. Sadly, we
> >> >cannot play with it directly in any manner. As such, we want to link,
>
> >> You need to clarify this. What mechanisms are available to transfer
> >> data? Can you log into the main database as a replication slave?
> >> Can you ftp files? FedEx USB drives?
>
> >> >synchronize, replicate (I don't know the proper term) with the
> >> >database in read-only and build another site on another host. Thus
> >> >enabling us to do whatever we'd like and allowing us to create new
> >> >functionality/reporting tools....
>
> >> Replication is real-time. However, it may require more access (such
> >> as exposing port 3306 of the master to the world, even if access
> >> is locked down by IP and password or SSL certificate) than you can
> >> have.
>
> >> There's also such things as doing a mysqldump on the master, say,
> >> once an hour, transferring the files somehow (even if by US Postal
> >> Service) and re-loading it on the slave.
>
> >> >The questions are:
> >> >1. Can this type of setup be achieved?
> >> >2. What is this called (synchronization, replication, something else)
> >> >3. Can it be done in real-time or will it be periodic updates
>
> >> >Any advise, readings, resources are greatly appreciated!
>
> >> >QB- Hide quoted text -
>
> >> - Show quoted text -
>
> >We are at the point of trying to figure it out. So limitation have
> >not been established yet. We will have to request access and see what
> >we are told. So Replication should be our 1st request?
>
> Replication gives you the most up-to-date data. The changes may
> propagate in fractions of a second. If the network goes down, it
> will catch up when it reconnects. However, it requires MySQL-to-MySQL
> access (usually on port 3306), and an account on the master server
> to allow this. Is this over the Internet at large? You may want
> to look up SSL encryption over the connection.
>
> >We are looking to a method that can be completely automated, so no
> >usb, cd.... but rather a dump perhaps with an automated upload or
> >replication....
>
> The next best involves dumping the database on the master end (which
> locks it, so you might do this daily at an off-hour), transferring
> the file, and loading it into the slave. A cron job on the master
> could create the dump file and put it in a non-anonymous ftp directory
> (encrypted?). A cron job on the slave, timed to be well after the
> master finishes, could fetch the file with ftp, decrypt it, and
> load it into the database with the command-line utility 'mysql'.
> Or, you could let one end drive the whole process if one end can do
> remote execution on the other end.
> (For example, on the master:
> mysqldump blah, blah, blah ... | ssh slave.domain.com mysql blah, blah, blah
>
> Advantages: No outside access to master. Encryption is done.
> Disadvantages: Piping mysqldump direct output over the net may increase the
> time the database is locked. (alternative: dump into a file, then send the
> file over the net)
> )
>
> >I am truly at the very beginning of this process. What question
> >should I be asking of the main webmaster to establish what can be
> >done?
>
> First off, find out if exporting the data off the main site is even
> acceptable *AT ALL*, or your project is dead. - Hide quoted text -
>
> - Show quoted text -

A couple more questions came to mind!

If I find out that the main db is an Oracle db (or something other
than MySQL). What can be done? Replication?Dumps?....

Where can I start to do some basic reading on SSL encryption for
replication over the net between 2 MySQL dbs?

Thank you!

<question.boy@hotmail.com> schreef in bericht
news:f3cba806-e420-4e7a-8c17-70f3b8210da5@j20g2000hsi.googlegroups.com...
> On Dec 15, 5:51 pm, gordonb.ii...@burditt.org (Gordon Burditt) wrote:
>> >> >I looking for some guidance, terminology for a project I have been
>> >> >made responsible for. I want to ensure I know what is needed prior
>> >> >to
>> >> >hiring it out.
>>
>> >> >We have a database driven website from the mother house. Sadly, we
>> >> >cannot play with it directly in any manner. As such, we want to
>> >> >link,
>>
>> >> You need to clarify this. What mechanisms are available to transfer
>> >> data? Can you log into the main database as a replication slave?
>> >> Can you ftp files? FedEx USB drives?
>>
>> >> >synchronize, replicate (I don't know the proper term) with the
>> >> >database in read-only and build another site on another host. Thus
>> >> >enabling us to do whatever we'd like and allowing us to create new
>> >> >functionality/reporting tools....
>>
>> >> Replication is real-time. However, it may require more access (such
>> >> as exposing port 3306 of the master to the world, even if access
>> >> is locked down by IP and password or SSL certificate) than you can
>> >> have.
>>
>> >> There's also such things as doing a mysqldump on the master, say,
>> >> once an hour, transferring the files somehow (even if by US Postal
>> >> Service) and re-loading it on the slave.
>>
>> >> >The questions are:
>> >> >1. Can this type of setup be achieved?
>> >> >2. What is this called (synchronization, replication, something else)
>> >> >3. Can it be done in real-time or will it be periodic updates
>>
>> >> >Any advise, readings, resources are greatly appreciated!
>>
>> >> >QB- Hide quoted text -
>>
>> >> - Show quoted text -
>>
>> >We are at the point of trying to figure it out. So limitation have
>> >not been established yet. We will have to request access and see what
>> >we are told. So Replication should be our 1st request?
>>
>> Replication gives you the most up-to-date data. The changes may
>> propagate in fractions of a second. If the network goes down, it
>> will catch up when it reconnects. However, it requires MySQL-to-MySQL
>> access (usually on port 3306), and an account on the master server
>> to allow this. Is this over the Internet at large? You may want
>> to look up SSL encryption over the connection.
>>
>> >We are looking to a method that can be completely automated, so no
>> >usb, cd.... but rather a dump perhaps with an automated upload or
>> >replication....
>>
>> The next best involves dumping the database on the master end (which
>> locks it, so you might do this daily at an off-hour), transferring
>> the file, and loading it into the slave. A cron job on the master
>> could create the dump file and put it in a non-anonymous ftp directory
>> (encrypted?). A cron job on the slave, timed to be well after the
>> master finishes, could fetch the file with ftp, decrypt it, and
>> load it into the database with the command-line utility 'mysql'.
>> Or, you could let one end drive the whole process if one end can do
>> remote execution on the other end.
>> (For example, on the master:
>> mysqldump blah, blah, blah ... | ssh slave.domain.com mysql blah,
>> blah, blah
>>
>> Advantages: No outside access to master. Encryption is done.
>> Disadvantages: Piping mysqldump direct output over the net may increase
>> the
>> time the database is locked. (alternative: dump into a file, then send
>> the
>> file over the net)
>> )
>>
>> >I am truly at the very beginning of this process. What question
>> >should I be asking of the main webmaster to establish what can be
>> >done?
>>
>> First off, find out if exporting the data off the main site is even
>> acceptable *AT ALL*, or your project is dead. - Hide quoted text -
>>
>> - Show quoted text -
>
> A couple more questions came to mind!
>
> If I find out that the main db is an Oracle db (or something other
> than MySQL). What can be done? Replication?Dumps?....
>
> Where can I start to do some basic reading on SSL encryption for
> replication over the net between 2 MySQL dbs?
>
> Thank you!


you can find any info on MySQL on http://dev.mysql.com/

documentation (version 5.0) :
http://dev.mysql.com/doc/refman/5.0/en/index.html

documentation on replication:
http://dev.mysql.com/doc/refman/5.0/en/replication.html
(or the 1st link on:
http://dev.mysql.com/doc/mysql/searc...x.html&lang=en )

happy reading!

>If I find out that the main db is an Oracle db (or something other
>than MySQL). What can be done? Replication?Dumps?....

You cannot replicate from Oracle to MySQL in the sense that MySQL
uses that term.

For any kind of SQL database where you know the schema, you should
be able to run a query which produces as its output SQL queries
(e.g. INSERT statements) which can later be run by MySQL to load
data into it.

This is essentially what mysqldump does, but it uses MySQL-specific
queries to get lists of table names and databases, table schemas,
column names, etc., and it uses MySQL-specific means to access the
database as there really isn't a generic means of running a SQL
query. (Things like ODBC or JDBC come close, but then there's the
problem of getting whatever-database-it-is ODBC driver.) mysqldump
is pretty much stuck with using MySQL-specific queries so it can
re-create MySQL-specific features of the schema (and you'll note
that it has options to make the output acceptable to different
versions of MySQL).

For example:

SELECT
CONCAT('INSERT INTO table1 VALUES(\'', id, '\', \'', logintime,
'\');')
FROM table1;

which might produce a bunch of statements like:

INSERT INTO table1 VALUES('55', '2007-01-03');
INSERT INTO table1 VALUES('86', '2007-06-13');

Now, for general strings, you have to think about quoting in case
there are single quotes in the string values. I cheated here and
selected an example where integers and dates don't have characters
that need quoting in them.

mysqldump also can produce SQL to create the tables on the destination
server. You need to do that manually or generate those commands
also.

>Where can I start to do some basic reading on SSL encryption for
>replication over the net between 2 MySQL dbs?

Start in the MySQL manual for SSL between a server and a client.
(The slave server *is* a MySQL client that logs in to the master
server.)

== Quote from question.boy (question.boy@hotmail.com)'s article
> On Dec 15, 5:51 pm, gordonb.ii...@burditt.org (Gordon Burditt) wrote:
> > >> >I looking for some guidance, terminology for a project I have been
> > >> >made responsible for. I want to ensure I know what is needed prior to
> > >> >hiring it out.
> >
> > >> >We have a database driven website from the mother house. Sadly, we
> > >> >cannot play with it directly in any manner. As such, we want to link,
> >
> > >> You need to clarify this. What mechanisms are available to transfer
> > >> data? Can you log into the main database as a replication slave?
> > >> Can you ftp files? FedEx USB drives?
> >
> > >> >synchronize, replicate (I don't know the proper term) with the
> > >> >database in read-only and build another site on another host. Thus
> > >> >enabling us to do whatever we'd like and allowing us to create new
> > >> >functionality/reporting tools....
> >
> > >> Replication is real-time. However, it may require more access (such
> > >> as exposing port 3306 of the master to the world, even if access
> > >> is locked down by IP and password or SSL certificate) than you can
> > >> have.
> >
> > >> There's also such things as doing a mysqldump on the master, say,
> > >> once an hour, transferring the files somehow (even if by US Postal
> > >> Service) and re-loading it on the slave.
> >
> > >> >The questions are:
> > >> >1. Can this type of setup be achieved?
> > >> >2. What is this called (synchronization, replication, something else)
> > >> >3. Can it be done in real-time or will it be periodic updates
> >
> > >> >Any advise, readings, resources are greatly appreciated!
> >
> > >> >QB- Hide quoted text -
> >
> > >> - Show quoted text -
> >
> > >We are at the point of trying to figure it out. So limitation have
> > >not been established yet. We will have to request access and see what
> > >we are told. So Replication should be our 1st request?
> >
> > Replication gives you the most up-to-date data. The changes may
> > propagate in fractions of a second. If the network goes down, it
> > will catch up when it reconnects. However, it requires MySQL-to-MySQL
> > access (usually on port 3306), and an account on the master server
> > to allow this. Is this over the Internet at large? You may want
> > to look up SSL encryption over the connection.
> >
> > >We are looking to a method that can be completely automated, so no
> > >usb, cd.... but rather a dump perhaps with an automated upload or
> > >replication....
> >
> > The next best involves dumping the database on the master end (which
> > locks it, so you might do this daily at an off-hour), transferring
> > the file, and loading it into the slave. A cron job on the master
> > could create the dump file and put it in a non-anonymous ftp directory
> > (encrypted?). A cron job on the slave, timed to be well after the
> > master finishes, could fetch the file with ftp, decrypt it, and
> > load it into the database with the command-line utility 'mysql'.
> > Or, you could let one end drive the whole process if one end can do
> > remote execution on the other end.
> > (For example, on the master:
> > mysqldump blah, blah, blah ... | ssh slave.domain.com mysql blah,
blah, blah
> >
> > Advantages: No outside access to master. Encryption is done.
> > Disadvantages: Piping mysqldump direct output over the net may increase the
> > time the database is locked. (alternative: dump into a file, then send the
> > file over the net)
> > )
> >
> > >I am truly at the very beginning of this process. What question
> > >should I be asking of the main webmaster to establish what can be
> > >done?
> >
> > First off, find out if exporting the data off the main site is even
> > acceptable *AT ALL*, or your project is dead. - Hide quoted text -
> >
> > - Show quoted text -
> A couple more questions came to mind!
> If I find out that the main db is an Oracle db (or something other
> than MySQL). What can be done? Replication?Dumps?....
> Where can I start to do some basic reading on SSL encryption for
> replication over the net between 2 MySQL dbs?
> Thank you!

actually, you can use a windoz application to sync between an oracle and mysql
dataqbases. use sqlyog to do this. it's a little hard to set it up right but after
the initial setup, you can put it in the event scheduler and let it run at
whatever intervals you'd like it to run.

for ssl, there's a bunch of material both on the web and on mysql's website.
--
POST BY: lark with PHP News Reader ;o)