List:General Discussion« Previous MessageNext Message »
From:David (SST - Adelaide) Logan Date:February 17 2005 2:48am
Subject:RE: Using Different Database Groups On the Same Computer
View as plain text  
You could start a second copy of the same server the way that I
described below. Just start the second copy listening on a different
port. You could use command line overrides to start it up with a
different my.cnf file with the changed parameters. eg. different
datadir, different port etc. This way you don't have to install a second
newer version but can run with the old one.

instance 1 starts with /etc/my.cnf (default) 
instance 2 starts with /etc/my.cnf.dev and listens on port 3307 with a
datadir of /some/where/else/on/the/disk

See chapter 4 of the manual
http://dev.mysql.com/doc/mysql/en/using-mysql-programs.html before I
give you a bum steer 8-) that has all the docs on how to set up the
correct option files you will require

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-----Original Message-----
From: Hal Vaughan [mailto:hal@stripped] 
Sent: Thursday, 17 February 2005 1:10 PM
To: mysql@stripped
Subject: Re: Using Different Database Groups On the Same Computer

On Wednesday 16 February 2005 09:27 pm, Logan, David (SST - Adelaide)
wrote:
> Ooops, sorry for the second time, I forgot to include the list.
>
> Yes, you can install the "normal" version of mysql (in my case 4.0.22)
> and leave everything default. You can then install a new version of
> mysql (4.1.3 or something) and then set up the my.cnf so that it
listens
> on a different tcp port. MySQL uses port 3306 by default but you can
> change this via the my.cnf. I also set up a different startup file in
> the rc3.d directory that starts the second server using different
> parameters passed via the command line.

Okay -- I follow most of this.  Just one other question: Is there a way
to set 
this up with only ONE install of MySQL?  (I'm running a Debian system
and, 
for the sake of updates and stuff, I'm trying to put as little as
possible on 
the system and keep it all so it can be easily updated through 
security.debian.org.)  In other words, could I make it so if I access it

through 3306, it goes to the current version, but port 3307 makes it use
the 
new databases?

> The docs do quite a good job on describing this.
>
> You can then specify this in your DBI call to MySQL like
>
> my $dsn =
> "DBI:mysql:host=$hostname;database=lcscreative;port=$newportnumber";
> my $dbh = DBI->connect($dsn, 'username', 'password', \%err_handle);
>
> You could also use a unix socket rather than tcp by specifying the
> socket name rather than a port number. This is documented in the DBI
> docs at CPAN.
>
> You can do the same thing from PHP, eg. specifying a different port
> number etc. You can do this from the command line by using mysql
> --username username --password --port=3307
--socket=somethingdifferent.
>
> Just make sure that you specify 2 different data directories, 1 for
the
> production or normal version and 1 on a different filesystem or
> whereever for the development version. It will not matter that the
> databases have the same name as they are in different mysql instances.

I figured I'd have to have different directories.  That also makes
backing up 
data easier.  (I'm backing everything up with rsync, so if I specify 
directories that are in the same directory tree the rest of the programs
and 
data are in, I can back up everything with one cron job instead of using

mysqldump AND backing up the file I generate with it.)

> I also use environment variables set in the apache config to ensure
> separation. I have 2 servers that I use, one production and one
> development. I have the following 2 statements in the virtual host
> section for the website

At this point, fortunately, I won't have to worry about doing anything
through 
Apache.  I think I'll miss that entirely.

> httpd.config on development
>
> SetEnv DB_HOST devhost
> SetEnv DB_USER root
>
> httpd.config on production
>
> SetEnv DB_HOST prodhost
> SetEnv DB_USER prod_username
>
> and then access these through the %ENV hash in perl or getenv function
> in PHP. That makes it easy, all I have to do is transfer the scripts
> over and they pick up the correct names. You could do this on a single
> server by having virtual hosts and setting the variables to different
> values dependent upon the url called.

I'm using Perl for the web stuff (which is only for a setting editor).
I'm 
not a professional programmer.  I hadn't touched a line of code in over
a 
decade when I started this.  I discovered Perl quickly, and I've done as
much 
as possible in Perl so I can give myself a sense of unity.  (I had to do

another part in Java, so I'm trying to stick with only those two
languages 
for now.)

> HTH

Thank you for all the info.  It's a big help!

Hal

> Regards
>
> David Logan
> Database Administrator
> HP Managed Services
> 148 Frome Street,
> Adelaide 5000
> Australia
>
> +61 8 8408 4273 - Work
> +61 417 268 665 - Mobile
> +61 8 8408 4259 - Fax
>
>
> -----Original Message-----
> From: Hal Vaughan [mailto:hal@stripped]
> Sent: Thursday, 17 February 2005 12:45 PM
> To: Logan, David (SST - Adelaide)
> Subject: Re: Using Different Database Groups On the Same Computer
>
> On Wednesday 16 February 2005 09:04 pm, you wrote:
> > Hi Hal,
> >
> > Do you mean using the same version but accessing 2 different sets of
> > tables? I'm a little confused as to what your requirements are.
>
> Or databases.  Basically, I want it all on one system, but I don't
want
> the
> current databases, which are in use by the current and running version
> of the
> program, to be effected in any way by the new version of my program
> (actually
> a suite of programs), which will be using databases and tables with
the
> same
> names as the current version.
>
> > You could install both versions and use aliases to ensure that you
can
> > access only one instance at a time by the method you described. You
> > would have new-mysql aliases to a mysql command with either a
>
> different
>
> > port number or hostname to access the newer version whereas the
mysql
> > would point to localhost on the standard port.
>
> Let me put it in my words, so I can be sure I understand you.  So I
> could
> install MySQL and leave everything "normal" for my current program to
> access
> everything it can access now (I'm accessing them through Perl, using
> DBI),
> AND I can also specify another group of databases that are accessed if
I
> try
> a different port from Perl, or perhaps, a different specification on
the
>
> command line.
>
> Is that it?
>
> If so, is there a technical term for what I'm trying to do, or what
each
> set
> of databases is called?  And what does this come under in the manuals
> and
> info so I can find out how to configure my system to do this?
>
> > I run 2 different versions on one of my smaller linux boxes for
> > precisely the reason you are talking about. If you are accessing
these
> > via php for example, you can use an environment variable to set
which
> > port, host etc. you wish to talk to.
>
> That's basically what I want to do, so I think we're talking about the
> same
> thing.
>
> Thanks!
>
> Hal
>
> > Regards
> >
> >
> > David Logan
> > Database Administrator
> > HP Managed Services
> > 148 Frome Street,
> > Adelaide 5000
> > Australia
> >
> > +61 8 8408 4273 - Work
> > +61 417 268 665 - Mobile
> > +61 8 8408 4259 - Fax
> >
> >
> > -----Original Message-----
> > From: Hal Vaughan [mailto:hal@stripped]
> > Sent: Thursday, 17 February 2005 12:23 PM
> > To: mysql@stripped
> > Subject: Using Different Database Groups On the Same Computer
> >
> > I'm not quite sure what search terms to use, otherwise I'm sure I
>
> could
>
> > find
> > this on Google.
> >
> > I have a working install of MySQL on an older computer, running the
> > current
> > version of my program.  I am about to start developing a newer
version
> > of my
> > program on a new computer.  I'd like to have both versions (current
>
> and
>
> > new)
> > on the same computer, however, that will cause a LOT of problems in
> > interference between the current and development versions of my
>
> program.
>
> > Is there any way to separate these, so I can run mysql one way to
>
> access
>
> > the
> > databases for the current version, and run it another way to access
>
> the
>
> > newer
> > databases for the development version (and yes, many databases in
the
> > development version will have the same name as those in the current
> > version).
> >
> > For example, if I ran a command "mysql", I'd get these tables:
> >
> > CaseData
> > Clients
> > GeneralSetup
> >
> > And if I ran an alias, like, say, "new-mysql", I'd get these tables:
> >
> > CaseData  (same name, different table)
> > Clients  (same name, different table)
> > GeneralSetup  (same name, different table)
> > DataFilters
> >
> > I am running MySQL on Linux, if that makes a difference.
> >
> > Hal

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Using Different Database Groups On the Same ComputerHal Vaughan17 Feb
  • Re: Using Different Database Groups On the Same ComputerHal Vaughan17 Feb
    • Re: Using Different Database Groups On the Same ComputerHassan Schroeder17 Feb
      • Re: Using Different Database Groups On the Same ComputerHal Vaughan17 Feb
        • Re: Using Different Database Groups On the Same ComputerHassan Schroeder17 Feb
          • Re: Using Different Database Groups On the Same ComputerHal Vaughan17 Feb
            • Re: Using Different Database Groups On the Same ComputerHassan Schroeder17 Feb
              • Re: Using Different Database Groups On the Same ComputerHal Vaughan17 Feb
        • Re: Using Different Database Groups On the Same ComputerKristen G. Thorson17 Feb
RE: Using Different Database Groups On the Same ComputerSST - Adelaide)17 Feb
  • Re: Using Different Database Groups On the Same ComputerHal Vaughan17 Feb
RE: Using Different Database Groups On the Same ComputerSST - Adelaide)17 Feb
  • Re: Using Different Database Groups On the Same ComputerHal Vaughan17 Feb
RE: Using Different Database Groups On the Same ComputerSST - Adelaide)17 Feb
  • Re: Using Different Database Groups On the Same ComputerHal Vaughan17 Feb