Copying A Database

[Taylor_1978]

Hiya...

Is there a simple, effective way to copy a database (without the data). I run a website that allows users to create their own league on Yahoo!... When the league is created at the moment I have to manually create a new database for them by entering the CREATE table for all the 13 tables within the database...

Is there a way to copy the database - something like COPY 'xcanasta' NAME 'xgin' - or sumthin LOL

Thanks in advance! :thumbsup:

Taylor.
www.ysuperleague.com

[raf]

Yes there is. You create a dumpfile of your db, and only include the db-design.
read the sticky to know how to do this in phpMyAdmin (each mysql front has an option to create dumpfiles). If you want to do it through the commandline or embedded sql, check http://www.mysql.com/doc/en/SELECT.html .

When a new db needs to be created, you then run the dumpfile,possebly after changing the db-name in it (dumpfile are just straight textfiles so you can read them with PHP or so, and replace the db-name with something else. The db-name then server as a placeholder, like §dbname§ (--> you first change the dbname inside the dumpfile by opening it in word and replacing your existing db-name with something like this).

But do you realy need a new db for each user?

[Taylor_1978]

Thanks for that. I sort of get it! LOL

Do I really need a different database for each user? Yes. Each "user", being a "league" (which also has it's own subdomain) has 13 tables - We could have anything up to and even beyond 2,000 leagues, with each league having anything up to and beyond 2,000 players with their own records... that would be one mighty big database to try and open through myPHPadmin !! LOL

Taylor.
www.ysuperleague.com

[raf]

i now see that i typed "dumpfile by opening it in word" but i means wordpad.

I don't see the problem running one db for all users. I mean, your not the first one that has tables with more then a million records.
I also don't understand the issue of opening it through phpMyAdmin. What do you want to open them for through phpMyAdmin? That is just a messy webfront that you can use if your site needs to be hosted and you need to access the db over the webserver. For serious db-management, you wount use a webinterface. If you want to use phpMyAdmin then you'll need to start by selecet the db out of a dropdown with 2000 options ... (might take some time to load, if phpMyAdmin can handle it at all)

I don't think it's very realistic to maintain 2000 db's. I sure hope you'll never need backups or that you will never need to add or change a table (in other words, you need to freeze the db-design if you realy want to take this path). And even if you have such psychic powers to foresee all future needs for your db, then having 2000 db's will generate much more overhead on the mySQL-server then one big db + it's virtualy impossible to manage and optimize the dataservers performance with such a fragmented datacollection.

If you think mySQL, can't hack it, then upsize to PostgreSQL, Oracle or DB2. If you realy get the 4 miljon users, you might need to set up a few multiple, federated, db's but i doubt that it will ever get that far, and by then, i assume you'll be needing a pretty good db-adminteam to keep everything tuned.
But creating a new db for each league is definitely not the path you want to take.

[Taylor_1978]

I see what you're saying and it certainly makes sense - however I am hesitant to place it all on one db for a couple of reasons...

1) At the moment I AM hosting on a webinterface, not from my own server; hence myPHPadmin - the league is only new - so it handles fine (for now). The reason I am on a web interface is because in order to get a static IP here in Australia, it costs over $1,500 a MONTH! - I'm not sure why - but it has become ridiculous - on ADSL you can only go static on a business account - so until the site gets big enough to warrant it - I need to stay on a web interface.

2) We have followed a similar format to MyLeague.com - and although they have been going for 6 years - they had to eventually split all their leagues on to 12 different servers due to being well past a comfortable capacity. I dred trying to split the database up that many times...

But I am all ears to suggestions... because you are right, if I need to amend a table - will be quite annoying to do it over and over and over lol

Thanks :thumbsup:

Taylor.
www.ysuperleague.com
Now there is a new way to create and run your own league!

[raf]

I don' know your situation or the development schema you have. But your hostingsituation will probably be
- regular shared hosting
- dedicated server
- seperate dataserver and werbserver
- seperate dataserver and a cluster of webservers
- database on mainframe

Somewhere along the path, you will probably upsize to another db-format ad you will start to reorganise your data --> putting all info (from all leageus) into seperate db's, based on what sort on info it is, and where you use it insede you db (so maybe a seperate db for the gameresults, a different db for the players info and history etc) By then , you'll also need a good archiving routine to keep your operational tables as smaal and performent as possible. You you will be starting to have precomputed data inthere, with the atual, detaildata in a seperate table, that will only be queried if you need to drill down on the precomputed data.

You see? If you split up the db, it wount be on 1 dimension (like league) but it will be to seperate meaingfull groups of information, that you can map to operational processes. You(ll get the best performance then if each group of operational precess only need data from 1 db.
And data will be precomputed on regular bases so you're probably gonna have an (offline) datawarehouse process running.

I think you may be quite sure that by the time you get realy big, you'll have to rewrite your db-design and application.