databases--where are they?


Recommended Posts

Sorry for the noob question. I can never figure out directories and settings files.

In what directory are the mysql database files kept? I created a d-b and I can see no trace of it.

Reason I want to know: I have already created several blogs on my LAN dev server including many posts and comments (I ported an existing TypePad blog). I do not want to create everything from scratch again. I want to FTP the entire db to the right place on Hawkhost.

Subsequent new entries will be made directly via the wordpress tools.

Thanks for your help,


Link to comment
Share on other sites

The direct databases files you cannot access as they are owned by the mysql user not you. How you import them is dump the current contents using mysqldump or phpmyadmin has a tool to do it. Then login to cPanel go to phpmyadmin and then you can import the .sql file.

This is far better than uploading the actual db files if that was possible. Uploading different files while mysql is running can produce unpredictable results. Also you uploading the files if your mysql server was running it might not have all the data in the files yet (might still be in strictly memory not written to disk).

Link to comment
Share on other sites

It's a one time problem in any case.

I guess this goes with being "shared".

Could I get my own instance of MySQL?

That's not possible if you wanted your own mysql server you'd need a virtual private server.

As I said what you're wishing to do is not even something you should be doing to begin with. Copying the data files there is no guarantee it's all there if mysql is running. The same with copying them into the actual folder where they are on the new system. You have many risks of corruption in there. That also does not take into accountant if the versions are different the problems that could cause.

This is why you also export them as a file using mysqldump then import them back in running the sql queries from the actual file. So either by uploading it in phpmyadmin it from ssh doing mysql -u username -p database

Link to comment
Share on other sites

I'll do what I need to do and it's not that bad.

But, what you are saying isn't actually right. Any serious site (which mine is not, to be fair) runs development, test, staging, and production. Various tools are used to prop content and code across these environments.

Here is a post in which the author is able to simply replace his entire d-b in either direction. When the various environments are carefully managed to be as nearly as identical as possible then this is manageable.

This is, of course, not the only way to do it. Another poster uses phymyadmin backup and restore to move between development and production. This is what I will have to do.

Not many people in the blogging world even care about this but in real applications, it must be done. My goal was to build and test everything on my dev server and then prop it to Hawkhost when complete. That is going to be harder than I wished it was. But, I am doing it early so the backup/restore processes, which are normally very slow, won't be that bad.

For changes in scripts, css, this is not a problem as I can ftp to file directories I have access to on Hawkhost.

I am surprised you haven't seen this among larger, commercial sites that you host. It is pretty much standard practice. But, if you are sharing an instance of MySQL across multiple customers, which seems like a truly terrible, terrible idea by the way, then this is impossible. This approach also assumes that production can be stopped for a window of time.

Note that it is just a one time problem to bootstrap from old Typepad blogs, which already contain content, to WordPress hosted on Hawkhost. Normally, little to no content would move back and forth between development and production.

So, aside from the intellectual discussion there are no worries. I'm talking a whopping 4M of data. It's 81 posts, some of which are quite long and I wanted to "automate" it. Backup and restore will work.

Link to comment
Share on other sites

The blog post you mention does not talk about replication of the database data strictly the files that are on FTP.

In order to copy the .frm, .myd and .myi files you need to shutoff both ends. Your development server gets shutoff you then shutoff production to copy the new files over. This is why backup systems when dealing with mysql need to flush everything otherwise it's highly likely the db files are corrupt.

So when you're dealing with a production system it would be causing down time every time you want to restore that data or take the data off of it for development systems.

So I don't think what you're wishing to do is a standard practice at all. What is standard is dumping the database once in a while to the development side to have real production data.

As far as each user having their own MySQL instance the overhead would be astronomical. Say we had 500 users on a single server that's 500 separate mysql instances. None of which can be binded to localhost they would need to be binded some other way. I've never seen this done in the shared hosting world. Closest you get is users purchasing their own mysql server essentially and they connect to it remotely. So the host offers for $30/month a mini MySQL which is sitting on a OpenVZ virtual machine or Xen or something.

How we do our production vs development is somewhat similar to the blog post you mentioned. We use mercurial to do our version control. Anyone working on the site either makes a dev environment on their system or they using our staging system. We keep a schema.sql file below the public root that we update once in a while for anyone doing development so there is some production data. The person makes the changes tests it locally and does however many revisions. Then once they believe it's ready they push it back out.

On our production side we have system in place to checkout the code from mercurial.

Link to comment
Share on other sites

(it's in the last line of the post I linked....)

You are right: if you move the entire d-b around you would have to shut down production (shutting down development/test/staging is no problem). Many sites would avoid this as much as possible.

I've got a trivial amount of data and I am not making any schema changes so backup/restore will be fine.

On a more practical note:

Your Fantastico scripts provide wordpress 2.8.3 (if I recall correctly). I am using wordpress 2.9.1. So, I am going to copy my own instance of wordpress.

Do you see any problems with this?

Link to comment
Share on other sites

... if you are sharing an instance of MySQL across multiple customers, which seems like a truly terrible, terrible idea ...

It may be truly terrible, terrible to you, but if you were to carefully think about it (I assume you have the capacity to think but I could be wrong), you will realize that...

it is definitely and absolutely, beyond a shadow of a doubt,

a practical, functional, commonsensical, feasible, sensible, and workable solution for shared hosting as implemented by 100% of web hosts globally.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.