Working with MySQL Databases

Working with MySQL Databases

The first time you start the MySQL server (using the start-up script described previously), the system creates the initial grant tables for the MySQL database. It does this by running the mysql_install_db command.

Note?

If for some reason the grant tables are not initialized, you can run the mysql_install_db command yourself (as root user). Running the command more than once won't hurt anything.

The mysql_install_db command starts you off with two databases: mysql and test. As you create data for these databases, that information is stored in the /var/lib/mysql/mysql and /var/lib/mysql/test directories, respectively.

Starting the mysql command

To get started creating databases and tables, you can use the mysql command. From any Terminal window, open the mysql database on your computer by typing the following:

# mysql -u root -p mysql
Enter password: *********
   
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39 to server version: 3.23.57
Type 'help;' or '\h' for help. Type '\c' to clear the buffer
   
mysql>

Type in the root user's MySQL password as prompted. The mysql> prompt appears, ready to accept commands for working with the mysql default database on the localhost. If you are connecting to the MySQL server from another host computer, add -h hostname to the command line (where hostname is the name or IP address of the computer on which the MySQL server is running). Remember, you can also log in as any valid mysql login you created, regardless of which Linux login account you are currently logged in under.

As the mysql text notes above, be sure to end each command that you type with a semicolon (;) or a \g. If you type a command and it appears to be waiting for more input, it's probably because you forgot to put a semicolon at the end.

Before you begin using the mysql interface to create databases, try checking the status of the MySQL server using the status command. The following is an example of output from the status command:

mysql> status
--------------mysql  Ver 11.18 Distrib 3.23.57, for pc-linux-gnu (i686)
   
Connection id:        43
Current database:     mysql
Current user:         root@localhost
Current pager:        stdout
Using outfile:        ''
Server version:       3.23.57
Protocol version:     10
Connection:           Localhost via UNIX socket
Client characterset:  latin1
Server characterset:  latin1
UNIX socket:          /var/lib/mysql/mysql.sock
Uptime:               1 day 2 hours 57 min 19 sec
   
Threads: 1  Questions: 136  Slow queries: 0  Opens: 12
Flush tables: 1  Open tables: 6 Queries per second avg: 0.001
--------------

The status information tells you about the version of the MySQL server (11.18) and the distribution (3.23.57). The output also reminds you of the current database (mysql) and your user name (root@localhost). You can see how long the server has been up (Uptime). You can also see how many threads are currently active and how many commands have been run to query this server (Questions).

Creating a database with mysql

Within an interactive mysql session, you can create and modify databases and tables. If you are not already connected to a mysql session, type the following command (assuming the mysql user name of root):

# mysql -u root -p
Enter password: *******
mysql>

The general steps for creating a MySQL database include creating the database name, identifying the new database as the current database, creating tables, and adding data to the tables. While you are connected to a mysql session, you can run the procedure below to create a sample database.

  1. To create a new database name, use the CREATE DATABASE command at the mysql> prompt. For example, to create a database named allusers, type the following:

    mysql> CREATE DATABASE allusers;
    

    This action creates a database called allusers in the /var/lib/mysql directory.

    Note?

    You could alternatively have created a database from the command line using the mysqladmin command. For example, to create the database named allusers with mysqladmin, you could have typed the following: mysqladmin -u root -p create allusers

  2. To see what databases are available for your mysql server, type the following from the mysql> command prompt:

    mysql> SHOW DATABASES;
    +----------+
    | Database |
    +----------+
    | allusers |
    | mysql    |
    | test     |
    +----------+
      3 rows in set (0.00 sec)

    The databases shown here are named allusers, mysql, and test. The allusers database is the one created in the previous step. The mysql database contains user access data. The test database is created automatically for creating test mysql databases.

  3. To work with the database you just created (allusers), you need to make allusers your current database. To do that, type the following from the mysql> command prompt:

    mysql> USE allusers;
    Database changed
  4. Creating a table for your database requires some planning and understanding of table syntax. You can type in the following commands and column information to try out creating a table. For more detailed information on creating tables and using different data types, refer to the section "Understanding MySQL Tables" later in this chapter.

    To create a table called "names," use the following CREATE TABLE command from a mysql> prompt:

    mysql> CREATE TABLE names (
    -> firstname      varchar(20)    not null,
    -> lastname       varchar(20)    not null,
    -> streetaddr     varchar(30)    not null,
    -> city           varchar(20)    not null,
    -> state          varchar(20)    not null,
    -> zipcode        varchar(10)    not null
    -> );
    Query OK, 0 rows affected (0.00 sec)

    You have now created a table called names for a database named allusers. It contains columns called firstname, lastname, streedaddr, city, state, and zipcode. Each column allows record lengths of between 10 and 30 characters. Although MySQL supports several different database formats, because none is specified here, the default MyISAM database type is used.

With a database and one table created, you can now add data to the table.

Adding data to a MySQL database table

After the database is created and the structure of a database table is in place, you can begin working with the database. You can add data to your MySQL database by manually entering each record during a mysql session or by adding the data into a plain-text file and loading that file into the database.

Manually entering data

To do the procedure in this section, I assume you have an open interactive mysql session and that you have created a database and table as described in the previous section. If you are not already connected to a mysql session, type the following command (assuming the mysql user name of root):

# mysql -u root -p
Enter password: *******
mysql>

To add data to an existing MySQL database, the following procedure describes how to view the available tables and load data into those tables manually. The next section describes how to create a plain-text file containing database data and how to load that file into your database.

  1. To make the database you want to use your current database (in this case, allusers), type the following command from the mysql> prompt:

    mysql> USE allusers;
    Database changed
  2. To see the tables that are associated with the current database, type the following command from the mysql> prompt:

    mysql> SHOW tables;
    +--------------------+
    | Tables_in_allusers |
    +--------------------+
    | names              |
    +--------------------+
    1 row in set (0.00 sec)

    You can see that the only table defined so far for the allusers database is the one called names.

  3. To display the format of the names table, type the following command from the mysql> prompt:

    mysql> DESCRIBE names;
    +-----------+------------+------+-----+---------+-------+
    | Field     | Type       | Null | Key | Default | Extra |
    +-----------+------------+------+-----+---------+-------+
    | firstname | varchar(20)|      |     |         |       |
    | lastname  | varchar(20)|      |     |         |       |
    | streetaddr| varchar(30)|      |     |         |       |
    | city      | varchar(20)|      |     |         |       |
    | state     | varchar(20)|      |     |         |       |
    | zipcode   | varchar(10)|      |     |         |       |
    +-----------+------------+------+-----+---------+-------+
  4. To add data to the new table, you can use the INSERT INTO command from the mysql> prompt. Here is an example of how to add a person's name and address to the new table:

    mysql> INSERT INTO names
    -> VALUES ('Jerry','Wingnut','167 E Street',
    -> 'Roy','UT','84103');
    

    In this example, the INSERT INTO command identifies the names table. Then it indicates that values for a record in that table include the name Jerry Wingnut at the address 167 E Street, Roy, UT 84103.

  5. To check that the data has been properly entered into the new table, type the following command from the mysql> prompt:

    mysql> SELECT * FROM names;
      +-----------+----------+-------------+-------+-------+---------+
      | firstname | lastname | streetaddr  | city  | state | zipcode |
      +-----------+----------+-------------+-------+-------+---------+
      | Jerry     | Wingnut  | 167 E Street| Roy   | UT    | 84103   |
      +-----------+----------+-------------+-------+-------+---------+
    

    The resulting output shows the data you just entered, displayed in the columns you defined for the names table. If you like, you can continue adding data in this way.

Typing each data item individually can be tedious. As an alternative, you can add your data to a plain-text file and load it into your MySQL database, as described in the following section.

Loading data from a file

Using the LOAD DATA command during a mysql session, you can load a file containing database records into your MySQL database. Here are a few things you need to know about creating a data file to be loaded into MySQL.

  • You can create the file using any Linux text editor.

  • Each record, consisting of all the columns in the table, must be on its own line. (A line feed indicates the start of the next record.)

  • Separate each column by a Tab character.

  • You can leave a column blank for a particular record by placing a \N in that column.

  • Any blank lines you leave in the file result in blank lines in the database table.

In this example, the text shown below is added into a plain-text file. The text is in a format that can be loaded into the "names" table created earlier in this chapter. To try it out, type the following text into a file. Make sure that you insert a Tab character between each value (indicated here as multiple spaces).

Chris    Smith      175 Harrison Street   Gig Harbor   WA   98999
John     Jones      18 Talbot Road NW     Coventry     NJ   54889
Howard   Manty      1515 Broadway         New York     NY   10028

When you are done entering the data, save the text to any accessible filename (for example, /tmp/name.txt). Remember the filename so that you can use it later. If you are not already connected to a mysql session, type the following command (assuming mysql user name root):

# mysql -u root -p
Enter password: *******
mysql>

Next, identify the database (allusers in this example) as the current database by typing the following:

mysql> USE allusers;
Database changed

To actually load the file into the names table in the allusers database, type the following command to load the file (in this case, /tmp/name.txt) from the mysql> prompt.

Note?

Either enter the full path to the file or have it in the directory where the mysql command starts. In the latter case, you can type the filename without indicating its full path.

mysql> LOAD DATA INFILE "/tmp/name.txt" INTO TABLE names;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Deleted: 0 Skipped: 0 Warnings: 0

Type the following at the mysql> prompt to make sure that the records have been added correctly:

mysql> SELECT * FROM names;
+----------+---------+--------------------+------------+-------+-------+
| firstname| lastname| streetaddr         | city       | state |zipcode|
+----------+---------+--------------------+------------+-------+-------+
| Chris    | Smith   | 175 Harrison Street| Gig Harbor | WA    | 98999 |
| John     | Jones   | 18 Talbot Road NW  | Coventry   | NJ    | 54889 |
| Howard   | Manty   | 1515 Broadway      | New York   | NY    | 10028 |
+----------+---------+--------------------+------------+-------+-------+



Part IV: Red Hat Linux Network and Server Setup