Recently I was trying to change MySQL's datadir setting away from the default of /var/lib/mysql to /data/mysql because I had mounted a cloud storage drive on /data. This proved to be more trouble than I expected.
I was using salt to provision the machine, so I had simply told the SaltStack mysql-forumula to make a my.cnf with the proper setting. This caused all sorts of silent errors and problems. It messed up apt-get to where I couldn't even install mysql-server-5.6 because APT's configure step wasn't completing. Here's what I was seeing
$ sudo dpkg --configure mysql-server-5.6
Setting up mysql-server-5.6 (5.6.28-0ubuntu0.14.04.1) ...
0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
0 [Note] /usr/sbin/mysqld (mysqld 5.6.28-0ubuntu0.14.04.1-log) starting as process 17185 ...
17185 [Warning] Can't create test file /data/mysql/web-test-2.lower-test
17185 [Warning] Can't create test file /data/mysql/web-test-2.lower-test
Can't create test file .lower-test
This error: Can't create test file xxx.lower-test made me immediately check the permissions. But the permissions were all correct. In a daze of frustration, I simply ran the dpkg command over and over. It didn't work.
This is one of those errors that really frustrates a person because you can't search for it. Dozens of other people have had this problem and they solved it by simply updating permissions. That was not my solution. I did find one answer on askubuntu that went through 3 solutions, one of them mentioned apparmor! Hooray! That makes sense.
The solution - bind mounts
Not wanting to completely disable and teardown apparmor, but realizing the problem was some security layer disallowing the mysql user access outside of /var/lib/mysql, I decided to simply mount the /data/mysql directory as /var/lib/mysql.
$ sudo mv /var/lib/mysql/* /data/mysql/
$ sudo mount -o bind /data/mysql /var/lib/mysql
$ echo '/data/mysql /var/lib/mysql none bind' >> /etc/fstab
Problem solved - and I didn't have to disable apparmor.
Now I have MySQL writing all table information to a cloud drive, which can be moved and re-attached to other cloud computing instances. This doesn't actually count as a back-up though, because catastrophic crashes will leave InnoDB tables in a corrupt state. Streaming binlogs to this drive might count as back-ups, though.