如何解決MySQL 開動不到的問題 MySQL start fail

昨天在服務器搬遷時遇到問題, 就是搬遷後, Mysql Start Fail – MySQL 資料庫不能開始. 找了很久都沒有答案, 檢查程序如下

以下的例子都是在 Ubuntu 下執行, 最後就是我的解決方法
檢查MySQL 的設定檔

Sudo vi /etc/mysql/my.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql # 確定 folder 是否存在
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
bind-address = 127.0.0.1# 如果這裡是其他 ip 地址, 有可能造成 MySQL start fail
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M
log_bin = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
expire_logs_days = 10
max_binlog_size = 100M
skip-bdb
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
檢查MySQL 資料夾的權限

權限分別為
keithyau@Yubis-production:/var/lib$ sudo ls -l /var/lib/mysql
total 20612
-rw-r–r– 1 root root 0 2008-03-16 22:01 debian-5.0.flag
drwx—— 2 mysql mysql 12288 2008-08-16 02:29 drupal
-rw-rw—- 1 mysql mysql 10485760 2009-02-26 21:00 ibdata1
-rw-rw—- 1 mysql mysql 5242880 2009-02-26 21:08 ib_logfile0
-rw-rw—- 1 mysql mysql 5242880 2008-03-16 22:01 ib_logfile1
drwxr-xr-x 2 mysql root 4096 2002-01-13 17:16 mysql
-rw——- 1 root root 6 2008-03-16 22:01 mysql_upgrade_info
keithyau@Yubis-production:/var/lib$ sudo ls -l /var/lib
total 176
drwxr-xr-x 4 root root 4096 2008-03-14 20:39 apt
drwxr-xr-x 2 root root 4096 2008-03-14 20:41 aptitude
drwxr-xr-x 2 root root 4096 2002-01-26 19:48 dhcp3
drwxr-xr-x 4 root root 4096 2008-03-14 20:52 dictionaries-common
drwxr-xr-x 2 root root 4096 2006-10-24 03:27 discover
drwxr-xr-x 7 root root 4096 2009-02-26 20:45 dpkg
drwxr-xr-x 3 root root 4096 2008-03-14 20:53 gstreamer
drwxr-xr-x 2 root root 4096 2008-03-14 20:39 initramfs-tools
drwxr-xr-x 2 root root 4096 2007-01-31 06:27 initscripts
drwxr-xr-x 2 root root 4096 2009-02-26 19:17 lib_mysql
drwxr-xr-x 2 root root 4096 2008-03-14 22:32 logrotate
drwxr-xr-x 6 root root 4096 2009-01-03 14:12 menu-xdg
drwxr-xr-x 2 root root 4096 2006-10-28 22:06 misc
drwxr-xr-x 10 mysql mysql 4096 2009-02-26 21:08 mysql
drwxr-xr-x 2 root root 4096 2008-01-27 19:46 mysql-cluster
檢查後依然不能開始MySQL 服務器 ……

打開 /var/log/syslog 看看有沒有以下一行
Feb 26 20:45:59 Yubis-production /etc/init.d/mysql[6254]: ^G/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
Feb 26 20:45:59 Yubis-production /etc/init.d/mysql[6254]: error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)’
Feb 26 20:45:59 Yubis-production /etc/init.d/mysql[6254]: Check that mysqld is running and that the socket: ‘/var/run/mysqld/mysqld.sock’ exists!
Feb 26 20:45:59 Yubis-production /etc/init.d/mysql[6254]:
Feb 26 20:46:04 Yubis-production mysqld_safe[6319]: started
Feb 26 20:46:04 Yubis-production mysqld[6328]: 090226 20:46:04 InnoDB: Started; log sequence number 0 43665
Feb 26 20:46:04 Yubis-production mysqld[6328]: 090226 20:46:04 [ERROR] Binlog has bad magic number; It’s not a binary log file that can be used by this version of MySQL
如果存在的話, 應該是你的 MySQL 服務器沒有正常關上, 那就 …

打開 /var/log/mysql
keithyau@Yubis-production:/var/lib$ sudo ls -l /var/log/mysql/
total 295696
-rw-rw—- 1 mysql adm 104885450 2009-02-18 12:36 mysql-bin.000077
-rw-rw—- 1 mysql adm 105175310 2009-02-24 11:27 mysql-bin.000078
-rw-rw—- 1 mysql adm 83164522 2009-02-26 19:24 mysql-bin.000079
-rw-rw—- 1 mysql adm 98 2009-02-26 19:56 mysql-bin.000080
-rw-rw—- 1 mysql adm 9233377 2009-02-27 16:10 mysql-bin.000081
-rw-rw—- 1 mysql adm 160 2009-02-26 21:08 mysql-bin.index
把最後一個 bin 檔案刪除
sudo rm /var/log/mysql/mysql-bin.00081
再打開 mysql-bin.index 把最後一行刪除
sudo vi /var/log/mysql/mysql-bin.index
/var/log/mysql/mysql-bin.000077
/var/log/mysql/mysql-bin.000078
/var/log/mysql/mysql-bin.000079
/var/log/mysql/mysql-bin.000080
/var/log/mysql/mysql-bin.000081
重新打開 MySQL 資料庫就完成了

Sudo /etc/init.d/mysql restart