MySQL – MySQL Not Working After Upgrade from 14.04 to 16.04

I suggest to back up, and then purge and reinstall mysql-server.

Can you install automysqlbackup ?

sudo apt-get install automysqlbackup

Then run automysqlbackup from the command-line, and make also a backup of /etc directory :

sudo /usr/sbin/automysqlbackup

sudo rsync -av /etc/ $HOME/etc-backup/

Check that automysqlbackup created the backups successfully :

sudo ls -la /var/lib/automysqlbackup/daily/

After that remove the mysql server packages, purge the configuration, and reinstall mysql-server :

sudo apt-get remove --purge mysql-server*

sudo apt-get install mysql-server

Put back your mysqldumps, by copying from the mentioned automysqlbackup "daily" dir, unzip the file, and then :

sudo mysql --defaults-file=/etc/mysql/debian.cnf

mysql> create database example-db;
mysql> quit

sudo mysql -u root -p example-db < example-db-daily.sql

Do the same for all of your databases.

Now based on the link you provided and another resource about the relationship between AppArmor and Mysql I was able to get it to work. The policy that governs mysql is loaded from this location /usr/sbin/mysqld which you can see when you run: sudo aa-status. This can be altered from,:

/etc/apparmor.d/usr.sbin.mysqld

and

/etc/apparmor.d/local/usr.sbin.mysqld

I will be altering the latter.

Steps:

1. I have created a folder /var/www/html/mysql and with placed a file (error.log) in there. I believe mysql will look for such a file. Now this is my custom log location.

2. Open the /etc/appparmor.d/local/usr.sbin.mysqld and add the following lines:

   /var/www/html/mysql/ r,
   /var/www/html/mysql/** rwk,
   

   • Interpretation:
     • /var/www/html/mysql/ r,:--> give mysql read access to that folder.
     • /var/www/html/mysql/** rwk,:--> give mysql read, write and lock access to contents in there.

3. Open /etc/mysql/mysql.conf.d/mysqld.cnf and change the log location to:

   #log_error = /var/log/mysql/error.log
   log_error = /var/www/html/mysql/error.log
   

4. Now restart apparmor: sudo systemctl restart apparmor

5. Now restart mysql: sudo systemctl restart mysql

Note:

1. I set the user:group of /var/www/html to $USER:www-data and the permissions to:
   • user: rwx,
   • group: rwx, and
   • others: r-x

2. Added mysql user to www-data group:

   sudo usermod -a -G www-data mysql
   

Theory:

Apparmor is Ubuntu's way of making sure there is fine grained control over who accesses the file system and how it is done. It has two modes, enforce and complain modes. The enforce mode is where mysql falls under ( to see these run sudo aa-status).

You can either change mysql to complain mode where apparmor will only complain but not prevent mysql accessing other areas of the file system. In this case I choose the enforce mode and simply altered the apparmor policy for mysql To alter these apparmor modes you have to install apparmor-utils and then you could just do sudo aa-complain /usr/sbin/mysqld and apparmor will now only complain about filesystem violations by mysql.

Two locations are used to alter the policy, these are (1) /etc/apparmor.d/usr.sbin.mysqld, and (2)/etc/appamrmor.d/local/usr/sbin/mysqld. Which chosen will depend on the type of change desired (network wide or machine specific). So alter to suite your plans.

Resource(please look at these):

https://blogs.oracle.com/jsmyth/apparmor-and-mysql

https://blogs.oracle.com/jsmyth/selinux-and-mysql