So I've been wanting to do a series of posts on MySQL for a while now. In this one we'll just start with the basics, on next ones we'll look into taking backups and replication. Good times ahead.

Install MySQL server (the client will also be installed as a dependency):

pkg_add -r mysql55-server

Check which version exactly you have:

pkg_info | grep mysql

You should see something like this:

mysql-client-5.5.9  Multithreaded SQL database (client)
mysql-server-5.5.9  Multithreaded SQL database (server)

To make MySQL start on boot - edit the file /etc/rc.conf

ee /etc/rc.conf

And add the line:

mysql_enable="YES"

Exit and save. Check the status?

/usr/local/etc/rc.d/mysql-server status

At this point you should probably see:

mysql is not running.

Start it up:

/usr/local/etc/rc.d/mysql-server start

You should see:

Starting mysql.

To stop MySQL:

/usr/local/etc/rc.d/mysql-server stop

On success:

Stopping mysql.
Waiting for PIDS: 1120.

Check what port MySQL is running on:

sockstat -4 | grep mysql

You should see something like this:

mysql    mysqld     1237  10 tcp4 6 *:3306                *:*

As you can see this allows access from any system. This can potentially be dangerous. If you want to only restrict access to localhost (127.0.0.1) you'll need to copy one of the template configuration files that comes with MySQL, edit it and restart MySQL - it's actually really easy:

cp /usr/local/share/mysql/my-small.cnf /usr/local/etc/my.cnf

Now edit the file:

ee /usr/local/etc/my.cnf

Scroll down to line 47 and add the following:

bind-address = 127.0.0.1

Now restart MySQL:

/usr/local/etc/rc.d/mysql-server restart

Now check what ports MySQL is running on again:

sockstat -4 | grep mysql

As you can see the result is very different:

mysql    mysqld     1860  10 tcp4   127.0.0.1:3306        *:*

Now let's connect to the server:

mysql

You'll get the MySQL prompt:

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.9 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

Check if there are any databases already living in mysql:

show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

Use one of the databases – NOTE: Capitalization doesn't matter for the commands but it DOES for the tables/databases!

E.g. :

USE MYSQL;

is not the same as:

USE mysql;

use mysql;

You should see:

Database changed

Let's see what tables this database has:

show tables;

Let's get details for the fields in a table:

describe servers;

You should see:

+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| Server_name | char(64) | NO   | PRI |         |       |
| Host        | char(64) | NO   |     |         |       |
| Db          | char(64) | NO   |     |         |       |
| Username    | char(64) | NO   |     |         |       |
| Password    | char(64) | NO   |     |         |       |
| Port        | int(4)   | NO   |     | 0       |       |
| Socket      | char(64) | NO   |     |         |       |
| Wrapper     | char(64) | NO   |     |         |       |
| Owner       | char(64) | NO   |     |         |       |
+-------------+----------+------+-----+---------+-------+
9 rows in set (0.01 sec)

Retrieve all information from a table:

select * from servers;

Let's create a new database:

create database users;

We should see this:

Query OK, 1 row affected (0.00 sec)

If we do a 'show databases;' again, we'll see the new 'users' database:

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| users              |
+--------------------+
5 rows in set (0.00 sec)

Say you don't like/need the users database anymore and want to get rid of it (drop):

drop database users;

This is all the confirmation you get:

Query OK, 0 rows affected (0.00 sec)

Let's make sure it's gone:

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

Let's create a new fancy database:

create database sheep;

Again the confirmation is really short:

Query OK, 1 row affected (0.00 sec)

And let's use it (meaning you can do stuff inside that database such as creating tables):

use sheep;
Database changed

Create a table (very basic - it only has a name and lastname columns that can be up to 20 characters each):

CREATE TABLE little_sheep (name VARCHAR(20), lastname VARCHAR(20) );

Again the confirmation is short:

Query OK, 0 rows affected (0.01 sec)

Let's see the result:

show tables;
+-----------------+
| Tables_in_sheep |
+-----------------+
| little_sheep    |
+-----------------+
1 row in set (0.00 sec)

Let's see the details of little_sheep table:

describe little_sheep;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(20) | YES  |     | NULL    |       |
| lastname | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Good times!

Now let's add a couple of sheep:

insert into little_sheep (name,lastname) values ('lampros','sheepah');
insert into little_sheep (name,lastname) values ('stelios','sheepah');

Now let's see the sheep that are into the little_sheep table:

select * from little_sheep;

And there are the sheep:

+---------+----------+
| name    | lastname |
+---------+----------+
| lampros | sheepah  |
| stelios | sheepah  |
+---------+----------+
2 rows in set (0.00 sec)

If you want to delete one of the sheep:

delete from little_sheep where name='stelios' and lastname='sheepah';

Let's see the result:

select * from little_sheep;

+---------+----------+
| name    | lastname |
+---------+----------+
| lampros | sheepah  |
+---------+----------+
1 row in set (0.00 sec)

Better times! Now let's edit the remaining sheep:

update little_sheep set name='john', lastname='blacksheep' where name='lampros' and lastname='sheepah';

Note the confirmation says 'Changed':

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Let's see the result:

+------+------------+
| name | lastname   |
+------+------------+
| john | blacksheep |
+------+------------+
1 row in set (0.00 sec)

As easy as that, we went through all the steps of CRUD:

Create, Read , Update, Delete.

We saw how to start,stop and restart MySQL on FreeBSD and how to lock it to be used only by localhost.

In the next post we'll hook MySQL up with Fat Free CRM.