mysql 5.7启动报错"Expected to open undo tablespaces but was able to find only 0"

红太狼 2024-04-18 00:34 46阅读 0赞

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2134371/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2134371/