phpMyAdmin

From DreamHost
Jump to: navigation, search


Overview

phpMyAdmin is a free and open source website software package written in PHP intended to handle the administration of a database in a web browser.

Frequently used operations (managing databases, tables, columns, relations, indexes, users, permissions, and so on) can be performed via the user interface in addition to having the ability to directly execute any SQL statement.

DreamHost provides phpMyAdmin for customers on shared, VPS, DreamPress, and Dedicated server hosting options. This wiki helps you access your databases using the DreamHost-managed phpMyAdmin software.

Prerequisites

Before you use phpMyAdmin at DreamHost, you must complete the following steps:

  1. Set up a domain for full or mirrored hosting on the (Panel > ‘Domains’ > ‘Manage Domains’) page. Redirect, parked, and cloaking options may not function properly.
  2. Do not enable Passenger on a hosted domain if you wish to access phpMyAdmin, as it intercepts the HTTP requests and will show an error.
  3. Add a MySQL hostname on the (Panel > ‘Goodies’ > ‘MySQL Databases’) page.
  4. Allow enough time after adding both the hostname and full hosting for DNS to propagate.

Once you complete these steps, phpMyAdmin will be available on your domain.

How to access phpMyAdmin

Finding your MySQL credentials

Every domain/subdomain hosted at DreamHost has phpMyAdmin configured to use HTTP authentication against the database server.

To access phpMyAdmin, you must have the following four pieces of information (collectively referred to as your MySQL credentials):

    • database name
    • username
    • password
    • hostname

To locate your MySQL credentials:

  1. Open the (Panel > 'Goodies' > 'MySQL Databases') page.
    The MySQL Databases page opens with a listing of your hostnames and databases.
    02 phpMyAdmin db username.fw.png
    • Under the section titled ‘Database(s) on this server’ you’ll see a list of all of your databases.
    • To the right of the database, under the ‘Users with Access’ column is your MySQL username.
  2. Click the MySQL username. If more than one username is listed as having access to the database, you can pick any of them to log into that specific database.
    The users page opens:
    01 phpMyAdmin db user password.fw.png
  3. Click the Show button to obtain the user’s password.
    03 phpMyAdmin return button.fw.png
  4. On the top left of this page, click the Return to MySQL database listing button to return to the main ‘MySQL Databases’ page.
    04 phpMyAdmin hostnames.fw.png
    In the above image:
      • the first section on the MySQL Databases page lists your database hostnames.
      • it states “these are all interchangeable” – this means that you can access any of your databases on this account using any of your active hostnames.
      • both hostnames will work to open phpMyAdmin and connect to the database.

Logging in to phpMyAdmin

To open phpMyAdmin:

    05 phpMyAdmin hostname link.fw.png
  1. Click the hostname on your MySQL databases page. Or, copy/paste the URL of your MySQL hostname into your browser (e.g., mysql.domain.com).
    A username and password prompt appear.
    06 phpMyAdmin login prompt.fw.png
  2. Enter the username and password you retrieved using the preceding ‘Finding your MySQL credentials’ section, and then click OK.
    Note2 icon.png Note: If none of your hostnames are working, visit the following wiki on how to access the database before your DNS updates:


How to improve security

Important icon.png Warning: Every domain hosted at Dreamhost has phpMyAdmin configured to use HTTP authentication against the database server. Unless your site uses HTTPS (Secure HTTP), your username, password, and all data viewed through phpMyAdmin will be unencrypted and therefore viewable by others on the same network if they are using the right tools. See below for instructions on improving security.


Accessing with Secure Hosting

For access via Secure HTTP, your domain must have Secure Hosting.

If your domain already has Secure Hosting, you can then access phpMyAdmin through the full URL using HTTPS. For example, say the MySQL hostname you want to connect with is mysql.yourdomain.com. You can then use the following to connect securely to your database via phpMyAdmin:


https://www.yourdomain.com/dh_phpmyadmin/mysql.yourdomain.com/

If you are using a self-signed certificate rather than a professionally-signed certificate, you will see SSL warnings (such as 'connection is not private', 'problem with this website’s security certificate', or 'Connection is Untrusted', and so on). This is due to the domain name mismatch between the domain on the self-signed certificate (dreamhost.com) and your domain. Since you’re merely trying to secure your MySQL communications and are not directly working with an eCommerce application, you can safely ignore this warning and continue to phpMyAdmin. If you wish, you can also tell your browser to permanently store this exception so that you can access phpMyAdmin securely and without any future errors.

For details on how to create an SSL certificate for your domain, please visit the Secure Hosting wiki.

If you do not wish to set up secure hosting, you can still access your database securely from your shell account. For more information, see the following wikis:

Setting up an SSH tunnel and local phpMyAdmin install alternative

phpMyAdmin can be used to securely interact with your database if you install it locally and connect to your database though an SSH tunnel and forwarding a port, such as 3307, to your database on port 3306 (such as mysql.mydomain.com:3306). On Windows, this tunnel can be with PuTTY. On Linux, you can simply add port forwarding as an argument to the SSH command. After creating the tunnel, you simply point phpMyAdmin to 127.0.0.1:3307 to enable a secure connection to your data.

How to export/import MySQL tables

The following steps outline how to copy a table from one MySQL database to another, and backup and restore a database.

Exporting a table

  1. Log into phpMyAdmin.
  2. Select the source database on the left pane.
  3. Select the table you wish to export on the left pane, under the database name.
  4. Click on the Export tab on the top right pane.
  5. Towards the bottom of the page, put a checkmark in the Save as file checkbox. This allows you to download it as a file, instead of dumping it into your browser window.
  6. Click the Go button on the bottom right to export the table.

Importing a table

  1. Log into phpMyAdmin.
  2. Select the destination database on the left pane.
  3. Click on the Import tab on the top right pane.
  4. Click Browse and locate the file with the .sql extension you saved earlier.
  5. Click OK and then the Go button on the bottom right to import the table.

The newly-imported table will appear in the list of tables.

How to backup/restore MySQL databases

phpMyAdmin is better situated for backing up and restoring smaller (< 5MB) databases only because of browser, php and other timeouts. If you have a larger database please consider working with MySQL via your shell account or the Account Backup feature in the control panel.

Backing up a database

  1. Log into phpMyAdmin.
  2. Select the source database on the left pane.
  3. Click on the Export tab on the top right pane.
  4. Towards the bottom of the page, put a checkmark in the Save as file checkbox. This allows you to download it as a file, instead of dumping it into your browser window.
  5. Click the Go button on the bottom right to export the database.

Restoring a database

  1. Log into phpMyAdmin.
  2. Select the destination database on the left pane.
  3. Click on the Import tab on the top right pane.
  4. Click Browse and locate the file with the .sql extension you saved earlier.
  5. Click OK and then the Go button on the bottom right to import the database.

If you wish to drop/delete a database

You cannot drop a database on DreamHost using phpMyAdmin; you can only delete a database on the (Panel > 'Goodies' > 'MySQL Databases') page.

Making customizations for VPS/Dedicated admin users

If you have created an admin user on a VPS or Dedicated server, you can modify the default phpMyAdmin installation. It’s located in the /dh/web/phpmyadmin directory.

You can also install a custom theme for phpMyAdmin using the sudo command. The directory for installation is /dh/web/phpmyadmin/themes.

07 phpMyAdmin theme.fw.png
  • Once you’ve uploaded the theme files to the directory, select the theme in the Interface section of the phpMyAdmin homepage.

Error Messages

Cannot load session or mysql extension

This error generally occurs when PHP has been customized via a custom PHP.ini or phprc file, and it no longer has the extension loaded. Please revert the custom PHP change to restore functionality.

Missing parameter: what (FAQ 2.8) / Missing parameter: export_type (FAQ 2.8)

This error is caused by sessions having difficulty to write to the /tmp partition. Please ensure that you have not modified your /tmp on a VPS or Dedicated server from the default 1777 permissions. It can also be caused by the /tmp partition being full. You can check this with the df -h /tmp command. A full temp looks like the following example:

root@ps379292:~# df -h /tmp
Filesystem      Size  Used Avail Use% Mounted on
none            128M  128M     0 100% /tmp

See also