Creating the Database - Administration

In the next sections, we will go through the steps involved in creating a web enabled database. First we create the database, and populate it with some data. After that is done, we create some PHP scripts that talk to the database and see it all work.

Make sure the MySQL server processes are running. If they aren't start them manually by running:

# /usr/local/mysql/support-files/mysql.server start 

Now we start the MySQL client as the administrator, this time we will see a password prompt:

# mysql -u root -p
Enter password: newpassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13 to server version: 3.22.21
Type 'help' for help.
mysql>

Create a database called example with the command create database example;. When using the MySQL client, remember to put a semicolon (;) after each command:

mysql> create database example;
Query OK, 1 row affected (0.03 sec) 
mysql> use example;
Database changed

Create a table in the example database called mytable:

mysql> CREATE TABLE mytable (
-> name CHAR(30),
-> phone CHAR(10)
-> );
Query OK, 0 rows affected (0.00 sec)

Here's a handy tip that will save you some typing. Use the up arrow and down arrow keys to recall back the previous/next lines you typed in.

Now let's populate this with some data, we will insert a few entries into the table. Enter in the following INSERT statments:

mysql> INSERT INTO mytable VALUES ("Homer Simpson", "555-1234");
Query OK, 1 row affected (0.05 sec) 
mysql> INSERT INTO mytable VALUES ("Bart Simpson", "555-4321");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mytable VALUES ("Lisa Simpson", "555-3214");
Query OK, 1 row affected (0.00 sec) 
mysql> INSERT INTO mytable VALUES ("Marge Simpson", "555-2314");
Query OK, 1 row affected (0.00 sec) 
mysql> INSERT INTO mytable VALUES ("Maggie Simpson", "555-3142");
Query OK, 1 row affected (0.00 sec)

Make sure everything is there by issuing with a SELECT statement:

mysql> SELECT * FROM mytable;
+----------------+----------+
| name           | phone    |
+----------------+----------+
| Homer Simpson  | 555-1234 |
| Bart Simpson   | 555-4321 |
| Lisa Simpson   | 555-3214 |
| Marge Simpson  | 555-2314 |
| Maggie Simpson | 555-3142 |
+----------------+----------+
5 rows in set (0.00 sec)

Looking good so far? Excellent.

We've created the database and put some data in there, now we must create a user account. This user will have access to this database. We create the user and grant privileges with the GRANT command:

mysql> GRANT usage
-> ON example.*
-> TO webuser@localhost;
Query OK, 0 rows affected (0.15 sec)

This creates a new user called webuser. This user can connect only from localhost, and he has the ability to connect to the example database. Next we have to specify what operations webuser can perform:

mysql> GRANT select, insert, delete
-> ON example.*
-> TO webuser@localhost;
Query OK, 0 rows affected (0.00 sec)

This gives webuser the ability to execute SELECT, INSERT, and DELETE queries on every table of the example database. Our work is done here, exit the MySQL client:

mysql> exit
Bye