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