Daiquiri

Daiquiri and MySQL

Daiquiri currently runs best with MySQL versions 5.5 and 5.6. If you do not plan to use any of our MySQL extension plugins and daemons, we suggest to install MySQL using your package manager version. Even if you want to use our plugins, you should be able to use the version coming with your distribution, if you can obtain the MySQL source code as well.

However if you want to use the most recent version of MySQL, you need to build it from scratch. We will describe both approaches.

Install distribution MySQL packages

To install the MySQL server including the source code (as root):

# on ubuntu/debian
apt-get install mysql-client mysql-server mysql-source

For CentOS or Scientific Linux, you first need to enable the mysql community repo from

http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm # CentOS 6
http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm # CentOS 7

The you can install the packages using:

yum install mysql-community-client mysql-community-server mysql-community-devel

After this is done, you should invoke mysql_secure_installation to set a secret root password. You also might want to make changes to your /etc/my.cnf file (see below for guidelines).

Compile MySQL from scratch:

Please consult the MySQL documentation for further instructions, pitfalls and peculiarities. These instructions are just a reference and we cannot take any responsibility that they are correct and lead to a secure installation of MySQL. Don't blame us if you have no clue what you are doing!

We are using cmake and cmake-gui, so make sure it is installed!

Download latest source version of MySQL (for this document we assume MySQL 5.6.10, http://dev.mysql.com/downloads/mysql/)

Move the downloaded tar file to a place where you can safely compile things. Then change to that directory and extract the files:

cd /path/to/tarfile
tar -xf mysql-5.6.10.tar.gz

Change to the MySQL sour directory, create a build directory, change to this directory, and start cmake-gui:

cd mysql-5.6.10
mkdir build
cd build
cmake-gui

Click "Configure" to let cmake find the proper settings. Review the settings. Pay special attention to the following options:

CMAKE_INSTALL_PREFIX (you might want to set this to the directory where you want MySQL to be installed)
MYSQL_DATADIR (where the data will lie, this can be changed through the configure file later on)
WITH_FEDERATED_STORAGE_ENGINE (you might need this)
WITH_PARTITION_STORAGE_ENGINE (you might need this)
WITH_PERFSCHEMA_STORAGE_ENGINE (you might need this)

Click "Generate" to generate the make files and exit cmake-gui. Then compile using make:

make

make -jX # where X stands for number of CPU cores you want to use for compilation

After compilation, install to CMAKE_INSTALL_PREFIX (as root):

make install

Setup and edit my.cnf file. If you are not using the my.cnf file provided through your linux distribution (i.e. if you have never installed MySQL before), obtain a template my.cnf file at http://www.fromdual.com/mysql-configuration-file-sample and place it in /etc (or /etc/mysql on Ubuntu) as /etc/my.cnf (or /etc/mysql/my.cnf on Ubuntu). You can also have multiple my.cnf files with different names. In this case, the files need to be specified in the init.d start file or in the mysqld command using the defaults-file flag, e.g.

mysqld --defaults-file=/path/to/YOUR_AWESOME_MY.cnf)

Notes on editing my.cnf

You might want to alter or add the following options to the MySQL configuration file:

port for server access / also important if multiple instances are present
socket for server access / also important if multiple instances are present
user the system user under which MySQL shall be run
basedir pointing to the directory MySQL is located
datadir the directory where all the data will be
bind-address to which IPs this instance will bind (i.e. from where the server will accept requests)
general_log set this to 0! Otherwise your log will explode, since every query will be mapped to the log
log_bin make sure this is commented out, otherwise MySQL server will be somewhat slower. This option turns the binary log on, which is needed for replication.

The remaining options can be set to whatever you would like to have. Please note however that fine tuning MySQL is an art which is described in many webpages and books. Things are not always logically connected ...

Setting up MySQL for the first time

Let me first make you aware of this page: http://dev.mysql.com/doc/refman/5.5/en/unix-postinstallation.html.

Make sure, that all the directories you specified in my.cnf are readable and writable by the MySQL user. This includes the data directory, the log files and the place where the socket and the pid file are placed.

Before you can start MySQL, you need to setup the initial databases. In the MySQL directory exists a script you can run, to set these up (in Ubuntu this is in /usr/bin if you installed your own version of MySQL this is in /path/to/mysql/scripts/. Just go to this directory and execute the script, e.g.:

cd /path/to/mysql/scripts
./mysql_install_db

or if you are using a non-standard my.cnf file:

./mysql_install_db --defaults-file=/path/to/YOUR_AWESOME_MY.cnf

Note, in case you get trouble with permissions, check if the mysql-user has access to the data directory etc. It may be a good idea to run mysql_install_db with root permissions, but specifying the mysql-user:

sudo scripts/mysql_install_db --user=mysql

After that, follow the instructions that mysql_install_db asks you to do (i.e. set root password).

If you get an error like:

Installing MySQL system tables...2013-04-26 14:39:12 11260
[ERROR] Can't read from messagefile '/usr/share/mysql/english/errmsg.sys'

you probably have/had an old installation of mysql to which these files belong. Use updatedb and locate to find the conflicting file. There should exist two versions. Identify the correct one (on Ubuntu it's probably the one in /usr/local/mysql/share) and set the directory in your my.conf (Ubuntu: /etc/mysql/my.cnf) accordingly:

lc-messages-dir = /usr/local/mysql/share

Try the script above again and hope that it works.

Now you can start the server by either using the init.d file or manually invoking:

/path/to/mysql/bin/mysqld

or if you are using non-standard my.cnf file:

/path/to/mysql/bin/mysqld --defaults-file=/path/to/YOUR_AWESOME_MY.cnf

When MySQL successfully started, you should change the root password. One recommended way of doing this is using the

mysql_secure_installation

command. It guides you through everything you should do.

After you are done with this, you can create new users. First connect to the server using

mysql --password=SECRET --user=root --socket=/PATH/TO/mysql.sock

mysql --password=SECRET --user=root --host=127.0.0.1 --port=3306 # or other

Once logged in as root, you can create administration accounts using

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;

or normal users with

mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP` ON expenses.* TO 'custom'@'host47.example.com';

Or similar. Remember to flush the privileges after all these operations

mysql> FLUSH PRIVILEGES;

Please consult http://dev.mysql.com/doc/refman/5.5/en/default-privileges.html and http://dev.mysql.com/doc/refman/5.5/en/adding-users.html for further information.