This document provides an introduction to the MySQL-client binary. Feel free to forward questions or clarification requests to portal@aero.und.edu.

• Login to a machine with mysql client capabilities
  • ssh to shell
  • login to a linux lab machine

We recommend that all new MySQL users read the remainder of this document. For the impatient we will make this command available.

mysql -h mysql.aero.und.edu -p -u $USER

Let's begin by exploring our local Linux environment as if we were lost in a dark room, and have to find the light. After all, your terminal session is white on black… so this aids in the effect :)

$ which mysql
/usr/bin/mysql

$ rpm -qa | grep -i mysql
MySQL-python-0.9.1-6
MySQL-shared-standard-4.1.15-0.rhel3
libdbi-dbd-mysql-0.6.5-5
php-mysql-4.3.2-25.ent
perl-DBD-MySQL-2.1021-3
MySQL-client-4.1.15-0.glibc23
MySQL-devel-4.1.14-0.glibc23

$ rpm -qf `which mysql`
MySQL-client-4.1.15-0.glibc23

Let's find out what kind of system we're logged in to.

$ cat /etc/redhat-release 
Red Hat Enterprise Linux AS release 3 (Taroon Update 5)

If we were inclined, we could download the appropriate package here http://dev.mysql.com/downloads/mysql/4.1.html.

First let's investigate the local system shell.aero.und.edu.

$ grep mysql /etc/services 
mysql           3306/tcp                        # MySQL
mysql           3306/udp                        # MySQL

When installed, the MySQL server runs on port 3306.

$ telnet localhost 3306
Trying 127.0.0.1...
telnet: Unable to connect to remote host: Connection refused

No, MySQL is not running locally. This is confirmed with the following command.

$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

The MySQL server is running somewhere other than shell. Luckily we remember that our friendly System Administrators gave us the necessary information in the reply to our MySQL Account Request.

We're left wondering, “How do we connect to the MySQL server at mysql.aero.und.edu?”

After investigating the manual page by running “man mysql” we see the following.

mysql(1)                        MySQL database                        mysql(1)

NAME
       mysql  -  text-based client for mysqld, a SQL-based relational database
       daemon

USAGE
       mysql [OPTIONS] [Database]

SYNOPSIS
       mysql [-B|--batch] [-#|--debug= logfile] [-T|--debug-info]  [-e|--exec=
       command]     [-f|--force]     [-?|--help]     [-h|--host=     hostname]
       [-n|--unbuffered]  [-p[pwd]]   [--password=[pwd]]   [-P|--port=   pnum]
       [-q|--quick]  [-r|--raw]  [-s|--silent] [-S|--socket= snum] [-u|--user=
       uname] [-v|--verbose] [-V|--version] [-w|--wait]

DESCRIPTION
       The mysql program provides a curses-based interface  to  the  SQL-based
       database server daemon, mysqld(1).  Full fuller documentation, refer to
       the HTML documents installed with the package.

Here are the interesting “OPTIONS”

• -h (hostname)
• -p (password)
• -u (username)

mysql -h mysql.aero.und.edu -p -u caylan
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1266841 to server version: 4.1.12-standard
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Excellent!

mysql> show databases;
+----------+
| Database |
+----------+
| caylan   |
| test     |
+----------+
2 rows in set (0.00 sec)

We must “use” or “select” a database before we can go any further.

mysql> use caylan
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> show tables;
+------------------+
| Tables_in_caylan |
+------------------+
| notes            |
+------------------+
1 row in set (0.00 sec)

mysql> select * from notes;
+----+------------+-----------+
| id | date       | content   |
+----+------------+-----------+
|  1 | 2005-11-28 | Test Note |
|  4 | 2005-11-28 | My Note   |
+----+------------+-----------+
2 rows in set (0.01 sec)

mysql> insert into notes (id, date, content) values (null, now(), "Hello World");
Query OK, 1 row affected (0.00 sec)

mysql> select * from notes;
+----+------------+-------------+
| id | date       | content     |
+----+------------+-------------+
|  1 | 2005-11-28 | Test Note   |
|  4 | 2005-11-28 | My Note     |
|  7 | 2005-12-05 | Hello World |
+----+------------+-------------+
3 rows in set (0.00 sec)                   

Sometimes a database schema is preserved in a file, and the user copy/pastes into their terminal with the mysql client open. Although this is fine in practice, it soon gets tedious. Enter the world of the command-line.

$ mysql -h mysql.aero.und.edu -p -u $USER caylan < my_schema.sql
Enter password:

drop table if exists notes;
 
CREATE TABLE `notes` (
`id` int(11) NOT NULL auto_increment,
`date` date default NULL,
`content` text,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
insert into notes (id, date, content) values (null, now(), "Test Note");
insert into notes (id, date, content) values (null, now(), "Test Sioux");
insert into notes (id, date, content) values (null, now(), "Sioux Note");
insert into notes (id, date, content) values (null, now(), "My Note");

• Google