Mysql

如何更新 mysql my.cnf 而不會出錯?

  • March 8, 2018

我更新了 MySQLmy.conf文件並重新啟動了我的伺服器,沒有出現任何問題。

一旦我嘗試訪問一個表,我就收到了錯誤

mysql #1033 - Incorrect information in file: 

我將my.cnf文件恢復為原始文件,伺服器工作正常。

我正在尋找更新一些 innodb 設置:

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_file_size
innodb_log_buffer_size

我想我看到我必須my.cnf在伺服器上創建任何數據庫之前更新文件,但這似乎不對。

my.cnf在伺服器上創建數據庫後,有沒有辦法更新文件?

CentOS 5.6

MySQL 5.0.77

原版的:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

新的:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# This is my configuration!
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=20M

innodb_log_file_size=1G
innodb_log_buffer_size=8M

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

你提到了這四個選項

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_file_size
innodb_log_buffer_size

讓我擔心的是innodb_log_file_size

初始啟動時,innodb_log_file_size為 5MB。您需要創建新的 InnoDB 日誌文件。有兩個 InnoDB 日誌文件,每個文件創建 5MB:

  • /var/lib/mysql/ib_logfile0
  • /var/lib/mysql/ib_logfile1

您所要做的就是:

service mysql stop
rm -f ib_logfile*
service mysql start

這兩個日誌文件將重新生成為 1GB 文件。我去年寫過這個

試一試 !!!

順便說一句,不要觸摸 /var/lib/mysql/ibata1 !!!

引用自:https://dba.stackexchange.com/questions/12695