Tag Archives: MySQL

AppArmor on Ubuntu and MySQL in custom directories

Leave a reply

AppArmor profile on Ubuntu is (rightly) restrictive and prevents the daemon binary mysqld from writing to unexpected locations.

So here’s another one in my series of ‘stupid notes to self’ — things that may help a reader, things that will help me, but things that are not proper or full guides to solving a problem.

Starting an already initialized datadir:

normal-user$ /usr/sbin/mysqld --defaults-file=/tmp/barproject-mysql-my.cnf
2018-01-09T21:24:28.090896Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2018-01-09T21:24:28.090907Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable

Initializing a new one:

normal-user$ /usr/sbin/mysqld --defaults-file=/tmp/barproject-mysql-my.cnf --datadir="$(pwd)/mysql" --log-level-verbosity=VERBOSE --initialize-insecure
mysqld: Can't create directory '/home/foo/projects/bar/_dev/mysql/' (Errcode: 13 -     Permission denied)
2016-10-10T16:23:29.515470Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-10-10T16:23:29.519420Z 0 [ERROR] Aborting

(Note that /tmp/barproject-mysql-my.cnf has been created from a template prior to running either of these. It specifies many values including datadir.)

First, I worked under the assumption the daemon was running under the wrong user or that the directory has wrong permissions. However, changing settings to any reasonable value did not get rid of errors with either initialization step or run step.

AppArmor has profiles that may block accesses atypical for the program executed. One such profile is for /usr/sbin/mysqld and is located in /etc/apparmor.d/usr.sbin.mysqld.

I tried symlinking it to /etc/apparmor.d/disable directory and restarting apparmor with systemctl restart apparmor. This didn’t change anything. I also tried whitelisting the directory by adding a local configuration change to /etc/apparmor.d/local/usr.sbin.mysqld:

/home/foo/projects/bar/_dev/mysql/** rwk,

systemctl restart apparmor — i.e. restarting through systemd — did not help.

However telling apparmor to tear itself down using the service command, as well as telling it to reload its profile cache, did:

service apparmor stop
service apparmor teardown
service apparmor recache
service apparmor start

There’s probably a smarter way, but this is good enough for me.

About downtime on my blog: lack of RAM and swap! Also, what is "swappiness"

Leave a reply

Over the last few days I had a bit of downtime. I have no monitoring set up for the services, and I didn’t have problems on the previous server. But having migrated to DigitalOcean, it turns out that its default configuration does not provide a swap partition, and that 512mb is too little for Apache2 and MySQL (at least with configurations I had on the previous server).

Even after a bit of reconfiguration to tell both Apache2 and MySQL to back off a bit, every few hours Linux would choose MySQL as the “less important” process to kill, due to lack of memory. And MySQL would not automatically restart (because it’s not set up to do so).

So I’ve done some deleting of old hosted files (freeing up a bit of disk space) and have set up a 1gb swapfile (note that the instructions are intended for 256mb servers, hence creating a 512mb swapfile).

In the process, I learned about sys.vm.swappiness, which is a kernel setting that apparently controls when the kernel will decide to swap things to the swap partition or swap file. By default set to 60, as the article instructs I’ve set it to 0 (which makes sense, as I want swap to be used as rarely as possible). IBM also suggests 0 in KVM-based virtual machines which is also what DigitalOcean uses.

This article from 2007 suggests you may even want to increase it in some cases (when you have a lot of inactive jobs waiting to be run), and force kernel to more aggressively find unused memory pages that it can swap to the disk. This AskUbuntu answer has a good explanation on what swappiness means: a setting of 60 means that at 40% usage kernel will attempt to find inactive pages to swap, and a setting of 10 means that at 90% usage kernel will attempt to find inactive pages to swap. Wikipedia also has an article, which sadly does little to explain the actual mechanisms.

A better solution would be to force MySQL and Apache2 to use appropriate amounts of RAM (and abide by some absolute limits, such as 196mb+196mb). But that’s something I’ll play with on another day… there are more important things to work on right now. 😉

After MySQL upgrade on OS X, all tables are missing

Leave a reply

When upgrading MySQL on OS X (anno domini 2010),  I learned the hard way that the “data” folder containing your databases and tables does not get moved. Look into /usr/local; you will probably have the old mysql-* folder there containing just the “data” folder. /usr/local/mysql is a symlink to the current MySQL installation, so move its data folder elsewhere, and put the old one in there.

[This is syndicated content. Read my blog at http://blog.vucica.net/ – please also leave comments there ]

PHP on Mac no longer able to connect to MySQL

Leave a reply

It’s been a while since I did web development on my Mac, and MySQL ceased to work. Could be related to that one time that I accidentally removed a bunch of dot-files from my home directory, could be related to MySQL upgrade. In any case, it no longer worked.
What I figured out was that the /var/mysql/mysql.sock UNIX domain socket was no longer generated. The /var/mysql folder was missing. When just creating it and assigning it to user _mysql (chown _mysql /var/mysql) did not help, I knew something was wrong with configuration.

MySQL tries to read configuration from /etc/my.cnf. To get its UNIX domain socket where PHP expects it, this needs to be contained in this configuration file:

[client]
port = 3306
socket = /var/mysql/mysql.sock


[mysqld]
port = 3306
basedir = /usr/local/mysql/
socket = /var/mysql/mysql.sock
; datadir = /servers/raiddrive/databases/

Please read the manual for details, and this is not administration advice; I’m not sure if this opens security holes, but I only use MySQL for development purposes. (Because you should know better than letting programmers near mission critical production servers.)

[This is syndicated content. Read my blog at http://blog.vucica.net/ – please also leave comments there ]