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
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
|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:
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.
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.
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
eBook Price: $23.99
Book Price: $39.99
|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.
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.
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.
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.
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.
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.
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.
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.
eBook Price: $23.99
Book Price: $39.99
|Read more about this book|
(For more resources on PHP, see here.)
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.
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.
- Importing Structure and Data in phpMyAdmin 3.3.x for Effective MySQL Management
- Performing Table and Database Operations in phpMyAdmin 3.3.x for Effective MySQL Management
- Installing phpMyAdmin
- Installing PHP-Nuke
- MySQL Admin: Configuring InnoDB and Installing MySQL as a Windows Service
- Sessions and Users in PHP 5 CMS
- Configuring MySQL
About the Author :
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.