8.1. MySQL DatabaseMySQL 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:
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 LineOne 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 dialogOnce 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 MySQLDon't let the MySQL command-line interface alarm you; it's not difficult to use. 8.1.1.1. PromptsAt 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.
8.1.1.2. CommandsTable 8-2 lists commands that are available at the MySQL prompt.
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)
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. |