KB / Web Programming / CGI, PHP, and Databases / MySQL

From DreamHost

Jump to: navigation, search

MySQL is a SQL (Standard Query Language) database engine that is well-suited for most web sites. We include unlimited MySQL database of all of our plans! The Official Site for MySQL has a lot of valuable information about it.

You may add, remove, and manage your MySQL database(s) from our DreamHost Web Panel.

Web based administration for all MySQL databases!
You can now access all MySQL databases with us via a web interface (php MyAdmin)! Simply go to your database hostname on the web! So if your MySQL host was mysql.mydomain.net, just point your browser to http://mysql.mydomain.net/ Please keep in mind that if you just created your MySQL hostname, that new domain name will have to propagate around the internet just like any other. This can take up to 3 days, depending on how cool your local ISP is.

Contents

Parent Article

Sub-Articles

Why can't I connect to local MySQL?

Because, unlike many webhosts, we don't run MySQL on our web servers.. they all run on separate database servers (unless you have your own dedicated server of course).

Therefore, whenever you connect to MySQL, whether it be from the command line or a website, you have to specify the MySQL hostname you chose when creating your database. If you don't specify a hostname, or use "localhost", you'll end up trying connecting to your web server instead of the correct database server, and get an error like "Can't connect to local MySQL server through socket...".

What this means is, any time you see "localhost" in a script's database settings, you must change it to the MySQL hostname you set up (maybe something like mysql.yourdomain.com). Also, anytime you run the mysql command line command, you're going to have to add a -h mysql.yourdomain.com argument to it!

How do I manage my mysql database?

There are multiple ways..
  1. By default we install "phpMyAdmin" for all MySQL databases, which gives a web-based interface to your tables and data at your database's hostname on the web (probably something like http://mysql.yourdomain.com/ ... you choose it yourself when setting up the db). You can also get to "phpMyAdmin" by looking at DreamHost Panel->Goodies->MySQL
  2. You can from a shell prompt use mysql's command line interface too. just type mysql -h mysqlhostname.domain.com -u user -p dbname to connect.
  3. There are other ways with things like MS Access and client applications you can install on your home computer, but they aren't supported by us (though work if you know how to configure them yourself!). Note that if you're connecting from an IP that's not hosted with us (like say, your home or office) you'll need to add it through our web panel as an allowable IP to access your database!

How do I dump data into MySQL?

There are three ways! The first two are done via the unix shell prompt, with one of these two commands:
  1. mysqlimport -L -hHOSTNAME -uUSER -pPASSWORD DATABASE DUMPED_TEXTFILE
  2. mysql -hHOSTNAME -uUSER -pPASSWORD DATABASE < DUMPED_TEXTFILE
Of course make sure to replace all the relevant info above with your correct info!


The third way is to use the "phpMyAdmin" tool at DreamHost Panel->Goodies->MySQL and select "import". This method is limited to about 10 MB, so its only good for small databases.
As for getting the DUMPED_TEXTFILE in the first place, see the next question. Note, we don't offer direct access to the MySQL datafiles in mysql/data because they reside on dedicated MySQL servers).

How do I dump data out of MySQL?

There are several ways! The first two are done via the unix shell prompt, using the mysqldump command.

  1. To dump the entire database
    mysqldump -hHOSTNAME -uUSER -pPASSWORD DATABASE > DUMPED_TEXTFILE
  2. To dump the entire database while compressing it 90%
    mysqldump -hHOSTNAME -uUSER -pPASSWORD DATABASE | zip > DUMPED_TEXTFILE.ZIP
  3. To dump just a few tables
    mysqldump -hHOSTNAME -uUSER -pPASSWORD DATABASE TABLENAME_1 TABLENAME_2 > DUMPED_TEXTFILE
  4. Use the "phpMyAdmin" tool at DreamHost Panel->Goodies->MySQL and select "export".
Of course make sure to replace all the relevant info above with your correct info!


Also, note that you cannot use the -T or --tab option in mysqldump because the database is on a different host.

If you enecounetr problesm/errors with the above approaches, try this via the unix command line:
mysql -u USERNAME -p -h FULLHOSTNAME DBNAME
You'll be prompted for your password, but once logged in to mysql type:
source PATH/TO/FILENAME.sql

How do I delete a database?

  1. Log in to the Account Control Panel, select the Goodies tab, and click on the MySQL link.
  2. Click on the Delete link beneath your database's details.
  3. Use this checkbox to confirm deletion: <img src = "./images/illustrations/yesdelete.gif">
  4. Press the Remove Database button, and you're done!
From the command prompt, you can first connect to your database using this command:

mysql -u username -p -h mysql.hostname.com database_name

Then just enter the command "drop database database_name" and you're done!

How do I add users to my database?

It's easy!
  1. Simply log on to the Account Control Panel, select the Goodies tab, then click on the MySQL link.
  2. Click the Add User link beneath your database's details.
  3. Give your user a name and a password: <img src = "./images/illustrations/dbadduser.gif">
  4. Press the Add button, and you're done!

How do I designate a host name for my MySQL database?

  1. Go to the "Goodies > MySQL" area of our web panel.
  2. Click on the Modify link for your database.
  3. Enter the hostname you'd like to use for your MySQL db and click "Modify hostname now!"
The change should take about 5-10 minutes to happen.

Due to a limitation in our system you cannot, for now at least, have more than one MySQL database per hostname.

How do I add a new MySQL database?

  1. Simply log into the Account Control Panel, select the Goodies tab, and click on the MySQL link.
  2. Click the Add New Database link.
  3. Give your database a name. The name may contain only letters, decimal numbers, and the characters $ and _ and must be unique across our entire system. (consider maybe naming your db yourwebid_dbname) <img src = "./images/illustrations/namedb.gif">
  4. Choose the service with which the database will be associated: <img src = "./images/illustrations/associatedb.gif"> Then press the Continue button.
  5. That's it! You're done, Baby!

How do I find my MySQL socket file?

  1. Log into your account via SSH.
  2. Navigate to your root directory with this classic UNIX command: </
  3. cd /
  4. Search your server for the MySQL configuration file named my.cnf with the UNIX font color=purple>find</find> command. Like so:
  5. find ./ -name my.cnf
  6. Now find the result that shows the file and it's path. Mine was here: ./etc/mysql/my.cnf
  7. View the file with your favorite editor, perhaps like this:
  8. vi /etc/mysql/my.cnf
  9. now you should be able to scroll up and down to find the socket entry in that file. Mine looked like this:
  10. socket = /var/run/mysqld/mysqld.sock
  11. If you don't know the Vim Editor then press ESC for good measure and then type ":q" then hit enter
  12. Now you can use your new knowledge however you please!

    13. (Check out http://unix.t-a-y-l-o-r.com/Vi.html for a great VI introduction and reference.)

Retrieved from "http://wiki.dreamhost.com/KB_/_Web_Programming_/_CGI%2C_PHP%2C_and_Databases_/_MySQL"
Personal tools