MySQL/Startup Guide

This document helps a user set up and use MySQL.

Background
MySQL is a popular database server that is used in various applications. SQL stands for (S)tructured (Q)uery (L)anguage, which is what MySQL uses to communicate with other programs. On top of that, MySQL has its own expanded SQL functions to provide additional functionality to users. In this document, we'll look at how to do the initial MySQL installation, set up databases and tables, and create new users. Let's start out with the installation.

MySQL Installation
First make sure you have MySQL installed on your system. In case you need specific functionality from MySQL, please make sure you have the required USE flags enabled as they will help fine tune your installation.

Upon completion of the installation, you will see the following notice:

MySQL einfo message

Since this is a new installation, we run the command. You need to press  when prompted while configuring the MySQL database. The configuration sets up the main MySQL database which contains administrative information such as databases, tables, users, permissions and more. The configuration recommends that you change your root password as soon as possible. We will definitely do this, otherwise someone could come along by chance and hack our default setup MySQL server.

Some MySQL non-ebuild specific information has been removed from here so as to keep this document as consistent as possible.

The config script has already printed out the commands we need to run to setup our password, so we shall now run them.

You can now test that your root password was successfully configured by trying to login to your MySQL server:

The  switch sets the user that will be logging in. The  switch sets the host. This will usually be  unless you are setting up a remote server. Finally,  tells the mysql client that you will be entering a password to access your database. Notice the  prompt. This is where you type in all your commands. Now that we're in the mysql prompt as the root user, we can begin to setup our database.

Creating A Database
We have logged in and a mysql prompt is displayed. First let's take a look at the databases we currently have. To do so, we use the  command.

Despite the fact that a test database is already created, we are going to create our own. Databases are created using the  command. We'll create one named "gentoo".

The response lets us know that the command was executed without any errors. In this case, 1 row was modified. This is a reference to the main mysql database, which carries a list of all the databases. You won't need to worry too much about the background details. The last number is how fast the query was executed. We can verify the database was created by running the  command again.

Indeed our database has been created. In order to work with creating tables for our new gentoo database, we need to select it as our current database. To do so, we use the  command. The  command takes the name of the database you wish to use as your current database. Another option is to set it on the command line after the  switch. Let's go ahead and switch to the gentoo database.

And the current database is now our previously created gentoo database. Now that we're using it, we can start to create some tables and put information in them.

Creating a Table
In the structure of MySQL, there are databases, tables, records, and fields. Databases hold together tables, tables hold together records, records hold together fields, which contain the actual information. This structure lets users select how they want to access their information. So far we've dealt with databases, now let's work with tables. First off, tables can be listed similiarly to databases using the  command. Right now there are no tables in our gentoo database, as running the command will show us:

This means we need to create some tables. In order to do so, we use the  command. However, this command is quite different from simple  command. This command takes a list of arguments. The form is as follows:

CREATE TABLE Syntax

table_name is the name of the table we wish to create. In this case, let's make a table named. This table will contain the developer's name, email and job.field_name will contain the name of the field. We have 3 required names in this case: name, email, and job. Thefield_data_type is what type of information will be stored. The different formats available can be found at theMySQL Column Types Page. For our purposes, we'll use the data type for all of our fields. is one of the simplest of data types when it comes to working with strings.size is how much of data a single field will store. In this case, we'll use 128. This means that the field can have data that is 128 bytes. You can safely think of this as 128 characters for the time being, though there is a somewhat more technical explanation that the above site will provide you with. Now that we know how we are going to create the table, let's do it.

Looks like our table was created ok. Let's check it with the  command:

Yes, there's our table. However, it doesn't seem to have any information on the types of fields we setup. For that, we use the  command (or   for short), which takes the name of the table as its argument. Let's see what that gives us for our developers table.

This shows the different fields and their types. It also shows a few extra attributes that are beyond the scope of this howto. Feel free to refer to the MySQL Reference Manual for more information. We now have a table to work with. Now let's go ahead and populate it.

Populating Our MySQL Database
We populate a table (or add data) using the  command. Like the  command, it also has a specific format:

INSERT Syntax

This command is used to insert a record into table. table contains the MySQL table we wish to enter the information into. The table name may be followed by the list of columns to insert data into and  contains the values you wish to insert into the table. You may omit the list of columns if you insert a value into each one and if you write the values in the same order the columns have been defined. In this case, we want to insert data into the developers table. Let's insert sample records:

According to our return result, it appears that the record was inserted correctly. What if we want to input more information than just one record? That's where the  command comes into play. This loads records from a tab separated file. Let's try that by editing a file in our home directory with the records. We'll call this file. Here's a sample:

~/records.txt

Now the  command has a somewhat elongated definition, but we'll use the simplest form here.

LOAD DATA Syntax

is the directory and filename that will be used. table is the name of our table. In this case, our file is and the table is developers.

That worked well. However, this simply inserts records, and does not give you any sort of control over MySQL. Many web applications use sql scripts in order to setup MySQL quickly and easily. If you want to use an sql script, you'll need to run mysql in batch mode, or source the file. Here's an example of running mysql in batch mode:

Like , be sure you can tell what  does. Failure to do so may cause your database to be compromised! Another way you can accomplish this is to use the  command. This command will run mysql commands from an sql file while in the mysql interactive mode. Here is how to source an sql file:

If you see a web application wanting you to run an sql file, the two above commands can be used to accomplish that task. We have our table setup, so how do we check our fields? We do this by searching our table with queries.

Browsing MySQL Tables With Queries
Queries are one of the main features of any SQL database. They help us turn data in our tables into something useful. Most queries are done with the  command. The  command is fairly complex, and we're only going to look at three main forms of the command in this document.

SELECT forms

Let's take a quick look at the first form. It's relatively simple and gives you an overall look of your table. We'll go ahead and run it to see what data we have so far.

We see both the data we inserted through  and those inserted by   present. Now, let's say that we just want to see the record for Chris White. We can do so with the second form of select as shown below.

As expected, the specific entry that we were looking for has been selected. Now, let's say we only wanted to know the person's job and email address, not their name. We can do so with the third form of  as shown here.

This method of selection is a lot easier to manage, expecially with larger amounts of information, as we'll see later on. Right now, being the root mysql user, we have unlimited permissions to do what we wish with the MySQL database. In a server environment, a user with such privileges can be quite problematic. In order to control who does what with the databases, we setup privileges.

Granting Privileges with GRANT
Privileges are what kind of access users have to databases, tables, pretty much anything. Right now in the gentoo database, the MySQL root account is the only account that can access it, given its permissions. Now, let's create two somewhat generic users, guest and admin, who will access the gentoo database and work with the information in it. The guest account will be a restricted one. All it will be able to do is get information from the database, and that's it. admin will have the same control as root, but only for the gentoo database (not the main mysql databases). Before we start on that, let's have a closer look at this somewhat simplified format of the  command.

GRANT Syntax

First we have the privileges we wish to assign. With what we've learned so far, here are some of the privileges you can set:


 * - Gives the all privilege control for the database
 * - Allows users to create tables
 * - Allows users to query tables
 * - Allows users to insert data into a table
 * - Allows users to see a list of databases
 * - User has no privileges
 * - Allows users to grant privileges

For our admin user, ALL will do. For the guest user,  will be sufficient for read only access. database is the database we wish the user to have these permissions on. In this example, gentoo is the database. The .* means all tables. If you wanted to, you could apply per table access. user is the name of the user and host is the hostname the user will be accessing from. In most cases, this will be localhost. Finally, password is the user's password. Given the information, let's go ahead and create our users.

Now that we have the users setup, let's test them out. First we quit mysql by typing  at the command prompt:

We're now back at the console. Now that we have our users setup, let's go ahead and see what they can do.

Testing User Permissions
We shall now attempt to login as the guest user. Currently, the guest user has  only privileges. This basically comes down to being able to search and nothing more. Go ahead and login with the guest account.

Now we should test the user restriction(s). Let's switch to the gentoo database:

Now let's try to do something we are not supposed to. We'll attempt to create a table.

As you can see, this function fails, as our user does not have the appropriate access. However, one access we did grant is the  statement. Let's go ahead and try that:

The command succeeds, and we're given a glimpse of what user permissions can do. We did, however, create an admin account as well. This was created to show that even all permissions granted users can still have limitations. Go ahead and quit MySQL and login as the admin.

To begin, we'll try creating a new database with our admin user. This admin user will have access similiar to the root MySQL account, and will be able to do any kind of modification to the gentoo database it chooses. This will test the user's access to the main MySQL database. Remember ealier that we only set permissions to a specific database.

Indeed, the admin user cannot create databases on the main MySQL database, despite all his permissions on the gentoo database. However, we're still able to use the admin account to modify the gentoo database, as shown here by this example data insertion.

The admin user can access the database as they please. Now sometimes, we need to get rid of user permissions. This could be anything from a problematic user to a retired employee. Let's take a look at how to disable user permissions with the  command.

Removing User Access With The REVOKE Command
The  command lets us deny access to a user. We can either deny full access, or specific access. In fact, the format is very similiar to.

REVOKE Syntax

Options here are explained in the  command section. In this section however, we're going to deny full access to a user. Let's say we find out the guest account is causing some problems security wise. We decide to revoke all privileges. We login as root and do the needful.

Now let's quit and attempt to login as a guest user.

Although we're able to login, our access to gentoo is now gone.

And our problematic user is no longer able to access the gentoo database. Please note that the user was still able to login. That is because they remain in the main MySQL database. Let's take a look at how to completely remove an account with  and the MySQL user table.

Removing Accounts Using DELETE
The MySQL user table is a listing of all users and information about them. Make sure you're logged in as root. Then go ahead and use the main MySQL database.

Now let's see what tables the mysql database has:

The user table is the table we're after. However, the user table contains 30 different fields, making it very hard to read. In order to make things easier to read, we'll go ahead and use the third version of the  statement. The fields we're after are Host and User.

Now that we have our information, we can get rid of the guest user. This is done with the  command and the syntax is shown below.

DELETE Syntax

You may notice that  is somewhat similiar to the   statement in its format. In this case, the field will be User, and the value guest. This will delete the record in the user table where the user is guest, successfully deleting our guest user account. Let's go ahead and do that:

It seems to have worked ok. Let's test by logging out and attempting to login as our guest user.

Our user is now successfully deleted!

Conclusion
While this guide focuses mainly on setting up MySQL on the command line, a few alternatives are available in GUI form:


 * phpMyAdmin - Popular php based MySQL administration tool.
 * mysqlnavigator - QT frontend to MySQL.
 * gmyclient - A GNOME based MySQL client.
 * knoda - A KDE MySQL client.

This ends the MySQL introductory tutorial. I hope this gives you a better understanding of the basics behind MySQL and getting a database set up.

Acknowledgements
We would like to thank the following authors and editors for their contributions to this guide:


 * Chris White
 * Shyam Mani
 * Xavier Neys