Administrating the MySQL Server

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

Mastering phpMyAdmin 3.4 for Effective MySQL Management — Save 50%

A complete guide to getting started with phpMyAdmin 3.4 and mastering its features book and ebook

$23.99    $12.00
by Marc Delisle | February 2012 | MySQL Content Management PHP

In this article wirtten by Marc Delisle, author of Mastering phpMyAdmin 3.4 for Effective MySQL Management, we will discus 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.

(For more resources on MySQL, see here.)

Managing users and their privileges

The Privileges sub-page (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 global, database, and table levels. This sub-page is hierarchical. When editing a user's privileges, we can see the global privileges as well as the database-specific privileges. Then, when viewing database-specific privileges for a user, we can view and edit this user's privileges for any table within this database.

The user overview

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

(Move the mouse over the image to enlarge.)

From this page, we can:

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

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

Column

Characteristic

User

The user account we are defining.

Host

The machine name or IP address, from which this user account will be connecting to the MySQL server. A % value here indicates all hosts.

Password

Contains Yes if a password is defined and No if it isn't. The password itself cannot be seen from phpMyAdmin's interface or by directly looking at the mysql.user table, as it is encrypted with a one-way hashing algorithm.

Global privileges

A list of the user's global privileges.

Grant

Contains Yes if the user can grant his/her privileges to others.

Action

Contains a link to edit this user's privileges or export them.

Exporting privileges

This feature can be useful when we need to create a user with the same password and privileges on another MySQL server. Clicking on Export for user marc produces the following panel:

Then it's only a matter of selecting these GRANT statements and pasting them in the SQL box of another phpMyAdmin window, where we have logged in on another MySQL server.

Privileges reload

At the bottom of User overview page, this 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 will describe the account itself, as shown in the following screenshot:

The second part of the Add a new User dialog is where we will specify the user's global privileges, which apply to the server as a whole (see the Assigning global privileges section of this article), as shown in the following screenshot:

Entering the username

The User name menu offers two choices. 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). More details about the anonymous user are available at http://dev.mysql.com/doc/refman/5.5/en/connection-access.html. Let us 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.host table for database-specific privileges. Choosing Use text field: allows us to enter the exact host value we want. Let us 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 button 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 Create database with same name and grant all privileges radio button. Selecting this checkbox automates the process by creating both the database (if it does not already exist) and assigning 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. Moreover, should a user account that has global privileges become compromised, the damage could be far greater.

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 resources limits on bill.

The following screenshot shows the status of the screen just before hitting Create user 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 whenever we click on Edit Privileges for a user in the User overview page. Let us try it for our newly created user bill. 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 global 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 to 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 as shown in the following screenshot:

This assigns privileges only for the chosen database. Secondly, 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 we give him/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 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 hover 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 (we could click on Edit Privileges link to edit or on Revoke link to revoke them) 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 privilege 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 log in 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.

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, and then select delete the old one from the user tables radio button, as shown in the following screenshot:

After clicking on Go, 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. However, 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 option, 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 the databases that have the same names as the users option to remove any databases that are named after the users we are deleting. A click on Go effectively removes the selected users.

Mastering phpMyAdmin 3.4 for Effective MySQL Management A complete guide to getting started with phpMyAdmin 3.4 and mastering its features book and ebook
Published: February 2012
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

(For more resources on MySQL, see here.)

Database information

The Databases sub-page is intended to create new databases, and 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 sub-page, 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 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. Next, the space that could be reclaimed by optimizing some tables in this database is presented under Overhead column header. Finally, we can see replication information, followed by Check Privileges links:

Sorting the 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 or link 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 Privileges link takes us to another page, which is used to edit the user's privileges.

We notice that this panel also contains the Add a new User link. 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, as shown in the following screenshot:

Dropping selected databases

To drop one or more databases, we go to Server view and click on the Databases menu tab; put check marks next to the names of the databases to be dropped; and then click on the Drop link 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

Both administrators and ordinary users can benefit from monitoring the server and obtaining information about its general configuration and behavior. 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.

Clicking on the Status menu tab 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 í indicates average), as shown in the following screenshot:

Next, the statistics about the queries are displayed (shown in part in the screenshot). 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 statement executed, including:

  • The absolute number of times each statement has been executed
  • The hourly average of execution
  • The percentage of execution for this statement compared to all statements

The presentation order is by descending percentage of utilization; in the following screenshot, we see that the set option statement is the one which is most received by this server with 37.40%:

After Query statistics, a Show query chart link, when clicked, produces a chart displaying the popular query types on this server, as shown in the following screenshot:

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

Server variables

The Variables sub-page 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 sub-page 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, as shown in the following screenshot:

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. On a busy server, we would see more processes running.

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.

Secondly, a click on one engine name brings up a detailed panel about its settings. Hovering 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 sub-page, 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 logging 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 limit, which depends on the particular browser we are using.

In the following screenshot, 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 article proposes exercises centered on the notion of a user and his or her privileges.


Further resources on this subject:


Mastering phpMyAdmin 3.4 for Effective MySQL Management A complete guide to getting started with phpMyAdmin 3.4 and mastering its features book and ebook
Published: February 2012
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

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


MySQL 5.1 Plugin Development
MySQL 5.1 Plugin Development

PHP Ajax Cookbook
PHP Ajax Cookbook

High Availability MySQL Cookbook
High Availability MySQL Cookbook

CakePHP 1.3 Application Development Cookbook
CakePHP 1.3 Application Development Cookbook

Elgg 1.8 Social Networking
Elgg 1.8 Social Networking

MySQL Admin Cookbook
MySQL Admin Cookbook

Facebook Application Development with Graph API Cookbook
Facebook Application Development with Graph API Cookbook

Yii 1.1 Application Development Cookbook
Yii 1.1 Application Development Cookbook


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
2
c
1
Z
G
n
Enter the code without spaces and pay attention to upper/lower case.
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