Questions on Private MySQL Instances and WordPress

Question

Hopefully I do a good job asking this question...

My web host (WebFaction) contacted me and said that one of the sites I'm hosting is using too many resources from the shared MySQL pool. They said that I should create a private MySQL instance on my application server and run things from there so as not to impact other sites.

An understandably fair and reasonable request. I've gone ahead and created the private instance database as they have a one-click installer. However from what I can tell it has no phpMyAdmin, and I've never worked with a "private instance" dB before so have some questions...

1. Being a 95% WordPress user do I have to have a private instance dB for every WordPress site I host, or can one handle the needs of many?
2. If the later...how ()?
3. I absolutely refuse to ssh into the dB to make any changes. I've never done it before, I don't know the syntax, and it scares the hell out of me truthfully. Is there an app/tool (I'm a Mac user) that makes interacting with my private instance dB simple and easy? I've been looking into Sequel Pro but I'm not sure.

Again, I hope the way I asked this isn't too confusing.

Answer 1

Yes, you can handle lots of WordPress instances from a single MySQL server instance. Your MySQL instance can contain lots of separate databases. You'll create a separate database for each WordPress instance. The WordPress Famous 5-Minute Install touches on this.

If you don't have access to phpMyAdmin and you can't use ssh, you're going to need ...

1. a client software package running on your developer machine, your mac.
2. your hosting service's network administrator to tweak their firewall to allow you to access your MySQL server from outside their internal network. They may not want to do this. But you should ask.

There are plenty of workable GUI-based MySQL clients for mac. You might look both at the MySQL and the MariaDB (a MySQL fork) web sites for good client software.

But, you might ask your hosting provider how to set up phpMyAdmin for your MySQL instance. That would be the easiest thing.