MySQL, Simple Installation

This article will walk you through a simple installation of MySQL server, for usage via localhost in a local environment. (For example, for use in conjunction with Apache and PHP in a web-database related role, with no direct foreign connections to the MySQL server. All connections will be made internally.)

See also: MySQL

Overview

For this article, I'll be installing MySQL Server and Client, version 4.0.x (current in my ports tree.) The steps listed here will very likely work for newer versions available when you read this, but you may want to google to make sure.

Install MySQL from ports

First, we need to install the port. Make sure you're logged in as root, and as always, update your ports tree with CVSUP before doing any major software installations.

oyabun# cd /usr/ports/databases/mysql40-server/
oyabun# make install clean BUILD_OPTIMIZED=YES BUILD_STATIC=yes

This will download the source for MySQL Server, compile it optimized for your particular system, and also install mysql40-client, as it is a dependency. This process will very likely take a decent amount of time. Oyabun is my BSD server, and it's an AthlonXP 2000+, compiling MySQL from source usually takes about 15 - 25 minutes. (Guesstimating, I've never actually timed it.)

When the installation is done, you'll see something very similar to this:

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/bin/mysqladmin -u root password 'new-password'
/usr/local/bin/mysqladmin -u root -h oyabun.n3s.local password 'new-password'
See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr/local ; /usr/local/bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the /usr/local/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
mysql:*:88:
You already have a group "mysql", so I will use it.
mysql:*:88:88::0:0:MySQL Daemon:/var/db/mysql:/sbin/nologin
You already have a user "mysql", so I will use it.
Changed home directory of "mysql" to "/var/db/mysql"
===>   Compressing manual pages for mysql-server-4.1.3
===>   Registering installation for mysql-server-4.1.3
===> SECURITY REPORT:
      This port has installed the following files which may act as network
      servers and may therefore pose a remote security risk to the system.
/usr/local/bin/ndb_drop_table
/usr/local/bin/ndb_delete_all
/usr/local/libexec/ndbd
/usr/local/bin/ndb_restore
/usr/local/bin/ndb_cpcd
/usr/local/libexec/ndb_mgmd
/usr/local/bin/ndb_select_all
/usr/local/bin/ndb_drop_index
/usr/local/bin/ndb_desc
/usr/local/lib/mysql/libndbclient.so.0
/usr/local/bin/ndb_show_tables
/usr/local/bin/ndb_waiter
/usr/local/bin/ndb_select_count

      This port has installed the following startup scripts which may cause
      these network services to be started at boot time.
/usr/local/etc/rc.d/mysql-server.sh

      If there are vulnerabilities in these programs there may be a security
      risk to the system. FreeBSD makes no guarantee about the security of
      ports included in the Ports Collection. Please type 'make deinstall'
      to deinstall the port if this is a concern.

      For more information, and contact details about the security
      status of this software, see the following webpage:
http://www.mysql.com/
===>  Cleaning for mysql-client-4.0.20
===>  Cleaning for libtool-1.5.8
===>  Cleaning for mysql-server-4.0.20

The really important parts are those about setting a MySQL password for your root account, and the addition of a mysql group and a mysql user to your base system. You'll notice in my example that I already had a mysql user and group, but you likely won't.

You'll also notice that the install added a startup script to /usr/local/etc/rc.d/, meaning MySQL server will start with every boot now. Let's move on to configuration and finish up this installation.

Configuration

MySQL is now installed, so let's get it ready for use.

First, we have to start the server. The following assumes you logged in as a regular account, then used su to become root.

oyabun# exit
exit
> su
Password:
oyabun# /usr/local/etc/rc.d/mysql-server.sh start
mysqloyabun#
oyabun#

It may not be necessary, but I usually exit my root shell and re-login in after major software installations, so the system can find necessary scripts and programs in the path, which the new software may have updated. It costs nothing but a few seconds. Anyway: You'll notice mysql notified you that it started by changing the next shell prompt to "mysqloyabun#, where oyabun = the name of your server.

Now, let's set that root password.

oyabun# /usr/local/bin/mysqladmin -u root password 'new-password'
oyabun# /usr/local/bin/mysqladmin -u root -h oyabun.n3s.local password 'new-password'

Change 'new-password' to whatever you really want use as your password. In the second line, change oyabun.n3s.local to whatever your current domain qualification is for your server. The MySQL install will tell you - remember, I pulled this example out of the output at end-of-install. If you're copying and pasting this as you go into your putty session or other ssh client, WATCH OUT FOR LINEBREAKS. If you copy the carriage return as well, you really did just set your password to 'new-password.' (Hey, don't laugh, it just happened to me.)

If that happens, you'll have to issue a slightly different command to change a current password.

oyabun# /usr/local/bin/mysqladmin -p password 'the-REAL-password'
Enter password:
oyabun#

Since you're logged in as root, this will change your password after you enter the current password, which if you screwed up like I did, is/was 'new-password'. Crisis averted.

Note: From now on, you can't type mysql at a command prompt to enter the mysql client, you have to add -p to make it challenge you for a password.

Testing the installation

We're going to get into a little bit of mysql syntax here. For more in-depth documentation, you should visit http://www.mysql.com or google the particular command you're having trouble with, using 'mysql grant syntax' as the search argument for example.

oyabun# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31 to server version: 4.0.20
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database testing;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on testing.* to 'user'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.02 sec)
mysql>

First, you'll notice that all commands in the mysql client have to be terminated with ;. If you forget and hit enter, you can just enter a lone ; on a new line, and it will work provided the syntax of the command is correct.

If both of those commands worked, then you just created a database named testing, and assigned full read/write priviledges to a user name 'user' with password 'password'.

To delete a database, issue the following command:

mysql> drop database testing;
Query OK, 0 rows affected (0.00 sec)

That's it! You now have MySQL added to your machine, and you begin using php scripts or other MySQL tools.