Installing and Using MySQL

Installation

Preparing...                ########################################### [100%]
   1:MySQL                  ########################################### [100%]

IGNORE THE MESSAGE TO CHANGE THE PASSWORD: DO NOT CHANGE PASSWORD NOW

Note: If you try to change the password at this point in time it will not work. But, if you did it anyway and now you cannot login as root (access denied) you need to uninstall MySQL server:

and go in /var/lib and delete the subdirectory mysql. To be in the safe side reboot. Once you re-start, install the server again and do NOT change the password at this time.

Everything is installed and you should now start an interactive session with MySQL as follows:

Your first login

[root@alaptop /root]# mysql -u root mysql

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.37

Type 'help;' or '\h' for help. Type '\c' to clear the buffer

mysql> set password for root=Password('pwserver');
Query OK, 0 rows affected (0.52 sec)

mysql> quit
Bye

Once I changed the password I quit to test the new password. I logged in with the new password as shown below:

[root@alaptop /root]# mysql -u root -p
Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.37

Type 'help;' or '\h' for help. Type '\c' to clear the buffer
mysql>

The new password worked and now you can issue commands. This is the end of the installation.

Server Administration

[root@alaptop /root]# mysqladmin  

Administration program for the mysqld daemon. Usage: mysqladmin [OPTIONS] command command....

  -#, --debug=...       Output debug log. Often this is 'd:t:o,filename`
  -f, --force           Don't ask for confirmation on drop database; with
                        multiple commands, continue even if an error occurs
  -?, --help            Display this help and exit
  --character-sets-dir=...
                        Set the character set directory
  -C, --compress        Use compression in server/client protocol
  -h, --host=#          Connect to host
  -p, --password[=...]  Password to use when connecting to server
                        If password is not given it's asked from the tty
  -P  --port=...        Port number to use for connection
  -i, --sleep=sec       Execute commands again and again with a sleep between
  -r, --relative        Show difference between current and previous values
                        when used with -i. Currently works only with
                        extended-status
  -E, --vertical        Print output vertically. Is similar to --relative,
                        but prints output vertically.
  -s, --silent          Silently exit if one can't connect to server
  -S, --socket=...      Socket file to use for connection
  -u, --user=#          User for login if not current user
  -v, --verbose         Write more information
  -V, --version         Output version information and exit
  -w, --wait[=retries]  Wait and retry if connection is down

Default options are read from the following files in the given order:
/etc/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqladmin client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit
--no-defaults           Don't read default options from any options file
--defaults-file=#       Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read

Possible variables for option --set-variable (-O) are:
connect_timeout       current value: 0
shutdown_timeout      current value: 3600

Where command is a one or more of: (Commands may be shortened)
  create databasename   Create a new database
  drop databasename     Delete a database and all its tables
  extended-status       Gives an extended status message from the server
  flush-hosts           Flush all cached hosts
  flush-logs            Flush all logs
  flush-status          Clear status variables
  flush-tables          Flush all tables
  flush-threads         Flush the thread cache
  flush-privileges      Reload grant tables (same as reload)
  kill id,id,...        Kill mysql threads
  password new-password Change old password to new-password
  ping                  Check if mysqld is alive
  processlist           Show list of active threads in server
  reload                Reload grant tables
  refresh               Flush all tables and close and open logfiles
  shutdown              Take server down
  status                Gives a short status message from the server
  start-slave           Start slave
  stop-slave            Stop slave
  variables             Prints variables available
  version               Get version info from server
  

Stop and start the MySQL server

• To properly shutdown the server use: mysqladmin -p shutdown

• To start the server log as the machine root and type: safe_mysqld &

Creating and droping data bases

• You create a new database by typing: mysqladmin -p create dbname.
  For example: mysqladmin -p create MYTEST creates a new data base MYTEST, which we will use in other examples.

• You drop (delete) a database by typing: mysqladmin -p drop dbname

Display existing data bases

mysql> show databases;
+----------+
| Database |
+----------+
| MYTEST   |
| mysql    |
| test     |
+----------+
3 rows in set (0.06 sec)

Adding users

The abento user was GRANTED select,insert,update,delete,create and drop permissions on all data bases (*.*). If we had a table people in the MYTEST data base, and we only wanted abento to have rights in the table people, we would have written: ON MYTEST.people. We tested if the user had been added by loging as the new user abento, and typing a command to see the existing data bases (to test if the permissions were granted).

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO abento@localhost IDENTIFIED BY 'pwserver';
Query OK, 0 rows affected (1.39 sec)
 
mysql> quit
Bye
[root@alaptop /root]# mysql -u abento -ppwserver
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26 to server version: 3.23.37
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer
 
mysql> show databases;
+----------+
| Database |
+----------+
| MYTEST   |
| mysql    |
| test     |
+----------+
3 rows in set (0.67 sec)

Others

[root@alaptop /root]# mysqladmin -p version
Enter password:
mysqladmin  Ver 8.19 Distrib 3.23.37, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
 
Server version          3.23.37
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 39 min 32 sec
 
Threads: 1  Questions: 22  Slow queries: 0  Opens: 12  Flush tables: 1  Open tables: 6 Queries per second avg: 0.009
[root@alaptop /root]# mysqladmin -p status
Enter password:
Uptime: 2592  Threads: 1  Questions: 23  Slow queries: 0  Opens: 12  Flush tables: 1  Open tables: 6 Queries per second avg: 0.009

[root@alaptop /root]# mysqladmin -p processlist
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

[root@alaptop /root]# mysql -u root -ppwserver
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 3.23.37
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer

Creating and manipulating tables

If you recall, in a prior example a user abento was created as follows:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO abento@localhost IDENTIFIED BY 'pwserver';
Query OK, 0 rows affected (1.39 sec)

[root@alaptop /root]# mysql -u abento -ppwserver
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29 to server version: 3.23.37

Type 'help;' or '\h' for help. Type '\c' to clear the buffer

mysql> \u MYTEST
Database changed

mysql> CREATE TABLE people (userid INT NOT NULL PRIMARY KEY, name CHAR(25), address CHAR(45));
Query OK, 0 rows affected (0.68 sec)

mysql> desc people;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| userid  | int(11)  |      | PRI | 0       |       |
| name    | char(25) | YES  |     | NULL    |       |
| address | char(45) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.09 sec)

mysql> ALTER TABLE people MODIFY address VARCHAR(100);
ERROR 1044: Access denied for user: 'abento@localhost' to database 'MYTEST'

mysql> quit
Bye

[root@alaptop /root]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30 to server version: 3.23.37
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer
 
mysql> use MYTEST
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> ALTER TABLE people MODIFY address VARCHAR(100);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc people;

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| userid  | int(11)      |      | PRI | 0       |       |
| name    | varchar(25)  | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 
mysql>

Inserting data into the tables

mysql> INSERT INTO people (userid,name,address) VALUES (347,'John Morris','1420 N. Charles St, Baltimore, MD 21201');
Query OK, 1 row affected (0.82 sec)

mysql> INSERT INTO people
 VALUES (458,'Al Bento','1420 N. Charles St, Baltimore, MD 21201');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO people VALUES (569,'Milt Jenkins','1420 N. Charles St, Baltimore, MD 21201');
Query OK, 1 row affected (0.00 sec)

Querying the table

mysql> SELECT * FROM people;
+--------+--------------+-----------------------------------------+
| userid | name         | address                                 |
+--------+--------------+-----------------------------------------+
|    347 | John Morris  | 1420 N. Charles St, Baltimore, MD 21201 |
|    458 | Al Bento     | 1420 N. Charles St, Baltimore, MD 21201 |
|    569 | Milt Jenkins | 1420 N. Charles St, Baltimore, MD 21201 |
+--------+--------------+-----------------------------------------+
3 rows in set (0.48 sec)
 
mysql>  SELECT * FROM people WHERE name = 'Al Bento';
+--------+----------+-----------------------------------------+
| userid | name     | address                                 |
+--------+----------+-----------------------------------------+
|    458 | Al Bento | 1420 N. Charles St, Baltimore, MD 21201 |
+--------+----------+-----------------------------------------+
1 row in set (0.51 sec)

mysql> SELECT userid,name FROM people WHERE address = '1420 N. Charles St, Baltimore, MD 21201';
+--------+--------------+
| userid | name         |
+--------+--------------+
|    347 | John Morris  |
|    458 | Al Bento     |
|    569 | Milt Jenkins |
+--------+--------------+
3 rows in set (0.00 sec)
 

Updating the tables

mysql> UPDATE people SET name = 'Al Bento' WHERE userid = '347';
Query OK, 1 row affected (0.49 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM people WHERE name = 'Al Bento';
+--------+----------+-----------------------------------------+
| userid | name     | address                                 |
+--------+----------+-----------------------------------------+
|    347 | Al Bento | 1420 N. Charles St, Baltimore, MD 21201 |
|    458 | Al Bento | 1420 N. Charles St, Baltimore, MD 21201 |
+--------+----------+-----------------------------------------+
2 rows in set (0.00 sec)
 
 
mysql> UPDATE people SET name = 'John Morris' WHERE userid = '458';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> SELECT * FROM people;
+--------+--------------+-----------------------------------------+
| userid | name         | address                                 |
+--------+--------------+-----------------------------------------+
|    347 | Al Bento     | 1420 N. Charles St, Baltimore, MD 21201 |
|    458 | John Morris  | 1420 N. Charles St, Baltimore, MD 21201 |
|    569 | Milt Jenkins | 1420 N. Charles St, Baltimore, MD 21201 |
+--------+--------------+-----------------------------------------+
3 rows in set (0.00 sec)

Looking into the user table of MySQL

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SELECT * FROM user;

+------------------+--------+------------------+-------------+-------------+-------------+--------------------+-----------+-------------+---------------+
| Host        | User      | Password         | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv      | Shutdown_priv |
 Process_priv | File_priv | Grant_priv       | References_priv | Index_priv | Alter_priv |
+------------------+--------+------------------+-------------+-------------+-------------+--------------------+-----------+-------------+---------------+
| localhost   | root      | 1b50d85063ea8a6e | Y           | Y           | Y           | Y           | Y           | Y         | Y                | Y           |
  Y           | Y         | Y                | Y           | Y           | Y           |
| %           | abento    | 1b50d85063ea8a6e | Y           | Y           | Y           | Y           | N           | N         | N                | N           |
  N           | N         | N                | N           | N           | N           |
| localhost   | abento    | 1b50d85063ea8a6e | Y           | Y           | Y           | Y           | Y           | Y         | N                | N           |
  N           | N         | N                | N           | N           | N    |
+------------------+--------+------------------+-------------+-------------+-------------+--------------------+-----------+-------------+---------------+

6 rows in set (0.30 sec)

This page is maintained by Al Bento who can be reached at abento@ubmail.ubalt.edu. This page was last updated on April 21, 2001. Although we will attempt to keep this information accurate, we can not guarantee the accuracy of the information provided.