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.20 (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 csup before doing any major software installations. You also need to have working DNS resolution for your machine's hostname - check the output of the hostname command, and make sure you can ssh into it (and that it is your box!). If you don't have working DNS resolution, you will need to either fix it or temporarily hack your way around the problem by issuing a hostname localhost command to set the machine's name to localhost.

oyabun# cd /usr/ports/databases/mysql40-server/
oyabun# make install clean

This will download the source for MySQL Server, compile it, 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.0.20
===>   Registering installation for mysql-server-4.0.20
===> 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 rehash your shell's PATH and start the server.

oyabun# rehash
oyabun# /usr/local/etc/rc.d/mysql-server.sh start
mysqloyabun#

You'll notice mysql's startup script notified you that it started by saying "mysql - there's no carriage return after a startup script calls out its notification like that, so instead of seeing a completely separate line, you see "mysqloyabun#" instead of "oyabun#" as the next command prompt.

Raven's Note: by default, your new MySQL installation does not have a root password set. If you want to keep shell users from logging into it as root, or if you want remote SQL clients to be able to authenticate themselves as root, you'll need to set a root password now.

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. You can find more articles on how to do things with MySQL at the MySQL Disambiguation page.