Make MySQL Your Own

With a fast, capable MySQL backend, you can turbocharge network services like Postfix and Samba.

By Carla Schroder | Posted Aug 23, 2005
Page 1 of 2
Print ArticleEmail Article
  • Share on Facebook
  • Share on Twitter
  • Share on LinkedIn

She who rules databases rules the world.

Computers are great at squirreling away vast quantities of data, but it doesn't do much good if we don't have a way to manipulate all that data. Databases (specifically relational database management systems, or RDBMS) are key to making sense of gigantic mounds of data.

MySQL is especially suited to common sysadmin chores. It doesn't have all the bells and whistles of RDBMS like Oracle and PostgreSQL, as database snobs will go out of their way to tell you. It's not supposed to, though it's getting more sophisticated as it matures. Its advantages for sysadmins are reliability, speed — it is extremely fast — and ease of use; it doesn't require a database programming guru to use it.

"Sounds good," you say, "but I'm just an old country system or network admin, not a librarian. Why should I care about databases in general, or MySQL in particular?"

Because most servers support database backends. This provides scalability, speed and flexible management options. Slashdot.org serves up three million pages per day from an Apache/mod_perl/MySQL backend. Running Postfix with a MySQL backend lets you handle squillions of users and domains with ease. Samba + MySQL = cross-platform authentication and single sign-on. System logs and the output from network utilities like Nmap can be stored in a MySQL database for fast, sophisticated analysis.

Installing and Starting MySQL
Perform the usual installation routine for your system (RPM/apt-get/Yum/YaST/build from sources); be sure to install both the server and the client. The current release is MySQL 5, and MySQL 4.x is perfectly fine if your Linux distribution doesn't support 5.0 yet.

After installation check to see if mysqld is running:

$ pgrep mysqld
4950
4987

On Red Hat and Fedora MySQL is stopped and started with these commands:

# service mysqld start
# service mysqld stop
# service mysqld restart

On Debian and its many offspring:

# /etc/init.d/mysql start
# /etc/init.d/mysql stop
# /etc/init.d/mysql restart

Every time you make a change to MySQL's configuration file it must be restarted, which is probably not something you'll be doing a lot.

Now you must create a MySQL root user, using the mysqladmin command:

# mysqladmin -u root password bigsecret

Test your new root user by logging in and bringing up MySQL's built-in command line:

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5 to server version: 4.1.13a-Debian_3-log

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

mysql>

Type exit or quit to quit MySQL.

Building a User Database
Let's leap right in and make a MySQL database. There are lots of nice graphical frontends to MySQL (see Resources) but we don't need any sissy GUIs because SQL (Structured Query Language) is easy and sensible. And understanding the command line means you'll be a more effective troubleshooter when you do use graphical tools. First create and name your new database:

mysql> create database samba_auth;
Query OK, 1 row affected (0.12 sec)

Then select your new database and add a table for your users' logins and data:

mysql> use samba_auth;
Database changed
mysql> create table users (
  ->uid int(12) not null auto_increment primary key,
  ->gid int(12) default '0' not null,
  ->login varchar(12) not null,
  ->password varchar(10) not null,
  ->first_name varchar(32) not null,
  ->last_name varchar(32) not null,
  ->creation_date datetime not null
  ->);

Query OK, 0 rows affected (0.10 sec)

The primary key is a unique identifier for every record in the table. You may override the auto_increment by specifying a number, and it will continue counting from that number. There maybe only one auto_incremented field in a table. int(12) means the format for that field is a whole number up to 12 digits. varchar is a case-sensitive text field that contains numbers or letters.

Don't panic if you make a mistake and get an error message; simply hit the "up" arrow to repeat the command, then find and correct your error, navigating with the right and left arrow keys. Once you have successfully created your shiny new table you can admire it with these commands:


mysql> show databases;
+——————+
| Database   |
+——————+
| mysql      |
| samba_auth |
| test       |
| users      |
+——————+
4 rows in set (0.00 sec)


mysql> use samba_auth; Database changed mysql> show tables; +———————————+ | Tables_in_samba_auth | +———————————+ | users | +———————————+ 1 row in set (0.00 sec)

Entering describe users; at the MySQL prompt will provide you with a text table describing the uid, gid, login, password, name and creation date fields.

Continued on page 2: Adding Users

Comment and Contribute
(Maximum characters: 1200). You have
characters left.
Get the Latest Scoop with Enterprise Networking Planet Newsletter