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
or
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 presentsocket
for server access / also important if multiple instances are presentuser
the system user under which MySQL shall be runbasedir
pointing to the directory MySQL is locateddatadir
the directory where all the data will bebind-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 loglog_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
or
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.