Table of Contents for
Mastering phpMyAdmin 3.4 for Effective MySQL Management

Version ebook / Retour

Cover image for bash Cookbook, 2nd Edition Mastering phpMyAdmin 3.4 for Effective MySQL Management by Marc Delisle Published by Packt Publishing, 2012
  1. Cover
  2. Mastering phpMyAdmin 3.4 for Effective MySQL Management
  3. Mastering phpMyAdmin 3.4 for Effective MySQL Management
  4. Credits
  5. About the Author
  6. About the Reviewers
  7. www.PacktPub.com
  8. Preface
  9. What you need for this book
  10. Who this book is for
  11. Conventions
  12. Reader feedback
  13. Customer support
  14. 1. Getting Started with phpMyAdmin
  15. What is phpMyAdmin?
  16. Installing phpMyAdmin
  17. Configuring phpMyAdmin
  18. Installing phpMyAdmin configuration storage
  19. Upgrading phpMyAdmin
  20. Summary
  21. 2. Configuring Authentication and Security
  22. Securing phpMyAdmin
  23. Summary
  24. 3. Over Viewing the Interface
  25. Customizing general settings
  26. Character sets and collations
  27. Navigation panel
  28. Main panel
  29. User preferences
  30. Query window
  31. Summary
  32. 4. Creating and Browsing Tables
  33. Creating our first table
  34. Inserting data manually
  35. Browse mode
  36. Profiling queries
  37. Creating an additional table
  38. Summary
  39. 5. Changing Data and Structure
  40. Changing table structure
  41. Summary
  42. 6. Exporting Structure and Data (Backup)
  43. Exporting a database
  44. Exporting a table
  45. Exporting selectively
  46. Exporting multiple databases
  47. Saving the export file on the server
  48. Memory limits
  49. Summary
  50. 7. Importing Structure and Data
  51. Importing SQL files
  52. Importing CSV files
  53. Importing other formats
  54. Reading files from a web server upload directory
  55. Displaying an upload progress bar
  56. Summary
  57. 8. Searching Data
  58. Performing a complete database search
  59. Stopping an errant query
  60. Summary
  61. 9. Performing Table and Database Operations
  62. Changing table attributes
  63. Emptying or deleting a table
  64. Renaming, moving, and copying tables
  65. Performing other table operations
  66. Multi-table operations
  67. Database operations
  68. Summary
  69. 10. Benefiting from the Relational System
  70. Defining relations with the relation view
  71. Defining relations with the Designer
  72. Benefiting from the defined relations
  73. Column commenting
  74. Summary
  75. 11. Entering SQL Statements
  76. The Query window
  77. Multi-statement queries
  78. Pretty printing (syntax highlighting)
  79. The SQL Validator
  80. Summary
  81. 12. Generating Multi-table Queries
  82. Exploring column criteria
  83. Generating automatic joins (internal relations)
  84. Executing the query
  85. The visual builder
  86. Summary
  87. 13. Synchronizing Data and Supporting Replication
  88. Supporting MySQL replication
  89. Summary
  90. 14. Using Query Bookmarks
  91. Creating bookmarks
  92. Recalling bookmarks from the bookmarks list
  93. Passing a parameter to a bookmark
  94. Summary
  95. 15. Documenting the System
  96. Generating relational schemas
  97. Summary
  98. 16. Transforming Data using MIME
  99. Enabling transformations
  100. Examples of transformations
  101. Summary
  102. 17. Supporting Features Added in MySQL 5
  103. Supporting routines—stored procedures and functions
  104. Executing code with triggers
  105. Using information_schema
  106. Partitioning
  107. Exploring the event scheduler
  108. Summary
  109. 18. Tracking Changes
  110. Prerequisites
  111. Principles
  112. Initiating tracking for one table
  113. Testing the tracking mechanism
  114. Determining tracking status
  115. Structure snapshot
  116. Exporting a version
  117. Creating a new version
  118. Deleting tracking information
  119. Summary
  120. 19. Administrating the MySQL Server
  121. Database information
  122. Server information
  123. Summary
  124. A. Troubleshooting and Support
  125. Seeking support
  126. Contributing to the project

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:

Verifying server status

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%:

Verifying server status

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:

Verifying server status

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

Server variables

The Variables 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 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:

Server processes

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.

Storage engines

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 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:

Examining binary logs