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
