Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to delete ibdata files in mysql, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

The innodb ibdata under the data file includes tablespace: ibdata1,ibdata2, rollback log 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] #

Now delete these files:

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

After deletion, the database can also work normally, remember not to stop the mysql service, otherwise the immortals can not be saved.

First find mysql's pid.

[root@localhost data] # netstat-ntlp | grep mysqld

Tcp 0 0: 3307: * LISTEN 4863/mysqld

Tcp 0 0: 3306: * LISTEN 2169/mysqld

The first one is the pid number we need, 4863.

Through the pid number, find the relevant files, marked in red are the files we need:

[root@localhost data] # ll / proc/4863/fd

Total 0

Lr-x-. 1 root root 64 Apr 24 10:58 0-> / dev/null

L / w / w 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)

At this time, the front-end business needs to be suspended, that is, the data write operation needs to be stopped.

Close the business, or: flush tables with read lock

Then brush the dirty pages into the disk as soon as possible.

Mysql > set global innodb_max_dirty_pages_pct=0

Then check the binlog log writes to make sure that the values of file and position do not change

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)

Then check the innodb status information to make sure the dirty page has been brushed to disk.

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 NRO < 18696 undo NRO < 0 state: running but idle

# # make sure that the undo log is cleared by the background purge process, and the transaction id is consistent.

-

INSERT BUFFER AND ADAPTIVE HASH INDEX

-

Ibuf: size 1, free list len 0, seg size 2, 0 merges

# # insert buffer merge insert cache equals 1

-

LOG

-

Log sequence number 5196495

Log flushed up to 5196495

Pages flushed up to 5196495

Last checkpoint at 5196495

# # make sure that these four values do not change

--

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

# # make sure the dirty page data is 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

# # make sure to insert, update and delete to 0

After the above confirmation, the recovery operation can be carried out. Copy some of the data shown above to the database directory:

[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

To authorize a file:

[root@localhost fd] # chown mysql:mysql / home/mysql3307/data/ib*

The above is how to delete the ibdata file in mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.