0

The ibdata1 file has increased to ~2TB and leaves only a few GBs free space in the same disk (this is because I forgot to turn on innodb_file_per_table, before it was too late)

From what I have read in What is the best way to reduce the size of ibdata in mysql? the only way to reduce ibdata1 is to:

backup -> delete ibdata1 -> restore database.

Now, since the ibdata1 is so large, what's the best way to do it?
And how much time will it take?

I have two other free disks of 2TB size available which can be used for backup.

My thought is as follows: 

mysqldump current database into disk-x  
restore from disk-x to disk-y  
delete original ibdata1 in disk-z

Is this good way and how long will it take?

Improve this question
9
  • 1
    Does this answer your question? What is the best way to reduce the size of ibdata in mysql?
    – Akina
    Commented Dec 12, 2019 at 7:06
  • I've reffered the SO already in my question. but main point is a little different. basically I'm a little concerned since my db size is tooo large and i'm not confident how long it will take
    – dingx
    Commented Dec 12, 2019 at 7:12
  • The DB size doesn't matter. There is a lot of problems for you. And the main problem is that 2Gb backup will take muct time, and the restore will take TOO muct time. So you need a lot of hours for this process, and your server will be offline during this process.
    – Akina
    Commented Dec 12, 2019 at 7:23
  • If you want to deсrease the time needed for transfer you may backup structure separately, data into CSV (SELECT .. INTO OUTFILE) separately too. Structure backup then must be divided (by hands) into 2 files, moving indices-FKs-triggers into 3rd separate file in proper order. Restore performed with the order of structure-data-indices. This process may save hours... but it requires more careful preparation and execution. In this case, the backup of the structure and its division into two files can be performed without stopping the server.
    – Akina
    Commented Dec 12, 2019 at 7:23
  • @Akina thanks , why the server will be offline? do you mean backup will break the server? and my db has only data there, you can think of there are no FK triggeres, but yes it has indices.
    – dingx
    Commented Dec 12, 2019 at 7:45

1 Answer 1

Reset to default
1

You may need to think further ahead -- When reloading, will you have enough space?

Setting innodb_file_per_table does not necessarily provide any extra space.

When you do SHOW TABLE STATUS;, the "Data_free" will indicate how much free space there is in ibdata1. How big is that? If it is small, then something more needs to be done.

Is it possible in that OS to "extend" your current drive with one of those spare drives? That is can you, with an OS command and without dumping, extend the current "drive" to 4TB? If so, that is is probably worth doing to get started.

If you do backup, it will need to be to some other drive, either on the same server or on another server. (That is, do the dump across the 'network'.)

Improve this answer
3
  • Hi, my current solution is prepare two new disk(of 2TB), x and y; then : backup current database into disk-x and then restore the database into disky; when eveything is fine delete the original ibdata1 files. is this good way?
    – dingx
    Commented Dec 12, 2019 at 6:53
  • btw, is it possible to extend current partition with new disk drive
    – dingx
    Commented Dec 12, 2019 at 7:13
  • @DingxinXu - Some Operating systems allow such an "extend"; that is what I was suggesting.
    – Rick James
    Commented Dec 12, 2019 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.