The FreeBSD Diary

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.

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.

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

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

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

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.

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

# mysqladmin -u root -p 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
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)

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.

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

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> 

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.

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

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