Mastering phpMyAdmin 3.1 for Effective MySQL Management Table of Contents

Mastering phpMyAdmin 3.1 for Effective MySQL Management

Preface
Chapter 1: Introduction and Installation
Chapter 2: Authentication and Security
Chapter 3: Interface Overview
Chapter 4: First Steps
Chapter 5: Changing Data
Chapter 6: Changing Table Structures
Chapter 7: Exporting Structure and Data (Backup)
Chapter 8: Importing Structure and Data
Chapter 9: Searching Data
Chapter 10: Table and Database Operations
Chapter 11: The Relational System
Chapter 12: Entering SQL Commands
Chapter 13: The Multi-Table Query Generator
Chapter 14: Bookmarks
Chapter 15: System Documentation
Chapter 16: MIME-Based Transformations
Chapter 17: MySQL 5.0 and 5.1 Support
Chapter 18: MySQL Server Administration
Appendix A: History of phpMyAdmin
Appendix B: Troubleshooting and Support
Index

Preface

Chapter 1: Introduction and Installation
- Introducing phpMyAdmin
  - Web applications
  - PHP and MySQL:The leading open source duo
  - What is phpMyAdmin?
  - phpMyAdmin features
- Installing phpMyAdmin
  - Required information
  - System requirements
  - Downloading the files
  - Installation procedure
    - Installation on a remote server using a Windows client
    - Installation on a local Linux server
    - Installation on local Windows servers (Apache, IIS)
- Configuring phpMyAdmin
  - The config.inc.php file
    - Permissions on config.inc.php
  - Configuration principles
  - Web-based setup script
  - Manual creation of config.inc.php
  - Tips for editing config.inc.php on a Windows Client
  - Description of some configuration parameters
    - PmaAbsoluteUri
    - Server-specific sections
- Upgrading phpMyAdmin
- Summary

Chapter 2: Authentication and Security
- MySQL authentication
  - Root user without password
  - Single-user authentication using config
    - Testing the connection
  - Multi-user authentication
    - Authentication types offered
    - The control user
    - Logging out
    - HTTP authentication
    - Cookie authentication
    - Signon authentication
  - Multi-server configuration
    - Servers defined in the configuration file
    - Arbitrary server
- Security
  - Directory-level protection
  - Error handling
  - IP-based access control
    - Rules
    - Order of interpretation for rules
    - Simplified rule for root access
  - Restricting the list of databases
  - Protecting in-transit data
  - Swekey hardware authentication
    - Configuration
    - Usage
    - Security note
- Summary

Chapter 3: Interface Overview
- Panels and windows
  - Login panels
  - Left and right panels
    - Homepage
    - Views
  - Query window
  - Starting page
- General customization
  - Window titles configuration
  - Icon configuration
  - Natural sort order for database and table names
  - Site-specific header and footer
  - MySQL documentation links
  - Themes
    - Theme configuration
    - Theme selection
  - The color picker
  - Sliders
- Character sets, collations and language
  - Collations
  - Unicode and UTF-8
  - Language selection
  - Effective character sets and collations
- Left panel (navigation)
  - Logo configuration
  - Database and table list
    - Light mode
    - Full mode
    - Table short statistics
    - Table quick-access icon
    - Nested display of tables within a database
  - Server-list choice
  - Handling many databases or tables
    - Limits on the interface
    - Improving fetch speed
- Right panel (main)
  - Homepage
  - Database view
  - Table view
  - Server view
  - Icons for homepage and menu tabs
- Query window
- Summary

Chapter 4: First Steps
- Database creation
  - No privileges?
  - First database creation is authorized
- Creating our first table
  - Choosing the fields
  - Table creation
  - Choosing keys
- Manual data insertion
  - Data entry panel tuning for CHAR and VARCHAR
- Browse mode
  - SQL query links
  - Navigation bar
  - Query results operations
  - Sorting results
    - Headwords
  - Color-marking rows
  - Limiting the length of each column
    - Display options
  - Browsing distinct values
  - Browse-mode customization
- Creating an additional table
- Summary

Chapter 5: Changing Data
- Edit mode
  - Moving to next field with the tab key
  - Moving with arrows
  - Handling NULL values
  - Applying a function to a value
  - Duplicating rows of data
- Multi-row editing
- Editing the next row
- Deleting data
  - Deleting a single row
  - Deleting many rows
  - Deleting all the rows in a table
  - Deleting all rows in many tables
- Deleting tables
- Deleting databases
- Summary

Chapter 6: Changing Table Structures
- Adding a field
  - Vertical mode
  - Horizontal mode
- Editing field attributes
- TEXT
- BLOB (Binary Large Object) fields
  - Binary content uploads
- ENUM and SET
- DATE, DATETIME, and TIMESTAMP
  - Calendar pop up
  - TIMESTAMP options
- Bit
- Index management
  - Single-field indexes
  - Multi-field indexes and index editing
  - FULLTEXT Indexes
  - Table optimization—explaining a query
  - Detection of index problems
- Summary

Chapter 7: Exporting Structure and Data (Backup)
- Dumps, backups, and exports
  - Scope of the export
- Database exports
  - The export subpanel
  - SQL
    - SQL options
  - The "Save as file" subpanel
    - File name template
    - Compression
    - Choice of character set
    - Kanji support
  - CSV
  - CSV for MS Excel
  - PDF
  - Microsoft Excel 2000
  - Microsoft Word 2000
  - LaTeX
  - XML
  - Native MS Excel (pre-Excel 2000)
  - Open document spreadsheet
  - Open document text
  - YAML
  - CodeGen
  - Texy! text
- Table exports
  - Split-file exports
- Selective exports
  - Exporting partial query results
  - Exporting and checkboxes
- Multi-database exports
- Saving the export file on the server
  - User-specific save directories
- Memory limits
- Summary

Chapter 8: Importing Structure and Data
- Limits for the transfer
  - Time limits
  - Other limits
  - Partial imports
  - Temporary directory
- Importing SQL files
- Importing CSV files
  - Differences between SQL and CSV formats
  - Exporting a test file
  - CSV
  - CSV using LOAD DATA
    - Requirements
    - Using the LOAD DATA interface
- Web server upload directories
- Summary

Chapter 9: Searching Data
- Daily usage of phpMyAdmin
- Single-table searches
  - Entering the search subpage
  - Search criteria by field—query by example
    - Searching for empty / non-empty values
  - Print view
  - Wildcard search
  - Case sensitivity
  - Combining criteria
  - Search options
    - Selecting the fields to be displayed
    - Ordering the results
    - Applying a WHERE clause
    - Obtaining distinct results
- Complete database search
  - Restricting search to a column
- Summary

Chapter 10: Table and Database Operations
- Table maintenance
- Changing table attributes
  - Table storage engine
  - Table comments
  - Table order
  - Table collation
    - Impact of switching connection collation
  - Table options
- Renaming, moving, and copying tables
  - Appending data to a table
- Multi-table operations
  - Repairing an "in use" table
- Database operations
  - Renaming a database
  - Copying a database
- Summary

Chapter 11: The Relational System
- Relational MySQL
  - InnoDB and PBXT
- Linked-tables infrastructure
  - Goal of the infrastructure
  - Location of the infrastructure
  - Installing linked-tables infrastructure
    - Multi-user installation
    - Single-user installation
- Defining relations with the relation view
  - Internal relations
    - Defining the relation
    - Defining the display field
  - Foreign key relations
    - Foreign keys without linked-tables infrastructure
- Defining relations with the Designer
  - Interface overview
  - Defining relations
    - Foreign key relations
  - Defining the display field
  - Exporting for PDF schema
- Benefits of the defined relations
  - Foreign key information
  - The drop-down list of foreign keys
  - The browseable foreign-table window
  - Referential integrity checks
  - Automatic updates of metadata
- Column-commenting
  - Automatic migration
- Summary

Chapter 12: Entering SQL Commands
- The SQL query box
  - The Database view
  - The Table view
    - The Fields selector
    - Clicking into the query box
- The Query window
  - Query window options
  - Session based SQL history
  - Database based SQL history (permanent)
  - Editing queries in the query window
- Multi-statement queries
- Pretty printing (syntax-highlighting)
- The SQL Validator
  - System requirements
  - Making the Validator available
  - Validator results
    - Standard-conforming queries
    - Non-standard-conforming queries
- Summary

Chapter 13: The Multi-Table Query Generator
- Choosing tables
- Column criteria
  - Field selector: Single-column or all columns
  - Sorts
  - Showing a column
  - Updating the query
  - Criteria
    - Adjusting the number of criteria rows
    - Adjusting the number of criteria columns
- Automatic joins
- Executing the query
- Summary

Chapter 14: Bookmarks
- Comparing bookmark and query history features
- Bookmark creation
  - Creating a bookmark after a successful query
  - Storing a bookmark before sending a query
  - Public bookmarks
  - The default initial query for a table
  - Multi-query bookmarks
- Recalling from the bookmarks list
  - Bookmark execution
  - Bookmark manipulation
- Bookmark parameters
  - Creating a parameterized bookmark
  - Passing a parameter value to a bookmark
- Executing bookmarks by browsing the pma_bookmark table
- Summary

Chapter 15: System Documentation
- Structure reports
  - Using print view
  - The database print view
    - The selective database print view
  - The table print view
  - The data dictionary
- Relational schema in PDF
  - Adding a third table to our model
  - Editing PDF pages
    - Page planning
    - Creating a new page
    - Editing a page
    - Displaying a page
    - A note about fonts used
  - Using the Designer for PDF layout
- Summary

Chapter 16: MIME-Based Transformations
- Display behavior in browse mode
  - Display options
- Enabling transformations
  - The MIME column's settings
    - MIME types
    - Browser transformations
    - Transformation options
  - Requirements for image generation
    - The GD2 library
    - The JPEG and PNG libraries
    - Memory limits
- Examples of transformations
  - Clickable thumbnail (.jpeg or .png)
  - Links to an image
  - Date formatting
  - Links from text
    - text/plain: link
    - text/plain: imagelink
  - Preserving the original formatting
  - Displaying parts of a text
  - Download link
  - Hexadecimal representation
  - SQL pretty printing
  - IP address
  - External applications
    - External application example: In-cell sort
- Summary

Chapter 17: MySQL 5.0 and 5.1 Support
- Views
  - Manually creating a view
  - Right panel and views
  - Creating a view from results
  - Renaming a view
  - Performance hint
- Routines—stored procedures and functions
  - Creating a stored procedure
    - Changing the delimiter
    - Entering the procedure
  - Testing the procedure
  - Manipulation
  - Manually creating a function
  - Testing the function
  - Exporting stored procedures and functions
- Triggers
  - Manually creating a trigger
  - Testing the trigger
- Information_schema
- Profiling
- Partitioning
  - Table creation
  - Operations
  - Exporting
- Event scheduler
  - Activating the scheduler
  - Granting EVENT permission
  - Creating an event
  - Event manipulation
  - Exporting
- BLOB streaming
  - System requirements
  - Configuration
  - Implementation limitations in phpMyAdmin
  - Creating the PBMS system tables
  - Table preparation
  - Uploading to BLOB repository
  - Streaming the data from repository
  - Changing repository data
- Summary

Chapter 18: MySQL Server Administration
- User and privileges management
  - The user overview
    - Privileges reload
  - Adding a user
    - User name
    - Host
    - Password
    - Database creation and rights
    - Global privileges
    - Resource limits
  - Editing a user
    - Edit privileges
    - Database-specific privileges
    - Changing the password
    - Changing login information or copying a user
  - Removing a user
- Database information
  - Enabling statistics
  - Sorting statistics
  - Checking the database privileges
  - Dropping selected databases
- Server information
  - Server status verification
    - The general status page
    - InnoDB status
  - Server variables
  - Server processes
  - Storage engines
  - Available character sets and collations
  - The binary log
- Summary

Appendix A: History of phpMyAdmin
- Early events
- Project re-launch
  - Distributors
- Evolution
- GoPHP5 and the 3.x branch
- Awards
- Summary

Appendix B: Troubleshooting and Support
- Base configuration
- Solving common errors
  - #2003 - Can't connect to MySQL server
  - Named pipe problem (Windows)
  - MySQL said: Access denied
  - When using http authentication
  - MySQL said: Error 127, Table Must Be Repaired
  - BLOB column used in key specification without a key length
  - IIS: No Input File Specified
  - A "404: page not found" error when modifying a row
  - Not being able to create a database
  - Problems importing large files or uploading large BLOB files
  - MySQL root password lost
  - Duplicate field names when creating a table
  - Authentication window displayed more than once
  - Column size changed by phpMyAdmin
  - Seeing many databases that are not ours
  - Not being able to store a value greater than 127

FAQs
Help forums
- Creating a SourceForge account
- Choosing the thread title
- Reading the answers
- Support tracker
- Bug tracker
- Environment description
- Bug description