Saturday, July 19, 2014

MySQL的secure_auth錯誤

Original post: http://anothermysqldba.blogspot.com/2014/07/mysql-secureauth-error.html

我以前處理的secure_auth錯誤時,在此它阻止複製的博客文章  

不過,我想通通過MySQL的客戶端連接時,我會把這個博客帖子更一般的修復。 這是MySQL 5.6之前的服務器。 

所以,如果你得到一個secure_auth錯誤,當連接到MySQL下面的步驟應該清除此錯誤。 

+---------------+-------------------------------------------+ 
| User | Password | 
+---------------+-------------------------------------------+ 
| authdtestuser | 34d22ac342c35af2
 +---------------+-------------------------------------------+ 
| 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) 
| |+---------------+-------------------------------------------+ 

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不能有訪問權限

Original post: http://anothermysqldba.blogspot.com/2014/07/mysql-ubuntu-mysqld-does-not-have.html
所以今天我正好需要從備份中恢復一個MySQL數據庫,所以我可以恢復一些表。 雖然我離開了他的生產數據庫通過端口3306上運行,我設置的備份通過端​​口3307。 

然而,當我試圖通過在mysql_restore目錄中的3307端口上啟動另一個版本,但我遇到了一些錯誤.... 


/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 


所以,我首先檢查了該目錄的權限,確保它被設置為700和mysql的擁有。 我也確保了數據目錄中的目錄設置為700和mysql的擁有。 該文件的其餘部分都設置為600(-RW-RW ----)。 為了安全起見我也確保了磁盤是不是滿了。 

所以錯誤日誌顯示的權限問題,但乍看之下所有權限是否正確。 那麼它是什麼? 

好吧,如果我是在RHEL上,或相關的分支,我覺得SELinux的的。 被它阻擋它在某種程度上。 
而Ubuntu的AppArmor需要在這種情況下進行審查。 


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


請注意,默認的“無功/ lib中/ MySQL的”正在解決。 我創建了恢復目錄是不是雖然。 所以,通過添加和AppArmor的重新啟動我當時能夠成功啟動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 



如果需要進行審查的其他網址: