PHP & MySQL: Novice to Ninja, 5th Edition (2012)

Chapter 10. MySQL Administration

At the core of most well-designed content-driven sites is a relational database. In this book, we’ve used the MySQL Relational Database Management System (RDBMS) to create our database. MySQL is a popular choice among web developers because it’s free, and because MySQL servers are fairly simple to set up. As I demonstrated in Chapter 1, armed with proper instructions a new user can have a MySQL server up and running in less than five minutes—under two if you practice a little! If all you want is a MySQL server to play with a few examples and experiment a little, the initial installation process we went through in Chapter 1 is likely to be all you’ll need. If, on the other hand, you want to set up a database back end to a real live website—perhaps a site your company depends on—there are a few more fundamentals you’ll need to learn before you can rely on a MySQL server day-in, day-out. First, we’ll look at backups. Backing up data that’s important to you or your business should be an essential item on any administrator’s list of priorities. Because administrators usually have more interesting work to do, though, backup procedures are often arranged once out of necessity and deemed “good enough” for all applications. If, until now, your answer to the question, “Should we back up our databases?” has been, “It’s okay; they’ll be backed up along with everything else,” you really should read on. I’ll show you why a generic file-backup solution is inadequate for many MySQL installations, and I’ll demonstrate the right way to back up and restore a MySQL database. Next, it’s time we looked more closely at how to control access to your MySQL database. I showed you the basics early in this book, but it turns out there are some tricky details that can make your life difficult if you don’t understand them. Oh, and I’ll show you how to regain control of your MySQL server should you forget your password! Then we’ll turn our attention to performance, and how to keep your SELECT queries running quickly. With the careful application of database indexes (a skill many working PHP developers lack, surprisingly), you can keep your database speedy even as it grows to contain thousands (or even hundreds of thousands) of rows. Finally, I’ll show you how to use a relatively new feature of MySQL—foreign keys—to express the structure of your database, and how each of the tables it contains are related to one another. As you can see, this chapter’s a real mixed bag, but by the end of it you’ll understand MySQL a whole lot better!

Backing Up MySQL Databases

Like web servers, most MySQL servers are expected to remain online 24 hours a day, seven days a week. This makes backups of MySQL database files problematic. Because the MySQL server uses memory caches and buffers to improve the efficiency of updates to the database files stored on disk, these files may be in an inconsistent state at any given time. Since standard backup procedures involve merely copying system and data files, backups of MySQL data files are unreliable, as there’s no guarantee the files that are copied are in a fit state to be used as replacements in the event of a crash. Furthermore, as many website databases receive new information at all hours of the day, standard backups can provide only periodic snapshots of database data. Any information stored in the database that’s changed after the last backup will be lost in the event that the live MySQL data files are destroyed or become unusable. In many situations, such as when a MySQL server is used to track customer orders on an ecommerce site, this is an unacceptable loss. Facilities exist in MySQL to keep up-to-date backups that are largely unaffected by server activity at the time at which the backups are generated. Unfortunately, they require you to set up a backup scheme specifically for your MySQL data, completely apart from whatever backup measures you’ve established for the rest of your data. As with any good backup system, however, you’ll appreciate it when the time comes to use it.

Database Backups Using phpMyAdmin

The browser-based MySQL administration tool we’ve been using throughout this book, phpMyAdmin, also offers a convenient facility for obtaining a backup of your site’s database. Once you’ve selected your database, click the Export tab as shown in Figure 10.1.

Click Export to save a backup of your database

Figure 10.1. Click Export to save a backup of your database

The default Export options, to perform a Quick export in SQL format, are perfect for our needs. Just click Go and your browser will download an ijdb.sql file (assuming your database is named ijdb). If you open this file in a text editor, you’ll find it contains a series of SQL CREATE TABLE andINSERT commands that, if run on a blank database, would reproduce the current contents of your database. Yes, a MySQL database backup is just a series of SQL commands! To restore your database from a backup file like this one, first make sure your database is empty (select all the tables on the Structure tab and from the With selected menu choose Drop); then just click the Import tab and select the backup file from your computer (as before, the other default options on this page are fine). Moments later, the contents of your database will be restored to their previous state. In this way, we can use phpMyAdmin to create backups of our databases. phpMyAdmin connects to the MySQL server to perform backups, rather than accessing directly the MySQL database data files. The backup it produces is therefore guaranteed to be a valid copy of the database, instead of merely a point-in-time snapshot of the database files stored on disk, which may be in a state of flux as long as the MySQL server is running.

Database Backups Using mysqldump

phpMyAdmin makes it really easy to obtain a database backup whenever the mood strikes you, but the best backups are automated, and an automated backup tool phpMyAdmin is not. As you’ll already know if you’ve ever worked with MySQL on Linux, the MySQL database server software comes with a handful of utility programs designed to be run from the command prompt. One of these programs is mysqldump. When run, mysqldump connects to a MySQL server (in much the same way as PHP does) and downloads the complete contents of the database(s) you specify. It then outputs the series of SQL commands required to create a database with those same contents. If you save the output of mysqldump to a file, you’ll have yourself the same kind of backup file that phpMyAdmin can generate for you!

Warning: New to the Command Prompt?

If you’re unfamiliar with your operating system’s command prompt, some of the instructions that follow may likely confuse you. Don’t sweat it! If you find yourself floundering, feel free to skip ahead to the next section of this chapter. Practically speaking, there’s no real need to set up automated MySQL backups on your development server. When you shut down XAMPP at the end of the day, whatever backup software you run on your computer (you do back up your computer, don’t you?) will be able to back up your MySQL database files in their dormant state. As for the production MySQL server you use when you launch your website to the Internet at large, any good web host will handle your MySQL backups for you. You’ll probably only need to set up automated MySQL backups if you launch and manage your own production database server. I’m going to assume that if that’s a job you’re tackling, you’re already familiar with using the command prompt on your operating system of choice. That said, if you’re a Windows user especially keen to learn this stuff, I wrote an article back in 2002 called Kev’s Command Prompt Cheat Sheet that will fill you in on the basics.

The following command (typed all on one line) connects to the MySQL server running on the local machine as user root with password password, and saves a backup of the ijdb database into the file ijdb.sql:[54]

                  mysqldump -u root -ppassword ijdb > ijdb.sql

To restore this database after a server crash, you could again feed this SQL file to phpMyAdmin;[55] alternatively, you could use the mysql utility program:

                  mysql -u root -ppassword ijdb < ijdb.sql

This command connects to the MySQL server, selects the ijdb database, and feeds in our backup file as a list of SQL commands to be executed. But how do we bridge the gap between these snapshots to maintain a database backup that’s always up to date?

Incremental Backups Using Binary Logs

As I mentioned, many situations in which MySQL databases are used would make the loss of data—any data—unacceptable. In cases like these, we need a way to bridge the gaps between the backups we made using phpMyAdmin or mysqldump as recently described. The solution is to configure the MySQL server to keep a binary log, a record of all SQL queries that were received by the database, and which modified the contents of the database in some way. This includes INSERT, UPDATE, and DELETE statements (among others), but excludes SELECT statements. The basic idea of a binary log is that you should be able to restore the contents of the database at the very moment at which a disaster occurs. This restoration involves applying a backup (made using phpMyAdmin or mysqldump), and then applying the contents of the binary logs that were generated after that backup was made. You can also edit binary logs to undo mistakes that might have been made. For example, if a co-worker comes to you after accidentally issuing a DROP TABLE command, you can export your binary log to a text file and then edit that file to remove the command. You can then restore the database using your last backup and then running the edited binary log. In this way, you can even preserve database changes that were made after the accident. And, as a precaution, you should probably also revoke your co-worker’s DROP privileges. To tell your MySQL server to keep binary logs, you need to edit the server’s my.ini (Windows) or my.cnf (OS X or Linux) configuration file. This is a simple text file with a list of options that control some of the finer points of how your MySQL server works. In many cases, MySQL is installed without a configuration file, and simply runs with the default settings. In this situation, you’ll need to create a new file and set the appropriate option.

Note: Where does my.ini/my.cnf belong?

On Windows, XAMPP comes with a my.ini file already made for you: C:\xampp\mysql\bin\my.ini. You can open it in Notepad to make changes. On OS X, the MySQL server built into MAMP will look for a /etc/my.cnf file to read when it starts up. You may have some trouble creating this file (Finder generally keeps you from messing with files in sensitive locations like this); you’ll need to create it elsewhere, and then use a sudo mv command in Terminal to move it to /etc with administrator privileges:

                  sudo mv my.cnf /etc/

To enable binary logging, you add a log-bin setting to the [mysqld] section of your configuration file. If you’re creating a new configuration file from scratch, you’ll have to type the [mysqld] section heading yourself on the first line before adding the setting on the next line. The log-binsetting tells MySQL where to store the binary log files and what name to give them. On Windows, for example, you might want it to store them in your MySQL data directory:[56]

[mysqld]

log-bin="C:/xampp/mysql/data/binlog"

On OS X, MAMP’s logs folder is a good place for them:

[mysqld]

log-bin=/Applications/MAMP/logs/binlog

Both these examples tell MySQL to create files named binlog.000001, binlog.000002, and so on. A new file will be created each time the server flushes its log files; in practice, this occurs whenever the server is restarted.

Tip: Store Binary Logs on Another Hard Disk

If possible, you should store your binary logs on a hard disk other than the one where your MySQL database files are stored. That way, if a hard disk goes bad, you won’t lose both your database and your backups!

With your new configuration file in place, restart your MySQL server. From now on, the server will create binary log files. To make sure, check the location you specified to verify that a new log file was created when the server started up. Obviously, binary logs can take up a lot of space on an active server. For this reason, it’s important to tell MySQL to delete obsolete binary logs whenever you perform a full backup. This is easy to do if you use mysqldump to perform your backup:

               mysqldump -u root -ppassword --flush-logs

--master-data=2 --delete-master-logs ijdb > ijdb.sql

The --flush-logs option tells the MySQL server to close the current binary log file and start a new one, as if the MySQL server had been restarted. The --master-data=2 option instructs mysqldump to include a comment at the end of the ijdb.sql file that indicates the name of the new binary log file; this will contain the first changes that are made to the database following the full backup. Finally, the --delete-master-logs command tells mysqldump to delete the binary log files that are no longer needed, now that a full backup has taken place. In the event of a disaster, as long as you have a full backup and the binary log files that were generated after the backup was made, restoring your database should be fairly simple. Set up a new, empty MySQL server, then apply the full backup as described in the previous section. All that’s left is to apply the binary logs using the mysqlbinlog utility program. mysqlbinlog’s job is to convert the data format of MySQL binary logs into SQL commands that you can run on your database. Say you had two binary log files that you needed to apply after restoring your most recent full backup. You can generate an SQL text file from the two files using mysqlbinlog, and then apply that file to your MySQL server just as you would a file generated by mysqldump:

               mysqlbinlog binlog.000041 binlog.000042 > binlog.sql

               mysql -u root -ppassword < binlog.sql

MySQL Access Control Tips

In Chapter 2, I mentioned that the database called mysql, which appears on every MySQL server, is used to keep track of users, their passwords, and what they’re allowed to do. In Chapter 4, I showed you how to use phpMyAdmin to create another user account, with access only to your website’s database. The MySQL access control system is fully documented in Chapter 5 of the MySQL Reference Manual. In essence, user access is governed by the contents of five tables in the mysql database: user, db, host, tables_priv, and columns_priv. If you plan to edit these tables directly using INSERT, UPDATE, and DELETE statements, I suggest you read the relevant section of the MySQL manual first. But, for us mere mortals, phpMyAdmin provides all the tools you’ll need to manage access to your MySQL server. As a result of the way the access control system in MySQL works, there are a couple of idiosyncrasies of which you should be aware if you’re going to be responsible for controlling access to a MySQL server.

Host Name Issues

When you create users that can log in to the MySQL server only from the computer on which that server is running (for example, you require them to log in to the server and run the mysql command prompt utility from there, or to communicate using server-side scripts like PHP), you may ask yourself what to enter in the Host field of phpMyAdmin’s Add a new User form. Imagine the server is running on www.example.com. Should you specify the Host as www.example.com or localhost? The answer is that both are unreliable to handle all connections. In theory, if, when connecting, the user specifies the host name either with the mysql command prompt utility program, or with PHP’s PDO class, that host name will have to match the entry in the access control system. However, as you probably want to avoid forcing your users to specify the host name a particular way (in fact, users of the mysql utility program are likely to want to steer clear of stating the host name at all), it’s best to use a workaround. For users who need the ability to connect from the machine on which the MySQL server is running, it’s best to create two user entries in the MySQL access system: one with the actual host name of the machine (www.example.com, for example), the other with localhost. Of course, you’ll have to grant/revoke all privileges to both user entries individually, but it’s the only workaround that you can really rely upon. Another problem commonly faced by MySQL administrators is that user entries whose host names contain wild cards (for example, %.example.com) may fail to work. When MySQL’s access control system behaves unexpectedly, it’s usually due to the way MySQL prioritizes the user entries. In particular, it orders entries so that more specific host names appear first (for example, www.example.com is absolutely specific, %.example.com is less specific, and % is totally unspecific). In a fresh installation,[57] the MySQL access control system contains two anonymous user entries (these allow connections to be made from the local host using any username—the two entries support connections from localhost and the server’s actual host name, as described before), and two root user entries. The problem just described occurs when the anonymous user entries take precedence over our new entry because their host name is more specific. Let’s look at the abridged contents of the user table on www.example.com, a fictitious MySQL server, where we’ve just added a new account for a user named Jess. The rows are sorted in the order in which the MySQL server considers them when it validates a connection:

Host

User

Password

localhost

root

encrypted value

www.example.com

root

encrypted value

localhost

   

www.example.com

   

%.example.com

jess

encrypted value

As you can see, since Jess’s entry has the least specific host name, it comes last in the list. When Jess attempts to connect from www.example.com, the MySQL server matches her connection attempt to one of the anonymous user entries (a blank User value matches anyone). Since a password is unnecessary for these anonymous entries, and presumably Jess enters her password, MySQL rejects the connection attempt. Even if Jess managed to connect without a password, she would be given the very limited privileges that are assigned to anonymous users, as opposed to the privileges assigned to her entry in the access control system. The solution is either to make your first order of business as a MySQL administrator the deletion of those anonymous user entries (DELETE FROM mysql.user WHERE User=""), or to give two more entries to all users who need to connect from localhost (that is, entries for localhost and the actual host name of the server):

Host

User

Password

localhost

root

encrypted value

www.example.com

root

encrypted value

localhost

jess

encrypted value

www.example.com

jess

encrypted value

localhost

   

www.example.com

   

%.example.com

jess

encrypted value

As it’s excessively burdensome to maintain three user entries (and three sets of privileges) for each user, I recommend that you remove the anonymous users, unless you have a particular need for them:

Host

User

Password

localhost

root

encrypted value

www.example.com

root

encrypted value

%.example.com

jess

encrypted value

Locked Out?

Like locking your keys in the car, forgetting your password after you’ve spent an hour installing and tweaking a new MySQL server can be an embarrassment—to say the least! Fortunately, if you have administrator access to the computer on which the MySQL server is running, or if you can log in as the user you set up to run the MySQL server, all is well. The following procedure will let you regain control of the server.

Warning: Command Prompt Knowledge Assumed

Again, I’m going to go ahead and assume in this section that if you’re locked out of your MySQL server, you know how to use your system’s command prompt. If you’re using the MySQL server bundled with XAMPP or MAMP on your development machine, there’s probably no mission-critical data stored in your database, and the easiest way to recover from a lost root password will usually be just to reinstall your server and start from scratch. That said, if you’re a Windows user, my trusty old Kev’s Command Prompt Cheat Sheet article should provide everything you need to get going.

First, you must shut down the MySQL server. If you normally do this using the mysqladmin command prompt utility, which requires your forgotten password, you’ll instead need to kill the server process to shut it down. Under Windows, use Task Manager to find and end the MySQL process, or simply stop the MySQL service if you’ve installed it as such. Under OS X or Linux, use the ps command, or look in the server’s PID file in the MySQL data directory to determine the process ID of the MySQL server; then terminate it with this command:

                  kill pid

pid is the process ID of the MySQL server. This should be enough to stop the server. Do not use kill -9 (a forced kill) unless absolutely necessary, as this may damage your data files. If you’re forced to do so, however, the next section provides instructions on how to check and repair those files. Now that the server’s down, you must restart it using the skip-grant-tables option. You can do this by adding the option to your MySQL server’s my.ini or my.cnf configuration file (see the instructions for setting up such a file in the section called “Incremental Backups Using Binary Logs”):

[mysqld]

skip-grant-tables

This instructs the MySQL server to allow unrestricted access to anyone. Obviously, you’ll want to run the server in this mode as briefly as possible, to avoid the inherent security risks. Once you’re connected to your server (using phpMyAdmin or the mysql command prompt utility), change your root password to a memorable one:

                  UPDATE mysql.user SET Password=PASSWORD("newpassword")

                  WHERE User="root"

Finally, disconnect, shut down your MySQL server, and remove the skip-grant-tables option. That does it—and nobody ever has to know what you did. As for locking your keys in your car, you’re on your own there.

Indexes

Just like the index in this book makes it a lot easier to find every mention of a particular topic in its pages, a database index can make it much easier for MySQL to find the records you’ve asked for in a SELECT query. Let me give you an example. As the Internet Joke Database grows, the joketable might grow to contain thousands, if not hundreds of thousands of rows. Now let’s say PHP asks for the text of a particular joke:

SELECT joketext FROM joke WHERE id = 1234

In the absence of an index, MySQL must look at the value of the id column in each and every row of the joke table, one by one, until it finds the one with the value 1234. Worse yet, without an index, MySQL has no way of knowing that there is only one row with that value, so it must also scan the rest of the table for more matching rows to make sure it gets them all! Computers are fast, and good at menial labor, but in the web development game where half seconds count, large tables and complex WHERE clauses can easily combine to create delays of 30 seconds or more! Fortunately for us, this query will always run quickly, and that’s because the id column of the joke table has an index. To see it, open phpMyAdmin, select the joke table, and click the Structure tab. There, below the list of columns in the table, you’ll see the list of indexes shown inFigure 10.2.

Each of our tables already has a single index

Figure 10.2. Each of our tables already has a single index

Take a look at the Column column; this index lists the values of the id column. From the name in the Keyname column, PRIMARY, you might even guess where this index came from: it was created automatically when we told MySQL to make the id column the primary key of this table. Remember how we defined the id column of the table:

CREATE TABLE joke (

  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

  joketext TEXT,

  jokedate DATE NOT NULL,

  authorid INT

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

In fact, “key” is just a fancy way to say “index” in database parlance, and a primary key is just an index named PRIMARY that requires each value in the table for that particular column to be unique. Note in Figure 10.2 that the Unique property of the index is Yes. What all this boils down to is that every database table we’ve created so far has an index on its id column. Any WHERE clause that seeks a particular id value will be able to find the record with that value quickly, because it will be able to look it up in the index. You can confirm this by asking MySQL to explain how it performs a particular SELECT query. To do this, just add the command EXPLAIN at the start of the query:

EXPLAIN SELECT joketext FROM joke WHERE id = 1

Note: Use Real Values to See Real Results

Note that I’ve specified a joke ID of 1 in this query, which actually exists in my database. Had I used a made-up value like 1234, MySQL is smart enough to know that this ID didn’t exist in the joke table and wouldn’t even try to fetch results from the table.

If you run this EXPLAIN query in phpMyAdmin, you will have a similar view to Figure 10.3.

These results confirm that the SELECT query will use the PRIMARY index

Figure 10.3. These results confirm that the SELECT query will use the PRIMARY index

By the same mechanism, an SQL query that joins two tables together using id values (for example, finding the author that goes with each joke using the value of the authorid column) will be able to find related records quickly. Now consider this SELECT query, which fetches all jokes by a particular author:

SELECT * FROM joke WHERE authorid = 2

Ask MySQL to EXPLAIN this SELECT, and Figure 10.4 shows the result.

Those NULLs indicate slowness

Figure 10.4. Those NULLs indicate slowness

As you can see, MySQL is unable to find an index to assist with this query, so it is forced to perform a complete scan of the table for results. We can speed up this query by adding an index to the table for the authorid column.

Note: But surely author IDs are already indexed?

Yes, the id column of the author table has an index by virtue of it being the primary key for that table. This won’t help in the case of this query, however, which has no involvement with the author table at all. The WHERE clause in this case is looking for a value in the authoridfield of the joke table, which is without an index.

In phpMyAdmin, select the joke table, click the Structure tab, then under Indexes use the form to “Create an index on 1 columns.” This will give you the Create a new index form shown in Figure 10.5.

Creating a new index for the authorid column

Figure 10.5. Creating a new index for the authorid column

Fill out the form as shown:

·        Set the Index name to match the name of the column it will index (although you could actually call it anything you like).

·        Choose to create a plain INDEX as opposed to a PRIMARY index (the table already has one), a UNIQUE index (there’s no requirement for each joke to have a unique author), or FULLTEXT index (an index used for searching large amounts of text).

·        Select the column to index—authorid in this case. Leave the Size field blank to index the full value of the column (as opposed to, say, just the first few characters of a text column).

Click Save, and you should see the second index listed along with the PRIMARY index. Ask MySQL to EXPLAIN the SELECT query again to confirm that it will use your new authorid index this time. It might be tempting to index each and every column in your database, but I’d advise against it. Not only do indexes require extra disk space, but every time you make a change to the contents of your database (with an INSERT or UPDATE query, for example), MySQL has to spend time rebuilding all affected indexes! For this reason, you should usually add the indexes required to keep your website’s SELECT queries speedy and no more.

Multicolumn Indexes

But wait! Not every table we’ve created so far has an id column. What about the jokecategory table?

CREATE TABLE jokecategory (

  jokeid INT NOT NULL,

  categoryid INT NOT NULL,

  PRIMARY KEY (jokeid, categoryid)

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

This table’s primary key is made up of two columns: jokeid and categoryid. Figure 10.6 shows what this index looks like in phpMyAdmin.

Indexes can contain multiple columns

Figure 10.6. Indexes can contain multiple columns

A multicolumn index like this is called a composite index . It’s great at speeding up queries that involve both indexed columns, like this one that checks if joke ID 3 is in category ID 4 (it is):

SELECT * FROM jokecategory WHERE jokeid = 3 AND categoryid = 4

A two-column index like this one can also be used as a one-column index on the first column in the list. In this case, that’s the jokeid field, so this query will also use the index to list the categories that joke ID 1 belongs to:

SELECT name

FROM jokecategory

  INNER JOIN category ON categoryid = category.id

WHERE jokeid = 1

Foreign Keys

By now, you should be used to the concept of a column in one table pointing to the id column in another table to represent a relationship between the two tables. For example, the authorid column in joke points to the id column in author to record which author wrote each joke. In database design lingo, a column that contains values that match those in another table is called a foreign key. That is, we say that authorid is a foreign key that references the id column in author. Up to this point, we’ve simply designed tables with foreign key relationships in mind, but these relationships have not been enforced by MySQL. That is, we’ve made sure to only store values in authorid that correspond to entries in the author table. But if we carelessly inserted an authorid value without any matches for an author record, MySQL would do nothing to stop us; as far as MySQL is concerned, authorid is just a column that contains whole numbers. MySQL supports a feature called foreign key constraints , which you can use to record relationships between tables like this one explicitly and have MySQL enforce them. You can include foreign key constraints in your CREATE TABLE commands, or you can add foreign key constraints to existing tables using ALTER TABLE:

chapter10/sql/ijdb.sql (excerpt)

CREATE TABLE joke (

  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

  joketext TEXT,

  jokedate DATE NOT NULL,

  authorid INT,

  FOREIGN KEY (authorid) REFERENCES author (id)

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB

ALTER TABLE joke

ADD FOREIGN KEY (authorid) REFERENCES author (id)

You can also use phpMyAdmin to create foreign key constraints. First, you must make sure the foreign key column (authorid in this case) has an index. MySQL will create this index for you automatically if you use either of these two queries, but phpMyAdmin requires you to do it yourself. Thankfully, we already added an index to authorid. Next, on the Structure tab for the joke table, click Relation view as shown in Figure 10.7.

Clicking Relation view to edit the foreign keys in a table

Figure 10.7. Clicking Relation view to edit the foreign keys in a table

The resulting page shows each of the columns in the table, and lets you configure a foreign key constraint for each one that has an index. For the authorid column, under Foreign key constraint (INNODB) choose `ijdb`.`author`.`id` (where ijdb is the name of your database). Leave the Internal relation option blank,[58] and leave the ON DELETE and ON UPDATE menus set to RESTRICT as shown in Figure 10.8, then click Save.

Setting up a foreign key in phpMyAdmin

Figure 10.8. Setting up a foreign key in phpMyAdmin

With this foreign key constraint in place, MySQL will reject any attempt to insert into joke an authorid value that fails to correspond to an entry in the author table; furthermore, it will stop you from deleting an entry in the author table unless you first remove any joke records that point to it. Perhaps best of all, however, in phpMyAdmin if you click on a value in the authorid column while browsing the joke table, you will now be taken immediately to the corresponding row in the author table! Because MySQL (and therefore phpMyAdmin) now understands the structure of your database, it can help you navigate it. To make sure you’ve got the hang of it, go ahead and create a foreign key constraint to represent each of the relationships between your tables. See if you can do it without peeking at the list of ALTER TABLE commands following (hint: there are four others):

ALTER TABLE jokecategory

ADD FOREIGN KEY (jokeid) REFERENCES joke (id)

ALTER TABLE jokecategory

ADD FOREIGN KEY (categoryid) REFERENCES category (id)

ALTER TABLE authorrole

ADD FOREIGN KEY (authorid) REFERENCES author (id)

ALTER TABLE authorrole

ADD FOREIGN KEY (roleid) REFERENCES role (id)

Note: A Word on Referential Actions

Instead of rejecting attempts to delete or update records that have foreign keys pointing to them (for example, preventing you from deleting authors who still have jokes associated with them), you can perform a referential action . This involves configuring a foreign key constraint in MySQL to automatically resolve the conflict. It can do this either by cascading the operation (that is, deleting any jokes associated with the author that you’re deleting), or by setting the values of any affected foreign key columns to NULL (setting the authorid of the author’s jokes to NULL). That’s what the ON RESTRICT and ON UPDATE options for the foreign key constraint in phpMyAdmin are all about. It can be tempting to use this feature to let MySQL take care of what happens to affected jokes when a user deletes an author or a category. It’s certainly easier to select an option in phpMyAdmin than it is to write the PHP code to automatically delete related jokes before removing an author. The problem with doing this is that it splits the logic of your website into two places: your PHP code and the foreign key constraints. No longer will you be able to see and control everything that happens when you delete a joke by just looking at the PHP controller responsible for doing that job. For this reason, most experienced PHP developers (myself included) prefer to avoid using referential actions in foreign key constraints. In fact, some developers prefer to avoid using foreign key constraints altogether!

Better Safe than Sorry

Admittedly, this chapter hasn’t been the usual nonstop, action–packed codefest to which you may have become accustomed by now. But our concentration on these topics—the backup and restoration of MySQL data, the inner workings of the MySQL access control system, the improvement of query performance with indexes, and the enforcement of the structure of your database with foreign keys—has armed you with the tools you’ll need to set up a MySQL database server that will stand the test of time, as well as endure the constant traffic your site will attract. In Chapter 11, we’ll return to the fun stuff and learn some advanced SQL techniques that can make a relational database server perform tricks you may never have thought possible.


[54To run mysqldump and the other MySQL utility programs, you need to be in the bin directory of your MySQL installation (or the /Applications/MAMP/Library/bin folder on a Mac with MAMP installed, or the mysql subdirectory of your XAMPP installation), or that directory must be added to the system path.

[55Unlike the SQL files created by phpMyAdmin’s Export facility, mysqldump backups include commands to drop the tables if they happen to exist before creating them, so you don’t have to worry if your database isn’t empty first.

[56Note that MySQL configuration files use slashes (/) instead of backslashes (\) in Windows filepaths.

[57All-in-one installers like XAMPP and MAMP tend to differ in this respect.

[58The Internal relation setting lets you record a relationship between tables in phpMyAdmin without actually creating a foreign key constraint (or even an index for the column) in the database. This enables you to navigate from table to table more easily in phpMyAdmin, but MySQL won’t enforce the relationship in any way.