Author: Joe 'Zonker' Brockmeier
You may have access only to phpMyAdmin if you’re using a hosting provider that doesn’t give you shell access to work with MySQL. Either way, phpMyAdmin can cut down on the amount of time it takes to administer MySQL databases or create new ones.
If you’re running a Web site through a hosting provider that uses cPanel, Plesk, or another control panel, it’s a good bet that you already have phpMyAdmin installed — though it may not be branded as such. If not, the path of least resistance is to install phpMyAdmin from packages from your vendor. If you’re running Debian or Ubuntu, just run apt-get install phpmyadmin. You’ll have a symlink for phpmyadmin set up under /var/www, and you can configure an Apache alias to reach phpMyAdmin. For example:
Alias /phpmyadmin/ /var/www/phpMyAdmin-2.8.2/ Alias /phpmyadmin /var/www/phpMyAdmin-2.8.2/ <Directory /var/www/phpMyAdmin-2.8.2/ > Options Indexes FollowSymLinks MultiViews AllowOverride None Order deny,allow Deny from all Allow from [ip address]
So, if your site is www.acmeco.com, you would be able to reach phpMyAdmin at www.acmeco.com/phpmyadmin/, as long as your IP address is included in the Allow from directive.
If your distro doesn’t provide a phpMyAdmin package, or you’d like a newer version, grab the tarball from the download page and uncompress it in the directory where you’d like it to live.
You can configure phpMyAdmin in two ways. First, you can hand-edit the configuration file, config.inc.php. If you’re configuring phpMyAdmin on a Debian or Ubuntu system from packages, it’s /etc/phpmyadmin/config.inc.php. Otherwise, look for a sample configuration file, called config.default.php, under the libraries directory. Open it in your favorite editor and tweak the settings to match your system. At a bare minimum, you need to set the hostname, username, password, and the method for authentication. Unfortunately, the configuration file isn’t the most readable file in the world, but these are the lines that you’re looking for:
$cfg['Servers'][$i]['host'] = 'localhost'; $cfg['Servers'][$i]['user'] = ''; $cfg['Servers'][$i]['password'] = ''; $cfg['Servers'][$i]['auth_type'] = 'auth_type';
For almost every MySQL install, the host configuration should be set to localhost. The user and password directives should be set only if you’re using the config authentication method — which basically means that anyone who loads the phpMyAdmin page will be authenticated as the user, so you’d want to use a second means of authentication, such as htaccess, or make sure you’re running phpMyAdmin on a secure network. For instance, if you’re running phpMyAdmin on your workstation to do MySQL development, it should be just fine to use the config method. Otherwise, you probably want to use the http method for auth_type and make sure to log in to phpMyAdmin over SSL.
The other way is to run the setup.php script that’s provided under the scripts directory. You’ll need to be able to write to the config directory under the main phpMyAdmin directory to use the setup script, or run the script and save it to your computer and then upload it to the phpMyAdmin directory. Again, the only directives that absolutely need to be configured are the hostname, authentication type, and username and password if you’re using the config authentication type.
For information about all of the configuration parameters, see the phpMyAdmin documentation.
Getting started with phpMyAdmin
Once you have phpMyAdmin installed, you can start creating and managing your MySQL databases. The main phpMyAdmin page has links to all of the operations you’re likely to want to use right away. If you want to see the MySQL runtime or system information, running processes, or the MySQL logfile, they’re just a click away.
Let’s start with creating a database. On the right side of the phpMyAdmin interface, you’ll see a link that says Create new database. All you need to do is enter the name of the database you’d like to create, select the character set and collation, and click Create. The odds are you can just accept the default for character set and collation.
After you create the database, phpMyAdmin will provide a success message and show you the SQL statement it used to create the database. Here’s one of the nifty things about phpMyAdmin — it not only makes it easy to manage MySQL, but it also usually shows you what it does, so you can pick up some of the syntax for MySQL commands just by using the phpMyAdmin interface and seeing what commands it issues.
You’ll also notice a message at the bottom of the database page indicating there are no tables found in your database, and phpMyAdmin will display a Create new table dialog. Since you’ll want at least one table in your new database, give the name of the new table and the number of fields you want in the new table, and select Go.
At this point, phpMyAdmin will take you to another page where you can define the fields in your new table, as well as the storage engine to be used by MySQL. Note that your MySQL installation has to have the storage engine support compiled in, so you may not be able to choose some storage types if they’re not compiled into your MySQL install. For example, on my Ubuntu 6.06 LTS system, BerkeleyDB and NDB support are not compiled in, so these options aren’t available — but MyISAM, InnoDB, and several others are.
As you can see, phpMyAdmin makes it easy to set up the fields in your table — just enter the field name and configure the other attributes for the fields. Once you’ve done that, save the fields or enter a number and click “Go” to add more fields for the table. Once again, after you’ve created the table, phpMyAdmin will show a success message and display the SQL query that was run to create the table and its fields.
Rinse and repeat as needed until you’ve created all of the tables you want for your database. Now it’s time to insert some data.
Next: Inserts
To insert data into your database, just go to the databases tab in phpMyAdmin and select the database you want to work with. From the database page, you’ll see all of the tables in the database, and next to the tables, several icons for actions that can be performed on the tables; in order, these are Browse, Structure, Search, Insert, Empty, and Drop. Some of the icons may be grayed out if the table has no data.
To start inserting data, select Insert and enter the values you want into each field, then select Go at the bottom of the page. Pretty simple, no? Again, phpMyAdmin will display the SQL query that it used to insert the data. What’s even niftier is that phpMyAdmin will show you the PHP code you’d need to insert the data — so phpMyAdmin can be pretty handy as you’re developing a PHP/MySQL application as you’re prototyping the application; you can just grab the PHP statements to insert code into the database, add variables instead of the literal data that you inserted manually, and go from there.
Constructing search queries
If you need to run a query against your MySQL database, phpMyAdmin offers a few ways to go about it. First, you could just go to the SQL tab and paste the query into the Run SQL query/queries box and run it from there — if you already know the SQL syntax for the query you want to run.
Another way to do it is to go to the Structure tab, then select Search for the table you want to search. This will take you to the Search page, which will allow you to construct a query for that table with minimal effort. On the search page, you’ll see a Select fields box on the left side, where you can select one or more fields from the table to run your query against. On the upper right side, you can set the number of rows per page (the default is 30) and a box to set the display order. Finally, there’s a box to add the “where” clause of the search.
Alternately, you can do a “query by example” on the Search page. At the bottom of the right side, you can choose one or more fields to search by — phpMyAdmin gives you a drop-down menu of valid operators and a text box for search strings.
If you want to search one or more tables, select the Search tab and enter the search value and select the table(s) you want to search.
You might, on occasion, need to run a SQL statement directly. You can do this at the server level, database level, or table level. For example, to run a SQL statement just by cutting and pasting it into phpMyAdmin, click the SQL button in the left side of the phpMyAdmin interface from the home phpMyAdmin page with no databases selected. This will give you a new phpMyAdmin window with three tabs: SQL, Import Files, and SQL History. You can paste your SQL query into the SQL tab, or select Import Files and you can upload a text file containing a SQL query.
Dumping and restoring databases
If you need to make a backup of your database, you can do so by going to the main phpMyAdmin page and selecting Export, or select the Export tab while you’re in the database admin screen for a specific database.
On the export page, you’ll be able to select the database(s) that you want to create a dump for, and select the format that you want to export the database in. This will usually be SQL, but phpMyAdmin also offers CSV, LaTeX, XML, and a few other formats.
If you’re downloading the file from a remote host, you’ll probably want to compress the file to make the download move along a bit faster — particularly if you’re still on dial-up. At the bottom of the Export page you’ll see a compression option — none, zipped, gzipped, or bzipped. Choose the compression format you prefer and click Go.
Note that’s it’s not necessary to export an entire database — you can select only the tables that you want to export from a database, which might be the way to go when working with large databases.
To import or restore a database, select Import from main phpMyAdmin page, or the Import tab from the database page. Note that phpMyAdmin is limited by PHP’s maximum file upload size, memory limit, and maximum post size. If upload_max_filesize is limited to 2MB in php.ini, for example, you won’t be able to import a 2.5MB text file.
I’ve also known phpMyAdmin to have problems with importing and exporting larger databases, regardless of the PHP limits. If you’re working with MySQL databases that are in the hundreds of megabytes (or larger), it’s best to use the MySQL command line tools to deal with imports and exports. (This may sound like common sense, but I have run into users who insist on trying to do dumps or restores of huge databases using phpMyAdmin out of fear of having to learn a few simple MySQL commands.)
You can also copy a database to a new database if you need to, which might be a good option if you’re doing work on a database and want to work on a copy rather than the original. To make a copy, go to the Operations tab from the database page. Here you’ll find a “Copy database to” field section, with a field for the name of the new database. Just give the name of the new database, and select CREATE DATABASE before copying. If you want to start working on the new database immediately after, select the Switch to copied database checkbox.
Managing users and privileges
Finally, let’s take a look at how phpMyAdmin can help you manage your users and their privileges. You can start managing users by going to the Privileges page from the main phpMyAdmin window. This will take you to the User overview page, where you’ll see all of the current users, what privileges they have, and whether they have a password set.
From here you can go to the edit privileges page to modify a user’s privileges, remove users, or add a new user. Note that phpMyAdmin does not prompt for confirmation before deleting a user, so be careful in removing users.
On the Add a new User page, you can define the new user and the user’s privileges and resource limits. Once the user is added, you can go to the Edit Privileges page and grant the user privileges to specific databases, modify the user’s password, and even copy the user to another username — which is useful if you’ve set up specific privileges for one user and want to re-use those permissions on another database for another user.
From the database window, selecting the Privileges tab will show you what users have access to the database, and you can edit those users’ privileges for that database, but you cannot create new users or grant existing users access to that database if they don’t have any access to it already.
Checking and repairing tables
If you’re unfortunate enough to have a corrupt table, or if you just want to analyze a table for errors, you can do so easily from the phpMyAdmin interface. Go to the database and select the table you want to want to work with. On the table page, select the Operations tab, and at the bottom of that page, you’ll see a Table maintenance menu.
From the maintenance menu, select Check table. If you get a status of OK, all’s well. If you don’t, go back to the maintenance menu and select Repair table. This will run the REPAIR TABLE command against the table, which usually fixes most basic problems you’ll run into with a MySQL table.
Of course, this is just an overview of some of the most common operations that you’ll want to use phpMyAdmin for. If you’d like to learn even more about phpMyAdmin, be sure to check out the documentation and FAQ on the phpMyAdmin Web site. If you spend any time at all working with MySQL databases, I’d recommend giving phpMyAdmin a shot.
