Installing and configuring mySQL

Installing and configuring mySQL

This article describes how I installed mySQL, a very popular
relational database, especially as a back-end database for web servers.

I won’t be
teaching you how to use SQL.  Sorry, but that is way beyond the scope of this website
let alone this article.

Resources I have found

These are the resources I used when installing mySQL.

mySQL http://www.mysql.org/

mySQL mirrors http://www.mysql.org/mirrors.html

mySQL documents http://www.devshed.com/Server_Side/MySQL/

intstalling mySQL. http://www.devshed.com/Server_Side/Administration/Database/page3.html

mySQL and php3. http://hotwired.lycos.com/webmonkey/99/21/index3a_page4.html?tw=programming

NEWS FLASH, 15 March 2001: I’ve just been told about a free product
from Ansgar Becker.  It’s an MS Windows application for working with mySQL databases.
  I’ve just downloaded and tried it.  It looks very good.  I’ll use it for
awhile and give some feedback later.  See http://www.anse.de/mysqlfront/
for more information.

The background

I’m surprised it’s taken me so long to get around to using mySQL.
  I’ve been involved in client server database applications for almost 15 years.
  mySQL should have been one of my first ventures.

When I
decided to add the forum to the website, the process was rather
straight forward.  My web site provider already supplied mySQL and php3
support, but I wanted that at home as well.

Installing

Remember, I have the entire ports tree installed.  So
it was easy.  I originally tried mySQL321, but that failed to
build/install.  I can’t recall why.

cd /usr/ports/databases/mysql322
make
make install

NOTE: Since I wrote this article, mysql322 has been replaced by mysql323-client
and mysql323-server.  In most circumstances, you would install them
both.  NOTE: if those directories don’t exist, have a look in /usr/ports/databases/
for something else starting with mysql…

Getting it running

The easiest way to get mysql running is to use the installed script:

# /usr/local/etc/rc.d/mysql.sh

You should now see something this:

# ps u | grep mysql
root 94672 0.0 2.0   876  584  p1 R+ 5:58PM 0:00.04 grep mysql
root 94642 0.0 0.6   500  176  p1 I  5:56PM 0:00.11 /bin/sh 
                      /usr/local/bin/safe_mysqld
root 94651 0.0 6.4 11076 1896  p1 I  5:56PM 0:00.43 
                        /usr/local/libexec/mysqld 
                        --basedir=/usr/local --datadir=/va

I only connect to my database server from localhost. So there is no need for network
connections. Therefore, I add this flag to the above script: --skip-networking.
The script looks something like this:

/usr/local/bin/safe_mysqld --skip-networking --user=mysql ...

This also eliminates the possibility that someone will connect to your database server
over the network/Internet.

Making it more secure

NOTE: Since I first wrote this article, the port has been modifed to add the mysql
user and group automatically.  So you may not have to do this step.

I created a
user to run the mysql daemon.  This is deemed to be more secure than running
a daemon as root.  If the daemon is compromised, then it doesn’t have root
privileges.  Here’s the entry from vipw.   Your numbers may be
different, but the basics are the same.  I suggest you use adduser to do
this.

mysql:*:1010:1010::0:0:mysql daemon:/nonexistent:/sbin/nologin

Then I modifed the /usr/local/etc/rc.d/mysql.sh startup script to include
the --user parameter.  Here is what my file looks like now.  The bit
I added is in bold.

#!/bin/sh
#

/sbin/ldconfig -m /usr/local/lib/mysql

if [ -x /usr/local/bin/safe_mysqld ]
then
   /usr/local/bin/safe_mysqld --user=mysql > /dev/null & 
                                     && echo -n ' mysql'
fi

In addition to the above, you’ll also need to change the file permissions on the
databases.  See the next section for more information.

The database files

In conjunction with the user change in the previous section, you should also change
the permissions on the database files.  I used the opportunity to move the database
files to another location.  By default, the database files are located in
/var/db/mysql.  This can be changed by modifying the script /usr/local/bin/safe_mysqld.
  But I prefer to leave scripts unchanged and just move the files.  So I did
this:

cd /var/db
mv mysql /usr/local/
ln -s /usr/local/mysql mysql

Then I changed the permissions.

cd /usr/local
chown -R mysql mysql

And then restarted mysqld.

/usr/local/etc/rc.d/mysql.sh

The sysadmin password

This bit is what I originally wrote regarding the sysadmin password:

You should set the sysadmin password.  I did it like this:

mysqladmin -u root password newpassword

Personally, I don’t like the way you have to specify the password on the command line.
  Perhaps there is another way to change the password, but not that I could see.

You have just set the password for the mySQL root user, the sysadmin.  
This is not the same as the UNIX root user.

Anthony Rubin wrote in with better
information than I originally supplied.  I thank him for that.

Just wanted to give you some more information on the root password issue for mysql.
  You most certainly do not have to put the password on the command line to change it
and you really shouldn’t.  Here is what you should do instead.  First type the
following:

mysql -u root -p

It will now prompt you for the current password.  After you enter the current
password, you will enter the mysql client where you should use something like the
following query to change your password:

SET PASSWORD FOR root = PASSWORD('secret'); 

In this case root’s password is changed to the word "secret".  There is
only one problem with this.  It will only change root’s password for connections from
wherever you are currently connected from, probably localhost.  mysql
usually has another entry for root for myhost.www.example.org.  To change this you can
use the following query:

SET PASSWORD FOR root@"myhost.www.example.org" 
                                    = PASSWORD('secret'); 

Of course you could also use a different password if you would like, although I’m not
sure what purpose this would serve.  If you want to try out this second password you
can connect in the following way:

mysql -h myhost.www.example.org -u root -p

If you want to check for all root entries you can do the following:

mysql -u root -p mysql

Then enter the following query:

SELECT Host, User, Password FROM user WHERE User = 'root';

I’m pretty sure this should take care of everything. I hope this helps you out a
little.

Yes Andrew, that does help out.  Thank you.

Shutdown

After setting the sysadmin password, here is how you shutdown mysqld:

# mysqladmin -u root -p shutdown
Enter password:

Creating a database

http://www.devshed.com/Server_Side/Administration/Database/page6.html
contains details on how to create a database.  But here’s what I did.  It is
important to note that this user root is not the UNIX user root.  It is the
mySQL user.  Remember to supply the password you specified when you set the sysadmin
password.

# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.22.22

Type 'help' for help.

mysql> create database firstone;
Query OK, 1 row affected (0.05 sec)

You now have a database.  If you check under /var/db/mysql, you’ll
see a new directory for firstone.  This is the database.

Creating mySQL users

In this stage, we create database users.  These are not UNIX users with a login.
  These are logical users.  When you connect to the database, you must supply a
user id and password.  I would suggest not using the same logins for both UNIX and mySQL,
just in case.  Similarly, don’t use the same password for mySQL and UNIX.
  That’s just asking for trouble.

See http://www.devshed.com/Server_Side/Administration/Database/page6.html
for details on how to create a new mySQL user.

Here is how I created a user, testuser, and gave them permissions on
everything in my database called test.

mysql> grant usage on test.* to testuser@localhost;
Query OK, 0 rows affected (0.02 sec)

mysql> grant select, insert,delete on test.* to
mysql>      testuser@localhost;
Query OK, 0 rows affected (0.02 sec)

You can now login to the database as testuser and retrieve the data.  Note that I
first created the table mytable as shown at http://www.devshed.com/Server_Side/Administration/Database/page6.html.

# mysql -u testuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
          Your MySQL connection id is 4 to server 
          version: 3.22.22

Type 'help' for help.

mysql> use test;
Reading table information for completion of table and 
        column names
You can turn off this feature to get a quicker startup 
        with -A

Database changed
mysql> select * from mytable;
+----------------+----------+
| name           | phone    |
+----------------+----------+
| Homer Simpson  | 555-1234 |
| Bart Simpson   | 555-4321 |
| Lisa Simpson   | 555-3214 |
| Marge Simpson  | 555-2314 |
| Maggie Simpson | 555-3142 |
+----------------+----------+
5 rows in set (0.02 sec)

Backups

The script I use for backing up my database is available from:

samples/database_dump.sh.txt

WARNING: I advise you to modify the above script according to the
instructions at Keeping mysql passwords secure.

The above script dumps just one database.  But using the "–opt" options
will dump everything.  Note that that is two hypens, – followed by – followed by opt.

Note that the above method uses ftp which is not secure (meaning, anyone snooping along
the way can read what you are transferring.  If your files contain sensitive
information, then I suggest you read How to copy files
around without anyone seeing them.

Restore

After using mysqldump to extract data from one database, here’s
how I loaded the data into another database:

# mysql -u <userid> -p <database> < /path/to/backup.file

Where:

• <userid> is the user id to use
• <database> is the database to use
• /path/to/backup.file contains the output from the dump in the previous
  section.

Column information

To view the columns in a table use this command:

mysql> SHOW COLUMNS FROM topics;  
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     |      | PRI | 0       |       |
| name        | varchar(30) |      |     |         |       |
| bookmark    | varchar(20) |      |     |         |       |
| active_date | date        | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql> 

That should get you started

I hope that all worked for you.  If it didn’t, please add your comments to this
article.

“Sorry, the host ‘nezlok’ could not be looked up'” (added on 14 January 2002)

I was setting up a new box and encountered this rather strange message:

===> Generating temporary packing list
/usr/local/bin/mysql_install_db
Sorry, the host ‘nezlok’ could not be looked up.
Please configure the ‘hostname’ command to return a correct hostname.
If you want to solve this at a later stage, restart this script with
the –force option
*** Error code 1

Stop in /usr/ports/databases/mysql323-server.
*** Error code 1

Stop in /usr/ports/databases/mysql323-server.
*** Error code 1

Stop in /usr/ports/databases/mysql323-server.

I could not figure this problem out. hostname returned nezlok.example.org. And nslookup
could resolve the name to an IP address. I even went so far as to add the name to /etc/hosts.
Still no good.

Finally, I asked on IRC, and someone suggested checking /etc/resolv.conf for the domain
setting in case the install process was using hostname -s which trims off any domain name.
Sure enough, the name was wrong in /etc/resolv.conf. I had taken over this box from someone
else and I had not changed domain to my domain. Once I modified the entry in /etc/resolv.conf
to domain example.org,
the hostname resolved correctly and mysql-server would install. I did have to do a make clean first though.

Or you can do a make SKIP_DNS_CHECK=yes.

Forgotten the root password? (added on 9 March 2002)

If you forget the root password, here is how you can reset it.

Stop mysqld with this command:


/usr/local/etc/rc.d/mysql-server.sh stop


Modify the start command to add this option to the command line.

WARNING: do
not leave this option on for long. Only for when you need it. It by passes
all usual mysql security.

-Sg|--skip-grant-tables
       This option causes the server not to use the privi-
       lege system at all. This gives everyone full access
       to all databases! (You can tell a running server to
       start  using  the  grant  tables again by executing
       mysqladmin flush-privileges or mysqladmin  reload.)

Here is the line after I modified it in /usr/local/etc/rc.d/mysql-server.sh:


/usr/local/bin/safe_mysqld --user=mysql --skip-grant-tables > /dev/null & && echo -n ' mysqld'


Start mysqld:


/usr/local/etc/rc.d/mysql-server.sh start


Connect to mysql


mysql


Be cautious; this next step will reset the passwords for all root users.
You may wish to restrict this SQL by including an “and host = 'something'” clause.
Inspect existing users with the “SELECT host, user from user;“.

Select the mysql database and reset the password.

This next step will reset the passwords for all root users.
You may wish to restrict this SQL by including an “and host = 'something'” clause.
Inspect existing users with the “SELECT host, user from user;“.


use mysql
update user set password = PASSWORD('secret') where user = 'root';


WARNING: Do not forget to undo that mysql bypass!

1. Stop mysql
2. Remove the option from the /usr/local/etc/rc.d/mysql-server.sh
3. Restart mysql: /usr/local/etc/rc.d/mysql-server.sh start