mysql --innodb_file_per_table

Question

I have a simple question regarding access to mysql via command. When I tried to access it as a root it bounce back a message:

mysql: unknown option '--innodb_file_per_table'

How would it get it back to work?

Thanks Rick. I have managed gain access and the server is working what worries me is bunch of errors during start/stop/restart

error.log

2020-12-17 19:16:40 0 [Note] InnoDB: Using Linux native AIO
2020-12-17 19:16:40 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-12-17 19:16:40 0 [Note] InnoDB: Uses event mutexes
2020-12-17 19:16:40 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-12-17 19:16:40 0 [Note] InnoDB: Number of pools: 1
2020-12-17 19:16:40 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-12-17 19:16:40 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-12-17 19:16:40 0 [Note] InnoDB: Completed initialization of buffer pool
2020-12-17 19:16:40 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority       ().
2020-12-17 19:16:40 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-12-17 19:16:40 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-12-17 19:16:40 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-12-17 19:16:40 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-12-17 19:16:40 0 [Note] InnoDB: 10.3.27 started; log sequence number 8459955162; transaction id 23688770
2020-12-17 19:16:40 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2020-12-17 19:16:40 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-12-17 19:16:40 0 [Note] Server socket created on IP: '127.0.0.1'.
2020-12-17 19:16:40 0 [Note] Reading of all Master_info entries succeeded
2020-12-17 19:16:40 0 [Note] Added new Master_info '' to hash table
2020-12-17 19:16:40 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.27-MariaDB-0+deb10u1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Debian 10
2020-12-17 19:16:40 0 [Note] InnoDB: Buffer pool(s) load completed at 201217 19:16:40

It doesn't make any sense to give server options to the client. What are you trying to do? — Michael Hampton, Dec 16 '20 at 16:29

score 0 · Answer 1 · answered Dec 17 '20 at 02:32

Plan A (messy and not recommended): Add that option to mysqld (the server program, not mysql, a client program).

Plan B: Change the setting in my.cnf (or wherever the config file is). Then restart the server. (The restart is OS-dependent.)

Plan C: Login as mysql's "root" user, do SET GLOBAL innodb_file_per_table = 1;. Henceforth (until you logout), tables that _this connection CREATEs will be file-per-table. Also, since that setting is "global", it will apply to others who login after doing the SET and before then next restart (or you turn off the flag).

Plan D: (In MySQL 8.0 only) Root can do the SET with a PERSIST option. (But I won't go into the details.)

Caveat: Various settings have various rules. These 4 Plans work for some settings. Fewer or more Plans apply to other settings. One clue is the documentation says whether the setting is "global" or "session" or both.

mysql --innodb_file_per_table

1 Answers1