首页 > 代码库 > MySQL学习之路：多实例无法启动排错
MySQL学习之路：多实例无法启动排错
由于我们的服务器会借给我们部分公司使用，但是部分公司有没有运维，都是开发直接操作，导致出现一些问题后直接来找我处理。为此，也学习到很多。
近来，一哥们由于不知道操作什么导致服务器直接挂掉，于是我联系机房启动机器后查看日志，没有发现什么明显的日志，只是有一条watchdog的日志信息。于是又多方搜寻关于watchdog的相关知识。发现的也大致是皮毛而已，也有部分提交一些内核信息是关于watchdog的bug。此处也无法做什么排查的(主要还是我的技术不精)。只能推测是开发使用应用程序触发了watchdog的监控。在指定时间内watchdog检测不通过导致reboot或者shutdown。好在这台机器他们只是测试使用。没有什么重要的业务。
不过不久后他们又呼叫我mysql多实例的3308端口无法启动。于是我又上机器排查。
登陆机器后首先查看下端口：
# ss -tunl Netid Recv-Q Send-Q Local Address:Port Peer Address:Port tcp 0 64 :::873 :::* tcp 0 50 *:3306 *:* tcp 0 128 :::11211 :::* tcp 0 128 *:11211 *:* tcp 0 50 *:3307 *:*
查看上述确实是没有3308端口，于是又查看下配置文件。看看他们的具体配置是怎么样的。
# cat /etc/my.cnf [client] #port = 3306 default-character-set = utf8 #socket = /usr/local/mysql/mysql.sock [mysqld_multi] mysqld = /data0/mysql/bin/mysqld_safe mysqladmin = /data0/mysql/bin/mysqladmin [mysqld1] socket = /usr/local/mysql/mysql1.sock port = 3306 pid-file = /usr/local/mysql/mysql1.pid datadir = /usr/local/mysql/data1 user = mysql log = /usr/local/msyql/e1.log server-id = 1 skip-name-resolve character-set-server = utf8 log-bin-trust-function-creators=1 back_log = 50 max_connections = 500 max_connect_errors = 32 max_allowed_packet = 16M table_cache = 2048 binlog_cache_size = 1M max_heap_table_size = 64M tmp_table_size = 64M #binlog_format = "MIXED" key_buffer_size = 32M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 8 thread_concurrency = 8 thread_stack = 192K slow_query_log long_query_time = 2 log-short-format myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover [mysqld2] socket = /usr/local/mysql/mysql2.sock port = 3308 pid-file = /usr/local/mysql/mysql2.pid datadir = /usr/local/mysql/data2 user = mysql log = /usr/local/msyql/e2.log server-id = 1 skip-name-resolve character-set-server = utf8 log-bin-trust-function-creators=1 back_log = 50 max_connections = 500 max_connect_errors = 32 max_allowed_packet = 16M table_cache = 2048 binlog_cache_size = 1M max_heap_table_size = 64M tmp_table_size = 64M #binlog_format = "MIXED" key_buffer_size = 32M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 8 thread_concurrency = 8 thread_stack = 192K slow_query_log long_query_time = 2 log-short-format myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192
配置文件如上，主要是看下数据目录和sock文件目录以及pid存放位置和log位置。
然后就是到指定目录下，查看相关信息。
# ls 3307 data1 docs e2.log lib mysql2-slow.log mysql-test sql-bench bin data2 e1.log include libexec mysql1-slow.log mysql2.pid mysql2.sock share var
看到上述情况我真是很郁闷了，这完全和配置文件对不上啊。明明mysql2.sock和pid文件存在，为毛却监听的是3306端口呢？此时就很郁闷了，于是又ps -ef | grep mysql看了下路径。不看不知道，看了下就更不对劲了。
# ps -ef | grep mysql root 18634 1 0 Jan12 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/localhost.localdomain.pid mysql 18763 18634 0 Jan12 ? 00:00:39 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/var/localhost.localdomain.err --pid-file=/usr/local/mysql/var/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306 root 29805 29780 0 14:40 pts/0 00:00:00 grep mysql
想必此时很多大神一眼就知道问题在哪里了。可惜我不是大神。一时也没想到那么多。于是一步一步的来往下查啊。我这里想到的是TM的居然启动了默认的mysql。于是我就停掉了这个3306这个端口。
删除了mysql2.sock和pid文件。再启动一次：
mysqld_multi start 1,2
可是事与愿违，3306端口还是起来了。但是3308还是没起来。于是再次ps -ef | grep mysql查看到mysqld_safe的进程起了两个，反正相同的都是两个。你懂得。这么奇葩。于是我再次kill掉。
再次启动时我先监控着多实例的日志：
tail -f mysqld_multi.log
mysqld_multi log file version 2.16; run: Fri Jan 16 13:53:44 2015 Starting MySQL servers 150116 13:53:44 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root! 150116 13:53:44 [ERROR] Aborting 150116 13:53:44 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete 150116 13:53:44 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root! 150116 13:53:44 [ERROR] Aborting 150116 13:53:44 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete mysqld_multi log file version 2.16; run: Fri Jan 16 13:56:32 2015
就类似上面这种。报错信息大致反映的就是不能以root启动这个意思。但是mysql的配置文件里指定的就是mysql用户。于是我又查看了数据目录的文件夹权限等乱七八糟的一堆。感觉都是很正常的。到这里时我就特别的郁闷了。那到底是哪里的问题呢。
于是我就去找错误日志，但是发现var下面的.err的日志居然没有记录。我思前想后难道是配置文件没有生效？此时的我只能先发会呆。
发完呆后我又仔细仔细看了看日志和配置文件。终于发现问题了。mysqld_multi的配置和mysqld下面的路径不一致啊。我就问了下开发是不是他们又装了一个mysql。果不其然，他们大方说原先的版本太低。于是就升级了一个版本。此时的我就明白一大半了。
于是再次的使用他们指定目录下面的启动脚本，并加以选项。这次虽然还是没能出现所要的结果。但是日志已经很明显的指出问题所在了。
# tail -f mysqld_multi.log mysqld_multi log file version 2.16; run: Fri Jan 16 14:10:30 2015 Starting MySQL servers 150116 14:10:30 mysqld_safe Logging to ‘/usr/local/mysql/var/localhost.localdomain.err‘. 150116 14:10:30 mysqld_safe Logging to ‘/usr/local/mysql/var/localhost.localdomain.err‘. cat: /usr/local/mysql/var/localhost.localdomain.pid: No such file or directory 150116 14:10:30 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var 150116 14:10:30 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var mysqld_multi log file version 2.16; run: Fri Jan 16 14:15:39 2015
于是配置一个mysql的环境变量，效果立马见效。
Starting MySQL servers 150116 14:15:39 mysqld_safe Logging to ‘/usr/local/mysql/data1/localhost.localdomain.err‘. 150116 14:15:39 mysqld_safe Logging to ‘/usr/local/mysql/data2/localhost.localdomain.err‘. 150116 14:15:39 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data1 150116 14:15:39 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data2
到此终于解决问题。此事说明解决问题前一定要先弄清楚之前做了什么操作，具体的环境是什么样子的。等等...
ps :有大神知道下面这个问题还请指教，感谢。
localhost kernel: iTCO_wdt: Unexpected close, not stopping watchdog!
本文出自 “Soul” 博客，请务必保留此出处http://chenpipi.blog.51cto.com/8563610/1604793
MySQL学习之路：多实例无法启动排错