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 -jX # where X stands for number of CPU cores you want to use for compilation
After compilation, install to
CMAKE_INSTALL_PREFIX (as root):
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/mysql on Ubuntu) as
/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.
Notes on editing my.cnf
You might want to alter or add the following options to the MySQL configuration file:
portfor server access / also important if multiple instances are present
socketfor server access / also important if multiple instances are present
userthe system user under which MySQL shall be run
basedirpointing to the directory MySQL is located
datadirthe directory where all the data will be
bind-addressto which IPs this instance will bind (i.e. from where the server will accept requests)
general_logset this to 0! Otherwise your log will explode, since every query will be mapped to the log
log_binmake 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:
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:
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:
or if you are using non-standard my.cnf file:
When MySQL successfully started, you should change the root password. One recommended way of doing this is using the
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.