Source of problem

• The company's database product architecture is changing recently. After the tester down loads a physical machine of a distributed storage node, MySQL will be dispatched to other nodes by default. However, during the scheduling process, it was found that MySQL could not be started, so a screenshot was sent:
  

problem analysis

• The first feeling is that there are problems with MySQL's ibdata and redo, so google hasn't gained much in a round
• So there's the next step of recurrence

Problem repetition

①. Confirm that MySQL is running

[root@10-10-30-236 mysql]# ps -ef|grep mysqld
mysql     5000     1  0 Apr21 ?        00:07:53 /usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my.cnf
root     32537 29366  0 15:57 pts/0    00:00:00 grep --color=auto mysqld

② modify some parameters of my.cnf configuration file

• Original my.cnf parameter

port=3306
log-bin=/var/lib/mysql/archive/mysql-bin
log-error=/var/log/mysql/error.log

• Parameters after modification

port=3307
log-bin=/var/lib/mysql/archive1/mysql-bin
log-error=/var/log/mysql/error1.log

③ create directory and start MySQL

# cp -r /var/lib/mysql/archive /var/lib/mysql/archive1
# cp -r /var/lib/mysql/redo /var/lib/mysql/redo1
# chown -R mysql:mysql /var/lib/mysql
# /opt/mysql-5.7.17-linux-glibc2.5-x86_64/bin/mysqld_safe --defaults-file=/opt/mysql-5.7.17-linux-glibc2.5-x86_64/my.cnf &

④ check the error.log information

[root@10-10-30-236 mysql]# cat /var/log/mysql/error1.log
2019-04-23T07:41:46.298871Z 0 [Warning] The syntax '--metadata_locks_hash_instances' is deprecated and will be removed in a future release
2019-04-23T15:41:46.299104+08:00 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2019-04-23T15:41:46.299135+08:00 0 [Note] /opt/mysql-5.7.17-linux-glibc2.5-x86_64/bin/mysqld (mysqld 5.7.17-log) starting as process 32464 ...
2019-04-23T15:41:46.312984+08:00 0 [Warning] option 'innodb-buffer-pool-size': signed value 0 adjusted to 5242880
2019-04-23T15:41:46.313352+08:00 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
2019-04-23T15:41:46.313363+08:00 0 [Warning] InnoDB: Using innodb_file_format_max is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
2019-04-23T15:41:46.313409+08:00 0 [Note] InnoDB: PUNCH HOLE support not available
2019-04-23T15:41:46.313419+08:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-04-23T15:41:46.313422+08:00 0 [Note] InnoDB: Uses event mutexes
2019-04-23T15:41:46.313426+08:00 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2019-04-23T15:41:46.313429+08:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2019-04-23T15:41:46.313432+08:00 0 [Note] InnoDB: Using Linux native AIO
2019-04-23T15:41:46.317318+08:00 0 [Note] InnoDB: Number of pools: 1
2019-04-23T15:41:46.317444+08:00 0 [Note] InnoDB: Not using CPU crc32 instructions
2019-04-23T15:41:46.320971+08:00 0 [Note] InnoDB: Initializing buffer pool, total size = 5M, instances = 1, chunk size = 5M
2019-04-23T15:41:46.321480+08:00 0 [Note] InnoDB: Completed initialization of buffer pool
2019-04-23T15:41:46.322157+08:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-04-23T15:41:46.332282+08:00 0 [ERROR] InnoDB: Unable to lock /var/lib/mysql/data/innodb_ts/ibdata1 error: 11
2019-04-23T15:41:46.332298+08:00 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2019-04-23T15:41:46.332302+08:00 0 [Note] InnoDB: Retrying to lock the first data file
2019-04-23T15:41:47.332427+08:00 0 [ERROR] InnoDB: Unable to lock /var/lib/mysql/data/innodb_ts/ibdata1 error: 11
2019-04-23T15:41:47.332471+08:00 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
...
2019-04-23T15:43:26.352934+08:00 0 [ERROR] InnoDB: Unable to lock /var/lib/mysql/data/innodb_ts/ibdata1 error: 11
2019-04-23T15:43:26.352988+08:00 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2019-04-23T15:43:26.353008+08:00 0 [Note] InnoDB: Unable to open the first data file
2019-04-23T15:43:26.353054+08:00 0 [ERROR] InnoDB: Operating system error number 11 in a file operation.
2019-04-23T15:43:26.353078+08:00 0 [ERROR] InnoDB: Error number 11 means 'Resource temporarily unavailable'
2019-04-23T15:43:26.353092+08:00 0 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2019-04-23T15:43:26.353099+08:00 0 [ERROR] InnoDB: Cannot open datafile '/var/lib/mysql/data/innodb_ts/ibdata1'
2019-04-23T15:43:26.353105+08:00 0 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2019-04-23T15:43:26.353110+08:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Cannot open a file
2019-04-23T15:43:26.953781+08:00 0 [ERROR] Plugin 'InnoDB' init function returned error.
2019-04-23T15:43:26.953831+08:00 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-04-23T15:43:26.953839+08:00 0 [ERROR] Failed to initialize plugins.
2019-04-23T15:43:26.953845+08:00 0 [ERROR] Aborting

2019-04-23T15:43:26.953852+08:00 0 [Note] Binlog end
2019-04-23T15:43:26.953922+08:00 0 [Note] Shutting down plugin 'MyISAM'
2019-04-23T15:43:26.954354+08:00 0 [Note] /opt/mysql-5.7.17-linux-glibc2.5-x86_64/bin/mysqld: Shutdown complete

Conclusion:

• The solution is to stop the original MySQL process and restart it, or to redefine the redo and data directories for the new mysql. As for why this problem occurs in the product, it can only be solved by the developers
• Sometimes when google is not reliable, it can only reproduce the problem according to its own experience and guess, so as to solve the problem