Re: Using Different Database Groups On the Same Computer
Hal Vaughan wrote: I've tried this by running 2 instances of mysqld, the first with no arguments, and the second like this: mysqld --port=3307 --datadir=/dbtest/mysql I have to run mysqld directly -- not through safe_mysqld (which /etc/init.d/mysql calls). If I run it through safe_mysqld, I can run only one instance at a time, it will exit without running a new instance if it detects one already running. FWIW, `/etc/init.d/mysql` and `safe_mysqld` are just shell scripts. Hence you can copy and change them easily to run multiple versions or instances of most software... -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
On Thursday 17 February 2005 10:18 am, Hassan Schroeder wrote: Hal Vaughan wrote: I've tried this by running 2 instances of mysqld, the first with no arguments, and the second like this: mysqld --port=3307 --datadir=/dbtest/mysql I have to run mysqld directly -- not through safe_mysqld (which /etc/init.d/mysql calls). If I run it through safe_mysqld, I can run only one instance at a time, it will exit without running a new instance if it detects one already running. FWIW, `/etc/init.d/mysql` and `safe_mysqld` are just shell scripts. Hence you can copy and change them easily to run multiple versions or instances of most software... I see that, but even when I bypass them, I can run 2 instances of mysqld, it shows up in the task list as 2 separate tasks, but they both use the data directory specified in the last instance I run. I'm trying to get 2 different instances of mysqld running at the same time, each using a different port and different data directory. Hal -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
Hal Vaughan wrote: I see that, but even when I bypass them, I can run 2 instances of mysqld, it shows up in the task list as 2 separate tasks, but they both use the data directory specified in the last instance I run. Uh, that doesn't really make sense -- a *running* instance isn't going to switch data directories because another process started up :-) Sounds like something in the way you're invoking this is causing the first instance to be restarted, and then it's using the second set of parameters. My preference to do this is to create a complete new config file, say /etc/alt.cnf, and use that to configure the second process. That way you can be relatively sure there aren't any overlapping resources. HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
http://dev.mysql.com/doc/mysql/en/multiple-unix-servers.html There may be some helpful notes there. How are you determining which datadir mysqld is using when it's actually running? Are you logging in with mysql or mysqladmin? Make sure that if you are doing so, that you're actually logging in to each server separately, and not just looking at the variables for the same server that you've logged into twice. At the very least, try passing --port and --datadir and any other necessary options on command line for both mysqld processes. Command line options should override all others, so if it works then, you know you have a problem with your cnf file or environment variables. Kristen Hal Vaughan wrote: On Thursday 17 February 2005 10:18 am, Hassan Schroeder wrote: Hal Vaughan wrote: I've tried this by running 2 instances of mysqld, the first with no arguments, and the second like this: mysqld --port=3307 --datadir=/dbtest/mysql I have to run mysqld directly -- not through safe_mysqld (which /etc/init.d/mysql calls). If I run it through safe_mysqld, I can run only one instance at a time, it will exit without running a new instance if it detects one already running. FWIW, `/etc/init.d/mysql` and `safe_mysqld` are just shell scripts. Hence you can copy and change them easily to run multiple versions or instances of most software... I see that, but even when I bypass them, I can run 2 instances of mysqld, it shows up in the task list as 2 separate tasks, but they both use the data directory specified in the last instance I run. I'm trying to get 2 different instances of mysqld running at the same time, each using a different port and different data directory. Hal -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code.
Re: Using Different Database Groups On the Same Computer
On Thursday 17 February 2005 10:56 am, Hassan Schroeder wrote: Hal Vaughan wrote: I see that, but even when I bypass them, I can run 2 instances of mysqld, it shows up in the task list as 2 separate tasks, but they both use the data directory specified in the last instance I run. Uh, that doesn't really make sense -- a *running* instance isn't going to switch data directories because another process started up :-) That's what's happening. I have no idea why. Here's what I'm doing now: (each command is in a separate console) mysqld --port=3307 --datadir=/dbtest/mysql mysqld --port=3306 --datadir=/var/lib/mysql mysql --port=3307 mysql --port=3306 When I do this, then do a show databases; in either new instance of mysql (both are open at the same time, in separate consoles), I get the databases in the last version of mysqld that I specified. Sounds like something in the way you're invoking this is causing the first instance to be restarted, and then it's using the second set of parameters. I do a ps-ax and get this (only mysqld tasks pasted in): 13391 pts/10 S 0:00 mysqld --port=3307 --datadir=/dbtest/mysql 13401 pts/10 S 0:00 mysqld --port=3307 --datadir=/dbtest/mysql 13402 pts/10 S 0:00 mysqld --port=3307 --datadir=/dbtest/mysql 13403 pts/10 S 0:00 mysqld --port=3307 --datadir=/dbtest/mysql 13450 pts/9S 0:00 mysqld --port=3306 --datadir=/var/lib/mysql 13451 pts/9S 0:00 mysqld --port=3306 --datadir=/var/lib/mysql 13452 pts/9S 0:00 mysqld --port=3306 --datadir=/var/lib/mysql 13453 pts/9S 0:00 mysqld --port=3306 --datadir=/var/lib/mysql My preference to do this is to create a complete new config file, say /etc/alt.cnf, and use that to configure the second process. That way you can be relatively sure there aren't any overlapping resources. I'll try that, in case config options can trump a command line. Hal HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
Hal Vaughan wrote: mysqld --port=3307 --datadir=/dbtest/mysql mysqld --port=3306 --datadir=/var/lib/mysql mysql --port=3307 mysql --port=3306 When I do this, then do a show databases; in either new instance of mysql (both are open at the same time, in separate consoles), I get the databases in the last version of mysqld that I specified. Aha. Light bulb :-) My preference to do this is to create a complete new config file, say /etc/alt.cnf, and use that to configure the second process. That way you can be relatively sure there aren't any overlapping resources. I'll try that, in case config options can trump a command line. They don't, but it's what you're *not* specifying here -- the two processes are sharing the default /tmp/mysql.sock socket, I'll wager. So the last daemon started is listening on it... I think you'll find creating two config files will make it a *lot* easier to get this going :-) FWIW! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
On Thursday 17 February 2005 11:48 am, Hassan Schroeder wrote: Hal Vaughan wrote: mysqld --port=3307 --datadir=/dbtest/mysql mysqld --port=3306 --datadir=/var/lib/mysql mysql --port=3307 mysql --port=3306 When I do this, then do a show databases; in either new instance of mysql (both are open at the same time, in separate consoles), I get the databases in the last version of mysqld that I specified. Aha. Light bulb :-) My preference to do this is to create a complete new config file, say /etc/alt.cnf, and use that to configure the second process. That way you can be relatively sure there aren't any overlapping resources. I'll try that, in case config options can trump a command line. They don't, but it's what you're *not* specifying here -- the two processes are sharing the default /tmp/mysql.sock socket, I'll wager. So the last daemon started is listening on it... Bingo! I tried adding --socket=/var/run/mysqld/mysqld6.sock for the process running with port 3306 and mysqld7.sock for the process running on port 3307. It works perfectly! Thanks! This will really help me out in getting rid of some extra boxen that are too slow and need to be retired to the parent's e-mail computers. Making the change for the one routine in my Perl modules that creates the connection to the database is trivial, too. Thanks to all who have helped with this. Hal I think you'll find creating two config files will make it a *lot* easier to get this going :-) FWIW! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
RE: Using Different Database Groups On the Same Computer
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. 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. 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. 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:[EMAIL PROTECTED] Sent: Thursday, 17 February 2005 12:23 PM To: mysql@lists.mysql.com 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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:[EMAIL PROTECTED] Sent: Thursday, 17 February 2005 12:23 PM To: mysql@lists.mysql.com 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/[EMAIL PROTECTED]
RE: Using Different Database Groups On the Same Computer
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. 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 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 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. HTH 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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] Sent: Thursday, 17 February 2005 12:23 PM To: mysql@lists.mysql.com 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
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:[EMAIL PROTECTED] 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
RE: Using Different Database Groups On the Same Computer
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:[EMAIL PROTECTED] Sent: Thursday, 17 February 2005 1:10 PM To: mysql@lists.mysql.com 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:[EMAIL
Re: Using Different Database Groups On the Same Computer
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:[EMAIL PROTECTED] Sent: Thursday, 17 February 2005 1:10 PM To: mysql@lists.mysql.com 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
Re: Using Different Database Groups On the Same Computer
On Wednesday 16 February 2005 08:52 pm, Hal Vaughan wrote: 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 I've tried this by running 2 instances of mysqld, the first with no arguments, and the second like this: mysqld --port=3307 --datadir=/dbtest/mysql I have to run mysqld directly -- not through safe_mysqld (which /etc/init.d/mysql calls). If I run it through safe_mysqld, I can run only one instance at a time, it will exit without running a new instance if it detects one already running. In all test cases, I've always run the plain version first, and the one using the 2nd port and new data dir last. No matter what I do, it always uses the new data dir, whether I try mysql or mysql --port=3307. Any ideas? The goal is to have 2 instances of MySQL running on the same computer (preferably without needing 2 different versions or 2 different installs), with each instance using different data directories (and, of course, to reach the separate instances, each listens on a different port). Thanks for any help. Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
