Using Dreamhost’s VPS as a MySQL Server

[Editor’s Note: Dreamhost no longer allows sudo access on their VPS servers, so the below is no longer possible. If you’re reading this, you might want to check out Amazon’s AWS. Nowhere near as user friendly as Dreamhost, but MUCH more powerful.]

I’ve been a happy customer of DreamHost for many years now — for $9.95 a month, I was able to get loads of diskspace and unlimited bandwidth, all from a great company that was staffed by great people who were very technically competent.

It is “shared hosting,” however — that means you’re getting it that cheap because you’re sharing server CPU power, available RAM, etc, with many other people who are also getting a great deal. You’ll notice this in any WordPress sites you may run on Dreamhost’s shared hosting — there’s about a 7-10 second delay between when you navigate to your site and when your page actually loads, mostly because your server processes are waiting in line behind everyone else’s.

So, Dreamhost does offer a “VPS” service — with this, you’re getting an absolute amount of RAM, CPU power, etc, that will only be used for your sites. It’s a tiny bit more expensive, at $14.95 or so a month, but it’s worth every penny.

If you’ve run WordPress installations on a Dreamhost site, though, you know that your mysql databases are on a seperate server entirely, and that may slow things down as well. Even if you move your WordPress installs to a VPS, your mysql databases will still be on shared hosting. DreamHost offers a “MySQL VPS,” but it costs another $15 a month (minimum), and you have no control over it at all.

A better solution would be to run your own MySQL server on DreamHost’s main VPS, since they give you root command-line access. There are a few hiccups in this process (some put there by DreamHost itself), but otherwise you should be able to do it.

  1. First, create an admin user for your VPS that has sudo abilities, and log into your VPS with that through ssh.
  2. Second, you have to tweak apt so you can even install the mysql-server package. It appears as if part of installing packages through apt involves temporarily storing files in /tmp and then running them from there.Unfortunately, the /tmp directory is mounted on DreamHost’s VPS servers with the noexec option, which means that you can’t run files that are present in that directory. That basically prevents you from installing the mysql-server package until you tweak apt to temporarily stage files in /var/tmp instead. Do this by:Creating a file called apt.conf in the /etc/apt/ directory, and edit it so the contents are the following:
    APT::ExtractTemplates::TempDir "/var/tmp";
  3. Then, install the mysql-server package:
    sudo apt-get install mysql-server;
    

    (When it asks to set a root password, make sure and set one.)

  4. Now, edit the file /etc/mysql/my.cnf and set the following options:
    bind-address=psXXXXX.dreamhostps.com

    (Replace psXXXXX with the name of your dreamhost VPS.)

  5. Restart your mysql service:
    sudo service mysql restart

At this point, you should be able to log in to your new mysql server:

mysql -u root -p

and then perform what SQL functions you need to.

A great thing to do is to install phpmyadmin using the tutorial here: http://wiki.phpmyadmin.net/pma/Quick_Install

First you’ll want to create a user (that isn’t your root user) to log into phpmyadmin:

mysql> CREATE USER ‘newusr’@’%’ IDENTIFIED BY ‘your_password';
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘newusr’@’%’ WITH GRANT OPTION;

At this point, you can sync your old databases to your new mysql server using the built-in sync tool that’s in DreamHost’s installations of phpmyadmin.  Then, just edit the wp-config.php file in the folder of your WordPress installations, and change the line that says the following to your DreamHost VPS:

define('DB_HOST', 'psXXXXXX.dreamhostps.com');

Sources:

http://howtogetitworking.com/2011/06/28/installing-mysqld-dreamhost-vps/

https://bugs.launchpad.net/ubuntu/+source/debconf/+bug/90085

http://serverfault.com/questions/72356/how-useful-is-mounting-tmp-noexec

 

 

  • xboredx

    So I’ve done this and have the mysqld installed – however phpmyadmin can never connect to the localhost and be able to see the installation.. ideas?

  • Don’t know — getting phpmyadmin to connect to the server once it was finally started was one part I didn’t have a problem with. :P My major issues were with getting the mysql server to run, at all.

  • xboredx

    have it running – had to upload the sql file directly and import it via the command line – currently unable to use phpmyadmin on my host – most likely due to my error or their setup..

    Attempted the synchronize from servers own phpmyadmin – and was able to sync however it failed to sync several tables – so doing it via CLI appears to have worked.

  • xboredx

    actually have it fully functional now and working with dreamhostvps..
    also have phpmyadmin working as well.

    had to use the servername in phpmyadmin/setup then save the config to the top level / root of phpmyadmin directory – seemed to have missed that the first time around..

    Either way all on one server at the moment and working!

  • xboredx

    working now – still slow – even have a ram bump from the base but I think the issue is the vps has a bunch of other sites on it not allowing the primary site the power it needs…

  • I have been evaluating to get a VPS from Dreamhost, my main concern was the database – I do not run anything fancy but the database was a concern to me – this article helped me to decide as I do not see myself paying extra money for a MySQL VPS that I will have little to no control.

    Excellent.

  • V’been contemplating this for a few days now after trying the mysql VPS , and only today did i notice your edit about no sudo . . .
    After some reading around – im just sad.