如何清理ibdata1
1， 加锁，然后全备份数据，可以用mysqldump，也可以使用其他的工具：
[root@localhost data]# mysqldump --all-databases > /root/all_databases.sql2，修改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]# ls
a.sql b.sql ib_logfile0 itdb localhost-relay-bin.index mysql-bin.000001 mysql-bin.000004 mysql-bin.000007 percona test
auto.cnf db2.sql ib_logfile1 localhost.localdomain.pid master.info mysql-bin.000002 mysql-bin.000005 mysql-bin.000008 performance_schema
books 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]# ls
a.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.info
auto.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 restart
Shutting down MySQL.... SUCCESS!
Starting MySQL... SUCCESS!
[root@localhost data]# du -sh ibdata1
201M 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]# ls
auto.cnf ibdata1 ib_logfile1 itdb localhost-relay-bin.000004 master.info mysql-bin.000001 mysql-bin.000003 mysql-bin.index performance_schema test
books 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]# ls
auto.cnf ib_logfile0 ib_logfile2 localhost-relay-bin.000004 master.info mysql-bin.000002 mysql-bin.000004 performance_schema
ibdata1 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 itdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> 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 pages
2018-01-21 13:06:42 127993 [Note] InnoDB: The InnoDB memory heap is disabled
2018-01-21 13:06:42 127993 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-01-21 13:06:42 127993 [Note] InnoDB: Memory barrier is not used
2018-01-21 13:06:42 127993 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-01-21 13:06:42 127993 [Note] InnoDB: Using Linux native AIO
2018-01-21 13:06:42 127993 [Note] InnoDB: Using CPU crc32 instructions
2018-01-21 13:06:42 127993 [Note] InnoDB: Initializing buffer pool, size = 64.0M
2018-01-21 13:06:42 127993 [Note] InnoDB: Completed initialization of buffer pool
2018-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 start
2018-01-21 13:06:42 127993 [Note] InnoDB: 5.6.36 started; log sequence number 1854145
2018-01-21 13:06:42 127993 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2018-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 events
2018-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: 0
2018-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的库也要一起移走；其他一样；
