vb mysql清理ibdata1_如何清理ibdata1

最新推荐文章于 2024-01-15 15:11:32 发布

张泰玩

最新推荐文章于 2024-01-15 15:11:32 发布

阅读量239

点赞数

文章标签： vb mysql清理ibdata1

本文链接：https://blog.csdn.net/weixin_33677704/article/details/113231630

版权

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]# 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

201Mibdata1

[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 ~]# 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;

+----+---------+-------+------+---------------+

+----+---------+-------+------+---------------+

| 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的库也要一起移走；其他一样；