如何解決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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s