UGN Security

Shared MySQL database

Posted By: Horus

Shared MySQL database - 01/12/07 02:16 AM

hi, i had a question for anyone familiar with mysql. i run a dual-boot machine with windows and linux. on that machine i run a website which uses perl and mysql. i'd like to know if it's possible to share the mysql database between both the windows and linux setup depending on what i'm booted into. for example, windows: f:\MySQL Datafiles\ibdata1 i want to use that in linux. i have f: mounted to /files in linux. apache runs fine with the perl scripts and all, i just need to find a way to share the database. all the solutions i've seen so far require dumping the database from windows (the database was originally created in windows) and then using that to set up mysql under linux. the problem with this is that it wont' update the windows mysql datafiles. and so, if i wanted to switch back to windows with an up-to-date databse, i'd have to dump the linux mysql one, and bring that back over. i understand that there are more files than just ibdata1 that might be required. really, i would just like to know if it's possible to share the same database files. note: also, i'm pretty sure all the database and table names are lowercase and i don't THINK i've used any floating point numbers in the database...although, i dont' know for sure. but does anyone know if the floating-poitn number format in windows and linux differ? never had to worry about that before. and i do believe the database type is innodb.//
Posted By: Gremelin

Re: Shared MySQL database - 01/12/07 02:21 AM

I could definately see data collisions with that setup; why not just set mysql to allow each pc to connect to the username to utilize the database through the server itself vs trying to run two servers with one set of data?
Posted By: Horus

Re: Shared MySQL database - 01/12/07 04:07 AM

do you mean set up a mysql server on a separate pc? it's b/c i'm only using one computer. the setup is just one pc, dual boot, with a fat partition setup so i can access certain files from both linux and windows. the files i'm mainly concerned with are the mysql datafiles (obviously). i dont' currently have a pc to dedicate as a mysql server. maybe down the road i'll buy some cheap parts and put one together. (it'd make things easier for the lan i have with my roommate to just set up a server/router pc) i'm just wondering if, given my current setup, it's possible to just share the same database file. i obviously don't have an in depth knowledge of how mysql stores data, but it seems like it would be a pretty trivial thing for mysql to be able to store it in a format that can be shared. (aka, being able to just copy files, or even having multiple servers access the same database) but then again, how many ppl out there are trying to do this? i'm sure there are plenty of reasons why//
Posted By: Gremelin

Re: Shared MySQL database - 01/12/07 04:22 AM

no, I'm saying, have the MySQL server run on one machine, and set the username to allow connections from the other machine; then you set the connection data on the one machine to connect to the server machien...

I'm trying to figure out why you want access to the mysql files in the first place; are you trying to use them to connecft with a seperate install and use that data or what? If you want both machines to use the same mysql data, then set machine A to be a server, and set machine B to use machine A's mysql server (using machineA's ip address on your network as the host, and the user account on machineA's user/password)

For example, I have 3 machines here. MySQL data is used on either Machine B or machineC (2 mysql instances because I do testing on multiple versions).
MachineA hosts final builds, so I use MachineB and MachineC to connect to it to use it as the server.
MachineB has my alpha work, just drawn out data, I do testing mainly just on that server to connect from php to its own sql server.

I also use SQLYog to connect from Laptop to Machine A and machineB to do some quick update work on them both.

It's all quite simple to do, just set the sql username to allow connections from ip's other than "localhost".
Posted By: Horus

Re: Shared MySQL database - 01/12/07 05:15 AM

yeah, i know what you mean. i'm saying, i only have Machine A, lol. i'm just doing this on my home computer. I'll boot into windows. on windows, i run apache with a mysql database to hold information. then i might want to boot into linux. in linux, i want to be able to be running the exact same website (hence havign all of my website stuff on a separate partition). but i want the databases which i use when booted in windows to be able to be access when i'm booted into linux. if i had two machines, i'd be doing exactly what you suggested. but having only one machine is what makes this difficult. y'know, lol. cuz once i boot into linux, the mysql server on my windows partition can't really still be running. so, to sum up. 1 machine, 2 operating systems (and thus, two separate mysql installations, one on the linux partition, one on the windows partition), and a shared partition with a mysql datafile used by the windows mysql server. i really don't think it's possible, i was just wondering if maybe someone knew. oh, when i mentioned the LAN in my earlier post, its just my buddies laptop, not really an option to run a mysql server on 24/7. so i only have 1 machine to do this with.//
Posted By: Gremelin

Re: Shared MySQL database - 01/12/07 05:30 AM

Oh, so you're dual booting, check...

Humm, it may be kinda difficult; I know that when you install MySQL via linux from source you can specify your path, so you could mount your fat drive and install MySQL to there; I however am not sure if the W32 version allows you to specify locations of mysql data... Even if it would, you'd want to ensure you're running the EXACT same versions of the W32 and Linux versions of MySQL...

If the Windows install allows you to specify the datafile location it shouldn't be too difficult as I'm sure it stores the data the exact same way... The only problem would be if MySQL stores EVERYTHING in the data directory (program files included)...

You'd ultimately have to give it a go and try to customize it... I don't dual boot here since I had the spare hw for a seperate linux box.
Posted By: Horus

Re: Shared MySQL database - 01/12/07 06:17 AM

ahh...after looking a bit more i found a problem. when you set the datafile in aparently only copies one of the necessary datafiles. the rest of the datafiles it keeps in a Mysql Server/data/ directory. anyway, i copied those files into the shared partition, however, after editing my.cnf and pointing it to the shared datafiles directory, mysql gives me a "datadir is empty or invalid" upon starting up...i have all the necessary files. i'm figuring it can't be done. lemme know what you think//
Posted By: Gremelin

Re: Shared MySQL database - 01/12/07 07:09 AM

Probably issues with the windows data, I'm not sure how it stores data or if it stores it properly (windows is so wonky lol).

You could try running your linux in a vmware session (or vice versa)
Posted By: §intå×

Re: Shared MySQL database - 01/16/07 02:02 PM

I would strongly advise against such a set up. At best you could install a version per OS and be sure to make one the primary and back up the other from that one.

Linux and windows use very different file systems and different hooks. The mysql version for windows and the version for Linux though they will act the same for the most part will be configured and have very different files that make them work.

The other alternative is to go out and buy a Pentium I or pentium II and install an OS it can handle (psst go linux or BSD) LAMP (Linux Apache MySQL Perl,Python,PHP).

Then your scripts on your local box can access a single data set on the cheapy MySQL box.
© 2018 UGN Security Forum