How to alter a table in large mysql DataBase

I have very large mysql DB(more than 100 G) and I want to migrate some changes in table. It need to posing that I have no space to make backup for this size, as results, it rolls back all changes. In clear way, when I want to alter table in mysql, it backup for it self in local disk and because of no space on my disk, it 's rolling back. Is there any one to help for this issue?

Solution 1:

It depends on the type of ALTER change. Since MySQL 5.6, some alterations can be done "inplace" which does not use extra storage. Read https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html for details.

But many types of alterations (basically any change that affects the storage size of a row), still require a "table rebuild" which means it makes a copy of the table in the process of doing the alter. This requires extra storage, usually about the same as the size of the original table. If your server does not have enough storage space to do this, then you cannot alter the table, FULL STOP. You should have addressed this long before the table grew to this size. It is your responsibility to monitor the size of the database and make sure you have enough storage space.

You said in the comments above that it is not possible to add more storage space.

Sometimes InnoDB tablespaces can become "fragmented." That means they could store the same rows using slightly less storage space. You can accomplish defragmentation with OPTIMIZE TABLE <name>; or ALTER TABLE <name> FORCE;

A possible strategy is to optimize each of your tables, starting with the smallest table and doing one by one in order of size. You hope that you have enough space for the copy table needed to do this, and any space freed up by optimizing the small tables will help you to optimize the medium-size tables, and so on. This is a long shot, because it's also possible you don't have enough "wasted" space to recover to make a difference. You may still not be able to alter your largest tables once you're all done with this process. Unfortunately, MySQL has no way of reporting fragmentation of the tables, so you can only try it and see how much space, if any, you recover.

After that, the only other option is to delete data from the table until you can do the alteration. The alter still requires extra storage, but only as much as needed to copy the remaining rows of data. That is, if you delete 50% of the rows, then the tablespace file that stores the copy only needs roughly 50% of the storage space.

You can also try dropping some of the indexes of the table as you do your alteration. Indexes take space in the same tablespace, and it's not uncommon if you have multiple indexes for them to take more space than the data itself. If you include some DROP INDEX operations in your ALTER TABLE statement, the resulting copy will take less space. On the other hand, those indexes might be necessary for proper query optimization, and without the indexes, your application won't be usable.