Shulou(Shulou.com)05/31 Report--

How to modify innodb_data_file_path parameter items in MySQL? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

The service is initialized and started without setting too many detailed parameters in the test environment. In the process of later optimization, it is found that the innodb_data_file_path setting is 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 is innodb_data_file_path = ibdata1:12M:autoextend

[mysqld] innodb_data_file_path = ibdata1:12M:autoextend

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

[mysqld] innodb_data_file_path = ibdata1:1G:autoextend

Otherwise, after starting the service, you can 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 size of ibdata1 is not 65536page*16KB/1024KB=1G, but 786page*16KB/1024KB=12M.

(compressed pages are not used)

Method 1: recommend

Instead, add another ibdata2:1G, as follows:

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

Restart the database!

Method 2: do not recommend

If you change it directly 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 is 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 exist2019-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 exist2019-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 exist2019-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 key2019-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 tables2019-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 them2019-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 exist2019-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 .f rm 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. Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.