MySQL Configuration

Have you just installed Slackware and now see a MySQL error at the login prompt?

This is a VERY quick HowTo and should take maybe 30 seconds to complete at its slowest. The reason I decided to do a write-up is because of the sheer number of people asking how to fix it.

Option 1

In a shell or xterm, type (you have to be logged in as superuser to use the mysql login):

su mysql

This logs you into the 'mysql' user account.

Next, run:

mysql_install_db

This will create the needed databases and set their permissions properly.

if your not logon as mysql user (root)

Don't forget to chown folder /var/lib/mysql

chown -R mysql.mysql /var/lib/mysql
chmod 755 /etc/rc.d/rc.mysqld

You're now finished and should not see the typical MySQL errors at the login prompt.

/etc/rc.d/rc.mysqld start

Now you should set a password for MySQL's root password:

mysqladmin -u root password 'new-password-here'

You can connect to your MySQL server with:

mysql -u root -p

For security reasons you should delete an empy user for localhost server

mysql> use mysql
mysql> SELECT user, host FROM user;
mysql> DELETE FROM user WHERE host='localhost' AND user=''; 

• This tutorial is currently linked at
• http://www.unixfool.com/mysql-slack.shtml

Option 2

1. log in as root and install the mysql package

2. killall -9 mysqld mysqld_safe to kill any running MySQL processes.

3. copy one of the my-size.cnf files in /etc to my.cnf (picking an apporpriate size):

cp /etc/my-medium.cnf /etc/my.cnf

4. Install the MySQL database-

Slackware 12.1 (and later):

mysql_install_db --user=mysql

Slackware 12.0 (and before):

su mysql

mysql_install_db

exit (to get out of the mysql login and back to root).

5. mysqld_safe --skip-grant-tables &

6. mysql -u root mysql

7. UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';

8. FLUSH PRIVILEGES;

9. exit

10. killall -9 mysqld mysqld_safe

11. mysqld_safe &

to check the new password, (as root) type:

mysqladmin -u root -p status

...and enter the new password.

Option 3

I'm starting from a very basic install.

The only additional package needed is mysql in the AP group.

# installpkg mysql-5.0.37-i486-1.tgz
# su mysql
$ mysql_install_db
Installing all prepared tables
Fill help tables
...
$ exit
# chmod 755 /etc/rc.d/rc.mysqld 
# /etc/rc.d/rc.mysqld start
Starting mysqld daemon with databases from /var/lib/mysql

MySQL is now installed, but there are no passwords defined yet.

# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.37 Source distribution

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

We can now set passwords from within the MySQL console. I know this can be achieved with mysqladmin, but then, several ways lead to Rome. It's a simple matter of habit.

mysql> show databases;
 +--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| test               | 
+--------------------+
3 rows in set (0.01 sec)

mysql> use mysql;
Database changed

mysql> select user, host, password from user where user = 'root';
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          | 
| root | slacktest |          | 
+------+-----------+----------+
2 rows in set (0.00 sec)

As you can see, there are two 'root' users here (and none of them is our system's root user, don't forget): root@localhost... and root@slacktest. We have to set a password for both of them. In theory, these can be different, but why make things more complicated than they already are? ;o)

mysql> set password for root@localhost = password('yatahongaga');
Query OK, 0 rows affected (0.00 sec)

Let's repeat our query above to see the actual changes:

mysql> select user, host, password from user where user = 'root';
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *71CDE2704222D8D5A7608C92AF78C53F78DA5EBA | 
| root | slacktest |                                           | 
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

You can see that root@localhost's password is displayed as a shadow password. Now let's set the password for root@slacktest (replace slacktest by your machine's hostname):

mysql> set password for root@slacktest = password('yatahongaga');
Query OK, 0 rows affected (0.00 sec)

What do we have now?

mysql> select user, host, password from user where user = 'root';
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *71CDE2704222D8D5A7608C92AF78C53F78DA5EBA | 
| root | slacktest | *71CDE2704222D8D5A7608C92AF78C53F78DA5EBA | 
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

Now we have some (very) basic security, we can leave the MySQL monitor.

mysql> quit;
Bye

To connect to our MySQL database as MySQL's root user (who, remember, is not the system's root), we can do the following:

# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.37 Source distribution

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

mysql>

If we have to do this often, we can create a /root/.my.cnf file and edit it as follows:

[mysql]
user            =       root
password        =       yatahongaga

Just to be on the safe side:

# chmod 0600 /root/.my.cnf

From now on, you can connect directly to the MySQL monitor, without having to type your password every time.