【trouble shooting】 启动mysqld服务失败
[root@localhost mysql]# bin/mysqld_safe --user=mysql &
[2] 3960
[root@localhost mysql]# 151223 14:09:03 mysqld_safe Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
151223 14:09:03 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
151223 14:09:06 mysqld_safe mysqld from pid file /usr/local/mysql/data/localhost.localdomain.pid ended
使用mysqld_safe启动服务时出现把结果记录到localhost.localdomain.errc 错误日志了，这表示有error出现了
[root@localhost mysql]# cat /usr/local/mysql/data/localhost.localdomain.err
151223 14:09:03 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
151223 14:09:03 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.47) starting as process 4038 ...
151223 14:09:03 [Note] Plugin 'FEDERATED' is disabled.
151223 14:09:03 InnoDB: The InnoDB memory heap is disabled
151223 14:09:03 InnoDB: Mutexes and rw_locks use GCC atomic builtins
151223 14:09:03 InnoDB: Compressed tables use zlib 1.2.3
151223 14:09:03 InnoDB: Using Linux native AIO
151223 14:09:03 InnoDB: Initializing buffer pool, size = 128.0M
151223 14:09:03 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
151223 14:09:03 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
151223 14:09:03 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
151223 14:09:03 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
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
151223 14:09:04 InnoDB: Waiting for the background threads to start
151223 14:09:05 InnoDB: 5.5.47 started; log sequence number 0
151223 14:09:05 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
151223 14:09:05 [Note] - '0.0.0.0' resolves to '0.0.0.0';
151223 14:09:05 [Note] Server socket created on IP: '0.0.0.0'.
151223 14:09:05 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
151223 14:09:05 [ERROR] Do you already have another mysqld server running on port: 3306 ?
151223 14:09:05 [ERROR] Aborting
151223 14:09:05 InnoDB: Starting shutdown...
151223 14:09:06 InnoDB: Shutdown completed; log sequence number 1595675
151223 14:09:06 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
151223 14:09:06 mysqld_safe mysqld from pid file /usr/local/mysql/data/localhost.localdomain.pid ended
提示有一个同样叫3306的端口正在被使用，那就查询端口检查一下
[root@localhost mysql]# ps -ef|grep 3306
root 4082 3440 0 14:27 pts/1 00:00:00 grep --color=auto 3306
[root@localhost mysql]# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3115/mysqld
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1243/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1703/master
tcp6 0 0 :::22 :::* LISTEN 1243/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1703/master
原因：我“mysql_install_db初始化数据库”后，我使用过 “bin/mysqld_safe --user=mysql &” 来启动myqld服务器，后来我把“数据目录datadir”删除后，又重新“初始化”了好几次，然后datadir的删除，并不会让mysqld服务自动关闭，这也是为什么，我们在卸载MySQL的时候，需要手工把"mysqld服务"关闭，下面演示一次
[root@localhost mysql]# ls -l
total 204
drwxr-xr-x. 2 root mysql 4096 Dec 23 13:24 bin
-rw-r--r--. 1 root mysql 17987 Nov 9 22:58 COPYING
drwxr-xr-x. 5 mysql mysql 4096 Dec 23 14:09 data
drwxr-xr-x. 2 root mysql 52 Dec 23 13:24 docs
drwxr-xr-x. 3 root mysql 4096 Dec 23 13:24 include
-rw-r--r--. 1 root mysql 151464 Nov 9 23:10 INSTALL-BINARY
drwxr-xr-x. 3 root mysql 4096 Dec 23 13:24 lib
drwxr-xr-x. 4 root mysql 28 Dec 23 13:24 man
drwxr-xr-x. 10 root mysql 4096 Dec 23 13:24 mysql-test
-rw-r--r--. 1 root mysql 2496 Nov 9 22:58 README
drwxr-xr-x. 2 root mysql 29 Dec 23 13:24 scripts
drwxr-xr-x. 27 root mysql 4096 Dec 23 13:24 share
drwxr-xr-x. 4 root mysql 4096 Dec 23 13:24 sql-bench
drwxr-xr-x. 2 root mysql 4096 Dec 23 13:24 support-files
[root@localhost mysql]# rm -rf ./data
[root@localhost mysql]# netstat -ntlp|grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3115/mysqld
所以我们只要把3306的进程kill掉，就可以重新使用“mysqld服务器开启”功能了
[root@localhost mysql]# netstat -ntlp|grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3115/mysqld
[root@localhost mysql]# kill -9 3115
[root@localhost mysql]# netstat -ntlp|grep 3306
[root@localhost mysql]#
[root@localhost bin]# ./mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.47 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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.
mysql>
后来虚拟机经过一次关机开机后，再次启动mysqld服务，启动如下，没有出现报错信息
[root@localhost mysql]# 151224 9:02:00 [Note] ./bin/mysqld (mysqld 5.5.47) starting as process 2402 ...
151224 9:02:00 [Note] Plugin 'FEDERATED' is disabled.
151224 9:02:00 InnoDB: The InnoDB memory heap is disabled
151224 9:02:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins
151224 9:02:00 InnoDB: Compressed tables use zlib 1.2.3
151224 9:02:00 InnoDB: Using Linux native AIO
151224 9:02:00 InnoDB: Initializing buffer pool, size = 128.0M
151224 9:02:00 InnoDB: Completed initialization of buffer pool
151224 9:02:00 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!
151224 9:02:00 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...
151224 9:02:01 InnoDB: Waiting for the background threads to start
151224 9:02:02 InnoDB: 5.5.47 started; log sequence number 1595675
151224 9:02:02 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
151224 9:02:02 [Note] - '0.0.0.0' resolves to '0.0.0.0';
151224 9:02:02 [Note] Server socket created on IP: '0.0.0.0'.
151224 9:02:02 [Note] Event Scheduler: Loaded 0 events
151224 9:02:02 [Note] ./bin/mysqld: ready for connections.
Version: '5.5.47' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
问题列表
1. Plugin 'FEDERATED' is disabled. /InnoDB: The InnoDB memory heap is disable 是什么意思呢？
2. 为什么能成功启动“myqld服务器”，还会显示记录 localhost.localdomain.err ？
3. 这部分的机制是什么？
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
151223 14:09:03 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
151223 14:09:03 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
151223 14:09:03 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
1. Plugin 'FEDERATED' is disabled. /InnoDB: The InnoDB memory heap is disable 是什么意思呢？
151223 14:09:03 [Note] Plugin 'FEDERATED' is disabled.
Plugin 'FEDERATED' is disabled. 是一个Note提醒，就像QQ有3条未读的信息提醒一样，它并不是一个错误，它告诉你一个叫FEDERATED的插件不能使用，其实这个插件是MySQL存储引擎之一，它可以让数据保存在远程的数据库，而不保存在本地数据库，详细看这里 The FEDERATED Storage Engine
mysql> show engines\G *************************** 1. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 9. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL 9 rows in set (0.00 sec)
151223 14:09:03 InnoDB: The InnoDB memory heap is disabled
查询表时会把数据从硬盘复制到内存，那系统需要分配内存空间给这部分数据，查询完以后再把这部分内存释放掉，这需要用到“内存分配器memory allocators”，做的工作就是C语言的malloc and free，但是对于“multi-tables joins 多表链接”，操作系统本身的“内存分配器memory allocators”性能低下，所以innoDB引擎自己也新建了一个“内存分配器”，所以你可以选择使用系统自带的内存分配器，或者是innodb的内存分配器，因为现在操作系统的内存分配器做得比以前要好很多，所以MySQL默认关闭了自己的内存分配器，详细可以看这里： 1.mysql error The InnoDB memory heap is disabled 2.Configuring the Memory Allocator for InnoDB
3. 这部分的机制是什么？
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
151223 14:09:03 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
151223 14:09:03 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
151223 14:09:03 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
这部分知识涉及到InnoDB引擎的初始化，初始化的意思就是从0到1，从无到有，使用“mysql_install_db”初始化数据库，datadir就是从无到有了。奇怪的是，这部分的信息，在MySQL 5.5版本的document没有提及，反倒是在MySQL 5.0版本有提到.document建议，第一次启动mysqld服务器时，为了显示更多的信息，建议使用mysqld方式而不用mysqld_safe方式，这样就能看到InnoDB会在datadir中创建数据库(data files)和日志，我以前还以为“mysql_install_db”是创建数据库的步骤，其实数据库在第一次启动时才是真正的创建
[root@localhost data]# ls -l
total 28692
-rw-rw----. 1 mysql mysql 18874368 Dec 23 14:38 ibdata1
-rw-rw----. 1 mysql mysql 5242880 Dec 24 09:02 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 Dec 23 14:38 ib_logfile1
-rw-r-----. 1 mysql root 1989 Dec 23 14:38 localhost.localdomain.err
-rw-rw----. 1 mysql mysql 5 Dec 23 14:38 localhost.localdomain.pid
-rw-rw----. 1 mysql mysql 5 Dec 24 09:02 localhost.pid
drwx------. 2 mysql root 4096 Dec 23 14:36 mysql
drwx------. 2 mysql mysql 4096 Dec 23 14:36 performance_schema
drwx------. 2 mysql root 6 Dec 23 14:36 test
这里唯一要注意的地方是，”data directory数据目录“ 必须要事先存在，InnoDB只创建文件，但不创建目录的，同时InnoDB往“data directory数据目录” 放东西时，需要有足够的权限，比如彼此都是mysql用户权限,详细内容看 Initializing InnoDB
心得总结
1.对于自己来说遇到的是新问题，对于历史来说都是旧问题，你遇到的问题别人早已提供了解决方案，这是stack exchange网站存在的意思
2.以前我更多奢求软件能正常运行就Ok了，不希望遇到错误，这导致trouble shooting能力很差，而消除故障，替客户和开发消灾解难是dba的职责，而且解决一个错误所获得的知识远远比顺利安装要多，这好比一个顺风顺水和经历风雨的人的区别
