Creating a Database With MySQL

Let’s say we want to track our servers using a database instead of those nasty spreadsheets that many of us use. MySQL is a pretty simple and cheap way to get started. We are going to use a blank root password in these examples to ease typing, however, you should change the root password following our article here.

Let’s create a database!

u-1@srv-1 mysqlart $ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.0.14-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database sysops;
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye
u-1@srv-1 mysqlart $ 

OK. We have a database. Now, let’s enter a table and some fields. The easiest way to do this is to create a file first:

CREATE TABLE systemsdoc (
manu text NOT NULL,
model text NOT NULL,
addr text NOT NULL,
zip text NOT NULL,
phone text NOT NULL,
deploy_date text NOT NULL,
sernum text NOT NULL,
assetnum text NOT NULL,
machname text NOT NULL,
sysversion text NOT NULL,
UID int(11) NOT NULL default '0',
PRIMARY KEY  (UID)
) TYPE=MyISAM;

We can create the table and fields:

u-1@srv-1 mysqlart $ mysql sysops --user root < initialtable.sql

Here are some commands to show our database, table, and fields:

u-1@srv-1 mysqlart $ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.14-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| sysops   |
| test     |
+----------+
3 rows in set (0.00 sec)
mysql> 
mysql> connect sysops;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id:    10
Current database: sysops
mysql> show tables;
+------------------+
| Tables_in_sysops |
+------------------+
| systemsdoc       |
+------------------+
1 row in set (0.00 sec)
mysql> 
mysql> describe systemsdoc;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| manu        | text    |      |     |         |       |
| model       | text    |      |     |         |       |
| addr        | text    |      |     |         |       |
| zip         | text    |      |     |         |       |
| phone       | text    |      |     |         |       |
| deploy_date | text    |      |     |         |       |
| sernum      | text    |      |     |         |       |
| assetnum    | text    |      |     |         |       |
| machname    | text    |      |     |         |       |
| sysversion  | text    |      |     |         |       |
| UID         | int(11) |      | PRI | 0       |       |
+-------------+---------+------+-----+---------+-------+
11 rows in set (0.00 sec)
mysql> quit
Bye
u-1@srv-1 mysqlart $