Want an example? If I would like to maintain one store of quotes in the database for my site named www.spot-on.tv, I would simply add my quotes into that database via the postnuke interface. When I am ready to add these quote to my other site (baybridge) I can do so via the creation of a symbolic link pointing links that use the same filename as the database files in baybridge database to point to elements (in this case, files) within the spot-on database.
The following was performed using:
RedHat 7.2
Kernel 2.4.7-10
mysql 3.23.41
filesystem ext3
Before you start messing around with the guts of mysql, make sure you have a good backup. The command shown below is what I use to back up my entire mysql structure. It is imperative you make a backup.
tar -cvjf /u0/backup/mysql.tbz /var/lib/mysql/*
Now, on with the show.
Inside my /var/lib/mysql directory are subdirectories which hold the actual data and indices for each database:
athena.ebzb.com[root]:/var/lib/mysql-> ls
Rogue_baybridge/ Rogue_ebzb/ Rogue_poorbastards_org/ Rogue_spot_on/
athena.ebzb.com[root]:/var/lib/mysql->
Inside each of these directories are three files for each table. Below, the files which comprise the quotes table are shown.
athena.ebzb.com[root]:/var/lib/mysql/Rogue_spot_on-> ls -l *quo*
-rw-r----- 1 mysql mysql 8606 Feb 13 01:15 nuke_spot_on_quotes.frm
-rw-r----- 1 mysql mysql 17436 Feb 13 01:15 nuke_spot_on_quotes.MYD
-rw-r----- 1 mysql mysql 2048 Feb 13 01:15 nuke_spot_on_quotes.MYI
Now, I look into the baybridge database (relative referencing)
athena.ebzb.com[root]:/var/lib/mysql/Rogue_spot_on-> ls -l ../Rogue_baybridge/*quo* ../Rogue_spot_on/Rogue_spot_on/nuke_spot_on_quotes.frm
-rw-rw---- 1 mysql mysql 8606 Feb 5 03:37 nuke_baybridge_quotes.frm.orig
lrwxrwxrwx 1 root root 54 Feb 13 18:00 nuke_baybridge_quotes.MYD -> ../Rogue_spot_on/Rogue_spot_on/nuke_spot_on_quotes.MYD
-rw-rw---- 1 mysql mysql 0 Feb 5 03:37 nuke_baybridge_quotes.MYD.orig
lrwxrwxrwx 1 root root 54 Feb 13 18:02 nuke_baybridge_quotes.MYI -> ../Rogue_spot_on/Rogue_spot_on/nuke_spot_on_quotes.MYI
-rw-rw---- 1 mysql mysql 1024 Feb 5 03:37 nuke_baybridge_quotes.MYI.orig
You will also note the links have retained the ownership of root, who created them-- but mysql user really needs to have the ownership.
# chown mysql:mysql *quo*
Now-- question is... is this supported? I seriously doubt it. If you want to play in this minefield, be very, very careful and make a complete backup before you touch a thing. The following command takes care of this by backup up all of my mysql databases and stores them on a different filesystem in /u0/backup.
tar -cvjf /u0/backup/mysql.tbz /var/lib/mysql/*
I have this in a cron job to run every day at 4AM, but you must run this immediatly before touching anything.
To review, steps are:
1) Shutdown database
service mysql stop
2) perform backup
tar -cvjf /u0/backup/mysql.tbz /var/lib/mysql/*
3) perform symbolic link procedure
4) Restart database
service mysql start
5) test, test, test.
I have posted this article on my www.poor-bastards.org site.
USE AT YOUR OWN RISK! NIETHER THE POSTNUKE FOLKS NOR I WILL ACCEP RESPONSIBILITY FOR TINKERING GONE BAD. IT WORKS FINE FOR ME, SO THIS ARTICLE IS MERELY AN OBSERVATION OF MY WORK. WHEN YOU PLAY WITH MYSQL COMPONENTS AT THE COMMAND LINE, YOU ARE PLAYING WITH FIRE.
COULD I POSSIBLY MAKE IT ANY CLEARER... STILL SOMEONE WILL GET THIER PANTIES BUNCH OVER A PLAY SESSION GONE BAD.