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

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.

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

# /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

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.

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

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.mydomain.com.  To change this you can use the following query:

SET PASSWORD FOR root@"myhost.mydomain.com" = 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.mydomain.com -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's does help out.  Thank you.

# mysqladmin --password shutdown
Enter 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.

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 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)

#!/bin/sh

#
# mysql databse backup
# Copyright 1999, 2000 DVL Software Limited
#
# Available from http://www.freebsddiary.org/samples/dns_fetch.sh
#

#
# the name of the backup file. file name format is 
# backup.2000.01.12.at.22.59.48.tgz
#
BackupFile="forum.backup.`date +%Y.%m.%d.at.%H.%M.%S`.tgz"

#
# dump the database.
# make the following replacements:
#
#     userid     - the user id to use when connecting to the database
#     password   - the password for the above user
#     database   - the name of database to dump
#
mysqldump -uuserid -ppassword -c --add-drop-table database > forum_backup.txt

#
# compress it
#
tar cfz $BackupFile forum_backup.txt

#
# copy it offsite
#
ftp -n -v ftp.unixathome.org  <<EoF
        user ftp dump@freebsddiary.org
        bin
        prompt
        cd incoming
        mput $BackupFile
EoF

#
# remove the files we created
#
rm $BackupFile forum_backup.txt