Friday, July 18, 2014

MySQL secure_auth error

I addressed the secure_auth errors before when it blocks replication in this blog post.

However, I figured I would make this blog post a more general fix when connecting via MySQL clients. This is for servers before MySQL 5.6.

So if you get a secure_auth error when connection to MySQL the following steps should clear this error.

+---------------+-------------------------------------------+
| User          | Password                                  |
+---------------+-------------------------------------------+
| authdtestuser | 34d22ac342c35af2                          
|   |+---------------+-------------------------------------------+

SELECT @@session.old_passwords, @@global.old_passwords;                                                            +-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       1 |                      1 |
+-------------------------+------------------------+

mysql> SET @@session.old_passwords = 0;  SET @@global.old_passwords=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.old_passwords, @@global.old_passwords;
+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       0 |                      0 |
+-------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SET PASSWORD FOR 'authdtestuser'@'localhost' = PASSWORD('sshthisisasecret');
Query OK, 0 rows affected (0.00 sec)

mysql> select User , Password from mysql.user where User = 'authdtestuser';
+---------------+-------------------------------------------+
| User          | Password                                  |
+---------------+-------------------------------------------+
| authdtestuser | *E48BD8BF1B9F29459E5284AD158443B5B33B70E4 |
+---------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @@session.old_passwords = 1;  SET @@global.old_passwords=1;

mysql> SELECT @@session.old_passwords, @@global.old_passwords;
+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       1 |                      1 |
+-------------------------+------------------------+
1 row in set (0.00 sec)


Wednesday, July 16, 2014

MySQL, Ubuntu:: mysqld does not have the access rights

So today I happen to need to restore a MySQL database from backups so I could recover some tables. While I left he production database running via port 3306, I set up the backup via port 3307.

However, when I attempted to start another version via the 3307 port in a mysql_restore directory but  I ran into some errors....


/usr/bin/mysqld_safe --defaults-file=/etc/my_3307.cnf

[Warning] Can't create test file /var/lib/mysql_restore/localhost.lower-test
[Warning] Can't create test file /var/lib/mysql_restore/localhost.lower-test
Can't find file: './mysql/plugin.frm' (errno: 13)

InnoDB: Completed initialization of buffer pool
InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

# perror 13
OS error code  13:  Permission denied


So I first checked the permissions on the directory, ensured that it was set to 700 and owned by mysql.  I also ensured that the directories inside the data directory was set to 700 and owned by mysql. The rest of the files all set to 600 ( -rw-rw---- ).  Just to be safe I also ensured that the disk was not full.

So the error log shows permission issues but at first glance all permissions are correct. So what it is?

Well, if I was on RHEL, or related branches,   I would think of SELinux. Was it blocking it somehow.
The Ubuntu Apparmor needed to be reviewed in this case.


# cat /etc/apparmor.d/usr.sbin.mysqld
...
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
...


Notice that the default "var/lib/mysql" is being addressed. The restore directory I created was not though. So by adding that and a restart of apparmor I was then able to successfully start MySQL.


# vi /etc/apparmor.d/usr.sbin.mysqld
...
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/lib/mysql_restore/ r,
  /var/lib/mysql_restore/** rwk,
...
/etc/init.d/apparmor {start|stop|restart|reload|force-reload|status|recache}
/var/lib/mysql_restore# /etc/init.d/apparmor restart
...
/usr/bin/mysqld_safe --defaults-file=/etc/my_3307.cnf
...
# mysql -P 3307 --socket=/var/lib/mysql_restore/mysqld_3307.sock



Additional URLs for review if needed: