mysql中怎么删掉ibdata文件
发布时间：2022-02-10 16:08:19 所属栏目：MySql教程 来源：互联网
导读：本篇文章给大家分享的是有关mysql中怎么删除ibdata文件，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 在数据文件下的innodb ibdata包括表空间:ibdata1,ibdata2,回滚日志ib_logfile0,ib_
|
本篇文章给大家分享的是有关mysql中怎么删除ibdata文件，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 在数据文件下的innodb ibdata包括表空间:ibdata1,ibdata2,回滚日志ib_logfile0,ib_logfile1,ib_logfile2. [root@localhost data]# ls 5k72 db-bin.000015 dbjijin-bin.000001 ibdata1 ib_logfile1 localhost-slow.log mysql.err test auto.cnf db-bin.000016 dbjijin-bin.000002 ibdata2 ib_logfile2 monitor performance_schema test333 db-bin.000014 db-bin.index dbjijin-bin.index ib_logfile0 localhost.localdomain.pid mysql rrr ttt [root@localhost data]# 现在删除这几个文件: [root@localhost data]# rm -f ib* [root@localhost data]# ls 5k72 db-bin.000015 dbjijin-bin.000001 localhost-slow.log mysql.err test auto.cnf db-bin.000016 dbjijin-bin.000002 monitor performance_schema test333 db-bin.000014 db-bin.index dbjijin-bin.index localhost.localdomain.pid mysql rrr ttt [root@localhost data]# 删除之后,数据库还可以正常工作,切记不要停止mysql服务,不然神仙也难救..... 先找到mysql的pid. [root@localhost data]# netstat -ntlp|grep mysqld tcp 0 0 :::3307 :::* LISTEN 4863/mysqld tcp 0 0 :::3306 :::* LISTEN 2169/mysqld 第一个是我们需要的pid号,4863 通过pid号,找到相关的文件,红色标记的是我们需要的文件: [root@localhost data]# ll /proc/4863/fd total 0 lr-x------. 1 root root 64 Apr 24 10:58 0 -> /dev/null l-wx------. 1 root root 64 Apr 24 10:58 1 -> /home/mysql3307/data/mysql.err lrwx------. 1 root root 64 Apr 24 10:58 10 -> /home/mysql3307/data/ib_logfile0 (deleted) lrwx------. 1 root root 64 Apr 24 10:58 11 -> /home/mysql3307/data/ib_logfile1 (deleted) lrwx------. 1 root root 64 Apr 24 10:58 12 -> /home/mysql3307/data/ib_logfile2 (deleted) . .. ... lrwx------. 1 root root 64 Apr 24 10:58 4 -> /home/mysql3307/data/ibdata1 (deleted) .. ... .... lrwx------. 1 root root 64 Apr 24 10:58 9 -> /home/mysql3307/data/ibdata2 (deleted) 这时,需要暂停前端的业务,也就是需要停止数据的写操作. 关闭业务,或者: flush tables with read lock; 然后把脏页尽快刷入到磁盘里, mysql>set global innodb_max_dirty_pages_pct=0; 然后查看binlog日志写入情况,确保file和position的值没有变化 Your MySQL connection id is 7 Server version: 5.6.20-log Source distribution Copyright (c) 2000, 2014, 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. mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | db-bin.000016 | 3415 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | db-bin.000016 | 3415 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | db-bin.000016 | 3415 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | db-bin.000016 | 3415 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 然后查看innodb状态信息,确保脏页已经刷入磁盘. mysql> show engine innodb status G ************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2015-04-24 11:51:52 7f7038202700 INNODB MONITOR OUTPUT ------------ TRANSACTIONS ------------ Trx id counter 18696 Purge done for trx's n:o < 18696 undo n:o < 0 state: running but idle ##确保后台purge进程吧 undo log 全部清除掉,事务id要一致. ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges ## insert buffer 合并插入缓存等于1 --- LOG --- Log sequence number 5196495 Log flushed up to 5196495 Pages flushed up to 5196495 Last checkpoint at 5196495 ## 确保这4个值不会变 ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 1098907648; in additional pool allocated 0 Dictionary memory allocated 74136 Buffer pool size 65528 Free buffers 65121 Database pages 406 Old database pages 0 Modified db pages 0 ## 确保脏页数据为0 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 1964, id 140119885477632, state: sleeping Number of rows inserted 1, updated 0, deleted 0, read 4 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ## 确保插入,更新,删除为0 经过上面确认后,可以进行恢复操作了.复制上面表示部分数据到数据库目录: [root@localhost fd]# cp 4 /home/mysql3307/data/ibdata1 [root@localhost fd]# cp 10 /home/mysql3307/data/ib_logfile0 [root@localhost fd]# cp 11 /home/mysql3307/data/ib_logfile1 [root@localhost fd]# cp 12 /home/mysql3307/data/ib_logfile2 给文件授权: [root@localhost fd]# chown mysql:mysql /home/mysql3307/data/ib* 以上就是mysql中怎么删除ibdata文件，小编相信有部分知识点可能是我们日常工作会见到或用到的。 （编辑：好传媒网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
