Database Tutorial

Course Tutorial Site

Site Admin

Configure MySQL 5.1

with 7 comments

Here are the command-line steps to create your own database and user. It also shows you how to grant privileges to the new user on only their database.

If you try these steps and get the following error message, you’ll need to create a small batch file that sets your Windows %PATH% environment variable ($PATH in Linux). The error message means that the operating system can’t find the mysql executable.

C:\Data>mysql
'mysql' is not recognized as an internal or external command, operable program or batch file.

Operating systems check the local directory for an executable by default, and then they look in the directory paths set in your %PATH% environment variable. Unless you checked the box to include mysql in your default %PATH%, you must create a batch file that you run before launching the mysql executable from any directory other than where it is physically located.

You can create a batch file in an editor or at the command line. First you must find the absolute path to the executable. This value depends on choices you made during the installation. Simply substitute your absolute path for the one below when it is different. The ^Z is the text representation of clicking the F6 key.

C:\Data> copy con mysql51.bat
SET PATH=C:\Program Files\MySQL51\MySQL Server 5.1\bin;%PATH%
^Z

After you check to verify that you can find the mysql executable, you may proceed with the following steps.

  1. You connect as the superuser by calling the client-side mysql program, like the following (assumes you set the password for the root user to cangetin). You don’t need to provide the port number if it is the default 3306 but will need to provide it for any other port that the mysqld service is listening on.
C:\Class\MySQL> mysql -uroot -pcangetin -P3306

There’s an alternative to this syntax that prevents disclosing your password during the log on process. You could also pass the -P3306 (or --port 3306) without error. The syntax is:

C:\Class\MySQL> mysql -uroot -p
Enter password:

You can confirm you’re the root user by running the following query:

mysql> SELECT CURRENT_USER();
  1. You create a database with the following syntax:
mysql> CREATE DATABASE sampledb;
  1. You create a user with the trivial password of the user’s name, which is a really bad example when you’re doing anything but writing documentation. The following creates the student user and then grants the user all privileges on their database.
mysql> CREATE USER 'student' IDENTIFIED BY 'student';
mysql> GRANT ALL ON sampledb.* TO 'student'@'localhost' IDENTIFIED BY 'student';
  1. You sign-off as the root user by typing quit, like
mysql> QUIT;
  1. You sign-on as the restricted myuser with the following syntax:
C:\Class\MySQL> mysql -ustudent -pstudent -P3306

You can confirm your the restricted user by running the following query:

mysql> SELECT CURRENT_USER();
  1. You can’t do much except explore generic metadata at this point because you must select a database. You select the sampledb database by doing the following:
mysql> USE sampledb;

You could now create a sample table that uses automatic numbering, insert values, and query the contents by using the following syntax.

mysql> CREATE TABLE sample ( sample_id   INT PRIMARY KEY AUTO_INCREMENT
                           , sample_text VARCHAR(20));
mysql> INSERT INTO sample (sample_text) VALUES ('One'),('Two'),('Three'),('Four'),('Five');
mysql> SELECT * FROM sample;
+-----------+-------------+
| sample_id | sample_text |
+-----------+-------------+
|         1 | One         |
|         2 | Two         |
|         3 | Three       |
|         4 | Four        |
|         5 | Five        |
+-----------+-------------+
  1. You can also describe the structure of the table after you create it. You DESCRIBE the sample table by doing the following:
mysql> DESCRIBE sample;

You see the following display:

+-------------+-------------+------+-----+---------+----------------+
| FIELD       | TYPE        | NULL | KEY | DEFAULT | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| sample_id   | INT(11)     | NO   | PRI | NULL    | AUTO_INCREMENT |
| sample_text | VARCHAR(20) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

Alternatively, you could place those three commands (minus the “mysql>” on each line) in a file and run the file from the command line. Just so there’s no misunderstanding, the file would look like this:

-- Open the database.
USE sampledb;
 
-- Split standard out, and write a log file to the absolute directory.
TEE C:/DATA/sample.txt
 
-- Run code.
DROP TABLE IF EXISTS sample;
CREATE TABLE sample ( sample_id   INT PRIMARY KEY AUTO_INCREMENT
                    , sample_text VARCHAR(20));
INSERT INTO sample (sample_text) VALUES ('One'),('Two'),('Three'),('Four'),('Five');
SELECT * FROM sample;
 
-- Turn off tee, close file to automatic appending.
NOTEE

If you named the file sample.sql and it is found in the C:\Data directory, you’d run it like this:

mysql> SOURCE C:/DATA/sample.sql

This completes the basic steps. At some later date, I’ll put a link here for a cross platform comparative tutorial on client-side command line interfaces, like Oracle’s sqlplus, MySQL’s mysql, and Microsoft SQL Server 2008’s sqlcmd.

Written by michaelmclaughlin

July 21st, 2009 at 10:41 pm

Posted in

7 Responses to 'Configure MySQL 5.1'

Subscribe to comments with RSS or TrackBack to 'Configure MySQL 5.1'.

  1. Those more familiar with the GUI interface in Windows would probably be more comfortable setting the Path variable for MySQL by clicking the Windows button (previously the Start button), right-clicking the Computer menu item, selecting Properties, selecting the Advanced system settings item, and clicking the Environment variables button. Navigate to the System variables box, select the Path variable, click the Edit button. Then add the path for the MySQL executable…
    Now the question becomes “How is the GUI interface easier, faster, better, or more efficient?” What do you think?

    Brother Godfrey

    4 Mar 11 at 5:08 pm

  2. i am new to my sql and finding this very very useful.

    thanks for help

    keep it up

    meera

    17 Jun 11 at 3:37 am

  3. i really liked these notes…its really helpful for beginners like me….thank u guys…

    Abhay

    17 Jan 12 at 3:00 am

  4. can i have a question ? how to create an table ?

    Saturnino

    22 Oct 14 at 7:29 pm

  5. Saturnino, You can find out how to create a table with the following web page.

    michaelmclaughlin

    25 Oct 14 at 4:57 pm

  6. thanku somuch its really helpful

    NYMISHA

    21 Nov 14 at 5:58 am

  7. A student posed the question about why table names are case sensitive. That’s because case sensitive table names are the default installation. You can verify that with the following query:

    SELECT CASE
         WHEN @@lower_case_table_names = 1 THEN
           'Case insensitive tables'
         ELSE
           'Case sensitive tables.'
         END AS "Table Name Status";

    The default value returned is:

    +------------------------+
| Table Name Status      |
+------------------------+
| Case sensitive tables. |
+------------------------+
1 row in set (0.00 sec)

    You can change the default by setting the following parameter in the my.cnf file on Linux or the my.ini file on Windows:

    # Make all tables case insensitive.
lower_case_table_names=1

    This lets you enter tables in upper or mixed case, and stores them as lowercase table names.

    Why are there lower case tables?

    20 Mar 15 at 4:10 pm

Leave a Reply