mysqld and options Flashcards

1
Q

mysqld reads options from

A

[mysqld] and [server] groups

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

mysqld_safe reads options from

A

the [mysqld], [server], [mysqld_safe], and [safe_mysqld] groups.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

mysql.server reads options from

A

[mysqld] and [mysql.server] groups.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

To assign a value to a server system variable

A

use an option of the form –var_name=value.

When you assign a value to a variable, MySQL might automatically correct the value to stay within a given range, or adjust the value to the closest permissible value if only certain values are permitted.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

To restrict the maximum value to which a system variable

A

set at runtime with the SET statement, specify this maximum by using an option of the form –maximum-var_name=value at server startup.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Which option:

Displays a short help message and exit.

A

–help

Use both the –verbose and –help options to see the full message

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Which option:

Controls whether loadable functions that have only an xxx symbol for the main function can be loaded.

A

–allow-suspicious-udfs[={OFF | ON}]

By default, the option is off and only loadable functions that have at least one auxiliary symbol can be loaded

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Which option:

Enable standard (ANSI) SQL syntax instead of MySQL syntax.

A

–ansi

For more precise control over the server SQL mode, use the –sql-mode option instead.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which option:

sets the basedir system variable.

A
  • -basedir=dir_name
  • b dir_name

Default Value: configuration-dependent default
Dynamic: No
Scope: Global

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Which option:

creates the MySQL privilege tables without having to start a full MySQL server.

A

–bootstrap (deprecated)

This option is used by the mysql_install_db program.

–bootstrap is mutually exclusive with –daemonize, –initialize, and –initialize-insecure.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

True or false:

mysql_install_db is deprecated because its functionality has been integrated into mysqld,

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Which option:

initializes a MySQL installation

A

–initialize or –initialize-insecure

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Which option:

Invokes do not ignore character set information sent by the client

A

–character-set-client-handshake[={OFF|ON}]

Default Value: ON

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Which option:

Puts the mysqld server in a closed environment during startup by using the chroot() system call. This is a recommended security measure.

A

–chroot=dir_name

Use of this option somewhat limits LOAD DATA and SELECT … INTO OUTFILE.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Which option:

(Windows only.) Writes the error log to stderr and stdout (the console).

A

–console

–console takes precedence over –log-error if both are given.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Which 3 are the options for Windows OS only:

  • -console
  • -default-time-zone=name
  • -install [service_name]
  • -install-manual [service_name]
A
  • -console
  • -install [service_name]
  • -install-manual [service_name]
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Which 3 are the options for Windows OS only:

  • -local-service
  • -remove [service_name]
  • -skip-stack-trace
  • -standalone
A
  • -local-service
  • -remove [service_name]
  • -standalone
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Which option:

Writes a core file if mysqld dies.

A

–core-file[={OFF | ON}]

The name and location of the core file is system dependent.
Default Value: OFF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Which option:

Causes the server to run as a traditional, forking daemon, permitting it to work with operating systems that use systemd for process control.

A

–daemonize[={OFF | ON}]

–daemonize is mutually exclusive with –bootstrap, –initialize, and –initialize-insecure.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Which option:

sets the datadir system variable.

A

–datadir=dir_name

Dynamic: No
Scope: Global

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Which option:

If MySQL is configured with the -DWITH_DEBUG=1 CMake option, you can use this option to get a trace file of what mysqld is doing.

A

–debug[=debug_options]

Dynamic: Yes
Default Value (Unix)	d:t:i:o,/tmp/mysqld.trace
Default Value (Windows)	d:t:i:O,\mysqld.trace
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Which option:

Controls whether the Debug Sync facility for testing and debugging is enabled.

A

–debug-sync-timeout[=#]

Use of Debug Sync requires that MySQL be configured with the -DENABLE_DEBUG_SYNC=1 CMake option

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Which option:

Sets the default server time zone. This option sets the global time_zone system variable.

A

–default-time-zone=name

If this option is not given, the default time zone is the same as the system time zone (given by the value of the system_time_zone system variable.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Which option:

Directs server to read this option file after the global option file but (on Unix) before the user option file.

A

–defaults-extra-file=file_name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Which option:

Directs server to read not only the usual option groups, but also groups with the usual names and a suffix of str.

A

–defaults-group-suffix=str

If this option is given as –defaults-group-suffix=_other, mysqld also reads the [mysqld_other] group.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Which option:

Directs server to read only the given option file.

A

–defaults-file=file_name

If file_name is not an absolute path name, it is interpreted relative to the current directory.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Which option:

Directs server to read the default DES keys from this file

A

–des-key-file=file_name

Deprecated: Yes
These keys are used by the DES_ENCRYPT() and DES_DECRYPT() functions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Which option:

Disables the startup check for tables with nonnative partitioning.

A

–disable-partition-engine-check[={OFF | ON}]

Default Value: (≥ 5.7.21) ON
Default Value: (≥ 5.7.17, ≤ 5.7.20) OFF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Which option:

tells the server which plugins to load before loading mandatory built-in plugins and before storage engine initialization.

A

–early-plugin-load=plugin_list

The option value is a semicolon-separated list of plugin_library and name=plugin_library values.

If the server is started using –initialize or –initialize-insecure, plugins specified by –early-plugin-load are not loaded.

Default Value (≥ 5.7.12):	empty string
Default Value (5.7.11):	keyring_file plugin library file name
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Which option:

is a bitmask of different flags that you can use for debugging the mysqld server.

A

–exit-info[=flags]

Type: Integer

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Which option:

Enables external locking (system locking), which is disabled by default.

A

–external-locking[={OFF | ON}]

Default Value OFF

If you use this option on a system on which lockd does not fully work (such as Linux), it is easy for mysqld to deadlock.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Which option:

Flushes (synchronize) all changes to disk after each SQL statement.

A

–flush[={OFF | ON}]

If –flush is specified, the value of flush_time does not matter and changes to flush_time have no effect on flush behavior.

Scope: Global
Dynamic Yes
Default Value OFF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Which option:

Installs an interrupt handler for SIGINT (needed to stop mysqld with ^C to set breakpoints) and disables stack tracing and core file handling

A

–gdb[={OFF | ON}]

Default Value OFF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

Which option:

tells the server to ignore the given directory name for purposes of the SHOW DATABASES statement or INFORMATION_SCHEMA tables.

A

–ignore-db-dir=dir_name

Deprecated 5.7.16

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

Which option:

initializes a MySQL installation by creating the data directory and populating the tables in the mysql system database.

A

–initialize[={OFF | ON}]

Default Value OFF

–initialize is mutually exclusive with –bootstrap and –daemonize.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

Which option:

initializes a MySQL installation by creating the data directory and populating the tables in the mysql system database.

A

–initialize-insecure[={OFF | ON}]

This option implies –initialize.

37
Q

Which option:

Sets an option for the InnoDB storage engine.

A

–innodb-xxx

38
Q

Which option:

(Windows only) Installs the server as a Windows service that starts automatically during Windows startup.

A

–install [service_name]

The default service name is MySQL if no service_name value is given.

If the server is started with the –defaults-file and –install options, –install must be first.

39
Q

Which option:

(Windows only) Installs the server as a Windows service that must be started manually.

A

–install-manual [service_name]

It does not start automatically during Windows startup.

The default service name is MySQL if no service_name value is given.

If the server is started with the –defaults-file and –install-manual options, –install-manual must be first.

40
Q

Which option:

Specifies the locale to use for error messages.

A

–lc-messages=name

Default Value en_US
Dynamic Yes

41
Q

Which option:

Specifies the directory where error messages are located.

A

–lc-messages-dir=dir_name

Dynamic No
Scope Global

42
Q

Which option:

(Windows only) is a –local-service option following the service name causes the server to run using the LocalService Windows account that has limited system privileges.

A

–local-service

If both –defaults-file and –local-service are given following the service name, they can be in any order

43
Q

Which option:

Writes the error log and startup messages to this file.

A

–log-error[=file_name]

If the option names no file, the error log file name on Unix and Unix-like systems is host_name.err in the data directory.

Scope Global
Dynamic No

44
Q

Which option:

Logs all MyISAM changes to this file (used only when debugging MyISAM).

A

–log-isam[=file_name]

45
Q

Which option:

Suppresses password rewriting for the general query log by starting the server with this option

A

–log-raw[={OFF | ON}]

Default Value OFF

If a query rewrite plugin is installed, the server logs the original statement as received.

46
Q

Which option:

Logs less information to the slow query log, if it has been activated.

A

–log-short-format[={OFF | ON}]

Default Value OFF

47
Q

Which option:

names of the memory-mapped transaction coordinator log file (for XA transactions that affect multiple storage engines when the binary log is disabled).

A

–log-tc=file_name

Default Value tc.log

48
Q

Which option:

Specifies the size in bytes of the memory-mapped transaction coordinator log.

A

–log-tc-size=#

Default Value (64-bit platforms, ≥ 5.7.21)	6 * page size
Default Value (64-bit platforms, ≤ 5.7.20)	24576
Default Value (32-bit platforms, ≥ 5.7.21)	6 * page size
Default Value (32-bit platforms, ≤ 5.7.20)	24576
49
Q

Which option:

Enables the production of additional warning messages to the error log.

A

–log-warnings[=#]

Default Value 2

To disable it, use –log-warnings=0

50
Q

Which option:

Locks the mysqld process in memory.

A

–memlock[={OFF | ON}]

Default Value OFF

Use of this option may require you to run the server as root, which, for reasons of security, is normally not a good idea

51
Q

Which option:

Specifies the block size to be used for MyISAM index pages.

A

–myisam-block-size=#

Default Value 1024

52
Q

Which option:

Tells the server to not read any option files.

A

–no-defaults

53
Q

Which option:

Enables old-style user limits.

A

–old-style-user-limits[={OFF | ON}]

Default Value OFF

54
Q

Which option:

Enables or disables user-defined partitioning support in the MySQL Server.

A

–partition[={OFF | ON}]

This option is deprecated in MySQL 5.7.16, and is removed from MySQL 8.0 because in MySQL 8.0, the partitioning engine is replaced by native partitioning, which cannot be disabled.

55
Q

Which option:

Configures a Performance Schema option.

A

–performance-schema-xxx

56
Q

Which option:

tells the server to load the named plugins at startup

A

–plugin-load=plugin_list

Dynamic No
Scope Global

57
Q

Which option:

adds a plugin or plugins to the set of plugins to be loaded at startup.

A

–plugin-load-add=plugin_list

Scope Global
Dynamic No

58
Q

Which option:

Specifies an option that pertains to a server plugin

A

–plugin-xxx

options for them can be specified with a –plugin prefix

For boolean options that can be enabled or disabled,

59
Q

Which option:

to use when listening for TCP/IP connections

A

–port=port_num

Scope Global
Dynamic No

Default Value 3306

60
Q

Which option:

indicates how many seconds the server should wait for the TCP/IP port to become free if it cannot be opened.

A

–port-open-timeout=#

Default Value 0

61
Q

Which option:

Prints the program name and all options that it gets from option files.

A

–print-defaults

must be the first option on the command line if it is used,

62
Q

Which option:

(Windows only) Removes a MySQL Windows service.

A

–remove [service_name]

The default service name is MySQL if no service_name value is given

63
Q

Which option:

If enabled, a user cannot create new MySQL users by using the GRANT statement unless the user has the INSERT privilege for the mysql.user system table or any column in the table.

A

–safe-user-create[={OFF | ON}]

Default Value OFF

64
Q

Which option:

causes the server not to read the grant tables in the mysql system database, and thus to start without using the privilege system at all.

A

–skip-grant-tables[={OFF | ON}]

Default Value OFF

65
Q

Which option:

Disables use of the internal host cache for faster name-to-IP resolution.

A

–skip-host-cache

Starting the server with –skip-host-cache does not prevent runtime changes to the value of host_cache_size, but such changes have no effect and the cache is not

66
Q

Which option:

disables (what used to be considered) new, possibly unsafe behaviors

A

–skip-new

It results in these settings: delay_key_write=OFF, concurrent_insert=NEVER, automatic_sp_privileges=OFF.

67
Q

Which option:

Disables user-defined partitioning.

A
  • -skip-partition
  • -disable-partition

Deprecated 5.7.16

68
Q

Which option:

sets the system variable that controls who is permitted to use the SHOW DATABASES statement

A

–skip-show-database

Default Value OFF
Dynamic No

69
Q

Which option:

Tells the server to not write stack traces

A

–skip-stack-trace

On some systems, you also must use this option to get a core file.

70
Q

Which option:

controls the Windows service control manager’s service start timeout.

A

–slow-start-timeout=#

Default Value 15000

71
Q

Which option:

specifies the Unix socket file to use when listening for local connections.

A

–socket={file_name|pipe_name}

Scope Global
Dynamic No

Default Value (Other) /tmp/mysql.sock

72
Q

Which option:

Set the SQL mode.

A

–sql-mode=name

Scope Global, Session
Dynamic Yes

73
Q

Which 3 are default SQL mode options:

ONLY_FULL_GROUP_BY
IGNORE_SPACE
STRICT_TRANS_TABLES
NO_ZERO_IN_DATE

A

ONLY_FULL_GROUP_BY
STRICT_TRANS_TABLES
NO_ZERO_IN_DATE

74
Q

Which 2 are default SQL mode options:

NO_DIR_IN_CREATE
NO_ZERO_DATE
ERROR_FOR_DIVISION_BY_ZERO

A

NO_ZERO_DATE

ERROR_FOR_DIVISION_BY_ZERO

75
Q

Which 2 are default SQL mode options:

HIGH_NOT_PRECEDENCE
NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION

A

NO_AUTO_CREATE_USER

NO_ENGINE_SUBSTITUTION

76
Q

Which option:

specifies that the server permits but does not require encrypted connections

A

–ssl[={OFF | ON}]

in negated form as –skip-ssl or a synonym (–ssl=OFF, –disable-ssl).

Disabled by skip-ssl
Default Value ON

77
Q

Which option:

Available on Windows only; instructs the MySQL server not to run as a service.

A

–standalone

78
Q

Which option:

(Solaris Only) enables uses of pages up to 256MB.

A

–super-large-pages[={OFF | ON}]

Default Value OFF

79
Q

Which option:

Enables symbolic link support.

A

–symbolic-links[={OFF | ON}]

in negated form as –skip-symbolic-links

Default Value ON

80
Q

Which option:

causes SYSDATE( ) to be a synonym for NOW( ).

A

–sysdate-is-now[={OFF | ON}]

Default Value OFF

SYSDATE( ) by default returns the time at which it executes, not the time at which the statement in which it occurs begins executing.

81
Q

Which option:

If specified, the server exits regardless of whether manual heuristic recovery is successful.

A

–tc-heuristic-recover=name

Default Value OFF
Valid Values OFF, COMMIT, ROLLBACK

the ROLLBACK option is not safe and causes recovery to halt with the following error:

82
Q

Which option:

On Linux, it causes most temporary files created by the server to use a small set of names, rather than a unique name for each new file.

A

–temp-pool[={OFF | ON}]

Deprecated	5.7.18 and is removed in MySQL 8.0.
Default Value (Linux)	ON
Default Value (Other)	OFF
83
Q

Which option:

Sets the default transaction isolation level.

A

–transaction-isolation=name

Scope (≥ 5.7.20) Global, Session
Dynamic (≥ 5.7.20) Yes

Default Value REPEATABLE-READ

Valid Values READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

84
Q

Which –transaction-isolation value (READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE):

reads are not consistent. This is also called a dirty read.

A

READ-UNCOMMITTED

SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used.

85
Q

Which –transaction-isolation value (READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE):

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.

A

READ-COMMITTED

86
Q

Which –transaction-isolation value (READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE):

Consistent reads within the same transaction read the snapshot established by the first read

A

REPEATABLE-READ

This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other

87
Q

Which –transaction-isolation value (READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE):

InnoDB implicitly converts all plain SELECT statements to SELECT … LOCK IN SHARE MODE if autocommit is disabled.

A

SERIALIZABLE

88
Q

Which option:

Sets the default transaction access mode.

A

–transaction-read-only[={OFF | ON}]

Scope (≥ 5.7.20) Global, Session
Dynamic (≥ 5.7.20) Yes
Default Value OFF

89
Q

Which option:

specifies the path of the directory to use for creating temporary files

A

–tmpdir=dir_name

Scope Global
Dynamic No