MySQL installation

Though you can use any MySQL or MariaDB version, I assume version MySQL 5.7 or MariaDB 10.2 is being installed for this course. Note that the client “dev” files must be installed too.

If you have chosen to install XAMPP, then you can skip to the installation checks section.
You should check your OS specific installation commands/tools. Here I assume a Debian / Ubuntu Linux or a Mac OS with Xcode/port facility installed.

Linux installation

See https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/. For example on Debian/Ubuntu:

shell> sudo apt install mysql-server libmysqlclient-dev

Mac OS installation

You have these options:

  1. Using the DMG+PKG Archive, download the file and follow these instructions.
  2. Installing XAMPP.
  3. Using port (see note below!)
    shell> sudo port install mysql
    See https://trac.macports.org/wiki/howto/MySQL

On Mac, we have experienced problems installing MySQL via port. Please do not use this installation method for the time being.

If you do not use a standard installation, e.g. via source code, remember to have your PATH updated to list the directory where mysql and the other MySQL related commands are. For example if it is in /usr/local/mysql/bin, for a bash shell:

export PATH=/usr/local/mysql/bin:$PATH

Write this at the end of your ~/.bash_profile or in the system wide bash configuration file (/etc/profile.d/local.sh for Debian systems or /etc/profile for MacOS).

Minimal installation checks

The first think to check is that the mysql command is in your path and that the MySQL root user has a password set. So this should work:

shell> mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql>

Note that for some fresh system installations (e.g. Ubuntu) it could be enough to become root (sudo -i) and then just type mysql to enter. Or sudo mysql -u root

If this is the first access to MySQL, please change the temporary password.

-- verify you are actually root
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+

mysql> ALTER USER root@localhost IDENTIFIED BY 'my_password';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

As mentioned, some system installations (e.g. on Ubuntu) do not use the standard authentication for the root user. In this case, if you want to get the standard access as user root from a non-root shell, you have to change the command a little:

ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY 'new_password';

See also this post.

Moreover:
shell> mysql_config
that will produce an output like this:

Usage: /usr/bin/mysql_config [OPTIONS]
Compiler: GNU 5.4.0
Options:
        --cflags         [-I/usr/include/mysql ]
        --cxxflags       [-I/usr/include/mysql ]
        --include        [-I/usr/include/mysql]
        --libs           [-L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread -lz -lm -lrt -ldl]
        --libs_r         [-L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread -lz -lm -lrt -ldl]
        --plugindir      [/usr/lib/mysql/plugin]
        --socket         [/var/run/mysqld/mysqld.sock]
        --port           [0]
        --version        [5.7.22]
        --libmysqld-libs [-L/usr/lib/x86_64-linux-gnu -lmysqld -lpthread -lz -lm -lrt -lcrypt -ldl -laio -llz4 -lnuma]
        --variable=VAR   VAR is one of:
                pkgincludedir [/usr/include/mysql]
                pkglibdir     [/usr/lib/x86_64-linux-gnu]
                plugindir     [/usr/lib/mysql/plugin]

Note the --socket value in [], e.g. /var/run/mysqld/mysqld.sock, then run (root password is required)
shell> mysqladmin -u root -p --socket=/var/run/mysqld/mysqld.sock version

You should get something like this:

mysqladmin  Ver 8.42 Distrib 5.7.22, for Linux on x86_64
Copyright (c) 2000, 2018, 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.

Server version		5.7.22-0ubuntu0.16.04.1
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysqld/mysqld.sock
Uptime:			14 days 4 hours 12 min 36 sec

Threads: 1  Questions: 16503418  Slow queries: 0  Opens: 58254  Flush tables: 1  Open tables: 416  Queries per second avg: 13.474

shell> ps aux | grep mysqld
Should see one or more lines with mysql owned process(es) like these:

mysql     1786  0.8  0.9 2421900 312244 ?      Ssl  May03 168:31 /usr/sbin/mysqld

or

_mysql    4185  0.0  0.0 3280384  7908 ??  S    10Apr18   5:06.94 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/xyz.err --pid-file=/usr/local/mysql/data/xyz.pid

shell> mysqladmin -u root -p variables
shell> mysqladmin -u root -p variables | grep datadir

And the output might look like this:

+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
...
| datadir       | /usr/local/mysql/var/ |
...

or
shell> mysql -u root -p -e "show variables like 'datadir'"
shell> mysql -u root -p -e "show databases"
And the output might look like this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| world              |
+--------------------+

Please see the step by step section for more examples.

For customization we might want to make, identify where the file my.cnf is located (e.g. /etc/my.cnf).
Can use locate on Linux, of mdfind on Mac, or check particular directories, like /etc/mysql/.

Automatic user login

It’s not recommended, but in some cases it could be useful to access the MySQL server without the need to enter manually user and password. This can be done inserting these information into a user specific configuration file: .my.cnf. It has to be in your home directory ($HOME) and look like this:

[client]
user = mpeusr
password = mpe2018pass
host = localhost

setting host is optional and additional parameters can be set in this file.
Of course if you prefer to work always with the root privileges, you can eventually use user = root and set its password.

Expert installation

In principle you do not need to know anything about relational databases to participate, but if you have programming skills, I would ask you “to try” to install MySQL using the Source distribution. This would allow you to install DIF and make it easier to customize the MySQL server for astronomical uses. If you have already a binary distribution installed and want to keep it, you have the option to install an additional version in a custom directory (e.g. /usr/local/mysql) by using the -DCMAKE_INSTALL_PREFIX=dir_name (--prefix=dir_name for 5.1) option when configuring it. As this is not mandatory, don’t worry to spend much time on this. But in this case at least install and configure a system ready version!
And of course if you have a Mac, be sure you have a development environment installed. I suggest MacPorts (https://www.macports.org/). See the requirements page.

Note: if configuring the source distribution, use the additional option “-DENABLED_LOCAL_INFILE=1”, and eventually the appropriate options for installing/using the Boost lib.

Assuming you’ll download version 5.7 that includes Boost Headers

tar zxvf mysql-boost-5.7.22.tar.gz
cd mysql-5.7.22
mkdir bld
cd bld
cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DENABLED_LOCAL_INFILE=1 -DWITH_BOOST=../boost
make
sudo make install

If instead you are using the version without Boost Headers, e.g.:

tar zxvf mysql-5.7.22.tar.gz
cd mysql-5.7.22
mkdir bld
cd bld
cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DENABLED_LOCAL_INFILE=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost_1_59_0
make
sudo make install

Note that in both cases we have compiled the code into the (user created) bld directory. This is recommended but not mandatory.

MySQL manual and downloads

Reference manual: https://dev.mysql.com/doc/refman/5.7/en/
The MySQL Command-Line Tool
MySQL Installation  –  Tutorial
MySQL Tutorial.org

A quick (local) MySQL guide here.

General downloads page: https://dev.mysql.com/downloads/

5.7 download page: https://dev.mysql.com/downloads/mysql/5.7.html

Source code download: https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22.tar.gz

Source installation info: https://dev.mysql.com/doc/refman/5.7/en/source-installation.html

GUI

For a GUI (not required!) can consider installing MySQL Workbench or phpMyAdmin.