Using multiple MySQL DB's on a Server

bladx has asked for the wisdom of the Perl Monks concerning the following question:

Hi everyone-
This past week, I experienced a terrible occurance having to do with MySQL mainly (as well as Perl, because that is how I can connect to the DB.) For a little background, I have set up and run the whole web site for my High School, for about a month and a half now. It is running on a server, and the main data for news articles, etc. are in one database (with many different tables too). Anyways, what happened this past week is: When I tried to connect to a new database for something else I was working on, it seemed to be working alright. However, the following day, when I came back to the server computer, I found out that all of the MySQL databases where deleted. This was not due to any mischievious deeds or anyone getting into the server itself. I suspect it had to do with me switching the databases when I was was using another DB the previous day. Unfortunately, I was naive enough to think that I wouldn't need to backup my DB for a while, but this happened before I actually did.

My main question is: Is there a way I can be running a server that uses one database in MySQL while I am using another database from either within mysql itself, or through connecting with say DBI in Perl? Or, another view on this problem is, is there an obvious reason (or reasons) why this type of thing would happen?

Thanks for any help, this has bothered me this whole week. Fortunately, we haven't had a lot of articles on the site quite yet, and that means not many things were lost in the lost DB.

Andy Summers
JATU -- Just Another Tilly Underling

Comment on Using multiple MySQL DB's on a Server

Replies are listed 'Best First'.
Re: Using multiple MySQL DB's on a Server by Zaxo (Archbishop) on Jan 12, 2002 at 13:49 UTC
I've only seen something similar to this when cheaper programmers were got to replace me. I don't know exactly what they kept doing, but I think they had the notion that they needed to delete a db to disconnect it. That only affected one database, though. Is something very privileged modifying database mysql? Check /var/lib/mysql (or wherever) to see if the databases are really gone or just inaccessible. With DBI, the connect method usually specifies the database. You can change db with: `use DBI; my ($user, $pass) = ('joe', 'joe'); my $dbh = DBI::connect('dbi:mysql:database=thisun',$user,$pass,{RaiseE +rror=>1}) or die $!; # now connected to thisun $dbh->do('use thatun'); # now connected to thatun` [download] Should be nothing there to wipe things out. After Compline, Zaxo	[reply] [d/l]
Re: Using multiple MySQL DB's on a Server by gav^ (Curate) on Jan 12, 2002 at 10:29 UTC
Hmmn... I guess the short answer is be careful and not screw up the database :) To be honest though, it sounds very strange to me that all the databases got deleted, I doubt that is something you can do accidently. You can use the GRANT statement in MySQL (see docs) to create a user that has limited SELECT/INSERT/DELETE permissions. This saves you from dropping tables etc by mistake. This is a good idea anyway, from a security standpoint. While you could use DBD::Multiplex to use 2 different databases a much better (and much simpler) solution would just be to have a cron job to back up your database on a regular (nightly, even hourly) basis. gav^	[reply]
Re: Using multiple MySQL DB's on a Server by screamingeagle (Curate) on Jan 12, 2002 at 13:33 UTC
in mySQL, you can make any one database the default database. however, you can simultaneously run a perl script which contains DBI commands which use another database to run operations on. (the command to switch the database is "use 'databasename'". I cant imagine how the databases got deleted just by switching the default database. in mySQL, when a database is created, an operating system folder is created called with the database name,which is under the MySQL\data\ folder (for e.g. c:\mySQL\data\database1), which contains all the data files for that database. you might want to be careful about who has access to those folders. the culprit might not even need to log into mySQL to delete the database...all he (or she) needs to do is delete those operating system files which i just mentioned in order to cause havoc...	[reply]