Is it possible ?

[XRayden]

I Used MySQL for a long time dev under PHP 3, 4 and now 5

 

now I'm facing a problems that i solved in the past with php, but i wonder if MySQL could do it on it's own, that'll help me a lot.

 

Multiple database, 1 shared table, is it possible?

 

i mean, i already done with 5 install of Xoops CMS and modified it that every install is on the same database, with different prefixes (like db1_ db2_ ...)

then i mod Xoops for each time it called for it's users or saved open sessions id in the DB it use the same table on all install... so the users seamlessly wonder about in 5 different xoops install without disconnect and change username.

 

now i face the same problem, i need a multi-install or a single install of a core system that will act as a multi-install software, but with the same set of users, sessions and some other things

 

is it possible to do that kind of trick to a MySQL database ?

 

Telling a database "this table is shared between all theses other database" and each change or sync result in a instant change in all platforms ?

 

can we use Linux "link" to do this kind of thing ?

[XRayden]

DONE!

 

woohoo! it worked this way...

 

i've create a database called "database1" and create 2 tables inside

 

copied it to "database2" now database1 and database2 are identical...

 

what i've done, on linux i've done that :

# rm -f table1.*

# ln -s /var/lib/mysql/database1/table1.frm /var/lib/mysql/database2/table1.frm

# ln -s /var/lib/mysql/database1/table1.MYD /var/lib/mysql/database2/table1.MYD

# ln -s /var/lib/mysql/database1/table1.MYI /var/lib/mysql/database2/table1.MYI

# service mysqld restart

 

so this make 3 symbolic links of the table1 of database1 to database2...

 

result? seamless work between database... db1 and db2 see and work with table1 like it was it's own... that's kinda cool...

 

 

[corbin]

Glad it worked, but you might get some weird lock errors or corruption later on.  Just warning you.  Or, I could be wrong, and things could go fine.

[XRayden]

i've found out that their is some things to know about that :

http://dev.mysql.com/doc/refman/5.0/en/symbolic-links-to-tables.html

 

seams that modification to the structure or repair or optimise on a symlink table will break the link

 

some ppl told me that I need to restart mysql server more often, like after every backup or one time per day

and if i repair or optimise, it need to be on the original database. hehe.

 

some ppl told me todo a shell script of making the symlink and that every month or week i auto-recreate the link for security reason.

 

seam to be a lot of work keeping that stable, but i think it's less problematic than to rework a WORKING software to do that...

[corbin]

Symlinks are fully supported only for MyISAM tables. For files used by tables for other storage engines, you may get strange problems if you try to use symbolic links.

 

 

Wow nice spam sneak in there.  Tacky!

 

 

 

 

Wonder why you need to recreate the link every now and then....  Weird.  Guess I'll read through that.

[trq]

Symlinks are fully supported only for MyISAM tables. For files used by tables for other storage engines, you may get strange problems if you try to use symbolic links.

 

 

Wow nice spam sneak in there.  Tacky!

 

 

 

 

Wonder why you need to recreate the link every now and then....  Weird.  Guess I'll read through that.

 

Yeah, so nice he was banned. It wasn't the first spam link he'd posted.