Some notes on modifying the innodb_data_file_path parameter of MySQL

Preface

innodb_data_file_path is used to specify the innodb tablespace file. If we do not specify innodb_data_home_dir and innodb_data_file_path in the My.cnf file, ibdata1 will be created as the innodb tablespace in the datadir directory by default.

illustrate

In the test environment, the service was initialized and started without setting too many detailed parameters. During the subsequent optimization process, it was found that the innodb_data_file_path was set too small:

root@node1 14:59: [(none)]> show variables like '%innodb_data_file_path%';
+----------------------+------------------------+
| Variable_name | Value |
+----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+----------------------+------------------------+
1 row in set (0.00 sec)

root@node1 14:59: [(none)]>

When innodb_data_file_path is not configured, the default innodb_data_file_path = ibdata1:12M:autoextend

[mysqld]
innodb_data_file_path = ibdata1:12M:autoextend

When you need to change it to 1G, you cannot directly change ibdata1 to 1G in the configuration file.

[mysqld]
innodb_data_file_path = ibdata1:1G:autoextend

Otherwise, after starting the service, you will see the following error from the error log:

2019-03-29T06:47:32.044316Z 0 [ERROR] InnoDB: The Auto-extending innodb_system data file './ibdata1' is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 65536 pages, max 0 (relevant if non-zero) pages!

The general meaning is that the size of ibdata1 is not 65536page*16KB/1024KB=1G , but 786page*16KB/1024KB=12M
(Compressed pages not used)

Method 1: Recommended

Instead, add another ibdata2:1G as follows:

[mysqld]
innodb_data_file_path = ibdata1:12M;ibdata2:1G:autoextend

Restart the database!

Method 2: Not recommended

Directly change to the following

[mysqld]
innodb_data_file_path = ibdata1:1G:autoextend

You can delete the ibdata1, ib_logfile0, and ib_logfile1 files in the $mysql_datadir directory:

rm -f ibdata* ib_logfile*

You can also start MySQL, but the following error will be reported in the MySQL error log:

2019-03-29T07:10:47.844560Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 65535)
2019-03-29T07:10:47.844686Z 0 [Warning] Changed limits: table_open_cache: 1983 (requested 2000)
2019-03-29T07:10:48.028262Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2019-03-29T07:10:48.147653Z 0 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
mysqld: Table 'mysql.plugin' doesn't exist
2019-03-29T07:10:48.147775Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2019-03-29T07:10:48.163444Z 0 [Warning] InnoDB: Cannot open table mysql/gtid_executed from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
mysqld: Table 'mysql.gtid_executed' doesn't exist
2019-03-29T07:10:48.163502Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-03-29T07:10:48.163658Z 0 [Warning] InnoDB: Cannot open table mysql/gtid_executed from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
mysqld: Table 'mysql.gtid_executed' doesn't exist
2019-03-29T07:10:48.163711Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-03-29T07:10:48.164619Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2019-03-29T07:10:48.166805Z 0 [Warning] InnoDB: Cannot open table mysql/server_cost from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.166891Z 0 [Warning] Failed to open optimizer cost constant tables

2019-03-29T07:10:48.168072Z 0 [Warning] InnoDB: Cannot open table mysql/time_zone_leap_second from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.168165Z 0 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
2019-03-29T07:10:48.169454Z 0 [Warning] InnoDB: Cannot open table mysql/servers from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.169527Z 0 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
2019-03-29T07:10:48.170042Z 0 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.170617Z 0 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.170946Z 0 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.171046Z 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2019-03-29T07:10:48.171272Z 0 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.171626Z 0 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.171688Z 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:

Difference between MySQL btree index and hash index
A brief understanding of the differences between MySQL InnoDB and MyISAM
Briefly describe the MySQL InnoDB storage engine
Detailed explanation of MySQL Innodb storage structure and storage of Null values
MySQL startup error InnoDB: Unable to lock/ibdata1 error
Detailed explanation of how MySQL (InnoDB) handles deadlocks
How to change mysql engine (InnoDB, MyISAM)
InnoDB configuration parameters that can improve MySQL performance
MySQL error: MySQL server version for the right syntax to use near type=InnoDB solution
Solution to MySQL database innodb startup failure and inability to restart
How to get the height of MySQL innodb B+tree

<<: Solve the error of installing VMware Tools on Ubuntu 18.04

>>: Implementation of TCPWrappers access control in Centos