Aerospace Software Ltd.

MySQL Howto

For Mandrake 9.1 with MySQL 4.0.11a

August 2003.

Herman Oosthuysen

General

This is yet another MySQL howto, but it is not intended to replace the documentation. It describes MySQL configuration and useage tricks and traps and debug issues, that may help you in your daily scirmishes with this powerful database system.

I wrote this guide, when I wanted to create a database and then could not get a common user to connect, due to a problem with the grant command. The solution is detailed below.

Installation

You need the following rpms from your Mandrake CDs:

perl-mysql
mysql-4.0.11a
mysql-client

Directories and files

A database is a directory filled with files.
These files are tables and indexes.
MySQL databases are by default located in /var/lib/mysql.
User and configuration information is stored in a database /var/lib/mysql/mysql.

Don't delete the mysql database. If you do so accidentally, then the easiest way to recover is to re-install MySQL from scratch, by removing the rpms and then installing them again and then re-create all the MySQL users and passwords.

Personal configuration is in file /home/user/.my.cnf:

[client]
user=username
password=userpassword

Putting the password in there is handy, but not a good idea in an insecure environment.

To enable host access add the following line to /etc/hosts.allow:

mysqld: localhost 192.168.10.

else tcpwrappers may block a connection attempt from another host on your network.

Note: For proper security, you have to create a password for the root user.

Common Commands

As root, you create a database, use the database and then create a table. Only then, can you assign users to the database and grant them privileges. With some versions of MySQL, you don't need to create a table first, but in all cases you must have a database to assign the users to. The grant command will also create a password for the user.

Some common administrative commands:

# mysql -uusername -ppassword -Ddatabase
> create database name;
> create table name (record type(size),...);
> set password=password('newpassword');
> set password for username = password('newpassword');
> use database;
> show databases;
> show tables;
> grant all privileges on database.* to username@localhost identified by 'password';
> grant all privileges on database.* to username@'192.168.10.%' identified by 'password';
> grant all privileges on database.* to adminusername@localhost identified by 'password' with grant option;
> flush privileges;
> show grants for username;
> exit;

Note that the grant command requires the @hostname specifier, else it won't work. I don't know whether this is a bug or an undocumented security update to MySQL. The command flush privileges will force MySQL to re-read the user privileges table.

Lost admin password

If you need to take over a database and the previous administrator neglected to tell you the password, use the following recipe:

Log on to your system as the Unix root user.
Stop the MySQL daemon:
# service mysqld stop
Set the user to mysql, the user the daemon normally runs as:
# su mysql
Restart the MySQL server with the special --skip-grant-tables option:
$ /usr/libexec/mysqld --skip-grant-tables --user=root &
(or mysqld_safe depending on your system)
Set a new password for the root@localhost MySQL account:
$ mysqladmin -u root flush-privileges password "newpwd"
Replace `newpwd'' with the actual root password that you want to use.
Alternatively, if you get a 'no matching records' error, use mysql:
$ mysql -u root
UPDATE mysql.user SET Password=PASSWORD('whatever') WHERE User='root';
FLUSH PRIVILEGES;
Exit back to the root user and restart the MySQL server without any special option:
$ exit
# service mysqld restart

You should now be able to connect using the new password.

Network Access

If you are trying to connect to a remote database and you cannot, check if MySQL is configured for networking. Disable option --skip-networking in the MySQL configuration file.

Hope this helps!

Herman