之前启动MySQL时,就不太正常,要多重启几次,不过能启动,也就没放在心上,今天测试测试又出现了问题,MySQL
启动正常,但是无法关闭:
 
[[email protected]
mysql]# service mysql stop
MySQL server PID file could not befound!                  [FAILED]
[[email protected]
mysql]# ./mysql.server stop
MySQL server PID file could not befound!                  [FAILED]
 
这里提示的PID
文件指:/var/lib/mysql/rac2.pid,该文件和/var/lib/mysql/mysql.sock
文件一样,仅在MySQL 运行时存在,后来的验证如下:
 
[[email protected]
lib]# ls /var/lib/mysql/rac2.pid
/var/lib/mysql/rac2.pid
[[email protected]
lib]# service mysql stop
Shutting down MySQL.                                       [  OK  ]
[[email protected]
lib]# ls /var/lib/mysql/rac2.pid
ls: /var/lib/mysql/rac2.pid: No such fileor directory
 
 所以当我的MySQL
运行正常时,应该是存在这个文件的,google的一个说法是my.cnf
参数文件的basedir
参数错误,注释掉这个参数即可,但是我都没有设过这个参数,所以和这个应该没有关系。
 
查看了一下默认值:
mysql> show variables like ‘%basedir%’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| basedir       | /usr |
+—————+——-+
1 row in set (0.00 sec)
 
[[email protected]
~]# ps -ef|grep mysql
root     2161     1  0 09:38 ?        00:00:00 /bin/sh
/usr/bin/mysqld_safe–datadir=/var/lib/mysql
–pid-file=/var/lib/mysql/rac2.pid
mysql    2418  2161  0 09:38 ?        00:00:00 /usr/sbin/mysqld
–basedir=/usr–datadir=/var/lib/mysql
–plugin-dir=/usr/lib64/mysql/plugin
–user=mysql–log-error=/var/lib/mysql/rac2.err
–pid-file=/var/lib/mysql/rac2.pid–socket=/var/lib/mysql/mysql.sock
–port=3306
root     3628  3596  0 09:57 pts/1    00:00:00 grep mysql
 
–log信息
[[email protected]
~]#cat /var/lib/mysql/rac2.err
111129 9:38:49 [Note] Plugin ‘FEDERATED’ is disabled.
111129 9:38:49 InnoDB: The InnoDB memory heap is disabled
111129 9:38:49 InnoDB: Mutexes and rw_locks use GCC atomic builtins
111129 9:38:49 InnoDB: Compressed tables use zlib 1.2.3
111129 9:38:49 InnoDB: Using Linux native AIO
111129 9:38:49 InnoDB: Initializing buffer pool, size = 128.0M
111129 9:38:50 InnoDB: Completed initialization of buffer pool
111129 9:38:50 InnoDB: highest supported file format is Barracuda.
111129 9:38:50  InnoDB: Waiting for thebackground threads to start
111129 9:38:51 InnoDB: 1.1.8 started; log sequence number 1605950
111129 9:38:52 [Note] Event Scheduler: Loaded 0 events
111129 9:38:52 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.5.15-log’  socket: ‘/var/lib/mysql/mysql.sock’  port: 3306
MySQL Community Server (GPL)
111129 10:50:36 mysqld_safe Starting mysqlddaemon with databases from
/var/lib/mysql
111129 10:50:37 [Note] Plugin ‘FEDERATED’is disabled.
111129 10:50:37 InnoDB: The InnoDB memoryheap is disabled
111129 10:50:37 InnoDB: Mutexes andrw_locks use GCC atomic builtins
111129 10:50:37 InnoDB: Compressed tablesuse zlib 1.2.3
111129 10:50:37 InnoDB: Using Linux nativeAIO
111129 10:50:37 InnoDB: Initializing bufferpool, size = 128.0M
111129 10:50:37 InnoDB: Completedinitialization of buffer pool
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already haveanother mysqld process
InnoDB: using the same InnoDB data or logfiles.
111129 10:50:37  InnoDB: Retrying to lock the first data file
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already haveanother mysqld process
InnoDB: using the same InnoDB data or logfiles.

InnoDB: Check that you do not already haveanother mysqld process
InnoDB: using the same InnoDB data or logfiles.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already haveanother mysqld process
–这个提示的意思有多个mysqld 进程在运行。
 
 
解决方法:
(1)重新用:ps -ef|grep mysql
查询所有的进程,然后把所有的进程kill掉,
(2)重新对相关的目录赋权
[[email protected]
/]# chown mysql.mysql -R/var/lib/mysql/*
[[email protected]
/]# chmod 755 -R/var/lib/mysql/*
 
–再次启动成功:
[[email protected]
/]# service mysql start
Starting MySQL……                                       [  OK  ]
[[email protected]
/]# ps -ef|grep mysql
root    27936     1  1 14:08 pts/1    00:00:00 /bin/sh
/usr/bin/mysqld_safe–datadir=/var/lib/mysql
–pid-file=/var/lib/mysql/rac2.pid
mysql   28193 27936  3 14:08 pts/1    00:00:00 /usr/sbin/mysqld
–basedir=/usr–datadir=/var/lib/mysql
–plugin-dir=/usr/lib64/mysql/plugin
–user=mysql–log-error=/var/lib/mysql/rac2.err
–pid-file=/var/lib/mysql/rac2.pid–socket=/var/lib/mysql/mysql.sock
–port=3306
root    28220  3596  0 14:08 pts/1    00:00:00 grep mysql
[[email protected]
/]# ll /var/lib/mysql/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Nov 29 14:08/var/lib/mysql/mysql.sock
 
再次查看log:
[[email protected]
lib]#  tail -20 /var/lib/mysql/rac2.err
111129 14:51:53 [Note] Event Scheduler:Purging the queue. 0 events
111129 14:51:53  InnoDB: Starting shutdown…
111129 14:51:54  InnoDB: Shutdown completed; log sequencenumber
1605950
111129 14:51:54 [Note] /usr/sbin/mysqld:Shutdown complete
 
111129 14:51:54 mysqld_safe mysqld from pidfile /var/lib/mysql/rac2.pid
ended
111129 14:53:34 mysqld_safe Starting mysqlddaemon with databases from
/var/lib/mysql
111129 14:53:34 [Note] Plugin ‘FEDERATED’is disabled.
111129 14:53:34 InnoDB: The InnoDB memoryheap is disabled
111129 14:53:34 InnoDB: Mutexes andrw_locks use GCC atomic builtins
111129 14:53:34 InnoDB: Compressed tablesuse zlib 1.2.3
111129 14:53:34 InnoDB: Using Linux nativeAIO
111129 14:53:34 InnoDB: Initializing bufferpool, size = 128.0M
111129 14:53:34 InnoDB: Completed initializationof buffer pool
111129 14:53:35 InnoDB: highest supportedfile format is Barracuda.
111129 14:53:35 InnoDB:1.1.8 started; log sequence number 1605950
111129 14:53:35 [Warning] Neither–relay-log nor –relay-log-index
were used; so replication may break when thisMySQL server acts as a
slave and has his hostname changed!! Please
use’–relay-log=rac2-relay-bin’ to avoid this problem.
111129 14:53:35 [Note] Event Scheduler:Loaded 0 events
111129 14:53:35 [Note]/usr/sbin/mysqld: ready for connections.
Version: ‘5.5.15-log’  socket: ‘/var/lib/mysql/mysql.sock’  port: 3306
MySQL Community Server (GPL)
[[email protected]
lib]#
 
 
说明:
      
因为之前没有保留的相关信息,修改之后,虽重复模拟多次,都不能还原当时的故障,所有缺少足够多的一手资料来证明这个问题。但是我这里的2种方法都可以尝试一下:
   
MySQL server PID file could not befound! 
–可能是相关目录的权限问题导致
 
Check that you do not already have anothermysqld process
–重复进程导致,可以先ps,在kill掉相关进程在重试。

MySQL启动故障处理一例(errno13)

之前在系统上装了个MySQL用于测试,今天发现启动mysqld_safe进程的时候报错,如下所示:
[[email protected]
local]# mysqld_safe & [1] 2631
[[email protected]
local]# Starting mysqld daemon with databases from
/data/mysql/mysql_3306/data STOPPING server from pid file
/data/mysql/mysql_3306/data/bak.pid 141017 09:20:23 mysqld ended

[1]+ Done mysqld_safe
由于我的my.cnf并没有改名,使用的是默认的/etc/my.cnf这个位置,因此mysqld_safe
&可以不用指定任何参数,需要的参数已经在my.cnf中指定了,如果使用的my.cnf位置不在/etc/下面,或者改过了自定义的名字,那么需要用–defaults-file参数来指定my.cnf文件的位置
[[email protected]
local]# cd /data/mysql/mysql_3306/logs/
[[email protected]
logs]# ll total 0
查看日志,并没有任何binlog生成,这里说明一下,这个环境是从原来的一台主机上clone过来的,clone完以后没有启动过MySQL,我想可能是因为这个缘故
[[email protected]
mysql_3306]# cd /usr/local
[[email protected]
local]# chown -R mysql:mysql /data/mysql/
[[email protected]
local]# chown -R mysql:mysql /usr/local/mysql/
[[email protected]
local]# ll total 76 drwxr-xr-x 2 mysql 502 4096 Aug 25 11:39 bin
-rw-rw—- 1 mysql root 432 Oct 17 09:20 error.log drwxr-xr-x 2 mysql
502 4096 Aug 8 2008 etc drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 games
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 include drwxr-xr-x 2 mysql 502
4096 Aug 8 2008 lib drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 libexec
lrwxrwxrwx 1 root root 38 Sep 24 00:12 mysql ->
/opt/mysql/mysql-5.5.39-linux2.6-i686/ drwxr-xr-x 2 mysql 502 4096 Aug 8
2008 sbin drwxr-xr-x 5 mysql 502 4096 Aug 25 11:39 share drwxr-xr-x 2
mysql 502 4096 Sep 23 20:34 src
[[email protected]
local]# id mysql uid=501(mysql) gid=1000(mysql) groups=1000(mysql)
mysql用户id为501,但不知为何显示的是mysql 502的形式,而不是mysql mysql
开始以为是因为/usr/local/mysql这个目录是root:root的缘故,但改为mysql:mysql后,依然无法启动
[[email protected]
data]# chown -R mysql:mysql /usr/local
[[email protected]
data]# cd /usr/local
[[email protected]
local]# ll total 76 drwxr-xr-x 2 mysql 502 4096 Aug 25 11:39 bin
-rw-rw—- 1 mysql root 432 Oct 17 09:20 error.log drwxr-xr-x 2 mysql
502 4096 Aug 8 2008 etc drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 games
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 include drwxr-xr-x 2 mysql 502
4096 Aug 8 2008 lib drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 libexec
lrwxrwxrwx 1 mysql mysql 38 Sep 24 00:12 mysql ->
/opt/mysql/mysql-5.5.39-linux2.6-i686/ drwxr-xr-x 2 mysql 502 4096 Aug 8
2008 sbin drwxr-xr-x 5 mysql 502 4096 Aug 25 11:39 share drwxr-xr-x 2
mysql 502 4096 Sep 23 20:34 src
[[email protected]
local]# mysqld_safe & [1] 2692
[[email protected]
local]# Starting mysqld daemon with databases from
/data/mysql/mysql_3306/data
[[email protected]
local]# mysql ERROR 2002 (HY000): Can’t connect to local MySQL server
through socket ‘/tmp/mysql.sock’ (2)
报了一个无法通过socket连接到服务器,于是想是不是应该在client项目中添加socket=/tmp/mysql.sock这个参数
–先查看下原来有的配置
[[email protected]
local]# cat /etc/my.cnf [client] port = 3306 socket =
/tmp/mysql.sock
[mysqld_safe] open_files_limit = 8192 user = mysql log-error =
error.log
…省略以下部分
看来client总已经指定了socket,应该不是这个问题,要判断问题出在哪里,最直接都就是查看error.log文件,由于我在my.cnf参数中指定了error.log的位置,放在了数据文件目录下,并更改了名字。如果没有改,默认的名字为<host_name>.error
[[email protected]
local]# cat /data/mysql/mysql_3306/data/error.log 141017 9:24:53
[Warning] Using unique option prefix myisam_recover instead of
myisam-recover-options is deprecated and will be removed in a future
release. Please use the full name instead.
141017 9:24:53 [Note] Plugin ‘FEDERATED’ is disabled.
/usr/libexec/mysqld: Table ‘mysql.plugin’ doesn’t exist 141017 9:24:53
[ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade
to create it. 141017 9:24:53 InnoDB: The InnoDB memory heap is disabled
141017 9:24:53 InnoDB: Mutexes and rw_locks use InnoDB’s own
implementation 141017 9:24:53 InnoDB: Compressed tables use zlib 1.2.3
141017 9:24:53 InnoDB: Using Linux native AIO 141017 9:24:53 InnoDB:
Initializing buffer pool, size = 128.0M 141017 9:24:53 InnoDB: Completed
initialization of buffer pool InnoDB: The first specified data file
./ibdata1 did not exist: InnoDB: a new database to be created! 141017
9:24:53 InnoDB: Setting file ./ibdata1 size to 100 MB InnoDB: Database
physically writes the file full: wait… InnoDB: Progress in MB: 100
141017 9:24:58 InnoDB: Log file ./ib_logfile0 did not exist: new to be
created InnoDB: Setting log file ./ib_logfile0 size to 100 MB InnoDB:
Database physically writes the file full: wait… InnoDB: Progress in
MB: 100 141017 9:25:03 InnoDB: Log file ./ib_logfile1 did not exist:
new to be created InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Database physically writes the file full: wait… InnoDB:
Progress in MB: 100 141017 9:25:08 InnoDB: Log file ./ib_logfile2 did
not exist: new to be created InnoDB: Setting log file ./ib_logfile2
size to 100 MB InnoDB: Database physically writes the file full: wait…
InnoDB: Progress in MB: 100 InnoDB: Doublewrite buffer not found:
creating new InnoDB: Doublewrite buffer created InnoDB: 127 rollback
segment(s) active. InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created 141017 9:25:13
InnoDB: Waiting for the background threads to start 141017 9:25:14
InnoDB: 5.5.39 started; log sequence number 0 141017 9:25:14 [Note]
Server hostname (bind-address): ‘0.0.0.0’; port: 3306 141017 9:25:14
[Note] – ‘0.0.0.0’ resolves to ‘0.0.0.0’; 141017 9:25:14 [Note]
Server socket created on IP: ‘0.0.0.0’. 141017 9:25:14 [ERROR] Fatal
error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t
exist 141017 9:26:15 [Warning] Using unique option prefix
myisam_recover instead of myisam-recover-options is deprecated and will
be removed in a future release. Please use the full name instead. 141017
9:26:15 [Note] Plugin ‘FEDERATED’ is disabled. /usr/libexec/mysqld:
Table ‘mysql.plugin’ doesn’t exist 141017 9:26:15 [ERROR] Can’t open
the mysql.plugin table. Please run mysql_upgrade to create it. 141017
9:26:15 InnoDB: The InnoDB memory heap is disabled 141017 9:26:15
InnoDB: Mutexes and rw_locks use InnoDB’s own implementation 141017
9:26:15 InnoDB: Compressed tables use zlib 1.2.3 141017 9:26:15 InnoDB:
Using Linux native AIO 141017 9:26:15 InnoDB: Initializing buffer pool,
size = 128.0M 141017 9:26:15 InnoDB: Completed initialization of buffer
pool 141017 9:26:15 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 48941 141017 9:26:15
InnoDB: Database was not shut down normally! InnoDB: Starting crash
recovery. InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer… InnoDB: Doing recovery: scanned up to log sequence
number 1595668 141017 9:26:15 InnoDB: Starting an apply batch of log
records to the database… InnoDB: Progress in percents: 17 18 19 20 21
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93
94 95 96 97 98 99 InnoDB: Apply batch completed 141017 9:26:15 InnoDB:
Waiting for the background threads to start 141017 9:26:16 InnoDB:
5.5.39 started; log sequence number 1595668 141017 9:26:16 [Note]
Recovering after a crash using /data/mysql/mysql_3306/logs/mysql-bin
141017 9:26:16 [Note] Starting crash recovery… 141017 9:26:16
[Note] Crash recovery finished. 141017 9:26:17 [Note] Server
hostname (bind-address): ‘0.0.0.0’; port: 3306 141017 9:26:17 [Note] –
‘0.0.0.0’ resolves to ‘0.0.0.0’; 141017 9:26:17 [Note] Server socket
created on IP: ‘0.0.0.0’. 141017 9:26:17 [ERROR] Fatal error: Can’t
open and lock privilege tables: Table ‘mysql.host’ doesn’t exist
日志中报错提示无法打开mysql.plugin表,也不存在mysql.host这张表,由于是测试环境,没有什么数据,于是决定重新初始化MySQL
[[email protected]
local]# cd mysql
[[email protected]
mysql]# ./scripts/mysql_install_db Installing MySQL system tables…
141017 9:28:24 [Warning] options –log-slow-admin-statements,
–log-queries-not-using-indexes and –log-slow-slave-statements have no
effect if –log_slow_queries is not set OK Filling help tables…
141017 9:28:25 [Warning] options –log-slow-admin-statements,
–log-queries-not-using-indexes and –log-slow-slave-statements have no
effect if –log_slow_queries is not set OK
To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so,
start the server, then issue the following commands:
/usr/local/mysql/bin/mysqladmin -u root password ‘new-password’
/usr/local/mysql/bin/mysqladmin -u root -h bak password ‘new-password’
Alternatively you can run:
/usr/local/mysql/bin/mysql_secure_installation
which will also give you the option of removing the test databases and
anonymous user created by default. This is strongly recommended for
production servers.
See the manual for more instructions.
You can start the MySQL daemon with: cd /usr/local/mysql ;
/usr/local/mysql/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl cd
/usr/local/mysql/mysql-test ; perl mysql-test-run.pl
Please report any problems at
[[email protected]
mysql]# mysqld_safe & [1] 2858
[[email protected]
mysql]# Starting mysqld daemon with databases from
/data/mysql/mysql_3306/data
[[email protected]
mysql]# mysqSTOPPING server from pid file
/data/mysql/mysql_3306/data/bak.pid l141017 09:28:36 mysqld ended

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket
‘/tmp/mysql.sock’ (2) [1]+ Done mysqld_safe
初始化时成功的,可以看到有2个OK,不过启动依然报错,再次查看error.log
[[email protected]
mysql]# cat /data/mysql/mysql_3306/data/error.log 141017 9:24:53
[Warning] Using unique option prefix myisam_recover instead of
myisam-recover-options is deprecated and will be removed in a future
release. Please use the full name instead. 141017 9:24:53 [Note]
Plugin ‘FEDERATED’ is disabled. /usr/libexec/mysqld: Table
‘mysql.plugin’ doesn’t exist 141017 9:24:53 [ERROR] Can’t open the
mysql.plugin table. Please run mysql_upgrade to create it. 141017
9:24:53 InnoDB: The InnoDB memory heap is disabled 141017 9:24:53
InnoDB: Mutexes and rw_locks use InnoDB’s own implementation 141017
9:24:53 InnoDB: Compressed tables use zlib 1.2.3 141017 9:24:53 InnoDB:
Using Linux native AIO 141017 9:24:53 InnoDB: Initializing buffer pool,
size = 128.0M 141017 9:24:53 InnoDB: Completed initialization of buffer
pool InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created! 141017 9:24:53 InnoDB: Setting
file ./ibdata1 size to 100 MB InnoDB: Database physically writes the
file full: wait… InnoDB: Progress in MB: 100 141017 9:24:58 InnoDB:
Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting
log file ./ib_logfile0 size to 100 MB InnoDB: Database physically
writes the file full: wait… InnoDB: Progress in MB: 100 141017 9:25:03
InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB:
Setting log file ./ib_logfile1 size to 100 MB InnoDB: Database
physically writes the file full: wait… InnoDB: Progress in MB: 100
141017 9:25:08 InnoDB: Log file ./ib_logfile2 did not exist: new to be
created InnoDB: Setting log file ./ib_logfile2 size to 100 MB InnoDB:
Database physically writes the file full: wait… InnoDB: Progress in
MB: 100 InnoDB: Doublewrite buffer not found: creating new InnoDB:
Doublewrite buffer created InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables InnoDB: Foreign
key constraint system tables created 141017 9:25:13 InnoDB: Waiting for
the background threads to start 141017 9:25:14 InnoDB: 5.5.39 started;
log sequence number 0 141017 9:25:14 [Note] Server hostname
(bind-address): ‘0.0.0.0’; port: 3306 141017 9:25:14 [Note] –
‘0.0.0.0’ resolves to ‘0.0.0.0’; 141017 9:25:14 [Note] Server socket
created on IP: ‘0.0.0.0’. 141017 9:25:14 [ERROR] Fatal error: Can’t
open and lock privilege tables: Table ‘mysql.host’ doesn’t exist 141017
9:26:15 [Warning] Using unique option prefix myisam_recover instead
of myisam-recover-options is deprecated and will be removed in a future
release. Please use the full name instead. 141017 9:26:15 [Note]
Plugin ‘FEDERATED’ is disabled. /usr/libexec/mysqld: Table
‘mysql.plugin’ doesn’t exist 141017 9:26:15 [ERROR] Can’t open the
mysql.plugin table. Please run mysql_upgrade to create it. 141017
9:26:15 InnoDB: The InnoDB memory heap is disabled 141017 9:26:15
InnoDB: Mutexes and rw_locks use InnoDB’s own implementation 141017
9:26:15 InnoDB: Compressed tables use zlib 1.2.3 141017 9:26:15 InnoDB:
Using Linux native AIO 141017 9:26:15 InnoDB: Initializing buffer pool,
size = 128.0M 141017 9:26:15 InnoDB: Completed initialization of buffer
pool 141017 9:26:15 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 48941 141017 9:26:15
InnoDB: Database was not shut down normally! InnoDB: Starting crash
recovery. InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer… InnoDB: Doing recovery: scanned up to log sequence
number 1595668 141017 9:26:15 InnoDB: Starting an apply batch of log
records to the database… InnoDB: Progress in percents: 17 18 19 20 21
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93
94 95 96 97 98 99 InnoDB: Apply batch completed 141017 9:26:15 InnoDB:
Waiting for the background threads to start 141017 9:26:16 InnoDB:
5.5.39 started; log sequence number 1595668 141017 9:26:16 [Note]
Recovering after a crash using /data/mysql/mysql_3306/logs/mysql-bin
141017 9:26:16 [Note] Starting crash recovery… 141017 9:26:16
[Note] Crash recovery finished. 141017 9:26:17 [Note] Server
hostname (bind-address): ‘0.0.0.0’; port: 3306 141017 9:26:17 [Note] –
‘0.0.0.0’ resolves to ‘0.0.0.0’; 141017 9:26:17 [Note] Server socket
created on IP: ‘0.0.0.0’. 141017 9:26:17 [ERROR] Fatal error: Can’t
open and lock privilege tables: Table ‘mysql.host’ doesn’t exist 141017
9:28:34 [Warning] Using unique option prefix myisam_recover instead
of myisam-recover-options is deprecated and will be removed in a future
release. Please use the full name instead. 141017 9:28:34 [Note]
Plugin ‘FEDERATED’ is disabled. /usr/libexec/mysqld: Can’t find file:
‘./mysql/plugin.frm’ (errno: 13) 141017 9:28:34 [ERROR] Can’t open the
mysql.plugin table. Please run mysql_upgrade to create it. 141017
9:28:34 InnoDB: The InnoDB memory heap is disabled 141017 9:28:34
InnoDB: Mutexes and rw_locks use InnoDB’s own implementation 141017
9:28:34 InnoDB: Compressed tables use zlib 1.2.3 141017 9:28:34 InnoDB:
Using Linux native AIO 141017 9:28:34 InnoDB: Initializing buffer pool,
size = 128.0M 141017 9:28:34 InnoDB: Completed initialization of buffer
pool 141017 9:28:34 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match InnoDB:
the log sequence number in the ib_logfiles! 141017 9:28:34 InnoDB:
Database was not shut down normally! InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files… InnoDB:
Restoring possible half-written data pages from the doublewrite InnoDB:
buffer… 141017 9:28:34 InnoDB: Waiting for the background threads to
start 141017 9:28:35 InnoDB: 5.5.39 started; log sequence number 1595668
/usr/libexec/mysqld: File
‘/data/mysql/mysql_3306/logs/mysql-bin.000004’ not found (Errcode: 13)
141017 9:28:35 [ERROR] Failed to open log (file
‘/data/mysql/mysql_3306/logs/mysql-bin.000004’, errno 13) 141017
9:28:35 [ERROR] Could not open log file 141017 9:28:35 [ERROR] Can’t
init tc log 141017 9:28:35 [ERROR] Aborting
141017 9:28:35 InnoDB: Starting shutdown… 141017 9:28:36 InnoDB:
Shutdown completed; log sequence number 1595668 141017 9:28:36 [Note]
/usr/libexec/mysqld: Shutdown complete
141017 9:30:32 [Warning] Using unique option prefix myisam_recover
instead of myisam-recover-options is deprecated and will be removed in a
future release. Please use the full name instead. 141017 9:30:32
[Note] Plugin ‘FEDERATED’ is disabled. /usr/libexec/mysqld: Can’t find
file: ‘./mysql/plugin.frm’ (errno: 13) 141017 9:30:32 [ERROR] Can’t
open the mysql.plugin table. Please run mysql_upgrade to create it.
141017 9:30:32 InnoDB: The InnoDB memory heap is disabled 141017 9:30:32
InnoDB: Mutexes and rw_locks use InnoDB’s own implementation 141017
9:30:32 InnoDB: Compressed tables use zlib 1.2.3 141017 9:30:32 InnoDB:
Using Linux native AIO 141017 9:30:32 InnoDB: Initializing buffer pool,
size = 128.0M 141017 9:30:32 InnoDB: Completed initialization of buffer
pool 141017 9:30:32 InnoDB: highest supported file format is Barracuda.
141017 9:30:32 InnoDB: Waiting for the background threads to start
141017 9:30:33 InnoDB: 5.5.39 started; log sequence number 1595668
/usr/libexec/mysqld: File
‘/data/mysql/mysql_3306/logs/mysql-bin.000004’ not found (Errcode: 13)
141017 9:30:33 [ERROR] Failed to open log (file
‘/data/mysql/mysql_3306/logs/mysql-bin.000004’, errno 13) 141017
9:30:33 [ERROR] Could not open log file 141017 9:30:33 [ERROR] Can’t
init tc log 141017 9:30:33 [ERROR] Aborting
141017 9:30:33 InnoDB: Starting shutdown… 141017 9:30:34 InnoDB:
Shutdown completed; log sequence number 1595668 141017 9:30:34 [Note]
/usr/libexec/mysqld: Shutdown complete
现在error.log里很明显地指出errno为13,我们知道13就是权限问题,这个可以perror命令查看
[[email protected]
mysql]# perror 13 OS error code 13: Permission denied
位置可以通过which来定位
[[email protected]
mysql]# which perror
/usr/bin/perror
或者用find定位也是可以的
[[email protected]
mysql]# find / -name perror -print
/opt/mysql/mysql-5.5.39-linux2.6-i686/bin/perror
/usr/bin/perror

既然找到了无法启动的真正原因,那么对症下药,把权限问题解决,问题就可以搞定了
[[email protected]
mysql]# cd /data/mysql/mysql_3306/data
[[email protected]
data]# ll total 410036 -rw-rw—- 1 mysql mysql 7950 Oct 17 09:30
error.log -rw-rw—- 1 mysql mysql 104857600 Oct 17 09:30 ibdata1
-rw-rw—- 1 mysql mysql 104857600 Oct 17 09:30 ib_logfile0 -rw-rw—-
1 mysql mysql 104857600 Oct 17 09:25 ib_logfile1 -rw-rw—- 1 mysql
mysql 104857600 Oct 17 09:25 ib_logfile2 drwx—— 2 root root 4096
Oct 17 09:28 mysql drwx—— 2 root root 4096 Oct 17 09:28
performance_schema drwx—— 2 root root 4096 Oct 17 09:28 test
[[email protected]
data]# chown -R mysql:mysql ./
[[email protected]
data]# ll total 410036 -rw-rw—- 1 mysql mysql 7950 Oct 17 09:30
error.log -rw-rw—- 1 mysql mysql 104857600 Oct 17 09:30 ibdata1
-rw-rw—- 1 mysql mysql 104857600 Oct 17 09:30 ib_logfile0 -rw-rw—-
1 mysql mysql 104857600 Oct 17 09:25 ib_logfile1 -rw-rw—- 1 mysql
mysql 104857600 Oct 17 09:25 ib_logfile2 drwx—— 2 mysql mysql 4096
Oct 17 09:28 mysql drwx—— 2 mysql mysql 4096 Oct 17 09:28
performance_schema drwx—— 2 mysql mysql 4096 Oct 17 09:28 test
开始还去调整了/data/mysql/mysql_3306/data目录下面几个数据库的权限,但发现依然无法启动,看来不是这几个目录的权限问题

[[email protected]
data]# mysqld_safe & [1] 2988
[[email protected]
data]# Starting mysqld daemon with databases from
/data/mysql/mysql_3306/data
[[email protected]
data]# mysqlSTOPPING server from pid file
/data/mysql/mysql_3306/data/bak.pid 141017 09:32:08 mysqld ended

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket
‘/tmp/mysql.sock’ (2) [1]+ Done mysqld_safe
error.log日志内容和之前一样,仍然报errno
13,就不贴了。针对之前给出的error.log日志里提示的对mysql-bin.000004读写权限的问题做一下处理
[[email protected]
mysql]# cd /data/mysql/mysql_3306/logs
[[email protected]
logs]# ll total 1092 -rw-rw—- 1 mysql mysql 107 Oct 17 09:25
mysql-bin.000001 -rw-rw—- 1 mysql mysql 107 Oct 17 09:26
mysql-bin.000002 -rw-rw—- 1 root root 27681 Oct 17 09:28
mysql-bin.000003 -rw-rw—- 1 root root 1070549 Oct 17 09:28
mysql-bin.000004 -rw-rw—- 1 mysql mysql 180 Oct 17 09:28
mysql-bin.index
[[email protected]
logs]# chown -R mysql:mysql ./
[[email protected]
logs]# ll total 1092 -rw-rw—- 1 mysql mysql 107 Oct 17 09:25
mysql-bin.000001 -rw-rw—- 1 mysql mysql 107 Oct 17 09:26
mysql-bin.000002 -rw-rw—- 1 mysql mysql 27681 Oct 17 09:28
mysql-bin.000003 -rw-rw—- 1 mysql mysql 1070549 Oct 17 09:28
mysql-bin.000004 -rw-rw—- 1 mysql mysql 180 Oct 17 09:28
mysql-bin.index
原来是root root权限,改为mysql mysql了,再次启动MySQL进程
[[email protected]
logs]# ps -ef|grep mysql root 3060 2591 0 09:35 pts/0 00:00:00 grep
mysql
[[email protected]
logs]# mysqld_safe & [1] 3061
[[email protected]
logs]# Starting mysqld daemon with databases from
/data/mysql/mysql_3306/data
[[email protected]
logs]# mysql Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 Server version: 5.5.39-log MySQL Community
Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights
reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input
statement.
(testing)[email protected]
[(none)]> exit
[[email protected]
logs]# ps aux | grep -v grep | grep mysql
root 3061 0.0 0.1 4484 1152 pts/0 S 09:35 0:00 /bin/sh
/usr/bin/mysqld_safe mysql 3095 0.0 3.1 330320 32216 pts/0 Sl 09:35
0:02 /usr/libexec/mysqld –basedir=/usr/local/mysql
–datadir=/data/mysql/mysql_3306/data –user=mysql
–pid-file=/data/mysql/mysql_3306/data/bak.pid –skip-external-locking
–port=3306 –socket=/tmp/mysql.sock
这次,数据库顺利启动了,从mysql进程中可以看到,mysqld_safe实际去调用的还是mysqld

总结:
当我们遭遇数据库无法启动,如:自动终止进程stopping from pid
file或是无法通过socket连接到服务器等错误,首先要去查看数据库的error.log日志,其中有详细的提示(各种ERROR和WARNING),来给你提供无法启动的线索,我们要做的就是更具这些线索,来做相应的处理,才能更快、更好地解决问题。

之前在系统上装了个MySQL用于测试,今天发现启动mysqld_safe进程的时候报错,如下所示:
[[email protected]
local]# mysql…

作者 tianlesoftware
 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图