MYSQL 修改共享表空间大小

1. 关闭数据库:
[root@localhost ~]# mysqladmin -uroot -p -S /tmp/mysql.sock shutdown
Enter password: 
[root@localhost ~]# ps -elf | grep mysql
0 S root      9735  9708  0  80   0 - 25824 pipe_w 23:08 pts/3    00:00:00 grep mysql

2. 修改配置文件共享表空间的大小。
innodb_data_file_path=ibdata1:300M:autoextend

3. 启动数据库:
InnoDB: Error: auto-extending data file ./ibdata1 is of a different size
InnoDB: 6400 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 19200 pages, max 0 (relevant if non-zero) pages!
在启动的时候报错了, 是因为我动了大小。
这里我把共享表空间的数据文件COPY出去。
mv /data/mysql/mysql_3306/data/ibdata1 /data/mysql/mysql_3306/

再次启动:
140612 23:12:44 InnoDB: Error: all log files must be created at the same time.
140612 23:12:44 InnoDB: All log files must be created also in database creation.
继续报错, 所有日志创建时间也不一致。

将REDO LOG移到另一个目录。
mv /data/mysql/mysql_3306/data/ib_logfile* /data/mysql/mysql_3306/
这里需要同时把共享表空间再移出去, 因为刚刚启动的时候又生成了一次共享表空间, 所以需要再次移出。
mv /data/mysql/mysql_3306/data/ibdata1 /data/mysql/mysql_3306/ibdata1bak

再次启动
[root@localhost bin]# ./mysqld_safe --defaults-file=/etc/my.cnf &
[1] 11923
[root@localhost bin]# 140612 23:16:55 mysqld_safe Logging to '/data/mysql/mysql_3306/data/error.log'.
140612 23:16:55 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data

启动成功, 下面我们来看一下MYSQL的日志。
140612 23:16:55 InnoDB: Initializing buffer pool, size = 2.0G
140612 23:16:56 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
140612 23:16:56  InnoDB: Setting file ./ibdata1 size to 300 MB    ----重创共享表空间。
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300
140612 23:16:58  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 100 MB     ---重创建REDO LOG。
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
140612 23:16:59  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
140612 23:17:00  InnoDB: Log file ./ib_logfile2 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile2 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
140612 23:17:00  InnoDB: Waiting for the background threads to start
140612 23:17:01 InnoDB: 5.5.37 started; log sequence number 0
140612 23:17:01 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140612 23:17:01 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140612 23:17:01 [Note] Server socket created on IP: '0.0.0.0'.
140612 23:17:01 [Note] Event Scheduler: Loaded 0 events
140612 23:17:01 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.5.37-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)


官方给出的办法:
1. 导出数据库:除mysql and performance_schema databases, information_schema,因为我这里就只有TEST数据库, 故这里就只导出TEST库吧。
mysqldump -uroot -p -S /tmp/mysql.sock --add-drop-database  --complete-insert --extended-insert --single-transaction --database test  --default-character-set=utf8  --triggers -R  >test.sql

2. 删除 mysql and performance_schema databases ,information_schema以外的数据库
(testing)root@localhost [(none)]> drop database test;
Query OK, 0 rows affected (0.03 sec)


3. 关闭数据库,并删除共享表空间以及REDO日志。
[root@localhost bin]# mysqladmin -uroot -p -S /tmp/mysql.sock shutdown
Enter password: 
140612 23:38:31 mysqld_safe mysqld from pid file /data/mysql/mysql_3306/data/localhost.localdomain.pid ended
[1]+  Done                    ./mysqld_safe --defaults-file=/etc/my.cnf


先做好一个COPY备份:
cp -r mysql_3306 mysql_3306bak

删除表空间文件,以及表定义文件。
cd /data/mysql/mysql_3306/data/
rm -rf ib*


3. 修改配置文件共享表空间的大小。
innodb_data_file_path=ibdata1:200M:autoextend

4. 启动数据库
[root@localhost bin]# ./mysqld_safe --defaults-file=/etc/my.cnf &
[1] 16449
[root@localhost bin]# 140613 00:02:28 mysqld_safe Logging to '/data/mysql/mysql_3306/data/error.log'.
140613 00:02:28 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data

查看日志:
140613  0:02:28 InnoDB: The InnoDB memory heap is disabled
140613  0:02:28 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140613  0:02:28 InnoDB: Compressed tables use zlib 1.2.3
140613  0:02:28 InnoDB: Using Linux native AIO
140613  0:02:28 InnoDB: Initializing buffer pool, size = 2.0G
140613  0:02:28 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
140613  0:02:28  InnoDB: Setting file ./ibdata1 size to 200 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
140613  0:02:29  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
140613  0:02:29  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
140613  0:02:30  InnoDB: Log file ./ib_logfile2 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile2 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
140613  0:02:30  InnoDB: Waiting for the background threads to start
140613  0:02:31 InnoDB: 5.5.37 started; log sequence number 0
140613  0:02:31 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140613  0:02:31 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140613  0:02:31 [Note] Server socket created on IP: '0.0.0.0'.
140613  0:02:31 [Note] Event Scheduler: Loaded 0 events
140613  0:02:31 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.5.37-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

5. 导入数据。
[root@localhost ~]# mysql -uroot -p -S /tmp/mysql.sock -e "source test.sql;"
Enter password: 
[root@localhost ~]# mysql -uroot -p -S /tmp/mysql.sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(testing)root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

  建议使用官方给的方法。

详细请参考:

http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html

posted @ 2014-06-13 01:46 SMALL-D 阅读(...) 评论(...)  编辑 收藏