MySQL Learning Path: Multiple instances fail to start troubleshooting

Since our server will be lent to some of our companies to use, but some companies do not have operations, are developing direct operations, resulting in a number of problems directly to me to deal with. To this end, also learn a lot.

Recently, a friend because do not know the operation of what caused the server to hang up, so I contacted the room to start the machine after viewing the log, did not find any obvious log, just have a watchdog log information. So they searched for the relevant knowledge about watchdog. The discovery is also roughly fur, and some of the kernel information is about watchdog bugs. There is no way to do any troubleshooting here (mostly my technology is not fine). It can only be inferred that the development uses the application to trigger watchdog monitoring. Watchdog detection does not pass through the resulting reboot or shutdown within a specified time. Fortunately for this machine they are only tested for use. There is nothing important in the business.

But soon they called me again. MySQL multi-instance port 3308 does not start. So I went on the machine again.

After landing the machine, first check the lower port:

# ss -tunlnetid 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                                                             *: *

View the above does not have 3308 ports, and then look at the next configuration file. Look at how they are configured.

# cat /etc/my.cnf[client] #port = 3306default-character-set = utf8#socket= /usr/ local/mysql/mysql.sock[mysqld_multi]mysqld = /data0/mysql/bin/mysqld_safemysqladmin = / data0/mysql/bin/mysqladmin[mysqld1]socket          = / usr/local/mysql/mysql1.sockport            =  3306pid-file        = /usr/local/mysql/mysql1.piddatadir          = /usr/local/mysql/data1user             = mysqllog              = /usr/local/msyql/e1.logserver-id=  1skip-name-resolvecharacter-set-server= utf8log-bin-trust-function-creators=1back_log = 50max_ connections = 500max_connect_errors = 32max_allowed_packet = 16mtable_cache = 2048binlog_cache_size = 1mmax_heap_table _size = 64mtmp_table_size = 64m#binlog_format=  "MIXED" key_buffer_size =  32mread_buffer_size = 2mread_rnd_buffer_size = 16mbulk_insert_buffer_size =  64msort_buffer_size = 8mjoin_buffer_size = 8mthread_cache_size = 8thread_ concurrency = 8thread_stack = 192kslow_query_loglong_query_time=  2log-short-formatmyisam_sort_buffer_size = 128mmyisam_max_sort_file_size = 10gmyisam_ repair_threads = 1myisam_recover[mysqld2]socket           = /usr/local/mysql/mysql2.sockport             = 3308pid-file        = /usr/local/mysql/ mysql2.piddatadir         = /usr/local/mysql/data2user             = mysqllog             = /usr /local/msyql/e2.logserver-id= 1skip-name-resolvecharacter-set-server=  Utf8log-bin-trust-function-creators=1back_log = 50max_connections = 500max_connect_errors  = 32max_allowed_packet = 16Mtable_cache = 2048binlog_cache_size =  1mmax_heap_table_size = 64mtmp_table_size = 64m#binlog_format=  "MIXED" key_buffer_size  = 32mread_buffer_size = 2mread_rnd_buffer_size = 16mbulk_insert_buffer_size  = 64Msort_buffer_size = 8Mjoin_buffer_size = 8Mthread_cache_size =  8thread_concurrency = 8thread_stack = 192kslow_query_loglong_query_time=  2log-short-formatmyisam_sort_buffer_size = 128mmyisaM_max_sort_file_size = 10gmyisam_repair_threads = 1myisam_recover[mysqldump]quickmax_ Allowed_packet = 16m[mysql]no-auto-rehash[isamchk]key_buffer  = 512msort_buffer_size  = 512Mread_buffer  = 8Mwrite_buffer  = 8M[myisamchk]key_buffer   = 512msort_buffer_size = 512mread_buffer  = 8mwrite_buffer  =  8m[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit = 8192

The configuration file, as above, mainly looks at the data directory and the sock file directory as well as the PID storage location and log location.

Then just go to the specified directory to see the relevant information.

# ls3307 data1 Docs e2.log lib Mysql2-slow.log mysql-test sql-benchbin data2 e1.log include libexec m Ysql1-slow.log mysql2.pid Mysql2.sock Share var

I am really depressed to see the above situation, this is completely and the configuration file is not AH. Clearly mysql2.sock and PID files exist, but for the hair is listening to the 3306 port it? At this time is very depressed, so again Ps-ef | grep MySQL looked down the path. If you don't see it, it's more wrong to look at it.

# ps -ef | grep mysqlroot     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.pidmysql    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=3306root      29805 29780  0 14:40 pts/0    00:00:00 grep mysql

It must have been a time when many great gods knew where the problem was. But I am not a great God. I didn't think of that much at all. So step-by-step to check AH. What I think about here is that the TM actually started the default MySQL. So I stopped this 3306 port.

Removed the Mysql2.sock and PID files. To start again:

Mysqld_multi start

But it backfired, 3306 Port still got up. But 3308 still didn't get up. So again Ps-ef | grep MySQL check see Mysqld_safe process up two, anyway the same is two. You know. So wonderful. So I kill again.

When I start again, I monitor the log of multiple instances:

Tail-f Mysqld_multi.log

Mysqld_multi log file version 2.16; run: fri jan 16 13:53:44  2015starting mysql servers150116 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] aborting150116 13:53:44  [note] /usr/local/mysql/libexec/mysqld: shutdown complete150116 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 ] aborting150116 13:53:44 [note] /usr/local/mysql/libexec/mysqld: shutdown  completemysqld_multi log file version 2.16; run: fri jan 16  13:56:32 2015

Just like the above. The error message roughly reflects the idea that you cannot start with root. But MySQL user is the one specified in the configuration file. So I looked at the folder permissions of the data directory and other messy piles. The feeling is normal. I was particularly depressed when I got here. So what is the problem?

So I went to the error log, but found that the. Err log under Var is actually not recorded. I after thinking is the configuration file not in effect? At this time I can only send a meeting to stay first.

After I finished my stay, I looked carefully at the logs and configuration files. Finally found the problem. Mysqld_multi Configuration and mysqld The following paths are inconsistent. I asked about the development is not they installed another MySQL. Sure enough, they generously said the original version was too low. A version is then upgraded. At this time I will understand the more than half.

Then use them again to specify the startup script below the directory and make the options. This time, though, it was not possible to make the desired result. But the log has clearly pointed to the problem.

# tail-f mysqld_multi.logmysqld_multi log file version 2.16; Run:fri Jan 14:10:30 2015Starting MySQL servers150116 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 directory150116 14:10:30 Mysqld_safe starting mysqld daemon WI th databases from/usr/local/mysql/var150116 14:10:30 mysqld_safe starting mysqld daemon with databases from/usr/local/my Sql/varmysqld_multi log file version 2.16; RUN:FRI Jan 16 14:15:39 2015

Then configure a MySQL environment variable, the effect immediately.

Starting MySQL servers150116 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/data1150116 14:15:39 Mysqld_safe starting mysqld daemon with D Atabases From/usr/local/mysql/data2

This finally solves the problem. It is important to figure out what to do before the problem is solved, and what the specific environment looks like. Wait a minute...

PS: have great God know the following question also please advise, thanks.

localhost kernel:iTCO_wdt:Unexpected close, not stopping watchdog!

This article is from the "Soul" blog, make sure to keep this source http://chenpipi.blog.51cto.com/8563610/1604793

MySQL Learning Path: Multiple instances fail to start troubleshooting