Build Your Own Database Driven Web Site Using PHP & MySQL, Part 1: Installation

Post-Installation Set-up Tasks

Regardless of which operating system you’re running, or how you set up your web server—once PHP is installed and the MySQL server is functioning, the very first action you need to perform is assign a root password for MySQL.

MySQL only allows authorized users to view and manipulate the information stored in its databases, so you’ll need to tell MySQL who’s authorized and who’s unauthorized. When MySQL is first installed, it’s configured with a user named root that has access to do most tasks without even entering a password. Your first task should be to assign a password to the root user so that unauthorized users are prohibited from tampering with your databases.

Why Bother?

It’s important to realize that MySQL, just like a web server, can be accessed from any computer on the same network. If you’re working on a computer connected to the Internet, then, depending on the security measures you’ve taken, anyone in the world could connect to your MySQL server. The need to pick a difficult-to-guess password should be immediately obvious!

To set a root password for MySQL, you can use the mysqladmin program that comes with MySQL. If you followed the instructions to install MySQL separately (as explained earlier in this chapter), the mysqladmin program should be on your system path. This means you can pop open a Terminal window (or in Windows, a Command Prompt) and type the name of the program without having to remember where it’s installed on your computer.

Go ahead and try this now, if you’ve yet to already. Open a Terminal or Command Prompt and type this command: (If you’re using Windows and are unfamiliar with the Command Prompt, check out my article Kev’s Command Prompt Cheat Sheet for a quick crash course.)

mysqladmin -u root status

When you hit Enter you should see a line or two of basic statistics about your MySQL server, like this:

Uptime: 102261  Threads: 1  Questions: 1  Slow queries: 0  Opens: 15      
Flush tables: 1  Open tables: 0  Queries per second avg: 0.0

If you’re seeing a different message entirely, it’s probably one of two options. First, you might see an error message telling you that the mysqladmin program was unable to connect to your MySQL server:

mysqladmin: connect to server at 'localhost' failed      
error: 'Can't connect to MySQL server on 'localhost' (10061)'      
Check that mysqld is running on localhost and that the port is 3306.      
You can check this by doing 'telnet localhost 3306'

This message normally means that your MySQL server simply isn’t running. If you have it set up to run automatically when your system boots, double-check that the setup is working. If you normally launch your MySQL server manually, go ahead and do that before trying the command again.

Second, if you’re using MAMP on the Mac, you’ll probably see this error message instead:

mysqladmin: connect to server at 'localhost' failed      
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

This error message means that the root user on your MySQL server already has a password set. It turns out that, with your security in mind, MAMP comes with a root password already set on its built-in MySQL server. That password, however, is root—so you’re probably still going to want to change it using the instructions below.

One way or the other, you should now be able to run the mysqladmin program. Now you can use it to set the root password for your MySQL server:

mysqladmin -u root -p password "newpassword"

Replace newpassword with whatever password you’d like to use for your MySQL server. Make sure it’s one you can remember, because if you forget your MySQL root password, you might need to erase your entire MySQL installation and start over from scratch! As we’ll see in Chapter 10, MySQL Administration, it’s usually possible to recover from such a mishap, but it’s definitely a pain in the neck.

When you hit Enter, you’ll be prompted to enter the current password for the root MySQL user. Just hit Enter again, since the root user has no password at this point, unless you’ve used MAMP to set up MySQL on your Mac; in this case you should type root, the default root MySQL password on MAMP.

Let me break this command down for you, so you can understand what each part means:

mysqladmin

This, of course, is the name of the program you wish to run.

-u root

This specifies the MySQL user account you wish to use to connect to your MySQL server. On a brand new server, there is only one user account: root.

-p

This tells the program to prompt you for the current password of the user account. On a brand new MySQL server, the root account has no password, so you can just hit Enter when prompted. It’s a good idea, however, to make a habit of including this option, since most of the time you will need to provide a password to connect to your MySQL server.

password "newpassword"

This instructs the mysqladmin program to change the password of the user account to newpassword. In this example, whatever password you specify will become the new password for the root MySQL user.

Now, to try out your new password, request once again that the MySQL server tell you its current status at the system command prompt, but this time include the -p option:

mysqladmin -u root -p status

Enter your new password when prompted. As before, you should see a line or two of statistics about your MySQL server.

Since the root account is now password-protected, attempting to run this command without the -p switch will give you an “Access Denied” error.

You’re done! With everything set up and running, you’re ready to write your first PHP script. Before we do that, however, you might want to write a short email to your web host.

What to Ask Your Web Host

While you tinker with PHP and MySQL on your own computer, it might be good to start collecting the information you’ll need when it comes time to deploy your first database driven web site to the public. Here’s a rundown of the details you should be asking your web host for.

First, you’ll need to know how to transfer files to your web host. You’ll upload PHP scripts to your host the same way you normally send the HTML files, CSS files, and images that make up a static web site, so if you already know how to do that, it’s unnecessary to bother your host. If you’re just starting with a new host, however, you’ll need to be aware of what file transfer protocol it supports (FTP or SFTP), as well as knowing what username and password to use when connecting with your (S)FTP program. You also have to know what directory to put files into so they’re accessible to web browsers.

In addition to these, you’ll also need to find out a few details about the MySQL server your host has set up for you. It’s important to know the host name to use to connect to it (possibly localhost), and your MySQL username and password, which may or may not be the same as your (S)FTP credentials. Your web host will probably also have provided an empty database for you to use, which prevents you from interfering with other users’ databases who may share the same MySQL server with you. If they have provided this, you should establish the name of that database.