OT: MySQL Databases

[Fyron]

If I have the actual files that constitute a MySQL database, but no access to the actual location on which they are stored on the server, only access to software like phpMyAdmin, how would I go about setting up the old database on a new server?

[Atrocities]

I would ask Gandalf Parker about this, or even Richard. They seem to be the most knowledgable about these kind of things.

[Richard]

Is this a table dump or just the physical data files fromt he mysql\data directory?

If it's a dump you can just put the insert and create statements in a phpmyadmin and off you go.

If it's the physical files you can TRY to ftp them to the mysql/data directory on the new box and it MIGHT work. Before I knew about mysqldump I used to transfer mysql databases that way and it has worked in the past BYMMV.

[Fyron]

It is a copy of the physical database files. EDIT: I can get access to the directory to upload them to, so that is not a problem. Is it a bad idea to do it this way? Should I manually create all of the databases via Cpanel first, then upload the files? Never worked with the actual database files.

Is there some easy way to convert them to SQL statements, without trying to get a working MySQL and web server on my own machine? Or would it be better to try to get them as an exported SQL file from my previous web host?

[Gandalf Parker]

Richards definetly the best choice on the SQL internals.

But I have seen alot of domain movement by CPanel. You might want to set it up in cpanel first in case there are settings appropriate to that site. Then when it creates the empty database in the right place with the right pointers, you can try overwriting it with the saved copy.

If that fails the export/import might be the next thing to try but again best to have an empty of the same name already in place.

As a last resort you could write a SQL script to access each record and "input" it into the new one. Definetly as a last resort but if the file has hundreds of records and not thousands then it wouldnt be too bad.

[tesco samoa]

yea do a copy out, set up the enviroment on the new server and do a copy in.

use the admin tool from mysql or the web based admin tool to do this work.

I would recommend you do a compression first to clean up the database and then the copy out.

Do you not back up your database each week ? You should do this.

[Fyron]

Yes I do a backup, but I had not made a backup yet before the old account was disabled.

[Instar]

I haven't read your problem at all, but I gotta say just ditch MySQL and download something else... you can get Oracle 10g or 9i for free, I think... maybe some licensing issues I dunno.
The devs of MySQL used to think that foreign keys were not needed... !!!!!
Sorry to be a purist, but Im a hardcore dev person and stuff like that just makes me angry

[Richard]

Fyron you can use the phpmyadmin in cpanel to create a sql statement dump of the database via the export feature. If you still have cpanel access to the old mysql database I would do that. Then you can recreate it via phpmyadmin on the new box.

Otherwise you can try to create a blank database and ftp the files into the mysql/data area, but it doesn't always work well.

[Fyron]

I don't have access to the old server anymore. I have requested such a dump from the host.

[Richard]

Instar said:
I haven't read your problem at all, but I gotta say just ditch MySQL and download something else... you can get Oracle 10g or 9i for free, I think... maybe some licensing issues I dunno.
The devs of MySQL used to think that foreign keys were not needed... !!!!!
Sorry to be a purist, but Im a hardcore dev person and stuff like that just makes me angry



You can get Oracle 9 or 10 for free? What about support? The other thing that sucks is the lack of cheap tools to work with Oracle, except for TOAD which I hate....

I probably wouldn't change anytime soon, since mysql has done well for us, but I know Oracle is more performant and supports HA.

[Instar]

Richard said:

Instar said:
I haven't read your problem at all, but I gotta say just ditch MySQL and download something else... you can get Oracle 10g or 9i for free, I think... maybe some licensing issues I dunno.
The devs of MySQL used to think that foreign keys were not needed... !!!!!
Sorry to be a purist, but Im a hardcore dev person and stuff like that just makes me angry



You can get Oracle 9 or 10 for free? What about support? The other thing that sucks is the lack of cheap tools to work with Oracle, except for TOAD which I hate....

I probably wouldn't change anytime soon, since mysql has done well for us, but I know Oracle is more performant and supports HA.


Well, MySQL is a valid relational database, and they have added foreign keys. Its just not natural to have devs that stupid; to think foreign keys are not needed.
As for Oracle being a free download, lemme find it...
http://www.oracle.com/technology/software/products/database/oracle10g/index.html

You can get it there. Support is limited at best, I would guess. But you could say the same about MySQL, it is open source, so support is whatever you can google or msn search for, unless you're willing to pay for it.

I'm not a DBA, but I can still rant about it... heh

[Fyron]

Instar said:
it is open source, so support is whatever you can google or msn search for, unless you're willing to pay for it.

Or whatever you can ask on free support forums, mailing lists, and/or IRC channels.