修改配置文件无法增大innodb_buffer_pool_size，求助解决ERROR1206问题

Hey there, let's walk through why your innodb_buffer_pool_size change isn't sticking and get that lock error resolved. Here's what to do step by step:

1. Find out which config file MySQL is actually using #

The my-default.ini is just a template—MySQL doesn't load it by default. First, confirm the active config file path by running this query in MySQL Workbench:

SHOW VARIABLES LIKE 'config_file';

This will return the full path of the config file your MySQL instance is reading. If it's not pointing to a my.ini file, that's why your changes aren't working.

2. Create/use the correct my.ini file #

On Windows (since you're using MySQL Server 5.6 with Workbench), follow these steps:

• Stop the MySQL service first:
  • Open the Windows Services app (press Win+R, type services.msc, hit Enter).
  • Find your MySQL service (usually named MySQL56 for Server 5.6), right-click it and select Stop.
• Copy my-default.ini to my.ini:
  • Navigate to ...\MySQL\MySQL Server 5.6\ where you found my-default.ini.
  • Make a copy of it and rename the copy to my.ini.
• Edit my.ini properly:
  • Open my.ini in a text editor (like Notepad++ to avoid formatting issues).
  • Find the [mysqld] section. Instead of adding a new line for innodb_buffer_pool_size, replace the commented-out line or ensure only one active line exists. For example:

    [mysqld]
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    innodb_buffer_pool_size = 1024M
    

  • Save the file.
• Restart the MySQL service:
  • Go back to the Windows Services app, right-click your MySQL service and select Start.

3. Verify the change took effect #

Run your query again to check the buffer pool size:

SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'innodb_buffer_pool_size';

For 1024M, the expected variable_value should be 1073741824 (since 1MB = 1048576 bytes, 1024 * 1048576 = 1073741824). If you see this number, the change worked.

4. Troubleshooting if it still doesn't work #

• Check for other config files: Sometimes MySQL reads a my.ini from C:\Windows\—check that location and make sure it doesn't override your settings.
• File permissions: Ensure the Windows user running the MySQL service has read access to your my.ini file. Right-click the file, go to Properties > Security and confirm the service user (usually NETWORK SERVICE) has read permissions.
• Double-check the config syntax: Make sure there are no typos in innodb_buffer_pool_size (no extra spaces, correct capitalization) and the line isn't commented out with a #.

Once the buffer pool size is increased, your lock table size error should be resolved, as InnoDB uses the buffer pool to manage locks and data caching.

内容的提问来源于stack exchange，提问作者MrTed