Administrating the MySQL Server with phpMyAdmin

Exclusive offer: get 50% off this eBook here
Mastering phpMyAdmin 3.3.x for Effective MySQL Management

Mastering phpMyAdmin 3.3.x for Effective MySQL Management — Save 50%

A complete guide to get started with phpMyAdmin 3.3 and master its features

€18.99    €9.50
by Marc Delisle | October 2010 | MySQL Open Source PHP

This article, Marc Delisle, author of Mastering phpMyAdmin 3.3.x for Effective MySQL Management, discusses how a system administrator can use the phpMyAdmin server management features for day-to-day user account maintenance, server verification, and server protection. The subject of how non-administrators can obtain server information from phpMyAdmin is also covered. Server administration is mostly done via the Server view, which is accessed via the menu tabs available on phpMyAdmin's home page.

Server administration is mostly done via the Server view, which is accessed via the menu tabs available on phpMyAdmin's home page.

This article specifically covers:

  • Managing users and their privileges
  • Database information
  • Server information

 

Mastering phpMyAdmin 3.3.x for Effective MySQL Management

Mastering phpMyAdmin 3.3.x for Effective MySQL Management

A complete guide to get started with phpMyAdmin 3.3 and master its features

  • The best introduction to phpMyAdmin available
  • Written by the project leader of phpMyAdmin, and improved over several editions
  • A step-by-step tutorial for manipulating data with phpMyAdmin
  • Learn to do things with your MySQL database and phpMyAdmin that you didn't know were possible!
        Read more about this book      

(For more resources on PHP, see here.)

Managing users and their privileges

The Privileges subpage (visible only if we are logged in as a privileged user) contains dialogs to manage MySQL user accounts. It also contains dialogs to manage privileges on the global, database, and table levels. This subpage is hierarchical. For example, when editing a user's privileges, we can see the global privileges as well as the database-specific privileges. We can then go deeper to see the table-specific privileges for this database-user combination.

The user overview

The first page displayed when we enter the Privileges subpage is called User verview. This shows all user accounts and a summary of their global privileges, as shown in the next screenshot:

From this page, we can:

  • Edit a user's privileges, via the Edit link for this user
  • Use the checkboxes to remove users, via the Remove selected users dialog
  • Access the page when the Add a new User dialog is available

The displayed users' list has columns with the following characteristics:

Privileges reload

At the bottom of User Overview, the following message is displayed:

Note: phpMyAdmin gets the users' privileges directly from MySQL's privilege tables. The content of these tables may differ from the privileges the server uses, if they have been changed manually. In this case, you should reload the privileges before you continue.

Here, the text reload the privileges is clickable. The effective privileges (the ones against which the server bases its access decisions) are the privileges that are located in the server's memory. Privilege modifications that are made from the User overview page are made both in memory and on disk, in the mysql database. Modifications made directly to the mysql database do not have immediate effect. The reload the privileges operation reads the privileges from the database and makes them effective in memory.

Adding a user

The Add a new User link opens a dialog for user account creation. First, we see the panel where we'll describe the account itself:

The second part of the Add a new User dialog is where we'll specify the user's global privileges, which apply to the server as a whole.

Entering the username

The User name menu offers two choices. Firstly, we can choose Use text field and enter a username in the box, or we can choose Any user to create an anonymous user (the blank user). Let's choose Use text field and enter bill.

Assigning a host value

By default, this menu is set to Any host, with % as the host value. The Local choice means "localhost". The Use host table choice (which creates a blank value in the host field) means to look in the mysql.hosts table for database-specific privileges. Choosing Use text field allows us to enter the exact host value we want. Let's choose Local.

Setting passwords

Even though it's possible to create a user without a password (by selecting the No password option), it's best to have a password. We have to enter it twice (as we cannot see what is entered) to confirm the intended password. A secure password should have more than eight characters, and should contain a mixture of uppercase and lowercase characters, digits, and special characters. Therefore, it's recommended to have phpMyAdmin generate a password—this is possible in JavaScript-enabled browsers. In the Generate Password dialog, clicking on Generate enters a random password (in clear text) on the screen and fills the Password and Re-type input fields with the generated password. At this point, we should note the password so that we can pass it on to the user.

Understanding rights for database creation

A frequent convention is to assign a user the rights to a database having the same name as this user. To accomplish this, the Database for user section offers the checkbox Create database with same name and grant all privileges. Selecting this checkbox automates the process by creating both the database (if it does not already exist) and the corresponding rights. Please note that, with this method, each user would be limited to one database (user bill, database bill).

Another possibility is to allow users to create databases that have the same prefix as their usernames. Therefore, the other choice, Grant all privileges on wildcard name (username\_%), performs this function by assigning a wildcard privilege. With this in place, user bill could create the databases bill_test, bill_2, bill_payroll, and so on; phpMyAdmin does not pre-create the databases in this case.

Assigning global privileges

Global privileges determine the user's access to all databases. Hence, these are sometimes known as "superuser privileges". A normal user should not have any of these privileges unless there is a good reason for this.

Of course, if we are really creating a superuser, we will select every global privilege that he or she needs. These privileges are further divided into Data, Structure, and Administration groups.

In our example, bill will not have any global privileges.

Limiting the resources used

We can limit the resources used by this user on this server (for example, the maximum queries per hour). Zero means no limit. We will not impose any resource limits on bill.

The following screenshot shows the status of the screen just before hitting Go to create this user's definition (with the remaining fields being set to default):

Editing a user profile

The page used to edit a user's profile appears after a user's creation, or whenever we click on Edit for a user in the User overview page. There are four sections on this page, each with its own Go button. Hence, each section is operated independently and has a distinct purpose.

Editing privileges

The section for editing the user's privileges has the same look as the Add a new User dialog, and is used to view and change global privileges.

Assigning database-specific privileges

In this section, we define the databases to which our user has access, and his or her exact privileges on these databases.

As shown in the previous screenshot, we see None because we haven't defined any privileges yet. There are two ways of defining database privileges. First, we can choose one of the existing databases from the drop-down menu:

This assigns privileges only for the chosen database. We can also choose Use text field and enter a database name. We could enter a non-existent database name, so that the user can create it later (provided that we give him or her the CREATE privilege in the next panel). We can also use special characters, such as the underscore and the percent sign, for wildcards.

For example, entering bill here would enable him to create a bill database, and entering bill% would enable him to create a database with any name that starts with bill. For our example, we will enter bill and then click on Go.

The next screen is used to set bill's privileges on the bill database, and create table-specific privileges.

To learn more about the meaning of a specific privilege, we can move the mouse over a privilege name (which is always in English), and an explanation about this privilege appears in the current language. We give SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, and DROP privileges to bill on this database. We then click on Go.

After the privileges have been assigned, the interface stays at the same place, so that we can refine these privileges further. We cannot assign table-specific privileges for the moment, as the database does not yet exist.

To go back to the general privileges page of bill, click on the 'bill'@'localhost' title.

This brings us back to the following, familiar page, except for a change in one section:

We see the existing privileges (which we can Edit or Revoke) on the bill database for user bill, and we can add privileges for bill on another database. We can also see that bill has no table-specific privileges on the bill database.

Changing the password

The Change password dialog is part of the Edit user page, and we can use it either to change bill's password or to remove it. Removing the password will enable bill to login without a password. The dialog offers a choice of password hashing options, and it's recommended to keep the default of MySQL 4.1+ hashing. For more details about hashing, please visit http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html

Mastering phpMyAdmin 3.3.x for Effective MySQL Management A complete guide to get started with phpMyAdmin 3.3 and master its features
Published: October 2010
eBook Price: €18.99
Book Price: €30.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on PHP, see here.)

Changing login information or copying a user

This dialog can be used to change the user's login information, or to copy his or her login information to a new user. For example, suppose that Bill calls and tells us that he prefers the login name billy instead of bill. We just have to add a y to the username, choose Local as the host, and select delete the old one from the user tables:

After this operation, bill no longer exists in the mysql database. Also, all of his privileges, including the privileges on the bill database, will have been transferred to the new user—billy. But the user definition of bill will still exist in memory, and hence it's still effective. If we had chosen the delete the old one from the user tables and reload the privileges afterwards option instead, the user definition of bill would immediately have ceased to be valid.

Alternatively, we could have created another user based on bill, by making use of the keep the old one choice. We can transfer the password to the new user by choosing Do not change the password, or change it by entering a new password, twice. The revoke all active privileges... option immediately terminates the effective current privileges for this user, even if he or she is currently logged in.

Removing a user

Removing a user is done from the User overview section of the Privileges page. We select the user to be removed. Then (in Remove selected users) we can select the Drop... option to remove any databases that have the same name as the user we are deleting. A click on Go effectively removes the selected users.

Database information

The Databases subpage is intended to quickly get privileges information for each database. Optionally, it can also be used to obtain global statistics on these databases, without having to click on each database in the navigation panel. When we enter the Databases subpage, we see the list of existing databases:

We also see an Enable Statistics link. By default, statistics are not enabled because computing the size of data and indexes for all the tables in all of the databases may consume valuable MySQL server resources.

Enabling statistics

If we click on the Enable Statistics link, a modified page appears. For each database, we get the default collation for tables in this database, along with the number of tables in the database and the total number of rows for all tables. Next, information about the space used by the data portion of the tables is given, followed by the space taken by all indexes and total space for all tables. Finally, the space that could be reclaimed by optimizing some tables in this database is presented under Overhead.

Sorting statistics

By default, the statistics list is sorted by database name in ascending order. If we need to find the database with the most tables or the database that takes the most space, a simple click on the Tables or Total column header sorts the list accordingly. A second click reverses the sort order.

Checking the database privileges

Clicking on the Check Privileges icon displays all of the privileges on a specific database. A user's global privilege might be shown here, as it gives him or her access to this database as well. We can also see the privileges specific to this database. An Edit link takes us to another page, which is used to edit the user's privileges.

We notice that this panel also contains this link: Add a new User. Clicking on this link is a convenient way of creating a user that has privileges to the database we are currently examining. Indeed, after entering the user creation panel from this link, a fourth choice in the database creation or privileges granting dialog is shown and selected by default:

Dropping selected databases

To drop one or more databases, we select the checkboxes next to the names of the databases to be dropped, and then click on the red X next to With selected. We then get a confirmation screen. Two of the databases (mysql and the virtual information_schema) cannot be selected, the first one to avoid making a big mistake and deleting all of our accounts, and the second one cannot be selected as this is not a real database.

This is an operation that should not be taken lightly, and it might be prudent to first export the whole database as a backup.

Server information

The Status, Variables, and Processes menu tabs can be used to get information about the MySQL server, or to act upon specific processes.

Verifying server status

The server status statistics reflect the MySQL server's total activity, including (but not limited to) the activity generated by queries sent from phpMyAdmin.

The general status page

Clicking on the Status link produces runtime information about the server. The page has several sections. First, we get information about the elapsed running time and the startup time. Then we get the total and average values for traffic and connections (where the ø means average).

Next, the statistics about the queries are displayed (shown in part here). The average number of queries per hour, minute, and second give a good indication of the server load.

The query statistics are followed by statistics about each MySQL command, including the absolute number of times each command has been executed, hourly average, and the number of times run as a percentage of the total. The presentation order is by descending percentage of utilization: here we see that the SELECT statement is the one most received by this server, with 30.17%:

Depending on the MySQL version, many other sections containing server information are also displayed.

InnoDB status

On servers supporting InnoDB, a link appears at the end of the InnoDB section. When this link is clicked, information about the InnoDB subsystem is displayed, including information about the last InnoDB error that occurred.

Server variables

The Variables subpage displays various settings for the MySQL server, which can be defined in, say, the my.cnf MySQL configuration file. These values can't be changed from within phpMyAdmin.

Server processes

The Processes subpage is available to both superusers and normal users. A normal user would see only the processes belonging to him or her, whereas a superuser sees all of the processes.

This page lists all active processes on the server. There is a Kill link that allows us to terminate a specific process:

This example has only two running processes— including the one created by the SHOW PROCESSLIST command itself. This process is not killable because it's no longer running when we get to see the page. We would normally see more processes running on a busy server.

Mastering phpMyAdmin 3.3.x for Effective MySQL Management A complete guide to get started with phpMyAdmin 3.3 and master its features
Published: October 2010
eBook Price: €18.99
Book Price: €30.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on PHP, see here.)

Storage engines

Information about the various storage engines is available in a two-level format. First, the Engines tab displays an overview of the possible engines for the current MySQL version. The names of the engines that are enabled on this server are clickable:

A click on one engine name brings up a detailed panel about its settings. Moving the mouse over the numbers in superscript reveals even more information about a particular setting.

Available character sets and collations

The Charsets menu tab on the home page opens the Server view for the Charsets subpage, which lists the character sets and collations supported by the MySQL server. The default collation for each character set is shown with a different background color (using the row-marking color defined in $cfg['BrowseMarkerColor']).

Examining binary logs

If MySQL's binary log is active on our server, the menu in the Server view changes so that a Binary log tab appears. This tab gives access to an interface, through the SHOW BINLOG EVENTS command. This command produces the list of SQL statements that have updated data on our servers. This list could be huge, and currently phpMyAdmin does not limit its display with a pagination technique. Hence, we could hit the browser's memory limits, which depend on the particular browser we are using.

In the following screen, we choose the binary log that we want to examine (unless the server has only one binary log), and the statements are then displayed.

Summary

This article covered various features available to system administrators, such as user account management, privileges management, database privileges checks, and server status verification. Appropriate knowledge of the MySQL privileges system is crucial in order to maintain a MySQL server adequately, and this chapter proposes exercises centered around the notion of a user and his or her privileges.


Further resources on this subject:


About the Author :


Marc Delisle

Marc Delisle was awarded "MySQL Community Member of the year 2009" because of his involvement with phpMyAdmin. He started to contribute to the project in December 1998, when he made the multi-language version. He is still involved with phpMyAdmin as a developer and project administrator.

Marc is a system administrator at Cegep de Sherbrooke, Québec, Canada. He has been teaching networking, security, and web application development. In one of his classes, he was pleased to meet a phpMyAdmin user from Argentina. Marc lives in Sherbrooke with his wife and they enjoy spending time with their four children.

Books From Packt


Expert PHP 5 Tools
Expert PHP 5 Tools

MySQL 5.1 Plugin Development
MySQL 5.1 Plugin Development

CMS Design Using PHP and jQuery
CMS Design Using PHP and jQuery

MySQL Admin Cookbook
MySQL Admin Cookbook

Drupal 6 Panels Cookbook
Drupal 6 Panels Cookbook

High Availability MySQL Cookbook
High Availability MySQL Cookbook

Drupal 7
Drupal 7

PHP 5 E-commerce Development
PHP 5 E-commerce Development


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software