mysql 5.7 undo_mysql 5.7启动报错"Expected to open undo tablespaces but was able to find only 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