phpMyAdmin & MySQL, pt. 1

phpMyAdmin is a great web interface for people to use to administer a MySQL database. Thousands of websites and web hosts use MySQL as a database backend for storing data and creating dynamic websites. PHP supports MySQL connections by default. In this series, I am going to explain how to install phpMyAdmin and use some basic functions to administer your MySQL database.

Installing phpMyAdmin
Some web host give you access to phpMyAdmin in the control panel. If yours does, you can skip this part of the tutorial. This part is for those of you that either:

  1. have a web host that does not have phpMyAdmin
  2. have a web host with phpMyAdmin available but it is an older version
  3. have MySQL installed on your local machine, develop web pages on your machine and want an easy to use interface to admin your MySQL install
    • This assumes you have IIS or Apache and PHP installed on your local machine.

As of this writing, the latest stable release of phpMyAdmin is 2.6.3-pl1. You may want to download and install this release on your website, even if your host offers phpMyAdmin, because it offers many security updates, PHP 5 support and an increased file size upload of 8,192 Kb (8 Mb). Versions previous to 2.6.x had an upload size limit of 2,048 Kb (2 Mb).

  1. Download the file you can use according to your OS. They offer three file types:

    • bzip2
    • gzip
    • zip
  2. The files will extract into a directory named phpMyAdmin-2.6.3-pl1
  3. Edit the config.inc.php file with a text editor.
    • In Windows, Notepad will make php text run together. Use Wordpad or try out ConTEXT (free).

The following are the basic variables you will need to configure to use phpMyAdmin.

Note: All variables should start and end with a single quote (‘) except for TRUE and FALSE variables.

  1. $cfg['PmaAbsoluteUri']

    • Set this to the URI used for your phpMyAdmin install. If your website is http://www.mywebsite.com, and you upload the files into a directory called myadmin, the absolute URI will be http://www.mywebsite.com/myadmin. This is important because you may experience errors in phpMyAdmin if it is not set.
  2. $cfg['Servers'][$i]['host']
    • Set this to the hostname or IP address of your MySQL server. If it is running on your local machine, you can leave it set to the default ‘localhost’. Your web host can tell you the web address, or IP number, of the MySQL database install used for your hosting.
  3. $cfg['Servers'][$i]['port']
    • Only change this if your host, or local install, has MySQL server running on a port other than the default port (3306).
  4. $cfg['Servers'][$i]['compress']
    • Set this to True and communications to/from the MySQL server will be compressed. This may or may not increase the transfer rate.
  5. $cfg['Servers'][$i]['auth_type']
    • Leave this as config if you want to store the username/password in the config.inc.php file and configure the following:

      • $cfg['Servers'][$i]['user']

        • The username used to connect to your MySQL database server.
      • $cfg['Servers'][$i]['password']
        • The password corresponding to the username to connect to the MySQL database server.
    • Set this to http if you want to prompt for the DB username/password. If so, leave $cfg['Servers'][$i]['user'] and $cfg['Servers'][$i]['password'] empty.
  6. $cfg['Servers'][$i]['only_db']
    • If you only have one database on the MySQL server, you can put the name of the database here. This is optional. If you have multiple databases, all database names will show up in phpMyAdmin if this is left blank. If you type in a database name, only that database will show up in phpMyAdmin.
  7. $cfg['Servers'][$i]['AllowRoot']
    • Set this to FALSE to prevent the username ‘root’ from being able to use phpMyAdmin to access the MySQL database.

      • It is much wiser, from a security standpoint, to never use root (or sa in MS SQL) as the user for your app/website to connect to your DB server. You should only use those accounts to create a user with just enough privileges to perform hat needs to be done in the database.

All other variables can be left to their default settings. There are settings to allow you to control several MySQL servers in a single phpMyAdmin interface but this tutorial only covers a single MySQL installation.

Once all variables have been set, save the file. Upload all the files into the directory you specified as the absolute URI. Be sure to password protect this directory. If you don’t, anyone hitting this directory will be able to access your MySQL database!

Part 2 will cover using phpMyAdmin to perform a backup.

27.Jul.05 Tech Tip, phpMyAdmin

«
»

You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.

2 Responses to “phpMyAdmin & MySQL, pt. 1”

  1. Stephen Stagg | February 27th, 2006 at 8:17 am

    LaTeX is a hardcore typesetting tool for masochistic Scientists(mainly). The phpmysql admin output to LaTeX is useful if you want to automatically insert tables into latex documents. If you want to use this function on its own, you have to install latex, and then edit the .tex file produced by phpmyadmin to add:

    \documentclass[12pt]{article}
    \usepackage{longtable}
    \begin{document}

    at the top and:

    \end{document}

    at the bottom.
    then you can go: pdflatex myfile.tex
    to create a pdf of your data.

  2. wyckedone | February 27th, 2006 at 10:56 am

    Thanks for the info. :)

Leave a Reply