31,19 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
phpMyAdmin is an open source tool written in PHP to handle MySQL administration over the World Wide Web. It can execute SQL statements and manage users and their permissions. However, when it comes to exploiting phpMyAdmin to its full potential, even experienced developers and system administrators are left baffled.Mastering phpMyAdmin 3.3.x for Effective MySQL Management is an easy-to-follow, step-by-step guide that walks you through every facet of this efficient tool. Author Marc Delisle draws on his experience as one of the leading developers and project administrator of phpMyAdmin and uses his unique tutorial approach to take full advantage of its potential. This book is filled with illustrative examples that will help you understand every phpMyAdmin feature in detail.The book helps you get started with installing and configuring phpMyAdmin and looks at its features. You then work on a sample project with two basic tables and perform basic actions such as creating, editing, and deleting data, tables, and databases. You will learn how to create up-to-date backups and import the data that you have exported. You will then explore different search mechanisms and options for querying across multiple tables.The book gradually proceeds to advanced features such as defining inter-table relations and installing the linked-tables infrastructure. Some queries are out of the scope of the interface and this book will show you how to accomplish these tasks with SQL commands.New features of version 3.3.x, such as synchronizing databases on different servers and managing MySQL replication to improve performance and data security, are covered in this book. 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.1. Version 3.3.x introduced features such as new import and export modules, tracking changes, synchronizing structure and data between servers, and providing support for replication.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 433
Veröffentlichungsjahr: 2010
Ähnliche
Table of Contents
Mastering phpMyAdmin 3.3.x for Effective MySQL Management
Mastering phpMyAdmin 3.3.x for Effective MySQL Management
Copyright © 2010 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: October 2010
Production Reference: 1041010
Published by Packt Publishing Ltd. 32 Lincoln Road Olton Birmingham, B27 6PA, UK.
ISBN 978-1-849513-54-8
www.packtpub.com
Cover Image by Marc Delisle ( <[email protected]> )
Credits
Author
Marc Delisle
Reviewers
Michal Čihař
Ben Dodson
Kai 'Oswald' Seidler
Development Editor
Reshma Sundaresan
Technical Editor
Conrad Sardinha
Indexer
Hemangini Bari
Editorial Team Leader
Akshara Aware
Project Team Leader
Lata Basantani
Project Coordinator
Sneha Harkut
Proofreader
Dirk Manuel
Production Coordinator
Adline Swetha Jesuthas
Cover Work
Adline Swetha Jesuthas
About the Author
Marc Delisle was awarded "MySQL Community Member of the year 2009" because of his involvement with phpMyAdmin. He started to contribute to the project in December 1998, when he developed the multi-language version. He is still involved with phpMyAdmin as a developer and project administrator.
Marc is a system administrator at Cegep de Sherbrooke, Québec, Canada. He has taught networking, security, and web application development. In one of his classes, he was pleased to meet a phpMyAdmin user from Argentina. Marc lives in Sherbrooke with his wife and they enjoy spending time with their four children.
This book was Marc's first one and was quickly followed by Creating your MySQL Database: Practical Design Tips and Techniques, also published by Packt Publishing.
I am truly grateful to Louay Fatoohi who approached me for this book project, and to the Packt team whose sound comments were greatly appreciated during the production. My thanks also go to the excellent reviewers Kai 'Oswald' Seidler, Ben Dodson, and Michal Čihař. Their sharp eyes helped in making this book clearer and more complete.
Finally, I wish to thank all contributors to phpMyAdmin's source code, translations, and documentation; their dedication to this project continues to push me forward.
About the Reviewers
Michal Čihař was born in 1980 in Prague, the capital city of the Czech Republic, and he is still living there. He studied Software Engineering at the Czech Technical University, and during these studies he started contributing to several free software projects, with the biggest contributions being made to phpMyAdmin and Gammu, and related projects. He currently works for Novell, mostly on the SUSE Linux Enterprise platform.
Michal has been active in the phpMyAdmin project since 2001, when he started as a translator for the Czech language, later moving to a developer role (working, for example, on index manipulation, and export and import subsystems), and since 2010 he has also acted as a secondary project manager.
Ben Dodson has worked with the Internet for over 15 years, yet originally studied Politics. He trained briefly as an Air Traffic Controller for the RAF before deciding to change career paths and work as a freelance HTML/PHP developer, in Devon. He relocated to London to work for a digital agency, and was rapidly promoted to Development Manager in charge of a team of front- and back-end web developers in several countries.
After working for a few more agencies, he decided to return to freelancing but this time as an iPhone Developer. He now splits his time in Central London between working on his own applications and working on applications for brand-name clients, incorporating everything from store locators to iPad magazine concepts.
Ben still maintains a strong connection to his web development roots by working on APIs for geo-coded Wikipedia Articles (www.wikilocation.org) and the London Underground (www.tubeupdates.com). One of his main interests is in the power of location-aware applications and so he has spent a lot of time developing Gowalla Tools, a suite of applications that utilize the popular Gowalla geo-location service.
Aside from his websites and iPhone applications, Ben enjoys working on everything from browser extensions to OpenID servers. He is a keen photographer and designer, and is currently working on his first book, which discusses Machiavellian republicanism.
Ben has been invited to speak at several conferences and has appeared on BBC Television's "Working Lunch" program to discuss e-commerce, and also Channel Five's "The Gadget Show" to demonstrate iPhone Application development.
Kai 'Oswald' Seidler was born in Hamburg in 1970. He graduated from the Technical University of Berlin with a Diplom Informatiker degree (Master of Science equivalent) in Computer Science. In the '90s he created and managed Germany's biggest IRCnet server, irc.fu-berlin.de, and co-managed one of the world's largest anonymous FTP servers, ftp.cs.tu-berlin.de. He professionally set up his first public web server in 1993. From 1993 until 1998, he was a member of Projektgruppe Kulturraum Internet, a research project on net culture and network organization. In 2002, he co-founded Apache Friends and created the multi-platform Apache web server bundle XAMPP. Around 2005, XAMPP became the most popular Apache stack worldwide. In 2006, his third book, Das XAMPP-Handbuch, was published by Addison Wesley.
Currently he's working as a technology evangelist for web tier products at Sun Microsystems.
To Carole, André, Corinne, Annie, and Guillaume, with all my love.
Preface
phpMyAdmin is an open source tool written in PHP. It handles the administration of MySQL over the World Wide Web (WWW). It can perform various tasks, such as creating, modifying, or deleting databases, tables, fields, or rows. It can also execute SQL statements and 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.3.x 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 to 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. You will first create two basic tables, and then edit and delete data, tables, and databases. As backups are crucial to a project, you will create up-to-date backups and take intermediary snapshots during development and production phases. Then you will look into importing the data that you have exported. You will also explore the various search mechanisms, and query across multiple tables.
Then, you will learn some advanced features, such as defining inter-table relations and installing the linked-tables infrastructure. Some queries are out of the scope of the interface; you will enter SQL commands to accomplish these tasks.
You will also learn some new features introduced in version 3.3.x, such as synchronizing databases on different servers, and managing MySQL replication in order 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.1. Version 3.3.x introduced features such as new import and export modules, tracking changes, synchronizing structure and data between servers, providing support for replication.
What this book covers
Chapter1, 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.
Chapter 2, Configuring Authentication and Security, provides an overview of various authentication types used in phpMyAdmin. It then covers the security issues related to the phpMyAdmin installation.
Chapter 3, Over Viewing the Interface, gives us an overview of the phpMyAdmin interface. This includes the login panel, the navigation and main panels in both Light mode and Full mode, and the Query window.
Chapter 4, Taking First Steps, is all about database creation. It teaches us how to create a table, how to insert data manually, and how to sort the data.
Chapter 5, Changing Data and Structure, covers the various aspects of data editing in phpMyAdmin. It teaches us how to handle NULL values, multi-row editing, and data deletion. Finally, it explores the subject of changing the structure of tables, focusing on editing field attributes 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 can be sent.
Chapter 7, Importing Structure and Data, tells us how to bring back exported data that was created for backup and transfer purposes. It covers the various options available in phpMyAdmin to import data, and different mechanisms involved in importing SQL and CSV files. Finally, it covers the limitations that may be faced while importing files, and the ways to overcome these limitations.
Chapter 8, Searching Data, presents the mechanisms that are useful for searching data effectively.
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 the advanced features of phpMyAdmin. The chapter explains how to define inter-table relations. It also explains how to install the linked-tables infrastructure—a prerequisite for the advanced features.
Chapter 11, Entering SQL Commands, teaches us how to enter our own SQL commands. The chapter also covers the Query window—the window used to edit an SQL query. Finally, it also shows us how to obtain the history of typed commands.
Chapter 12, Generating Multi-table Queries, covers the multi-table query generator, which allows us to produce these queries without actually typing them.
Chapter 13, Synchronizing Data and Supporting Replication, teaches us how to synchronize databases on the same server, or from one server to another one. It then covers how to manage MySQL replication.
Chapter 14, Using Bookmarks, covers one of the features of the linked-tables infrastructure. It explains how to record bookmarks and how to manipulate them. Finally, it covers how to pass parameters to bookmarks.
Chapter 15, Documenting the System, gives an overview of how to produce documentation that explains the structure of a database, by using the tools offered by phpMyAdmin.
Chapter 16, Transforming Data Using MIME, explains how to apply transformations to data in order to customize its format at view time.
Chapter 17, Supporting MySQL 5.0 and 5.1, covers phpMyAdmin's support for the MySQL features that are new in these versions.
Chapter 18, Tracking Changes, teaches us how to record structure and data changes done from the phpMyAdmin interface.
Chapter 19, Administrating the MySQL Server with phpMyAdmin, 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 A, The History of phpMyAdmin, provides a history of the project, from its roots back in 1998 through the project re-launch in 2001, and its subsequent evolution.
Appendix B, Troubleshooting and Support, explains how to troubleshoot phpMyAdmin by examining some of its error messages, and proposing appropriate solutions. 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:
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.
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 <[email protected]>, and mention the book title via the subject of your message.
If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or e-mail <[email protected]>.
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.
Tip
Downloading the example code for this book
You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.
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 on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
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 <[email protected]> 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 <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.
Chapter 1. Getting Started with phpMyAdmin
I wish you a warm welcome to this book! The goal of this first chapter is to:
Introducing phpMyAdmin
This section describes the place of phpMyAdmin in the context of PHP/MySQL web applications.
Web applications
The Web has evolved! In the last few years the Web has changed dramatically. In its infancy, the Web was a medium used mainly to convey static information ("Look, my home-page is on the Web!"). Now, large parts of the Web carry information that is dynamically generated by application programs on which enterprises, and even individuals, rely for their intranets and public websites.
Because of the clear benefits of databases—better accessibility and structuring of information—web applications are mostly database driven. While the frontend is usually a well-known (and quickly deployed) web browser, there is a database system at the backend. Application programs provide the interface between the browser and the database.
Those who are not operating a database-driven website are not using the medium to its fullest capability. Also, they could be lagging behind competitors who have made the switch. So, it is not a question of whether we should implement a database-driven site, but rather about when and how to implement it.
Why web applications? Because they improve the user experience and involve users in the process by opening up possibilities such as:
Nowadays, WWW might stand for World Wide Wave—a big wave that profoundly modifies the way developers think about user interface, data presentation, and most of all, the way data reaches users and comes back to the data center.
PHP and MySQL: The leading open source duo
When we look at the web applications platforms currently offered by host providers, we will see that most prevalent is the PHP/MySQL combination.
Well-supported by their respective homesites—http://www.php.net and http://www.mysql.com—this duo has enabled developers to build a lot of ready-made open source web applications and, most importantly, enabled in-house developers to quickly put solid web solutions in place.
MySQL, which is mostly compliant with the SQL:2003 standard, is a database system well known for its speed, robustness, and a small connection overhead. This is important in a web context where pages must be served as quickly as possible.
PHP, usually installed as a module on the web server, is a popular scripting language in which applications are written to communicate with MySQL (or other database systems) on the backend and browsers on the frontend. Ironically, the acronym's significance has evolved along with the evolution of the Web, from Personal HomePage to Professional HomePage to its current recursive definition: PHP: Hypertext Processor. An explanation of the successive name changes can be seen in PHP's source code at http://svn.php.net/viewvc/archived/php3/trunk/CHANGES?r1=5246&r2=5459. Available on millions of web domains, PHP drives its own wave of quickly-developing applications.
What is phpMyAdmin?
phpMyAdmin (see the official home page at http://www.phpmyadmin.net) is a web application written in PHP; it contains (like most web applications) XHTML, CSS, and JavaScript client code. This application provides a complete web interface for administering MySQL databases, and is widely recognized as the leading application in this field. Being open source since its birth, it has enjoyed support from numerous developers and translators worldwide (being translated into 58 languages at the time of writing this book). The project is currently hosted at Sourceforge.net and developed using their facilities by the phpMyAdmin team.
Host providers everywhere are showing their trust in phpMyAdmin by installing it on their servers. The popular cPanel (a website control application) interfaces with phpMyAdmin. In addition, we can install our own copy of phpMyAdmin inside our webspace, as long as our provider respects the minimum requirements (see the System requirements section later in this chapter).
The goal of phpMyAdmin is to offer the complete web-based management of MySQL servers and data, and to keep up with the evolution of MySQL and web standards. While the product is always evolving, it supports all standard operations, along with extra features.
The development team constantly fine-tunes the product based on the reported bugs and requested features, releasing new versions regularly.
phpMyAdmin offers features that cover basic MySQL database and table operations. It also has an internal relational system that maintains metadata to support advanced features. Finally, system administrators can manage users and privileges from phpMyAdmin. It is important to note that phpMyAdmin's choice of available operations depends on the rights the user has on a specific MySQL server.
Project documentation
Further information about phpMyAdmin is available on the homesite's documentation page, located at http://www.phpmyadmin.net/home_page/docs.php. Moreover, the development team, helped by the community, maintains a wiki at http://wiki.phpmyadmin.net.
Installing phpMyAdmin
It's time to install the product and configure it minimally for first-time use.
Our reason for installing phpMyAdmin could be one of the following:
Required information
Some host providers offer an integrated web panel where we can manage accounts, including MySQL accounts, and also a file manager that can be used to upload web content. Depending on this, the mechanism that we use to transfer phpMyAdmin source files to our webspace may vary. We will need some specific information (listed below) before starting the installation:
System requirements
The up-to-date requirements for a specific phpMyAdmin version are always stated in the accompanying Documentation.html file. For phpMyAdmin 3.3, the minimum PHP version required is PHP 5.2 with session support and the Standard PHP Library (SPL). Moreover, the web server must have access to a MySQL server (version 5.0 or later)The up-to-date requirements for a specific phpMyAdmin version—either locally or on a remote machine. It is strongly recommended that the PHP mcrypt extension be present for improved performance in cookie authentication mode (more on this in Chapter 2, Configuring Authentication and Security). In fact, on a 64-bit server, this extension is required.
On the browser side, cookie support must be activated, whatever authentication mode we use.
Downloading the files
There are various files available in the Download section of http://www.phpmyadmin.net. There might be more than one version offered here, and it is always a good idea to download the latest stable version. We only need to download one file, which works regardless of the platform (browser, web server, MySQL, or PHP version). For version 3.3, there are two groups of files— english and all-languages. If we need only the English interface, we can download a file whose name contains "english"—for example, phpMyAdmin-3.3.2-english.zip. On the other hand, if we have the need for at least one other language, choosing all-languages would be appropriate.
If we are using a server supporting only PHP4—for which the PHP team has discontinued support since December 31, 2007—the latest stable version of phpMyAdmin is not a good choice for download. I recommend using version 2.11.x, which is the latest branch that supports PHP4.
The files offered have various extensions: .zip, .tar.bz2, .tar.gz, .7z. Download a file having an extension for which you have the corresponding extractor. In the Windows world, .zip is the most universal file format, although the files are bigger than .gz or .bz2 files (which are common in the Linux/Unix world). The .7z extension denotes a 7-Zip file, which is a format that achieves a higher compression ratio than the other formats offered—an extractor is available at http://www.7-zip.org. In the following examples, we will assume that the chosen file was phpMyAdmin-3.3.2-all-languages.zip.
After clicking on the appropriate file, the nearest mirror site will be chosen by Sourceforge.net. The file will start to download, and we can save it on our computer.
Installing on different platforms
The next step depends on the platform you are using. The following sections detail the procedures for some common platforms. You may proceed directly to the relevant section.
Installing on a remote server using a Windows machine
Using the File explorer, we double-click the phpMyAdmin-3.3.2-all-languages.zip file that we just downloaded on the Windows machine. A file extractor will start, showing us all of the scripts and directories inside a main phpMyAdmin-3.3.2-all-languages directory.
Use whatever mechanism your file extractor offers to save all the files, including subdirectories, to some location on your workstation. Here, we have chosen C:\.Therefore, a C:\phpMyAdmin-3.3.2-all-languages directory has been created for extraction.
Now, it's time to transfer the entire directory structure C:\phpMyAdmin-3.3.2-all-languages to the web server in our webspace. We use our favorite SFTP or FTP software, or the web control panel, for the transfer.
The exact directory under which we transfer phpMyAdmin may vary. It could be our public_html directory or another directory to which we usually transfer web documents. For further instructions about the exact directory to be used, or the best way to transfer the directory structure, we can consult our host provider's help desk.
After the transfer is complete, these files can be removed from our Windows machine as they are no longer needed.
Installing on a local Linux server
Let's say we chose phpMyAdmin-3.3.2-all-languages.tar.gz and downloaded it directly to some directory on the Linux server. We move it to our web server's document root directory (for example, /var/www/html) or to one of its subdirectories (for example, /var/www/html/utilities). We then extract it by issuing the following shell command or by using any graphical file extractor that our window manager offers:
We must ensure that the permissions and ownership of the directory and files are appropriate for our web server. The web server user or group must be able to read them.
Installing on local Windows servers (Apache, IIS)
The procedure here is similar to that described in the Installation on a remote server using a Windows machine section, except that the target directory will be under our DocumentRoot (for Apache) or our wwwroot (for IIS). Of course, we do not need to transfer anything after modifications are made to config.inc.php (described in the next section), as the directory is already on the webspace.
Apache is usually run as a service. Hence, we have to ensure that the user under whom the service is running has normal read privileges to access our newly-created directory. The same principle applies to IIS, which uses the IUSR_machinename user. This user must have read access to the directory. You can adjust permissions in the Security/permissions tab of the directory's properties.
