Section 8.1. MySQL Database

8.1. MySQL Database MySQL has its own client interface, allowing you to move data around and change database configuration. Note that you must use a password to log in. Assigning database users allows you to limit access to server tables that have multiple users. Each MySQL server, where tables are grouped together, can host many databases. Normally, a web application has its own proprietary database. You may have installed MySQL yourself or have access through your ISP. Most ISPs that support PHP also provide a MySQL database for your use. Should you have difficulty, check their support pages or contact them to determine connection details. You'll need to know the following: The IP address of the database server The name of the database The username The password If you've installed MySQL on your computer, you'll be able to use the defaults from the installation and the password you specified. This chapter looks at two ways to communicate with MySQL, the command line and phpMyAdmin, a web-based tool. 8.1.1. Accessing the Database with the Command Line One way of communicating with MySQL is via the MySQL Command Line Client. Depending on which operating system you're using, you either need to open a command shell for Windows (type cmd from the Run dialog, as shown in Figure 8-1) or open a terminal session, in Mac OS X and Unix environments. Figure 8-1. Windows Run dialog Once you reach the command line, type mysql and press Enter. The syntax for the mysql command is: mysql -h hostname -u user -p If you've installed MySQL on your computer, the default username is root. You can omit the hostname flag and value. Enter your password when MySQL displays the "Enter password" prompt. If the password, username, and hostname are correct, you'll see a banner message like that in Figure 8-2. Figure 8-2. A successful login to MySQL Don't let the MySQL command-line interface alarm you; it's not difficult to use. 8.1.1.1. Prompts At the MySQL prompt, you can enter database commands followed by Enter. There is also a set of commands that MySQL itself interprets. For a list of these commands, type help or \h at the mysql> prompt. Table 8-1 shows some of the prompts you'll see and summarizes what they mean. Table 8-1. Command prompt meanings Prompt Meaning mysql> Waiting for a command -> Waiting for the next line of a command '> Waiting for the next line of a string that starts with a single quote "> Waiting for the next line of a string that starts with a double quote 8.1.1.2. Commands Table 8-2 lists commands that are available at the MySQL prompt. Table 8-2. MySQL client commands Command Parameter Meaning quit   Exit the command-line utility use Database name Use a specific database show tables or databases Show lists such as tables or databases available describe Table name Describe a table's columns status   Display database version and status source Filename Execute commands from a file as a script These commands allow you to perform tasks such as executing SQL commands that are stored in a script file using the source. To display the available databases, type: mysql> SHOW DATABASES; which returns: +----------+ | Database | +----------+ | mysql | +----------+ 1 rows in set (0.00 sec) To scroll back though commands you've already entered in MySQL, use the up arrow key. The default database that is present after an install is called mysql. The mysql database also stores the database user authentication information. Don't delete it! When you started mysql, you didn't specify connection to a particular database. The use command allows you to do this. To connect to the mysql database, type the following at the MySQL prompt: USE `mysql`; This returns: Database changed If your ISP supplied a different database name, use that instead of mysql.