修改MYSQL的REDO大小

If you need to change MySQL’s innodb_log_file_size parameter (see How to calculate a good InnoDB log file size), you can’t just change the
parameter in the my.cnf file and restart the server. If you do, InnoDB will refuse to start because the existing log files don’t match the configured size.
You need to shut the server down cleanly and normally, and move away (don’t delete) the log files,
which are named ib_logfile0, ib_logfile1, and so on. Check the error log to ensure there was no problem shutting down.
Then restart the server and watch the error log output carefully. You should see InnoDB print messages saying that the log files don’t exist.
It will create new ones and then start. At this point you can verify that InnoDB is working, and then you can delete the old log files.


正确修改REDO大小的方法:
首先将数据库停止掉,以正常方法, 不能够KILL进程来关机,促使REDO里的数据都写到了数据文件中。
将原先的REDO LOG备份至别的目录。
修改配置文件。
启动数据库。

[root@localhost ~]# mysqladmin -uroot -p shutdown
Enter password:
[root@localhost ~]# ps -elf | grep mysql
0 S root 25453 25423 0 78 0 - 15306 pipe_w 10:26 pts/4 00:00:00 grep mysql

修改大小:
innodb_log_files_in_group=4
innodb_log_file_size=1020M

备份文件:
[root@localhost data]# mv ib_logfile0 ib_logfile0bak
[root@localhost data]# mv ib_logfile1 ib_logfile1bak
[root@localhost data]# mv ib_logfile2 ib_logfile2bak
[root@localhost data]# mv ib_logfile3 ib_logfile3bak

启动数据库:
[root@localhost data]# mysqld_safe --defaults-file=/etc/my.cnf &
[1] 20206
[root@localhost data]# 140509 11:07:39 mysqld_safe Logging to '/u01/mysql/logs/mysql/error3306.log'.
140509 11:07:39 mysqld_safe Starting mysqld daemon with databases from /u01/mysql/data


查看日志:
140509 11:04:51 mysqld_safe mysqld from pid file /u01/mysql/data/localhost.localdomain.pid ended
140509 11:07:39 mysqld_safe Starting mysqld daemon with databases from /u01/mysql/data
140509 11:07:40 InnoDB: The InnoDB memory heap is disabled
140509 11:07:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140509 11:07:40 InnoDB: Compressed tables use zlib 1.2.3
140509 11:07:40 InnoDB: Using Linux native AIO
140509 11:07:40 InnoDB: Initializing buffer pool, size = 2.0G
140509 11:07:40 InnoDB: Completed initialization of buffer pool
140509 11:07:40 InnoDB: Log file /u01/mysql/data/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /u01/mysql/data/ib_logfile0 size to 1020 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
140509 11:07:46 InnoDB: Log file /u01/mysql/data/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /u01/mysql/data/ib_logfile1 size to 1020 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
140509 11:07:55 InnoDB: Log file /u01/mysql/data/ib_logfile2 did not exist: new to be created
InnoDB: Setting log file /u01/mysql/data/ib_logfile2 size to 1020 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
140509 11:07:59 InnoDB: Log file /u01/mysql/data/ib_logfile3 did not exist: new to be created
InnoDB: Setting log file /u01/mysql/data/ib_logfile3 size to 1020 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
140509 11:08:03 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140509 11:08:03 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140509 11:08:03 InnoDB: Waiting for the background threads to start
140509 11:08:04 InnoDB: 5.5.30 started; log sequence number 1595916
140509 11:08:04 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140509 11:08:04 [Note] - '0.0.0.0' resolves to '0.0.0.0';
140509 11:08:04 [Note] Server socket created on IP: '0.0.0.0'.
140509 11:08:04 [Warning] 'user' entry 'root@localhost.localdomain' ignored in --skip-name-resolve mode.
140509 11:08:04 [Warning] 'user' entry '@localhost.localdomain' ignored in --skip-name-resolve mode.
140509 11:08:04 [Warning] 'proxies_priv' entry '@ root@localhost.localdomain' ignored in --skip-name-resolve mode.
140509 11:08:04 [Note] Event Scheduler: Loaded 0 events
140509 11:08:04 [Note] /u01/mysql/bin/mysqld: ready for connections.
Version: '5.5.30-log' socket: '/tmp/mysql3306.sock' port: 3306 Source distribution
140509 11:08:04 [Note] Event Scheduler: scheduler thread started with id 1

从以上日志可以看出日志被扩展到了1020M

If you need to change MySQL’s innodb_log_file_size parameter (see How to calculate a good InnoDB log file size), you can’t just change the 
parameter in the my.cnf file and restart the server. If you do, InnoDB will refuse to start because the existing log files don’t match the configured size.
You need to shut the server down cleanly and normally, and move away (don’t delete) the log files, 
which are named ib_logfile0, ib_logfile1, and so on. Check the error log to ensure there was no problem shutting down. 
Then restart the server and watch the error log output carefully. You should see InnoDB print messages saying that the log files don’t exist. 
It will create new ones and then start. At this point you can verify that InnoDB is working, and then you can delete the old log files.


正确修改REDO大小的方法:
首先将数据库停止掉,以正常方法, 不能够KILL进程来关机,促使REDO里的数据都写到了数据文件中。
将原先的REDO LOG备份至别的目录。
修改配置文件。
启动数据库。

[root@localhost ~]# mysqladmin -uroot -p shutdown
Enter password: 
[root@localhost ~]# ps -elf | grep mysql
0 S root     25453 25423  0  78   0 - 15306 pipe_w 10:26 pts/4    00:00:00 grep mysql

修改大小:
innodb_log_files_in_group=4
innodb_log_file_size=1020M

备份文件:
[root@localhost data]# mv ib_logfile0 ib_logfile0bak
[root@localhost data]# mv ib_logfile1 ib_logfile1bak
[root@localhost data]# mv ib_logfile2 ib_logfile2bak
[root@localhost data]# mv ib_logfile3 ib_logfile3bak

启动数据库:
[root@localhost data]#  mysqld_safe --defaults-file=/etc/my.cnf &
[1] 20206
[root@localhost data]# 140509 11:07:39 mysqld_safe Logging to '/u01/mysql/logs/mysql/error3306.log'.
140509 11:07:39 mysqld_safe Starting mysqld daemon with databases from /u01/mysql/data


查看日志:
140509 11:04:51 mysqld_safe mysqld from pid file /u01/mysql/data/localhost.localdomain.pid ended
140509 11:07:39 mysqld_safe Starting mysqld daemon with databases from /u01/mysql/data
140509 11:07:40 InnoDB: The InnoDB memory heap is disabled
140509 11:07:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140509 11:07:40 InnoDB: Compressed tables use zlib 1.2.3
140509 11:07:40 InnoDB: Using Linux native AIO
140509 11:07:40 InnoDB: Initializing buffer pool, size = 2.0G
140509 11:07:40 InnoDB: Completed initialization of buffer pool
140509 11:07:40  InnoDB: Log file /u01/mysql/data/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /u01/mysql/data/ib_logfile0 size to 1020 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
140509 11:07:46  InnoDB: Log file /u01/mysql/data/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /u01/mysql/data/ib_logfile1 size to 1020 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
140509 11:07:55  InnoDB: Log file /u01/mysql/data/ib_logfile2 did not exist: new to be created
InnoDB: Setting log file /u01/mysql/data/ib_logfile2 size to 1020 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
140509 11:07:59  InnoDB: Log file /u01/mysql/data/ib_logfile3 did not exist: new to be created
InnoDB: Setting log file /u01/mysql/data/ib_logfile3 size to 1020 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
140509 11:08:03 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140509 11:08:03  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140509 11:08:03  InnoDB: Waiting for the background threads to start
140509 11:08:04 InnoDB: 5.5.30 started; log sequence number 1595916
140509 11:08:04 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140509 11:08:04 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140509 11:08:04 [Note] Server socket created on IP: '0.0.0.0'.
140509 11:08:04 [Warning] 'user' entry 'root@localhost.localdomain' ignored in --skip-name-resolve mode.
140509 11:08:04 [Warning] 'user' entry '@localhost.localdomain' ignored in --skip-name-resolve mode.
140509 11:08:04 [Warning] 'proxies_priv' entry '@ root@localhost.localdomain' ignored in --skip-name-resolve mode.
140509 11:08:04 [Note] Event Scheduler: Loaded 0 events
140509 11:08:04 [Note] /u01/mysql/bin/mysqld: ready for connections.
Version: '5.5.30-log'  socket: '/tmp/mysql3306.sock'  port: 3306  Source distribution
140509 11:08:04 [Note] Event Scheduler: scheduler thread started with id 1

从以上日志可以看出日志被扩展到了1020M

  

posted @ 2014-06-04 16:44 SMALL-D 阅读(...) 评论(...)  编辑 收藏