Sanjeev Sabhlok's notes on technology, hardware, gardening

Dreamhost’s MySQL VPS and VPS – some notes

Switched back to shared hosting since memory issues were more chronic on VPS mySQL

I agree with this commentator:

@Ipstenu-DH Yeap I can’t complain about the Shared Hosting, it worked really well. With some minor exceptions here and there… over all I can say DH’s Shared Hosting is quite good.
The site gets only 3000-5000 UV (unique visitors) per day on average. On Shared Hosting it worked well even with 30K per day. Actually I remember 2 years ago (2011) it even held up perfectly a record 75,000 in a single day + 70,000 the second day UV. It worked like a charm.

I barely get 1500+ visitors on all my sites, combined, so shared hosting should be perfectly fine.

KEY DIFFERENCE BETWEEN DREAMHOST AND OTHER VPSs

On Dreamhost, mysql databases are on a separate server (a typical shared MySQL allows up to 300 connections at one time – which should be fine in most cases).

Even with VPS (called “file VPS”) your mysql databases will still be on shared hosting (unless you install MySQL server on your VPS and move your databases to it). Shared MySQL databases are included for free with web (file) VPSes.

Question: Can I run my own mysql server on the same VPS server as my websites?

No. You can continue to use shared MySQL servers if you have VPS OR you can move your MySQL databases to a separate MySQL VPS.  [BOTH cost separately]

Dreamhost doesn’t support running a database server on your web VPS. You can install MySQL yourself using an admin user, but it won’t be available through the Panel.

VPS MYSQL

“Add a VPS MySQL Server” on dreamhost

DreamHost offers “MySQL VPS” that costs $15 a month (minimum). Source

http://wiki.dreamhost.com/DreamHost_PS_MySQL#Adding_MySQL_VPS

MySQL VPS – These databases are hosted on a MySQL service isolated from other customers. RAM is guaranteed for the customer. MySQL VPS is optimized with database query caching. About three quarters of VPS MySQL Dreamhost customers run at 300 MB.

[DreamHost PS MySQL uses Linux-VServer to offer an isolated database server.This is different from the standard shared MySQL server which an account uses by default, as the shared MySQL server shares other users databases and resources. With the PS MySQL, your databases and resources are protected from other users in the same was a web private server is.] VPS MySQL service starts at $15/month, which includes 300MB RAM and unlimited disk space. The most expensive plan is $200/month, which includes 4000MB RAM. When you activate VPS MySQL for the first time, you receive a one week trial. During the trial period, the amount of RAM is set at 2300MB RAM. When the free trial ends, the RAM adjusts to your site’s needs.  [Source]

My initial conclusion: Given the technical complexity and challenge of installing MySQL on one’s own VPS, it is not necessary to get a separate VPS. managed SQL VPS is good enough.

RUNNING MYSQL ON THE VPS SERVER ON DREAMHOST

If you get VPS you should create your own SQL server on the VPS, else you’ll continue to pay for shared SQL.

Source (for info below)

You can run your own MySQL server on DreamHost’s VPS.

  1. Create an admin user for your VPS that has sudo abilities, and log into your VPS with that through ssh.
  2. Tweak apt so you can install the mysql-server package. 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”;

  1. Then, install the mysql-server package:
  2. sudo apt-get install mysql-server;

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

  1. 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.)

  1. 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.

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’);

Print Friendly

sabhlok

View more posts from this author
One thought on “Dreamhost’s MySQL VPS and VPS – some notes

Leave a Reply

Your email address will not be published. Required fields are marked *