Databases are all the rage for one good reason: You need them to do just about anything. From Web sites to employee records to vendor listings to payrolls, databases affect nearly all levels of the enterprise. Because of this, duties that once belonged to the database administrator are quickly becoming the duties of the systems/network administrators.

Unfortunately, database administration is not a simple task. Most good databases require command-line interfaces that many administrators are not yet comfortable working with. In this Daily Feature, I am going to acquaint you with Linux’s MySQL command-line interface so that you will be able to connect to your MySQL database in order to create and use a simple database. From there, you’ll be on the fast track to mastering the intricacies of MySQL.

Assumptions made

This article assumes you have a working MySQL installation on your Linux machine as well as a fundamental understanding of database design.

Starting MySQL
I am going to assume that you have not taken any steps in administering your MySQL database, so that passwords have not been set and the daemon is not running. With that in mind, you’ll first need to get your database engine running. On Red Hat-based distributions, there are two ways to start your database. The first—and more universal—is by running (as root) the command mysql_safe, which, when run from the command, will not give back a command prompt. When starting in this manner, the only way to get your prompt back is to put the process into a zombie state (by hitting [Ctrl]Z).

The second way to start the MySQL daemon—and the way I prefer—is to run the command /etc/rc.d/init.d/mysqld start. Starting the daemon this way will present the prompt when the command runs. You can also add the latter command into the /etc/rc.local file to have the daemon start at boot.

User passwords
At this point, since I’ve already established that no passwords have been set, you can connect to MySQL without a single password. So this is the perfect time to set certain passwords, especially for the root user. If you don’t set a password, anyone can connect to your database to edit databases, drop databases, remove databases, and so on. To set a password, use the mysqladmin tool with the password option. First, set the root password with the following command:
mysqladmin -u root password NEWPASSWORD

where “NEWPASSWORD” is the intended password. With the root password set, you can at least be assured (to some degree) that no one is going to be able to misuse your MySQL root privileges.

If you know you will have other users that will need access to the MySQL database tool, you will want to give them passwords as well (or let them do this on their own). The syntax for this change is the same as the above syntax, only the users will supply their own usernames where the root user supplied root.

Connecting to MySQL
Before you move on to administering various users, you will need to be able to connect to MySQL. Connecting to MySQL can be as simple as running the mysql command. Of course, this is allowed only on machines that can connect anonymously to localhost. If you have set passwords, then you will have to add (at minimum) the -p option, as in:
mysql -p

which will prompt you for a password. If you do not use the –p option and you’ve set passwords, you will not be prompted for a password, and the connection will fail. If you want to connect to MySQL as a different user (say you have added the user database that is strictly for the administration of MySQL), you would add the -u switch, as in mysql -u database -p (where “database” is the name of the user you want to connect as). Upon hitting [Enter], you will be asked for your password.

Entering your first query
After connecting to the MySQL server, it is always a good idea to check to see what’s available by running some general queries. MySQL has various prompts that will be presented while entering certain types of commands. The primary prompts are:

  • ·        mysql>
    This is the MySQL main prompt.
  • ·        ->
    This prompt indicates that MySQL is awaiting the next line of a multiline command.
  • ·        ‘>
    This prompt indicates that MySQL is awaiting the remainder of a string that began with a single quote but was not terminated with a single quote before hitting [Enter].
  • ·        “>
    This is the same as above—only this prompt is expecting the closing double quote.

The first query I will run will return the version of MySQL installed on the system and the current date. Before you run this query, you must connect to MySQL (as shown above). Once the mysql> prompt appears, run the following command:
SELECT VERSION(), CURRENT_DATE;

This command presents a table with the current MySQL version along with the current date (click here to see the results).

This command is somewhat trivial as shown and it returns very little information, but it’s a good start when first learning about queries. One fundamental principle to learn from the above command is that every MySQL command consists of MySQL statement(s) followed by a semicolon (;). Should you run the above command without the trailing semicolon, you would be presented with a -> command prompt return, indicating that the prompt was waiting for further instructions. For instance, if you wanted to know when “now” was, you could add the statement:
-> select now()
->;

which would return the current time and date. One other fundamental idea inherent in the above command is that MySQL keywords can be given in any case. All three of the commands below are legal and will output the same information:
CURRENT_DATE();
Curent_Date();
cUrEnT_dAtE();

A final fundamental element you’ll need to learn about the MySQL command is that commands can be run all in one single line, such as:
SELECT VERSION(), CURRENT_DATE, NOW ();

or each command can be issued from its own line, as in (the original MySQL prompt is included in the example below for clarity):
mysql> SELECT VERSION()
    -> CURRENT_DATE()
    -> NOW()
    -> ;

The output of both sets of the above commands would be the same. Also note that the SELECT keyword is only used once—regardless of how you choose to enter your commands.

Creating/using a database
It’s now time to create and use your first database (including a table). The database you will create, which will be named USERS, will be a very basic set of data that holds a user’s name, e-mail, phone number, and age.

First, you’ll need to examine a listing of current databases to make sure that there isn’t already one with the same name. To verify current databases, run the command:
SHOW DATABASES;

Click here to see the list of databases created by this command.

This list of databases is pretty bare-bones, so let’s add to it. In order to create a database, you will connect to MySQL as root and use the CREATE DATABASE command. For this example, you will create the database USERS (MySQL is case-sensitive when it comes to database names), which will eventually have tables and some data.

To create the database, first connect as root to MySQL, as in:
mysql -u root -p

After you give the administrator’s password, the MySQL prompt will appear. At this new prompt, issue the command:
CREATE DATABASE USERS;

If you run the SHOW DATABASES; command now, you will see the database list again with the USERS database included (click here to see the list).

The next step is to begin creating tables within the database. Before you actually add to this database, however, you must select it for use with the USE database command (where “database” is the name of the database to be used. In this case, it would be USE USERS). Now you are ready to create a table within the database. To make sure you aren’t going to write over any current tables, take a moment to check that there are no tables already on the USERS database with the SHOW TABLES; command.

The SHOW TABLES; command should, at this point, return something similar to:
Empty set (0.00 sec)

which tells you there are no tables in the USERS database. Now that you are ready to insert tables, I need to make it clear that this tutorial is only meant to guide you in the usage of the MySQL command line, not in the design of databases. Accordingly, I will assume a basic understanding of database design.

Your table entry for the USERS database will consist of the following:

  • ·        fname
    The user’s first name (type varchar(20))
  • ·        lname
    The user’s last name (type varchar(20))
  • ·        email
    The user’s e-mail address (type varchar(20))
  • ·        bday
    The user’s birthday (type DATE)

Each table you enter must be given a name, so you can name this table JWallen. The command to create this first table is (the initial MySQL prompt is shown for clarity):
mysql>create table JWallen (lname varchar(20), fname varchar(20),
    ->email varchar(20), bday DATE);

Now, when you run the command show tables (from the mysql prompt, of course), you’ll see the first table (click here to see this table).

If you want to make sure the table was created correctly, use the DESCRIBE statement, as in:
DESCRIBE JWallen;

This statement will result in an empty table (click here to see an example).

Now, it’s time to populate this table with some data! Loading data into a table is done with the LOAD DATAi and INSERT statements. One way to populate this table would be to create a text file and then, using the LOAD DATA statement, transfer the contents to the table. This method works best when adding multiple entries, where it will drastically cut down on the entry time. Before you load via file, you’ll first load the data manually. In order to load the values Wallen, Jack, jack.wallen@cnet.com, 10/31/1900 (you didn’t think I would actually give away my real birthday, did you?) into the table Jwallen, you would issue the command (the prompts are intact for clarity):
mysql> INSERT INTO JWallen
         ->VALUES (‘Wallen’, ‘Jack’, ‘jack.wallen@cnet.com’, ‘1900/10/30’);

To view the newly inserted data, run the command:
SELECT * FROM JWallen

This command will create a table complete with the newly inserted data (click here to see an example).

For inputting multiple entries, a text file including tab-delineated data is, by far, the quickest method. For this, you will create a text file named user.txt with each piece of data separated by a single tab. For the above entry, the text file will look like:
Wallenj     Jack     jack.wallen@cnet.com     1900/10/31

It will be entered into the database table with the following command (assuming you are already within the root MySQL prompt):
LOAD DATA LOCAL INFILE “user.txt” INTO TABLE JWallen;

If you have certain fields in an entry that must be blank, use NULL values (such as \n) to represent blank entries. There must be some type of placeholder for that spot or the data entry will be off.

The retrieval of data is based on the following keywords:

  • ·        SELECT
    What you want to see
  • ·        FROM
    Which table you want to pull data from
  • ·        WHERE
    Conditions that must be met

Conclusion
Armed with the above information, you are ready to move on to the next level of MySQL database administration. As you can tell from this article, the role of database manager covers a large amount of territory and requires an equally large amount of knowledge. Because of the scope and complexity of the MySQL database engine, it is critical that you understand the fundamentals. Only once you have mastered the fundamentals can you successfully move on to the next level.