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?

John K. N.
  • 17,649
  • 12
  • 51
  • 110
dingx
  • 101
  • 4
  • 1
    Does this answer your question? What is the best way to reduce the size of ibdata in mysql? – Akina Dec 12 '19 at 07: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 Dec 12 '19 at 07: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 Dec 12 '19 at 07: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 Dec 12 '19 at 07: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 Dec 12 '19 at 07:45
  • why the server will be offline? The method described by the link needs in drop databases and switch server off... From drop till restoring finish DBs will be unavailable. there are no FK triggeres, but yes it has indices INSERT + CREATE INDEX is more fast than CREATE INDEX + INSERT. Exclusion - primary index which must be created before insert (because it is clustered). – Akina Dec 12 '19 at 08:23
  • @Akina thanks, as i mentioned, i will drop the original db only after i restored it in new disk, so this is no offline now? – dingx Dec 12 '19 at 08:44
  • In such case you do not need in backuo at all, you may simply copy old database tables into new database, then kill all external connections except self and rename databases. DB will be offline during rename (2-3 second) and during server restart (for to delete ibdata file). Do not forget to set innodb_file_per_table before create new database. – Akina Dec 12 '19 at 08:46
  • Let us continue this discussion in chat. – dingx Dec 12 '19 at 08:54

1 Answers1

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'.)

Rick James
  • 78,038
  • 5
  • 47
  • 113
  • 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 Dec 12 '19 at 06:53
  • btw, is it possible to extend current partition with new disk drive – dingx Dec 12 '19 at 07:13
  • @DingxinXu - Some Operating systems allow such an "extend"; that is what I was suggesting. – Rick James Dec 12 '19 at 16:53