Wednesday, August 7, 2013

Creating a MySQL db on Ubuntu as a normal user

Lately I tried to create a MySQL db on Ubuntu (version 11 which has MySQL 5.1 preinstalled). I was logged in under my normal username but I got a surprise when running the mysql_install_db command.
$ /usr/bin/mysql_install_db --datadir=./mysql/data
Installing MySQL system tables...

130806 22:17:21 [Warning] Can't create test file /home/andreash/mysql/data/andreas-Ub-2.lower-test
130806 22:17:21 [Warning] Can't create test file /home/andreash/mysql/data/andreas-Ub-2.lower-test

Installation of system tables failed!  Examine the logs in
./mysql/data for more information.

There were not log files though and checking directories and permissions didn't reveal any problems.
So I started to search and found that Ubuntu uses a security mechanism called apparmor which can be used to control certain aspects of an application.
In regards to MySQL that means that there exists a MySQL profile which defines which directories can be accessed (and how) by the MySQL programs. The profile for the daemon mysqld is defined in /etc/apparmor.d/usr.sbin.mysqld and looks like this:

# Last Modified: Tue Jun 19 17:37:30 2007
#include <tunables/global>

/usr/sbin/mysqld {
  #include <abstractions/base>
  #include <abstractions/nameservice>
  #include <abstractions/user-tmp>
  #include <abstractions/mysql>
  #include <abstractions/winbind>

  capability dac_override,
  capability sys_resource,
  capability setgid,
  capability setuid,

  network tcp,

  /etc/hosts.allow r,
  /etc/hosts.deny r,

  /etc/mysql/*.pem r,
  /etc/mysql/conf.d/ r,
  /etc/mysql/conf.d/* r,
  /etc/mysql/*.cnf r,
  /usr/lib/mysql/plugin/ r,
  /usr/lib/mysql/plugin/*.so* mr,
  /usr/sbin/mysqld mr,
  /usr/share/mysql/** r,
  /var/log/mysql.log rw,
  /var/log/mysql.err rw,
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
  /{,var/}run/mysqld/ w,
  /{,var/}run/mysqld/mysqld.sock w,

  /sys/devices/system/cpu/ r,

  # Site-specific additions and overrides. See local/README for details.
  #include <local/usr.sbin.mysqld>

So in order to enable MySQL to access a subdirectory of my $HOME I had to edit the file as root (sudo vi ...) and add this line to the list (I put it right under the /sys/devices line)

  /home/andreas/mysql/** rw,

The apparmor man page explains the syntax and attributes in detail. For my purposes it suffices to know that ** stands for the directory and all subdirectories underneath and rw is of course read/write.

Then this new profile needs to be activated replacing the old one via

$ sudo apparmor_parser -rv /etc/apparmor.d/usr.sbin.mysqld
Replacement succeeded for "/usr/sbin/mysqld".

Finally running the MySQL program again did create the databases.

Installing MySQL system tables...
Filling help tables...

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

To do so, start the server, then issue the following commands:


Not knowing much about apparmor yet I wonder how one would go about to allow all users (on a bigger multi-user server) to use MySQL or any other application which is secured in the same way. It would be impractical to add all users home directories to the profile file so I guess there must be some shortcut. This needs more reading.


  1. Harvard Business Review named data scientist the "sexiest job of the 21st century".This Data Science course will cover the whole data life cycle ranging from Data Acquisition and Data Storage using R-Hadoop concepts, Applying modelling through R programming using Machine learning algorithms and illustrate impeccable Data Visualization by leveraging on 'R' capabilities.With companies across industries striving to bring their research and analysis (R&A) departments up to speed, the demand for qualified data scientists is rising.

    data science training in bangalore

  2. myTectra offers Big Data and Hadoop training in Bangalore using Class Room.
    myTectra offers Live Online Big Data and Hadoop training Globally.
    Big Data and Hadoop training Unlike traditional systems, Big Data and Hadoop enables multiple types of analytic workloads to run on the same data, at the same time, at massive scale on industry-standard hardware.myTectra Big Data and Hadoop training is designed to help you become a expert Hadoop developer. myTectra offers Big Data Hadoop Training in Bangalore using Class Room. myTectra offers Live Online Big Data and Hadoop training Globally.
    hadoop training in bangalore

  3. Python has adopted as a language of choice for almost all the domain in IT including the most trending technologies such as Artificial Intelligence, Machine Learning, Data Science, Internet of Things (IoT), Cloud Computing technologies such as AWS, OpenStack, VMware, Google Cloud, etc.., Big Data Analytics, DevOps and Python is prepared language in traditional IT domain such as Web Application Development, Infrastructure Automation ,Software Testing, Mobile Testing.

    python online training