2014
Feb
16





最近将伺服器搬家,於是将原机器所装的软体改装到新机器,并且 OS 从 CentOS 5.8 升到 CentOS 6.5,结果其中一个软体 Mysql 一直有问题。

Mysql Error
  1. 140216 13:56:04 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
  2.  
  3. 140216 13:56:05 mysqld_safe Starting mysqld daemon with databases from /www/data/mysqldata/
  4.  
  5. /usr/mysql/libexec/mysqld: Table 'mysql.plugin' doesn't exist
  6.  
  7. 140216 13:56:05 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
  8.  
  9. 140216 13:56:05 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
  10.  
  11. 140216 13:56:05 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50024, now running 50170. Please use mysql_upgrade to fix this error.

首先我使用 mysql_upgrade 更新资料库看看,结果如下:

mysql_upgrade
  1. Looking for 'mysql' as: mysql
  2. Looking for 'mysqlcheck' as: mysqlcheck
  3.  
  4. Running 'mysqlcheck with default connection arguments
  5.  
  6. mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect
  7. FATAL ERROR: Upgrade failed

在这里有看到一个 error 2002 ,去 Mysql 官网查询后,发现是 Permission Deny ,一开始想说可能是我的资料库 owner 并不是 「mysql」这个帐号,但是我启动 mysql 时,有指定 --user=mysql, 接著我使用指令

sudo chown -R mysql:mysql /mysqldata

将资料库档案的 owner , group 全部改掉,都改成 Mysql。

但是改完之后,问题仍然存在,后来追查下发现 /etc/my.cnf 这个 Mysql config 的设定没有更新到,於是我更新这个档案,加入如下的设定后, Mysql 就能正常的启动罗

my.cnf
  1. [mysqld]
  2. datadir=/mysqldata
  3. socket=/tmp/mysql.sock
  4. [client]
  5. socket=/tmp/mysql.sock

问题 2 : mariadb.pid

Mysql 不知为何,突然说无法建立这个档案 /var/run/mariadb/mariadb.pid

log file: /var/log/mariadb/mariadb.log
  1. 160226 16:10:27 mysqld_safe Starting mysqld daemon with databases from /var/MysqlData/
  2. 160226 16:10:27 [ERROR] /usr/local/share/mysql/libexec/mysqld: Can't create/write to file '/var/run/mariadb/mariadb.pid' (Errcode: 13)
  3. 160226 16:10:27 [ERROR] Can't start server: can't create PID file: Permission denied
  4. 160226 16:10:27 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended

解决方式为建立 /var/log/mariadb/, /var/run/mariadb/ 资料夹 ,并将这个资料夹的权限改成 777 或是 owner 改成 mysql 。


目前回應 Comments(2 comments)

  • 殺手 2014/11/11

    刪除了/etc/my.cnf以及/etc/mysql/my.cnf文件,重啓MySql服務,成功了,不過還是感謝博主的網誌~

    Reply

    Admin

    "Can not open the mysql.plugin table"

    這個問題我曾經有碰多好多次,幾乎都是在重新安裝 Mysql ,搬移舊資料庫的時候發生,問題有蠻多種可能的,但是我已經忘記以前是怎麼解決的,等我以後又碰到類似的問題,再回來補足詳細資訊。

  • 殺手 2014/11/10

    我的 /etc/my.cnf配置是正確的,可是還是啓動失敗,看了一下我的mysql數據庫目錄,是存在plugin這個表的。

回應 (Leave a comment)