Preface

phpMyAdmin is an open source web interface that handles the administration of MySQL. It can perform various tasks such as creating, modifying, or deleting databases, tables, columns, or rows. It can also execute SQL statements or manage users and their permissions. When it comes to exploiting phpMyAdmin to its full potential, even experienced developers and system administrators search for tutorials to accomplish their tasks.

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 book jump starts with installing and configuring phpMyAdmin, and then looks into phpMyAdmin's features. This is followed by configuring authentication in phpMyAdmin and setting parameters that influence the interface as a whole, including the new user preferences feature. You will first create two basic tables and then edit, delete data, tables, and databases. As backups are crucial to a project, you will create up-to-date backups and then look into importing the data that you have exported. You will also explore the various search mechanisms and query across multiple tables.

Now you will learn some advanced features such as defining inter-table relations, both with relation view and the Designer panel. Some queries are out of the scope of the interface; you will enter SQL command to accomplish these tasks.

You will also learn about synchronizing databases on different servers and managing MySQL replication to improve performance and data security. You will also store queries as bookmarks for their quick retrieval. Towards the end of the book you will learn to document your database, track changes made to the database, and manage user accounts using phpMyAdmin server management features.

This book is an upgrade from the previous version that covered phpMyAdmin version 3.3. Version 3.4.x introduced features such as a user preferences module, relation schema export to multiple formats, an ENUM/SET column editor, a simplified interface for export and import, AJAX interface on some pages, charts generation, and a visual query builder.

What this book covers

Chapter 1, Getting Started with phpMyAdmin, gives us the reasons why we should use phpMyAdmin as a means of managing MySQL databases. It then covers the downloading and installation procedures for phpMyAdmin. Installing the phpMyAdmin configuration storage is covered as well.

Chapter 2, Configuring Authentication and Security, provides an overview of various authentication types used in phpMyAdmin. It then covers the security issues related to phpMyAdmin.

Chapter 3, Over Viewing the Interface, gives us an overview of the phpMyAdmin interface. It includes the login panel, the navigation and main panels with the Light and the Full mode, and the Query window. The new user preferences module is examined in this chapter.

Chapter 4, Creating and Browsing Tables, is all about database creation. It teaches us how to create a table, how to insert data manually, and how to sort the data. It also covers how to produce charts from data.

Chapter 5, Changing Data and Structure, covers the aspects of data editing in phpMyAdmin. It teaches us handling NULL values, multi-row editing, and data deletion. Finally it explores the subject of changing the structure of tables, with focus on editing column attributes (including the new ENUM/SET editor) and index management.

Chapter 6, Exporting Structure and Data (Backup), deals with backups and exports. It lists various ways to trigger an export, available export formats, the options associated with export formats, and the various places where the export files may be sent.

Chapter 7, Importing Structure and Data, tells us how to bring back exported data created for backup and transfer purposes. It covers the various options available in phpMyAdmin to import data, and different mechanisms involved in importing SQL files, CSV files, and other formats. Finally, it covers the limitations that may be faced while importing files, and the ways to overcome them.

Chapter 8, Searching Data, presents the mechanisms that are useful for searching data effectively, per table or inside an entire database.

Chapter 9, Performing Table and Database Operations, covers ways to perform some operations that influence and can be applied on entire tables or databases as a whole. Finally, it deals with table maintenance operations for table repair and optimization.

Chapter 10, Benefiting from the Relational System, is where we start covering advanced features of phpMyAdmin. The chapter explains how to define inter-table relations and how these relations can help us while browsing tables, entering data, or searching for it.

Chapter 11, Entering SQL Statements, helps us enter our own SQL commands. The chapter also covers the Query window—the window used to edit an SQL query. Finally, it also helps us to obtain the history of typed commands.

Chapter 12, Generating Multi-table Queries, covers the multi-table query generator, which allows us to produces these queries without actually typing them. The visual query builder is covered as well.

Chapter 13, Synchronizing Data and Supporting Replication, teaches us how to synchronize databases on the same server or from one server to another. It then covers how to manage MySQL replication.

Chapter 14, Using Query Bookmarks, covers one of the features of the phpMyAdmin configuration storage. It shows how to record bookmarks and how to manipulate them. Finally, it covers passing parameters to bookmarks.

Chapter 15, Documenting the System, gives an overview of how to produce documentation which explains the structure of the databases, using the tools offered by phpMyAdmin.

Chapter 16, Transforming Data Using MIME, explains how to apply transformations to the data in order to customize its format at view time.

Chapter 17, Supporting Features Added in MySQL 5, covers phpMyAdmin's support for the MySQL features that are new in MySQL 5.0 and 5.1, such as views, stored procedures, and triggers.

Chapter 18, Tracking Changes, teaches us how to record structure and data changes done from the phpMyAdmin interface.

Chapter 19, Administrating the MySQL Server, is about the administration of a MySQL server, focusing on user accounts and privileges. The chapter discusses how a system administrator can use phpMyAdmin's server management features for day-to-day user account maintenance, server verification, and server protection.

Appendix, Troubleshooting and Support, explains how to troubleshoot phpMyAdmin by performing simple verifications. It also explains how to interact with the development team for support, bug reports, and contributions.

What you need for this book

You need to have access to a server or workstation that has the following installed:

• A web server with PHP 5.2 or later

• MySQL 5.0 or later

Who this book is for

If you are a developer, system administrator, or web designer who wants to manage MySQL databases and tables efficiently, then this book is for you. This book assumes that you are already well-acquainted with MySQL basics. This book is a must read for every serious phpMyAdmin user who would like to use this outstanding application to its full power.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: "If this information is not available, a good alternate choice is localhost."

A block of code is set as follows:

$i++;
$cfg['Servers'][$i]['host'] = '';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

UPDATE `marc_book`.`book` SET `some_bits` = b '101'
WHERE `book`.`isbn` = '1-234567-89-0' LIMIT 1;

Any command-line input or output is written as follows:

tar -xzvf phpMyAdmin-3.4.5-all-languages.tar.gz

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "There are various files available in the Download section."

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to <[e-mail]>, and mention the book title through the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website, or added to any list of existing errata, under the Errata section of that title.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at <[e-mail]> with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions

You can contact us at <[e-mail]> if you are having a problem with any aspect of the book, and we will do our best to address it.