Query regarding MySQL Databases....

[Autistic]

Hi folks,

I have a query regarding MySQL Databases. I am thinking of setting up a home server to store data in MySQL Databases. I know that If I run out-of-space, I can install a secondary HDD and create a seperate MySQL database and store data in the newly created database. But, How can I retrieve data stored in both database at once?

For example, If I have record of two years in the database in primary HDD and the third year record in the new database in the secondary HDD. How can I retrieve data from both the databases based on some criteria, say the last 1.5 years record?

[techgnome]

Actually, what would be better is if you set it up as a RAID so that even when you add additional drives, it acts as a single drive... and your data would automatically be spread across all drives as needed. That said... if you pop for a Terabyte, it will be along long long time before you should need to think about adding drives.

Point is, you should never segment your data across multiple databases based on something so arbitrary... it should be all contained within the same database... for exactly the reason you're asking about.

-tg

[Autistic]

Actually, what would be better is if you set it up as a RAID so that even when you add additional drives, it acts as a single drive... and your data would automatically be spread across all drives as needed. That said... if you pop for a Terabyte, it will be along long long time before you should need to think about adding drives.

Point is, you should never segment your data across multiple databases based on something so arbitrary... it should be all contained within the same database... for exactly the reason you're asking about.

-tg

Thanks for the reply. Can you elaborate the "RAID" part and teach me how to set up? BTW, If I am developing a Web Application, Multiple queries from multiple users will make the server down. In that case, I have to set up many separate servers. Is there any way to link all the servers to retrieve data?

Reps added....

[Serge]

Relational database doesn't really scale horizontally. So you can either use RAID5/10/50/60 of multiple drives or deal with the drive size later. If you don't need transactions then I would even recommend using NoSQL database (CouchDB, MongoDB etc) because these databases have "sharding" support where your data is being distributed horizontally (across multiple servers/drives).

[techgnome]

Thanks for the reply. Can you elaborate the "RAID" part and teach me how to set up? BTW, If I am developing a Web Application, Multiple queries from multiple users will make the server down. In that case, I have to set up many separate servers. Is there any way to link all the servers to retrieve data?

Reps added....

How much data are you dealing with that you're so concerned with? Especially on a home server... If it's really that much of a concern, then you might want to think about something in the enterprise class.... SQL Server (not Express), Oracle (also not of the Express variety) or MySQL Enterprise ... because the free/express version of those are probably not going to be able to handle the amount of data you're talking about... and then there's the hardware to think about as well... it would need to be a SERVER... I mean a true server, with Windows Server (or what ever flavor of OS that can act as a server) and not some PC that's shared and acting as a server.

As for multiple users, unless you've got thousands of requests a minute, odds are, you're not going to see much activity that takes the server down... and if you are, you may want to consider a hosted option rather than running it out of your house (speaking of which, your connection to the interwebs is probably going to be a bigger factor than anything else - you'll need to find out if there is a capacity limit - my inbound capacity is virtually unlimited, but my upload speeds are curtailed some).

-tg