Make MySQL Your Own

By Carla Schroder | Aug 23, 2005 | Print this Page
http://www.enterprisenetworkingplanet.com/netos/article.php/3529516/Make-MySQL-Your-Own.htm

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

Continued From Page 1

Adding Users
Now it's time to populate the users table with some actual users:

mysql> insert into users values (
  -> '500', '500', 'cschroder', encrypt('secretword'), 'carla', 'schroder');

Query OK, 1 row affected (0.00 sec)
mysql>insert into users values (
  ->', '501', 'thanson', encrypt('bigsecret'), 'terry', 'hanson');

Query OK, 1 row affected (0.00 sec)

And so forth, until you have a nice batch of users in your new table. View the contents by entering select * from users; at the MySQL prompt, which should provide you with a text table that reflects the user and group i.d. of each user, as well as their passwords, logins, and names.

Creating Administrative Users
You can give your lackeys limited privileges to maintain specific databases like this, preferably using something a bit more secure than "password" for the password:

mysql> grant all privileges on samba_auth.* to sqllackey01@"localhost" identified by 'password';
Query OK, 0 rows affected (0.08 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Then your new user logs in to work like this:

$ mysql -u sqllackey01 -p samba_auth
Enter password:

Adding, Deleting, and Changing
MySQL lets you make all kinds of changes without giving you a lot of backtalk, though you must exercise care and not make a hash of your hard work, which is easy to do. You can delete a table or an entire database:

mysql> drop table users; mysql> drop database samba_auth;

Delete a single entry in a table. Using the field containing the primary key is safest because that is a unique identifier:

mysql> delete from users where uid=500;

Delete a field from a table:

mysql> alter table users drop gid;

Add a field to a table, specifying that it either comes first, or follows another field:

mysql> alter table users add location varchar(64) first;
mysql> alter table users add date_of_hire date after location;

For most sysadmin-type jobs, a simple flat database is all you need, so the basic commands covered here will serve you well. Next week we'll learn how to backup MySQL, connect to remote MySQL servers, and hook into common applications like Apache and Samba.

Resources