[GLLUG] Re: MySql: Database inside of a Database

Szymon Machajewski SMachaje at grcc.edu
Wed Feb 11 10:20:51 EST 2004

Maybe a simple solution:

grant select on database.* to user@'%' identified by 'password';
flush privileges;

You can create a user with limited rights.  Replace select with "all"
for all rights or any combination of (select, update, delete etc). 
Replace database with the database name.  Replace user and password
So this is how you can create a user that will be able to change one
database but no other or will be able to change one table in a database
but not other.

Also you can duplicate a table by running:

insert into new_table select * from old_table


CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works
FROM artist LEFT JOIN work ON artist.id = work.artist_id
GROUP BY artist.id;



Szymon Machajewski MCSD, CNA, MySQL Core
Grand Rapids Community College

Open Sourceror

>>> "Scott Henry Harrison" <harris41 at msu.edu> 2/11/2004 8:06:23 AM >>>
> Is it possible to have a database inside of a database?

Anything is possible, but I am not sure that I recognize what
you are suggesting as a conventional solution. 

> Or maybe some one can offer a better solution:

> I would like to do this so that when the stat1's user no longer
> the stats, I can just delete the database and not have to go through
> deleting 5 to 10 tables.


To replicate a database, you can dump out its tables and the
load into new database: 

mysqldump -u delta statdb -p > statdb.copy.dump
mysqladmin create stat1
mysql -u root mysql -p < grant_privileges_SEE_BELOW_FOR_HOWTO
mysql -u delta stat1 -p < statdb.copy.dump 

To delete a database: 

mysqladmin drop stat1 

Alternatively, you can replicate and delete databases
underneath DB_DIR, where DB_DIR is something like

cd /var/db/mysql
cp -pR statdb stat1 


cd /var/db/mysql
rm -Rf stat1 

> If this is possible, how can I grant a user name and password so
> they can do every thing on statdb and included databases but leave
> rest of the databases alone?


You can create an automated script to do this: 

Just specify the right command sequence inside

mysql -u root mysql -p < grant_privileges.sh 

mysqladmin may require a password (and you would need
to then also have -p, like "mysqladmin create somedb -p").
Of course, if you directly copy directories underneath
/var/db/mysql or whatever DB_DIR is (and "violate"
expected usage of mysqladmin), then you can avoid this
password requirement. 

For a really programmatic approach, Perl's DBI interface
to MySQL works quite well. 


linux-user mailing list
linux-user at egr.msu.edu 

More information about the linux-user mailing list