MySQL Configuration

From SlackWiki
Revision as of 15:30, 6 August 2012 by Arfon (talk | contribs) (/etc/rc.d/rc.mysql start --> /etc/rc.d/rc.mysqld start AND ADDED: mysql> use mysql)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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=''; 

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.