[Mysql Case] Configure my. cnf multi-instance in mysql5.6.14, mysql

Source: Internet
Author: User
1.1.1.mysql5.6.14 when multiple instances my. cnf are initialized, The my. cnf configuration file [environment description] is not read. In the etcmy. cnf environment configured for multiple instances, an error is reported when Mysql is started after mysql_install_db is executed. [Operation] etcmy. cnf configuration file: [mysqld3307] innodb_data_file_pathibdata1: 1G: autoextend

1.1.1.mysql5.6.14 multi-instance my. during cnf initialization, my is not read. cnf configuration file [environment description] in the Multi-instance configuration/etc/my. in the cnf environment, after mysql_install_db is executed, Mysql is started to report an error. [Procedure]/etc/my. cnf configuration file: [mysqld3307] innodb_data_file_path = ibdata1: 1G: autoextend

1.1.1. mysql5.6.14 when multiple instances are my. cnf, The my. cnf configuration file is not read during initialization.

[Environment description]

In the/etc/my. cnf environment configured for multiple instances, run mysql_install_db and start Mysql to report an error.

Procedure]

/Etc/my. cnf configuration file:

[Mysqld3307]

Innodb_data_file_path = ibdata1: 1G: autoextend

Initialize the database:

[Root @ test home] # mysql_install_db -- datadir =/home/mysql_3307 -- user = mysql -- defaults-file =/etc/my. cnf

Installing MySQL system tables... 2014-05-1311: 07: 33 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. please use -- explicit_defaults_for_timestamp server option (see documentationfor more details ).

11:07:33 8897 [Note] InnoDB: TheInnoDB memory heap is disabled

11:07:33 8897 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

11:07:33 8897 [Note] InnoDB: Compressed tables use zlib 1.2.3

11:07:33 8897 [Note] InnoDB: Using Linux native AIO

11:07:33 8897 [Note] InnoDB: Using CPU crc32 instructions

11:07:34 8897 [Note] InnoDB: Initializing buffer pool, size = 128.0 M

11:07:34 8897 [Note] InnoDB: Completed initialization of buffer pool

11:07:34 8897 [Note] InnoDB: Thefirst specified data file./ibdata1 did not exist: a new database to becreated!

11:07:34 8897 [Note] InnoDB: Setting file./ibdata1 size to 12 MB

11:07:34 8897 [Note] InnoDB: Database physically writes the file full: wait...

11:07:34 8897 [Note] InnoDB: Setting log file./ib_logfile101 size to 48 MB

11:07:34 8897 [Note] InnoDB: Setting log file./ib_logfile1 size to 48 MB

11:07:35 8897 [Note] InnoDB: Renaming log file./ib_logfile101 to./ib_logfile0

11:07:35 8897 [Warning] InnoDB: New log files created, LSN = 45781

11:07:35 8897 [Note] InnoDB: Doublewrite buffer not found: creating new

11:07:35 8897 [Note] InnoDB: Doublewrite buffer created

When the mysql instance is started, error. log reports the following error:

11:11:15 8989 [Note] InnoDB: Completed initialization of buffer pool

11:11:15 8989 [ERROR] InnoDB: auto-extending data file. /ibdata1 is of a differentsize 768 pages (rounded down to MB) than specified in. cnf file: initial65536 pages, max 0 (relevant if non-zero) pages!

11:11:15 8989 [ERROR] InnoDB: cocould not open or create the system tablespace. If you tried to add new datafiles to the system tablespace, and it failed he

Re, you shoshould now editinnodb_data_file_path in my. cnf back to what it was, and remove the new ibdatafiles InnoDB created in this failed attempt. InnoDB only wrote

Those files full of zeros, but did not yetuse them in any way. But be careful: do not remove old data files which containyour precious data!

11:11:15 8989 [ERROR] Plugin 'innodb' init function returned error.

11:11:15 8989 [ERROR] Plugin 'innodb' registry as a storage engine failed.

11:11:15 8989 [ERROR] Unknown/unsupported storage engine: InnoDB

11:11:15 8989 [ERROR] Aborting

11:11:15 8989 [Note] Binlog end

11:11:15 8989 [Note] Shuttingdown plugin 'partition'

11:11:15 8989 [Note] Shuttingdown plugin 'archive'

Although the/etc/my. cnf configuration file is specified when the mysql_install_db command is executed, it is not read during database initialization:

Output log during initialization:

11:07:34 8897 [Note] InnoDB: Setting file./ibdata1 size to 12 MB

View the size of/home/mysql_3307/ibdata1:

[Root @ test mysql_3307] # ls-ltrh | grep ibdata1

-Rw ---- 1 mysql 12 M May 13 11: 07 ibdata1

It can be found that the size of the actually created ibdata1 file is also the default mysql 12 M, rather than the 1g size configured in the configuration file. An error is reported when the instance is started, the size of the ibdata1 file does not match that in the configuration file.

[Error cause]

In mysql5.6.14, the mysql_install_db command reads my. the cnf configuration file only reads the [mysqld] configuration module and does not read the [mysqldNNN] configured in multiple instances. Therefore, in this case. the ibdata size of cnf is 1 GB, but mysql still uses the default 12 Mb.

[Solution]

Modify my. cnf and [mysqldNNN] to [mysqld] in sequence. After initialization, modify it back.



[Other possible reasons]

An error is reported on the Internet due to insufficient datadir path space. However, unless the ibdata configured in my. cnf is very large, it will not happen online.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.