Mysql Multiple data files rollover size
((100G is not the same as 100M.  typo was fixed))

The line you gave is what you do -- in my.cnf.  (Double check the syntax.)

That configuration was very important 15 years ago -- when some operating systems could not create a file bigger than 2GB (or 4GB).  Now is is just an artifact.  Most people simply have autoextend on the last entry and forget about it.

The config says, as you say, "fill up one before moving on to the next".  The number (eg, "100M") is the pre-allocated size.

Why do you want this config?  I am curious as to whether you have a use case for current machines.

It is usually better to have innodb_file_per_table=ON.  Each table that you subsequently CREATE or ALTER is put into it's own "tablespace", which is identified by a file: &lt;tablename&gt;.ibd.  At that point, you don't need a huge, growing, set of ibdata* files.  (Leave autoextend on the last one, just in case.)

Limitations... There is no way to have that config automatically add another ibdata* file name, only autoextend the last one.  Once created, you can't get rid of any of the files without dumping and reloading.

EDIT. "but we have many dynamically created tables per second" -- Ouch!  How many tables do you have?  How tiny are they?  In general, thousands of tables is bad.  Might it make sense to combine tables in some way?  Would you care to elaborate on the design that 'needs' lots of tables?

Lots of tiny tables are better off in ibdata* (file_per_table = OFF).  The OS overhead for more files is non-zero.  (For big tables, the maintenance benefit of .ibd wins.)  Regardless of the setting, there will be a .frm file per table.

Yes, lots of ibdata* files would let you migrate some to new drives.  Use "symbolic links" (if unix) or the equivalent if Windows (Junctions?).