You are previewing Mastering phpMyAdmin 3.4 for Effective MySQL Management.
O'Reilly logo
Mastering phpMyAdmin 3.4 for Effective MySQL Management

Book Description

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

  • A step-by-step tutorial for manipulating data with the latest version of phpmyadmin

  • Administer your MySQL databases with phpMyAdmin

  • Manage users and privileges with MySQL Server Administration tools

  • Learn to do things with your MySQL database and phpMyAdmin that you didn't know were possible!

In Detail

phpmyAdmin is one of the most widely used open source applications, which is written in PHP. phpMyAdmin supports a wide range of operations with MySQL. Currently, it can create and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage keys on fields, manage privileges, export data into various formats and is available in 52 languages.

phpMyAdmin is a web-based front-end to manage MySQL databases and has been adopted by a number of Open-Source distributors.

Mastering phpMyAdmin 3.4 for Effective MySQL Management is an easy-to-read, step-by-step practical guide that walks you through every facet of this legendary tool—phpMyAdmin—and takes you a step ahead in taking full advantage of its potential. This book is filled with illustrative examples that will help you understand every phpMyAdmin feature in detail.

This is the official guide to this popular MySQL web interface. It starts with installing and configuring phpMyAdmin, including the phpMyAdmin Configuration Storage, which is the key to its advanced features. This is followed by configuring authentication in phpMyAdmin and setting parameters that influence the interface as a whole.

You will also learn some advanced features such as defining inter-table relations with the advanced Designer module. You will practice synchronizing databases on different servers and managing MySQL replication to improve performance and data security. Moreover, you will also store queries as bookmarks for their quick retrieval.

In addition to it, this book helps you to learn new features introduced in version 3.4.x such as users’ preferences, producing charts and the visual multi-table query builder.

Table of Contents

  1. Mastering phpMyAdmin 3.4 for Effective MySQL Management
    1. Mastering phpMyAdmin 3.4 for Effective MySQL Management
    2. Credits
    3. About the Author
    4. About the Reviewers
    5. www.PacktPub.com
      1. Support files, eBooks, discount offers, and more
        1. Why Subscribe?
        2. Free Access for Packt account holders
    6. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Errata
        2. Piracy
        3. Questions
    7. 1. Getting Started with phpMyAdmin
      1. PHP and MySQL: The leading open source duo
      2. What is phpMyAdmin?
        1. Project documentation
      3. Installing phpMyAdmin
        1. Required information
        2. System requirements
        3. Downloading the files
        4. Installing on different platforms
          1. Installing on a remote server using a Windows client
          2. Installing on a local Linux server
          3. Installing on a local Windows server (Apache, IIS)
      4. Configuring phpMyAdmin
        1. The config.inc.php file
          1. Avoiding false error messages about permissions on config.inc.php
        2. Configuration principles
        3. Web-based setup script
        4. Manually creating config.inc.php
        5. Tips for editing config.inc.php on a Windows client
        6. Description of some configuration parameters
          1. PmaAbsoluteUri
          2. Server-specific sections
            1. extension
            2. connect_type, socket, and port
            3. compress
            4. PersistentConnections
            5. controluser
      5. Installing phpMyAdmin configuration storage
        1. Goal of the configuration storage
        2. Location of the configuration storage
        3. Performing the installation
          1. Installing for a single user
          2. Installing for multiple users
      6. Upgrading phpMyAdmin
      7. Summary
    8. 2. Configuring Authentication and Security
      1. Logging in to MySQL through phpMyAdmin
        1. Logging in to an account without a password
        2. Authenticating a single user with config
          1. Testing the MySQL connection
        3. Authenticating multiple users
          1. Authenticating with HTTP
          2. Authenticating with cookie values
          3. Authenticating with signon mode
        4. Configuring for multiple server support
          1. Defining servers in the configuration file
          2. Authenticating through an arbitrary server
        5. Logging out
      2. Securing phpMyAdmin
        1. Protecting phpMyAdmin at directory level
        2. Displaying error messages
        3. Protecting with IP-based access control
          1. Defining rules
          2. Order of interpretation for rules
          3. Blocking root access
        4. Protecting in-transit data
      3. Summary
    9. 3. Over Viewing the Interface
      1. Over viewing panels and windows
        1. Login panels
        2. Navigation and main panels
          1. Home page
          2. Views
        3. Query window
        4. Starting page
      2. Customizing general settings
        1. Configuring window title
        2. Natural sort order for database and table names
        3. Creating site-specific header and footer
        4. Themes
          1. Configuring themes
          2. Selecting themes
        5. Selecting a language
        6. Slider
        7. Restricting the list of databases
        8. Deactivating Ajax
      3. Character sets and collations
        1. Effective character sets and collations
      4. Navigation panel
        1. Configuring the logo
        2. Database and table list
          1. Light mode
            1. Tree display of database names
            2. Table name filter
          2. Full mode
          3. Table abridged statistics
          4. Table quick-access icon
          5. Nested display of tables within a database
          6. Counting the number of tables
        3. Choosing from the server list
        4. Handling many databases or tables
          1. Limits on the interface
          2. Improving fetch speed
      5. Main panel
        1. Home page
        2. Database view
        3. Table view
        4. Server view
        5. Icons for home page and menu tabs
        6. Opening a new phpMyAdmin window
      6. User preferences
        1. Accessing user preferences
        2. Possible locations for saving preferences
          1. Saving in phpMyAdmin configuration storage
          2. Saving in a file
          3. Saving in the browser's local storage
        3. Changing settings
        4. Disallowing specific preferences
        5. Showing developer settings
      7. Query window
      8. Summary
    10. 4. Creating and Browsing Tables
      1. Creating a database
        1. No privileges
        2. First database creation is authorized
      2. Creating our first table
        1. Choosing the columns
        2. Creating a table
        3. Choosing keys
      3. Inserting data manually
        1. Data entry panel tuning for CHAR and VARCHAR
      4. Browse mode
        1. SQL query links
        2. Navigation bar
        3. Query results operations
          1. Displaying data as a chart
        4. Sorting results
          1. Headwords
        5. Color-marking rows or columns
        6. Limiting the length of each column
          1. Display options
        7. Browsing distinct values
      5. Profiling queries
      6. Creating an additional table
      7. Summary
    11. 5. Changing Data and Structure
      1. Changing data
        1. Entering edit mode
          1. Moving to next field with the tab key
          2. Moving with arrows
          3. Handling NULL values
          4. Applying a function to a value
          5. Duplicating rows of data
        2. Multi-row editing
        3. Editing the next row
        4. Inline row editing
        5. Deleting data
          1. Deleting a single row
          2. Deleting multiple rows
          3. Deleting all of the rows in a table
          4. Deleting all rows in multiple tables
        6. Deleting tables
        7. Deleting databases
      2. Changing table structure
        1. Adding a column
          1. Vertical mode
        2. Editing column attribute
        3. TEXT column type
        4. BLOB (Binary Large Object) column type
          1. Uploading binary content
        5. ENUM and SET column types
        6. DATE, DATETIME, and TIMESTAMP column types
          1. Calendar pop up
          2. TIMESTAMP option
        7. Bit column type
        8. Managing indexes
          1. Single-column indexes
          2. Multi-column indexes and index editing
          3. FULLTEXT indexes
          4. Optimizing indexes with EXPLAIN
          5. Detecting index problems
      3. Summary
    12. 6. Exporting Structure and Data (Backup)
      1. Dumps, backups, and exports
        1. Scope of the export
      2. Exporting a database
        1. The Table(s) sub-panel
        2. The Output sub-panel
          1. File name template
          2. Choosing a character set
          3. Kanji support
          4. Compression
        3. Export formats
          1. SQL
          2. CSV
          3. CSV for Microsoft Excel
          4. PDF
          5. Microsoft Word 2000
          6. LaTeX
          7. XML
          8. Open document spreadsheet
          9. Open document text
          10. YAML
          11. CodeGen
          12. Texy! text
          13. PHP array
          14. MediaWiki table
          15. JSON
      3. Exporting a table
        1. Split-file exports
      4. Exporting selectively
        1. Exporting partial query results
        2. Exporting and checkboxes
      5. Exporting multiple databases
      6. Saving the export file on the server
        1. User-specific save directories
      7. Memory limits
      8. Summary
    13. 7. Importing Structure and Data
      1. Limits for the transfer
        1. Time limits
        2. Other limits
        3. Handling big export files
        4. Uploading into a temporary directory
      2. Importing SQL files
      3. Importing CSV files
        1. Differences between SQL and CSV formats
        2. Exporting a test file
          1. CSV
        3. CSV using LOAD DATA
          1. Requirements
          2. Using the LOAD DATA interface
      4. Importing other formats
        1. Open Document Spreadsheet
        2. XML
      5. Reading files from a web server upload directory
      6. Displaying an upload progress bar
        1. Configuring APC
      7. Summary
    14. 8. Searching Data
      1. Single-table searches
        1. Entering the search page
        2. Searching criteria by column—query by example
          1. Searching for empty / non-empty values
        3. Producing reports with Print view
        4. Searching with wildcard characters
        5. Case sensitivity and search
        6. Combining criteria
        7. Search options
          1. Selecting the columns to be displayed
          2. Ordering the results
          3. Applying a WHERE clause
          4. Avoiding repeated results
      2. Performing a complete database search
        1. Restricting search to a column
      3. Stopping an errant query
      4. Summary
    15. 9. Performing Table and Database Operations
      1. Maintaining a table
      2. Changing table attributes
        1. Table storage engine
        2. Table comments
        3. Table order
        4. Table collation
        5. Table options
      3. Emptying or deleting a table
      4. Renaming, moving, and copying tables
        1. Appending data to a table
      5. Performing other table operations
      6. Multi-table operations
        1. Repairing an "in use" table
      7. Database operations
        1. Renaming a database
        2. Copying a database
      8. Summary
    16. 10. Benefiting from the Relational System
      1. Relational MySQL
        1. InnoDB and PBXT
      2. Defining relations with the relation view
        1. Defining internal relations
          1. Defining the relation
          2. Defining the display column
        2. Foreign key relations
          1. Foreign keys without phpMyAdmin configuration storage
      3. Defining relations with the Designer
        1. Over viewing the interface
        2. Defining relations
          1. Defining foreign key relations
        3. Defining the display column
        4. Exporting for PDF schema
      4. Benefiting from the defined relations
        1. Foreign key information
        2. The drop-down list of foreign keys
        3. The browseable foreign-table window
        4. Referential integrity checks
        5. Automatic updates of metadata
      5. Column commenting
        1. Automatically migrating column comments
      6. Summary
    17. 11. Entering SQL Statements
      1. The SQL query box
        1. The Database view
        2. The Table view
          1. The Columns selector
          2. Clicking into the query box
      2. The Query window
        1. Query window options
        2. Session-based SQL history
        3. Database-based SQL history (permanent)
        4. Editing queries
      3. Multi-statement queries
      4. Pretty printing (syntax highlighting)
      5. The SQL Validator
        1. System requirements
        2. Making the Validator available
        3. Validator results
          1. Standard-conforming queries
          2. Non standard-conforming queries
      6. Summary
    18. 12. Generating Multi-table Queries
      1. Choosing tables
      2. Exploring column criteria
        1. Column selector: Single column or all columns
        2. Sorting columns
        3. Showing a column
        4. Updating the query
        5. Adding conditions to the criteria box
          1. Adjusting the number of criteria rows
          2. Adjusting the number of criteria columns
      3. Generating automatic joins (internal relations)
      4. Executing the query
      5. The visual builder
      6. Summary
    19. 13. Synchronizing Data and Supporting Replication
      1. Synchronizing data and structure
        1. Goals of synchronization
          1. Moving between the development and production servers
          2. Collaboration between database designers
          3. Preparing for replication
        2. Over viewing the synchronization process
        3. Preparing for the synchronization exercise
        4. Choosing source and target servers and databases
        5. Analyzing comparison results
        6. Performing a complete synchronization
        7. Performing a selective synchronization
      2. Supporting MySQL replication
        1. The Replication menu
        2. Configuring replication
          1. Master server configuration
          2. Slave server configuration
        3. Setting up a test environment
        4. Controlling a slave server
        5. Obtaining replication information
          1. Gathering replication status
          2. Replicated databases
          3. Replicated tables
      3. Summary
    20. 14. Using Query Bookmarks
      1. Comparing bookmark and query history features
      2. Creating bookmarks
        1. Creating a bookmark after a successful query
        2. Storing a bookmark before sending a query
        3. Making bookmarks public
        4. The default initial query for a table
        5. Multi-query bookmarks
      3. Recalling bookmarks from the bookmarks list
        1. Executing bookmarks
        2. Manipulating bookmarks
      4. Passing a parameter to a bookmark
        1. Creating a parameterized bookmark
        2. Passing the parameter value
      5. Summary
    21. 15. Documenting the System
      1. Producing structure reports
        1. Creating a printable report
        2. The database print view
          1. The selective database print view
        3. The table print view
        4. Preparing a complete report with the data dictionary
      2. Generating relational schemas
        1. Adding a third table to our model
          1. Producing schema pages
          2. Page planning
          3. Creating a new page
          4. Editing a page
          5. Exporting a page for display
          6. Changing the font in PDF schema
        2. Laying out a schema with the Designer feature
      3. Summary
    22. 16. Transforming Data using MIME
      1. Browsing data without transformations
        1. Switching display options
      2. Enabling transformations
        1. Configuring settings for MIME columns
          1. Selecting the MIME type
          2. Browser transformations
          3. Assigning values to transformation options
        2. Requirements for image generation
          1. Configuring GD2 library availability verification
          2. Asserting support of JPEG and PNG libraries
          3. Evaluating the impact of memory limits
      3. Examples of transformations
        1. Clickable thumbnail (JPEG or PNG)
        2. Adding links to an image
        3. Date formatting
        4. Links from text
          1. text/plain: link
          2. text/plain: imagelink
        5. Preserving the original formatting
        6. Displaying parts of a text
        7. Displaying a download link
        8. Hexadecimal representation
        9. SQL pretty printing
        10. IP address
        11. Transforming data via external applications
          1. External application example: In-cell sort
      4. Summary
    23. 17. Supporting Features Added in MySQL 5
      1. Supporting views
        1. Creating a view from results
        2. Main panel and views
        3. Controlling row counting for improved performance
      2. Supporting routines—stored procedures and functions
        1. Creating a stored procedure
          1. Changing the delimiter
          2. Entering the procedure
        2. Testing the procedure
        3. Manipulating procedures and functions
        4. Manually creating a function
        5. Testing the function
        6. Exporting stored procedures and functions
      3. Executing code with triggers
        1. Manually creating a trigger
        2. Testing the trigger
      4. Using information_schema
      5. Partitioning
        1. Creating a table with partitions
        2. Maintaining partitions
        3. Exporting a partition definition
      6. Exploring the event scheduler
        1. Activating the scheduler
        2. Granting EVENT permission
        3. Creating an event
        4. Manipulating events
        5. Exporting
      7. Summary
    24. 18. Tracking Changes
      1. Understanding the goals of the tracking system
        1. Tracking in other software applications
        2. Tracking in phpMyAdmin
      2. Prerequisites
        1. Configuring a basic tracking mechanism
      3. Principles
        1. Versioning
        2. Taking a snapshot of the current structure
        3. Understanding archiving issues
      4. Initiating tracking for one table
        1. Choosing the statements to be tracked
      5. Testing the tracking mechanism
        1. Tracking report
      6. Determining tracking status
        1. Deactivating and activating tracking
      7. Structure snapshot
      8. Exporting a version
      9. Creating a new version
        1. Quickly accessing tracking information
      10. Deleting tracking information
      11. Summary
    25. 19. Administrating the MySQL Server
      1. Managing users and their privileges
        1. The user overview
          1. Exporting privileges
          2. Privileges reload
        2. Adding a user
          1. Entering the username
          2. Assigning a host value
          3. Setting passwords
          4. Understanding rights for database creation
          5. Assigning global privileges
          6. Limiting the resources used
        3. Editing a user profile
          1. Editing global privileges
          2. Assigning database-specific privileges
          3. Changing the password
          4. Changing login information or copying a user
        4. Removing a user
      2. Database information
        1. Enabling statistics
        2. Sorting the statistics
        3. Checking the database privileges
        4. Dropping selected databases
      3. Server information
        1. Verifying server status
        2. Server variables
        3. Server processes
        4. Storage engines
        5. Available character sets and collations
        6. Examining binary logs
      4. Summary
    26. A. Troubleshooting and Support
      1. Troubleshooting
        1. System requirements
        2. Verifying the base configuration
        3. Solving common errors
      2. Seeking support
        1. FAQs
        2. Help forums
          1. Creating a SourceForge account
          2. Choosing the thread title
          3. Reading the answers
        3. Using the support tracker
        4. Using the bug tracker
          1. Environment description
          2. Bug description
      3. Contributing to the project
        1. The code base
        2. Translation updates
        3. Patches