1， 加锁，然后全备份数据，可以用mysqldump，也可以使用其他的工具：
[root@localhost data]# mysqldump --all-databases > /root/all_databases.sql
2，修改my.cnf文件为你想要的大小
innodb_data_home_dir=/tmp/mysql-5.6/data
innodb_data_file_path=ibdata1:200M:autoextend:max:2G注： ibdata1有最小限制
• For an innodb_page_size value of 16KB or less, the minimum file size is 3MB.
• For an innodb_page_size value of 32KB, the minimum file size is 6MB. • For an innodb_page_size value of 64KB, the minimum file size is 12MB.3， 删除ibdata1和ib_logfile0，ib_logfile1,ib_logfile2
[root@localhost data]# lsa.sql b.sql ib_logfile0 itdb localhost-relay-bin.index mysql-bin.000001 mysql-bin.000004 mysql-bin.000007 percona testauto.cnf db2.sql ib_logfile1 localhost.localdomain.pid master.info mysql-bin.000002 mysql-bin.000005 mysql-bin.000008 performance_schemabooks ibdata1 ib_logfile2 localhost-relay-bin.000008 mysql mysql-bin.000003 mysql-bin.000006 mysql-bin.index relay-log.info[root@localhost data]#[root@localhost data]# rm -rf ibdata1[root@localhost data]# rm -rf ib_log*[root@localhost data]# lsa.sql books db2.sql localhost.localdomain.pid localhost-relay-bin.index mysql mysql-bin.000002 mysql-bin.000004 mysql-bin.000006 mysql-bin.000008 percona relay-log.infoauto.cnf b.sql itdb localhost-relay-bin.000008 master.info mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.000007 mysql-bin.index performance_schema test[root@localhost data]#
4，重启mysql,此时发现ibdata1变成我们想要的大小了；
[root@localhost data]# service mysqld restartShutting down MySQL.... SUCCESS!Starting MySQL... SUCCESS![root@localhost data]# du -sh ibdata1201M ibdata1[root@localhost data]#
5， 登陆mysql,删除数据库，此时发现无法删除；因为数据字典所在的表空间被删了；
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || books || itdb || mysql || percona || performance_schema || test |+--------------------+7 rows in set (0.00 sec) mysql> drop database books;ERROR 1010 (HY000): Error dropping database (can't rmdir './books', errno: 39)mysql>
此时把database文件夹移走；performance_schema不要动，其他的都移走
root@localhost data]# lsauto.cnf ibdata1 ib_logfile1 itdb localhost-relay-bin.000004 master.info mysql-bin.000001 mysql-bin.000003 mysql-bin.index performance_schema testbooks ib_logfile0 ib_logfile2 localhost.localdomain.pid localhost-relay-bin.index mysql mysql-bin.000002 mysql-bin.000004 percona relay-log.info[root@localhost data]# mkdir /bak[root@localhost data]# mv itdb books mysql percona test /bak[root@localhost data]# lsauto.cnf ib_logfile0 ib_logfile2 localhost-relay-bin.000004 master.info mysql-bin.000002 mysql-bin.000004 performance_schemaibdata1 ib_logfile1 localhost.localdomain.pid localhost-relay-bin.index mysql-bin.000001 mysql-bin.000003 mysql-bin.index relay-log.info[root@localhost data]#
移完之后，查看系统只有information_schema和performance_schema2个库；
然后进行恢复数据mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || performance_schema |+--------------------+2 rows in set (0.00 sec) mysql>[1]+ Stopped mysql (wd: /tmp/mysql-5.6/data)(wd now: ~)[root@localhost ~]#[root@localhost ~]#[root@localhost ~]# mysql -e "source /root/all_databases.sql" > source.log
然后查看数据：
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || books || itdb || mysql || percona || performance_schema || test |+--------------------+7 rows in set (0.00 sec) mysql> use itdbReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> show tables;+------------------+| Tables_in_itdb |+------------------+| contact || customers || kepler_promotion || orderitems || orders || productnotes || products || shopping || tt || vendors |+------------------+mysql> select * from contact limit 5;+----+---------+-------+------+---------------+| id | user_id | name | ext | email |+----+---------+-------+------+---------------+| 0 | 0 | NULL | NULL | NULL || 1 | 1 | user1 | 2000 | user1@163.com || 2 | 2 | user2 | 2001 | user2@163.com || 3 | 3 | user3 | 2002 | user1@164.com || 4 | 4 | user4 | 2003 | user2@164.com |+----+---------+-------+------+---------------+5 rows in set (0.01 sec)
OK，完成；
重启下服务，查看错误日志，看是否有ERR；经查没有，OK，完成2018-01-21 13:06:42 127993 [Note] Plugin 'FEDERATED' is disabled.2018-01-21 13:06:42 127993 [Note] InnoDB: Using atomics to ref count buffer pool pages2018-01-21 13:06:42 127993 [Note] InnoDB: The InnoDB memory heap is disabled2018-01-21 13:06:42 127993 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2018-01-21 13:06:42 127993 [Note] InnoDB: Memory barrier is not used2018-01-21 13:06:42 127993 [Note] InnoDB: Compressed tables use zlib 1.2.32018-01-21 13:06:42 127993 [Note] InnoDB: Using Linux native AIO2018-01-21 13:06:42 127993 [Note] InnoDB: Using CPU crc32 instructions2018-01-21 13:06:42 127993 [Note] InnoDB: Initializing buffer pool, size = 64.0M2018-01-21 13:06:42 127993 [Note] InnoDB: Completed initialization of buffer pool2018-01-21 13:06:42 127993 [Note] InnoDB: Highest supported file format is Barracuda.2018-01-21 13:06:42 127993 [Note] InnoDB: 128 rollback segment(s) are active.2018-01-21 13:06:42 127993 [Note] InnoDB: Waiting for purge to start2018-01-21 13:06:42 127993 [Note] InnoDB: 5.6.36 started; log sequence number 18541452018-01-21 13:06:42 127993 [Note] Server hostname (bind-address): '0.0.0.0'; port: 33062018-01-21 13:06:42 127993 [Note] - '0.0.0.0' resolves to '0.0.0.0';2018-01-21 13:06:42 127993 [Note] Server socket created on IP: '0.0.0.0'.2018-01-21 13:06:42 127993 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=localhost-relay-bin' to avoid this problem.2018-01-21 13:06:42 127993 [Note] Event Scheduler: Loaded 0 events2018-01-21 13:06:42 127993 [Note] /tmp/mysql-5.6/bin/mysqld: ready for connections.Version: '5.6.36-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)2018-01-21 13:06:42 127993 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.2018-01-21 13:06:42 127993 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 02018-01-21 13:06:42 127993 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000003' at position 1205, relay log './localhost-relay-bin.000001' position: 4
我这是mysql 5.6版本，经测试mysql 5.7版本也是一样的方法，移动的时候多了个sys的库也要一起移走；其他一样；