Mastering phpMyAdmin 3.4 for Effective MySQL Management by Delisle, Marc -- Read -- Imperial Library of Trantor

Index

Mastering phpMyAdmin 3.4 for Effective MySQL Management

Mastering phpMyAdmin 3.4 for Effective MySQL Management Credits About the Author About the Reviewers www.PacktPub.com

Support files, eBooks, discount offers, and more

Why Subscribe? Free Access for Packt account holders

Preface

What this book covers What you need for this book Who this book is for Conventions Reader feedback Customer support

Errata Piracy Questions

1. Getting Started with phpMyAdmin

PHP and MySQL: The leading open source duo What is phpMyAdmin?

Project documentation

Installing phpMyAdmin

Required information System requirements Downloading the files Installing on different platforms

Installing on a remote server using a Windows client Installing on a local Linux server Installing on a local Windows server (Apache, IIS)

Configuring phpMyAdmin

The config.inc.php file

Avoiding false error messages about permissions on config.inc.php

Configuration principles Web-based setup script Manually creating config.inc.php Tips for editing config.inc.php on a Windows client Description of some configuration parameters

PmaAbsoluteUri Server-specific sections

extension connect_type, socket, and port compress PersistentConnections controluser

Installing phpMyAdmin configuration storage

Goal of the configuration storage Location of the configuration storage Performing the installation

Installing for a single user Installing for multiple users

Upgrading phpMyAdmin Summary

2. Configuring Authentication and Security

Logging in to MySQL through phpMyAdmin

Logging in to an account without a password Authenticating a single user with config

Testing the MySQL connection

Authenticating multiple users

Authenticating with HTTP Authenticating with cookie values Authenticating with signon mode

Configuring for multiple server support

Defining servers in the configuration file Authenticating through an arbitrary server

Logging out

Securing phpMyAdmin

Protecting phpMyAdmin at directory level Displaying error messages Protecting with IP-based access control

Defining rules Order of interpretation for rules Blocking root access

Protecting in-transit data

Summary

3. Over Viewing the Interface

Over viewing panels and windows

Home page Views

Query window Starting page

Customizing general settings

Configuring window title Natural sort order for database and table names Creating site-specific header and footer Themes

Configuring themes Selecting themes

Selecting a language Slider Restricting the list of databases Deactivating Ajax

Character sets and collations

Effective character sets and collations

Navigation panel

Configuring the logo Database and table list

Light mode

Tree display of database names Table name filter

Full mode Table abridged statistics Table quick-access icon Nested display of tables within a database Counting the number of tables

Choosing from the server list Handling many databases or tables

Limits on the interface Improving fetch speed

Main panel

Home page Database view Table view Server view Icons for home page and menu tabs Opening a new phpMyAdmin window

User preferences

Accessing user preferences Possible locations for saving preferences

Saving in phpMyAdmin configuration storage Saving in a file Saving in the browser's local storage

Changing settings Disallowing specific preferences Showing developer settings

Query window Summary

4. Creating and Browsing Tables

Creating a database

No privileges First database creation is authorized

Creating our first table

Choosing the columns Creating a table Choosing keys

Inserting data manually

Data entry panel tuning for CHAR and VARCHAR

Browse mode

SQL query links Navigation bar Query results operations

Displaying data as a chart

Sorting results

Headwords

Color-marking rows or columns Limiting the length of each column

Display options

Browsing distinct values

Profiling queries Creating an additional table Summary

5. Changing Data and Structure

Changing data

Entering 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 Inline row editing Deleting data

Deleting a single row Deleting multiple rows Deleting all of the rows in a table Deleting all rows in multiple tables

Deleting tables Deleting databases

Changing table structure

Adding a column

Vertical mode

Editing column attribute TEXT column type BLOB (Binary Large Object) column type

Uploading binary content

ENUM and SET column types DATE, DATETIME, and TIMESTAMP column types

Calendar pop up TIMESTAMP option

Bit column type Managing indexes

Single-column indexes Multi-column indexes and index editing FULLTEXT indexes Optimizing indexes with EXPLAIN Detecting index problems

Summary

6. Exporting Structure and Data (Backup)

Dumps, backups, and exports

Scope of the export

Exporting a database

The Table(s) sub-panel The Output sub-panel

File name template Choosing a character set Kanji support Compression

Export formats

SQL CSV CSV for Microsoft Excel PDF Microsoft Word 2000 LaTeX XML Open document spreadsheet Open document text YAML CodeGen Texy! text PHP array MediaWiki table JSON

Exporting a table

Split-file exports

Exporting selectively

Exporting partial query results Exporting and checkboxes

Exporting multiple databases Saving the export file on the server

User-specific save directories

Memory limits Summary

7. Importing Structure and Data

Limits for the transfer

Time limits Other limits Handling big export files Uploading into a 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

Importing other formats

Open Document Spreadsheet XML

Reading files from a web server upload directory Displaying an upload progress bar

Configuring APC

Summary

8. Searching Data

Single-table searches

Entering the search page Searching criteria by column—query by example

Searching for empty / non-empty values

Producing reports with Print view Searching with wildcard characters Case sensitivity and search Combining criteria Search options

Selecting the columns to be displayed Ordering the results Applying a WHERE clause Avoiding repeated results

Performing a complete database search

Restricting search to a column

Stopping an errant query Summary

9. Performing Table and Database Operations

Maintaining a table Changing table attributes

Table storage engine Table comments Table order Table collation Table options

Emptying or deleting a table Renaming, moving, and copying tables

Appending data to a table

Performing other table operations Multi-table operations

Repairing an "in use" table

Database operations

Renaming a database Copying a database

Summary

10. Benefiting from the Relational System

Relational MySQL

InnoDB and PBXT

Defining relations with the relation view

Defining internal relations

Defining the relation Defining the display column

Foreign key relations

Foreign keys without phpMyAdmin configuration storage

Defining relations with the Designer

Over viewing the interface Defining relations

Defining foreign key relations

Defining the display column Exporting for PDF schema

Benefiting from 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

Automatically migrating column comments

Summary

11. Entering SQL Statements

The SQL query box

The Database view The Table view

The Columns selector Clicking into the query box

The Query window

Query window options Session-based SQL history Database-based SQL history (permanent) Editing queries

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

12. Generating Multi-table Queries

Choosing tables Exploring column criteria

Column selector: Single column or all columns Sorting columns Showing a column Updating the query Adding conditions to the criteria box

Adjusting the number of criteria rows Adjusting the number of criteria columns

Generating automatic joins (internal relations) Executing the query The visual builder Summary

13. Synchronizing Data and Supporting Replication

Synchronizing data and structure

Goals of synchronization

Moving between the development and production servers Collaboration between database designers Preparing for replication

Over viewing the synchronization process Preparing for the synchronization exercise Choosing source and target servers and databases Analyzing comparison results Performing a complete synchronization Performing a selective synchronization

Supporting MySQL replication

The Replication menu Configuring replication

Master server configuration Slave server configuration

Setting up a test environment Controlling a slave server Obtaining replication information

Gathering replication status Replicated databases Replicated tables

Summary

14. Using Query Bookmarks

Comparing bookmark and query history features Creating bookmarks

Creating a bookmark after a successful query Storing a bookmark before sending a query Making bookmarks public The default initial query for a table Multi-query bookmarks

Recalling bookmarks from the bookmarks list

Executing bookmarks Manipulating bookmarks

Passing a parameter to a bookmark

Creating a parameterized bookmark Passing the parameter value

Summary

15. Documenting the System

Producing structure reports

Creating a printable report The database print view

The selective database print view

The table print view Preparing a complete report with the data dictionary

Generating relational schemas

Adding a third table to our model

Producing schema pages Page planning Creating a new page Editing a page Exporting a page for display Changing the font in PDF schema

Laying out a schema with the Designer feature

Summary

16. Transforming Data using MIME

Browsing data without transformations

Switching display options

Enabling transformations

Configuring settings for MIME columns

Selecting the MIME type Browser transformations Assigning values to transformation options

Requirements for image generation

Configuring GD2 library availability verification Asserting support of JPEG and PNG libraries Evaluating the impact of memory limits

Examples of transformations

Clickable thumbnail (JPEG or PNG) Adding links to an image Date formatting Links from text

text/plain: link text/plain: imagelink

Preserving the original formatting Displaying parts of a text Displaying a download link Hexadecimal representation SQL pretty printing IP address Transforming data via external applications

External application example: In-cell sort

Summary

17. Supporting Features Added in MySQL 5

Supporting views

Creating a view from results Main panel and views Controlling row counting for improved performance

Supporting routines—stored procedures and functions

Creating a stored procedure

Changing the delimiter Entering the procedure

Testing the procedure Manipulating procedures and functions Manually creating a function Testing the function Exporting stored procedures and functions

Executing code with triggers

Manually creating a trigger Testing the trigger

Using information_schema Partitioning

Creating a table with partitions Maintaining partitions Exporting a partition definition

Exploring the event scheduler

Activating the scheduler Granting EVENT permission Creating an event Manipulating events Exporting

Summary

18. Tracking Changes

Understanding the goals of the tracking system

Tracking in other software applications Tracking in phpMyAdmin

Prerequisites

Configuring a basic tracking mechanism

Principles

Versioning Taking a snapshot of the current structure Understanding archiving issues

Initiating tracking for one table

Choosing the statements to be tracked

Testing the tracking mechanism

Tracking report

Determining tracking status

Deactivating and activating tracking

Structure snapshot Exporting a version Creating a new version

Quickly accessing tracking information

Deleting tracking information Summary

19. Administrating the MySQL Server

Managing users and their privileges

The user overview

Exporting privileges Privileges reload

Adding a user

Entering the username Assigning a host value Setting passwords Understanding rights for database creation Assigning global privileges Limiting the resources used

Editing a user profile

Editing global privileges Assigning database-specific privileges Changing the password Changing login information or copying a user

Removing a user

Database information

Enabling statistics Sorting the statistics Checking the database privileges Dropping selected databases

Server information

Verifying server status Server variables Server processes Storage engines Available character sets and collations Examining binary logs

Summary

A. Troubleshooting and Support

Troubleshooting

System requirements Verifying the base configuration Solving common errors

Seeking support

FAQs Help forums

Creating a SourceForge account Choosing the thread title Reading the answers

Using the support tracker Using the bug tracker

Environment description Bug description

Contributing to the project

The code base Translation updates Patches

← Prev
Back
Next →

← Prev
Back
Next →