mysql5.7启动报错"Expectedtoopenundotablespacesbutwasabletofindonly0"的解决方法

这篇文章主要讲解了“MySQL 5.7启动报错"Expected to open undo tablespaces but was able to find only 0"的解决方法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“mysql 5.7启动报错"Expected to open undo tablespaces but was able to find only 0"的解决方法”吧！

创新互联是一家专注于网站设计制作、成都网站建设与策划设计,海晏网站建设哪家好?创新互联做网站,专注于网站建设10余年,网设计领域的专业建站公司;建站业务涵盖:海晏等地区。海晏做网站价格咨询:18980820575

mysql 5.7启动报错
错误日志中的错误如下
[ERROR] InnoDB: Expected to open 4 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0

报错原因：
在初始化数据库的时候没有指定innodb_undo_tablespaces参数

解决方法：
在初始化数据库的时候写上--defaults-file参数，在对应的配置文件中写上对应的innodb_undo_tablespaces参数
[mysql@localhost mysql_data]$ vi /tmp/my.cnf
##InnoDB engine undo log related setting
innodb_undo_directory                           = /u01/mysql_data
innodb_undo_tablespaces                         = 4

[mysql@localhost percona]$ bin/mysqld --defaults-file=/tmp/my.cnf --user=mysql --initialize-insecure
2017-02-27T06:49:49.898827Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 9103)
2017-02-27T06:49:49.899000Z 0 [Warning] Changed limits: max_connections: 214 (requested 900)
2017-02-27T06:49:49.899005Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 4096)
2017-02-27T06:49:49.899292Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.
2017-02-27T06:49:49.899447Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2017-02-27T06:49:49.899457Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2017-02-27T06:49:49.899486Z 0 [Note] Ignoring --secure-file-priv value as server is running with --initialize(-insecure) or --bootstrap.
2017-02-27T06:49:49.899521Z 0 [Note] bin/mysqld (mysqld 5.7.17-11-log) starting as process 11301 ...
2017-02-27T06:49:49.901716Z 0 [Note] --initialize specifed on an existing data directory.
2017-02-27T06:49:49.951056Z 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
2017-02-27T06:49:49.951102Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-02-27T06:49:49.951115Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-02-27T06:49:49.951120Z 0 [Note] InnoDB: Uses event mutexes
2017-02-27T06:49:49.951125Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-02-27T06:49:49.951130Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-02-27T06:49:49.951136Z 0 [Note] InnoDB: Using Linux native AIO
2017-02-27T06:49:49.951520Z 0 [Note] InnoDB: Number of pools: 1
2017-02-27T06:49:49.951684Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-02-27T06:49:49.954229Z 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M
2017-02-27T06:49:50.011189Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-02-27T06:49:50.036761Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-02-27T06:49:50.046967Z 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!
2017-02-27T06:49:50.050339Z 0 [Note] InnoDB: Setting file './ibdata1' size to 12 MB. Physically writing the file full; Please wait ...
2017-02-27T06:49:50.950797Z 0 [Note] InnoDB: File './ibdata1' size is now 12 MB.
2017-02-27T06:49:50.951686Z 0 [Note] InnoDB: Setting log file /u01/mysql_log/ib_logfile101 size to 256 MB
2017-02-27T06:49:50.951938Z 0 [Note] InnoDB: Progress in MB:
 100 200
2017-02-27T06:49:57.462978Z 0 [Note] InnoDB: Setting log file /u01/mysql_log/ib_logfile1 size to 256 MB
2017-02-27T06:49:57.463714Z 0 [Note] InnoDB: Progress in MB:
 100 200
2017-02-27T06:50:05.225550Z 0 [Note] InnoDB: Setting log file /u01/mysql_log/ib_logfile2 size to 256 MB
2017-02-27T06:50:05.225866Z 0 [Note] InnoDB: Progress in MB:
 100 200
2017-02-27T06:50:13.334802Z 0 [Note] InnoDB: Data file /u01/mysql_data/undo001 did not exist: new to be created
2017-02-27T06:50:13.334881Z 0 [Note] InnoDB: Setting file /u01/mysql_data/undo001 size to 10 MB
2017-02-27T06:50:13.334893Z 0 [Note] InnoDB: Database physically writes the file full: wait...
2017-02-27T06:50:13.699071Z 0 [Note] InnoDB: Data file /u01/mysql_data/undo002 did not exist: new to be created
2017-02-27T06:50:13.699115Z 0 [Note] InnoDB: Setting file /u01/mysql_data/undo002 size to 10 MB
2017-02-27T06:50:13.699128Z 0 [Note] InnoDB: Database physically writes the file full: wait...
2017-02-27T06:50:14.327848Z 0 [Note] InnoDB: Data file /u01/mysql_data/undo003 did not exist: new to be created
2017-02-27T06:50:14.328029Z 0 [Note] InnoDB: Setting file /u01/mysql_data/undo003 size to 10 MB
2017-02-27T06:50:14.328048Z 0 [Note] InnoDB: Database physically writes the file full: wait...
2017-02-27T06:50:15.021202Z 0 [Note] InnoDB: Data file /u01/mysql_data/undo004 did not exist: new to be created
2017-02-27T06:50:15.021233Z 0 [Note] InnoDB: Setting file /u01/mysql_data/undo004 size to 10 MB
2017-02-27T06:50:15.021241Z 0 [Note] InnoDB: Database physically writes the file full: wait...
2017-02-27T06:50:15.483984Z 0 [Note] InnoDB: Opened 4 undo tablespaces
2017-02-27T06:50:15.484016Z 0 [Note] InnoDB: 0 undo tablespaces made active
2017-02-27T06:50:16.120523Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /u01/mysql_data/xb_doublewrite, size 31457280 bytes
2017-02-27T06:50:16.608949Z 0 [Note] InnoDB: Renaming log file /u01/mysql_log/ib_logfile101 to /u01/mysql_log/ib_logfile0
2017-02-27T06:50:16.609000Z 0 [Warning] InnoDB: New log files created, LSN=49319
2017-02-27T06:50:16.609010Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-02-27T06:50:16.609036Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-02-27T06:50:16.986856Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-02-27T06:50:16.986975Z 0 [Note] InnoDB: Doublewrite buffer not found: creating new
2017-02-27T06:50:17.285288Z 0 [Note] InnoDB: Doublewrite buffer created
2017-02-27T06:50:17.397967Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-02-27T06:50:17.397993Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-02-27T06:50:17.399086Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-02-27T06:50:17.404597Z 0 [Note] InnoDB: Foreign key constraint system tables created
2017-02-27T06:50:17.404638Z 0 [Note] InnoDB: Creating tablespace and datafile system tables.
2017-02-27T06:50:17.404910Z 0 [Note] InnoDB: Tablespace and datafile system tables created.
2017-02-27T06:50:17.404934Z 0 [Note] InnoDB: Creating sys_virtual system tables.
2017-02-27T06:50:17.405099Z 0 [Note] InnoDB: sys_virtual table created
2017-02-27T06:50:17.405120Z 0 [Note] InnoDB: Creating zip_dict and zip_dict_cols system tables.
2017-02-27T06:50:17.405507Z 0 [Note] InnoDB: zip_dict and zip_dict_cols system tables created.
2017-02-27T06:50:17.406948Z 0 [Note] InnoDB: Waiting for purge to start
2017-02-27T06:50:17.457485Z 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.17-11 started; log sequence number 0
2017-02-27T06:50:17.459000Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-02-27T06:50:17.610665Z 0 [Note] Salting uuid generator variables, current_pid: 11301, server_start_time: 1488178189, bytes_sent: 0,
2017-02-27T06:50:17.610808Z 0 [Note] Generated uuid: 'ffbc8dfc-fcb8-11e6-b9d4-0800272757c8', server_start_time: 3180948713295312946, bytes_sent: 76747152
2017-02-27T06:50:17.610852Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ffbc8dfc-fcb8-11e6-b9d4-0800272757c8.
2017-02-27T06:50:17.616590Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-02-27T06:50:18.158260Z 0 [Note] Auto generated SSL certificates are placed in data directory.
2017-02-27T06:50:18.163337Z 0 [Warning] CA certificate ca.pem is self signed.
2017-02-27T06:50:18.206839Z 0 [Note] Auto generated RSA key files are placed in data directory.
2017-02-27T06:50:18.207215Z 1 [Note] Creating the system database
2017-02-27T06:50:18.207222Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2017-02-27T06:50:18.244847Z 1 [Note] Creating the system tables
2017-02-27T06:50:27.495592Z 1 [Note] Filling in the system tables, part 1
2017-02-27T06:50:27.499607Z 1 [Note] Filling in the system tables, part 2
2017-02-27T06:50:27.499686Z 1 [Note] Filling in the mysql.help table
2017-02-27T06:50:29.336103Z 1 [Note] Creating the sys schema
2017-02-27T06:50:31.409726Z 1 [Note] Bootstrapping complete
2017-02-27T06:50:31.409994Z 0 [Note] Giving 0 client threads a chance to die gracefully
2017-02-27T06:50:31.410011Z 0 [Note] Shutting down slave threads
2017-02-27T06:50:31.410019Z 0 [Note] Forcefully disconnecting 0 remaining clients
2017-02-27T06:50:31.410286Z 0 [Note] Binlog end
2017-02-27T06:50:31.467679Z 0 [Note] InnoDB: FTS optimize thread exiting.
2017-02-27T06:50:31.467840Z 0 [Note] InnoDB: Starting shutdown...
2017-02-27T06:50:31.569708Z 0 [Note] InnoDB: Dumping buffer pool(s) to /u01/mysql_data/ib_buffer_pool
2017-02-27T06:50:31.569921Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 170227 14:50:31
2017-02-27T06:50:32.676438Z 0 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
2017-02-27T06:50:33.785734Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2412572
2017-02-27T06:50:33.786149Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
[mysql@localhost percona]$ bin/mysqld_safe --defaults-file=/tmp/my.cnf &
[1] 11369
[mysql@localhost percona]$  mysqld_safe Adding '/database/percona/lib/mysql/libjemalloc.so.1' to LD_PRELOAD for mysqld
2017-02-27T06:52:37.984265Z mysqld_safe Logging to '/u01/mysql_data/mysqld_error.log'.
2017-02-27T06:52:37.986593Z mysqld_safe Logging to '/u01/mysql_data/mysqld_error.log'.
2017-02-27T06:52:38.030572Z mysqld_safe Starting mysqld daemon with databases from /u01/mysql_data

[mysql@localhost percona]$ bin/mysql --defaults-file=/tmp/my.cnf
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-11-log Percona Server (GPL), Release 11, Revision f60191c

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, 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> show variables like '%undo%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| innodb_max_undo_log_size | 1073741824      |
| innodb_undo_directory    | /u01/mysql_data |
| innodb_undo_log_truncate | OFF             |
| innodb_undo_logs         | 128             |
| innodb_undo_tablespaces  | 4               |
+--------------------------+-----------------+
5 rows in set (0.04 sec)

[mysql@localhost mysql_data]$ ls -l /u01/mysql_data/undo00*
-rw-r----- 1 mysql mysql 10485760 Feb 27 14:50 /u01/mysql_data/undo001
-rw-r----- 1 mysql mysql 10485760 Feb 27 14:50 /u01/mysql_data/undo002
-rw-r----- 1 mysql mysql 10485760 Feb 27 14:50 /u01/mysql_data/undo003
-rw-r----- 1 mysql mysql 10485760 Feb 27 14:50 /u01/mysql_data/undo004

注意初始化数据库语句的写法，将--defaults-file放到语句最后会导致配置文件中的参数不会生效
[mysql@localhost percona]$ bin/mysqld --initialize-insecure --user=mysql --defaults-file=/tmp/my.cnf
mysqld: Can't create directory '/var/lib/mysql/' (Errcode: 13 - Permission denied)
2017-02-27T06:34:06.250482Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-02-27T06:34:06.250613Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2017-02-27T06:34:06.250955Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-02-27T06:34:06.251097Z 0 [ERROR] Can't find error-message file '/usr/local/Percona-Server-5.7.17-11-Linux.x86_64.ssl101/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2017-02-27T06:34:06.253894Z 0 [ERROR] Aborting

[2017-02-27_14_34_06][ERROR][installMYSQL_INSTANCE.sh]: Create MySQL instance ...... start to evaluate creation result
[2017-02-27_14_34_06][ERROR][installMYSQL_INSTANCE.sh]: Folder /u01/mysql_data/mysql not exist
[2017-02-27_14_34_06][ERROR][installMYSQL_INSTANCE.sh]: Folder /u01/mysql_data/performance_schema not exist
[2017-02-27_14_34_06][ERROR][installMYSQL_INSTANCE.sh]: Folder /u01/mysql_data/test not exist
[2017-02-27_14_34_06][ERROR][installMYSQL_INSTANCE.sh]: ibdata /u01/mysql_data/ibdata1 file not exist
[2017-02-27_14_34_06][ERROR][installMYSQL_INSTANCE.sh]: Create MySQL instance ...... FAILED

感谢各位的阅读，以上就是“mysql 5.7启动报错"Expected to open undo tablespaces but was able to find only 0"的解决方法”的内容了，经过本文的学习后，相信大家对mysql 5.7启动报错"Expected to open undo tablespaces but was able to find only 0"的解决方法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！