The FreeBSD Diary

The FreeBSD Diary (TM)

Providing practical examples since 1998

If you buy from Amazon USA, please support us by using this link.
[ HOME | TOPICS | INDEX | WEB RESOURCES | BOOKS | CONTRIBUTE | SEARCH | FEEDBACK | FAQ | FORUMS ]

Things look quiet here. But I've been doing a lot of blogging at dan.langille.org because I prefer WordPress now. Not all my posts there are FreeBSD related. I am in the midst of migrating The FreeBSD Diary over to WordPress (and you can read about that here). Once the migration is completed, I'll move the FreeBSD posts into the new FreeBSD Diary website.

Installing and configuring mySQL 2 January 2000
Share
Need more help on this topic? Click here
This article has 6 comments
Show me similar articles
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)
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'" 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? 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
Share
Need more help on this topic? Click here
This article has 6 comments
Show me similar articles