MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Hal Vaughan Date:February 17 2005 3:14am
Subject:Re: Using Different Database Groups On the Same Computer
View as plain text  
On Wednesday 16 February 2005 09:48 pm, Logan, David (SST - Adelaide) wrote:
> 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

Since you gave me this, I was able to find most of what I needed with mysqld 
--help.  I knew I could set the port, but I still wanted to be sure I could 
have two instances (that's the word I couldn't remember when I was trying to 
figure out what to Google for) running, using different data sources without 
them interfering with each other.

From what I see, I can take /etc/init.d/mysql and copy it 
to /etc/init.d/mysql2.  I can modify the 2nd version to specify a different 
port and a different config file (which may not be needed if I specify the 
different data directory) on the command line.

I'll try this tomorrow, when I'm more alert.  I just didn't want to poke 
around with something like that and find out later that I was doing something 
that had effects I wouldn't see until it was too late.

Thank you for all the info.  This is huge help to me (it lets me get rid of 2 
old boxen that'll go to my parents and another relative and move everything 
onto one system -- at long last!).

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 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