User management Flashcards
What does the field POSSIBLE KEYS mean?
mysql> explain select * from store limit 3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: store
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
The possible_keys column indicates the indexes from which MySQL can choose to find the rows in this table.
shows the keys that can be used by MySQL to find rows from the table, though they may or may not be used in practice. In fact, this column can often help in optimizing queries since if the column is NULL, it indicates no relevant indexes could be found.
What does the field KEY mean?
mysql> explain select * from store where manager_staff_id = 1 limit 3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: store
partitions: NULL
type: const
possible_keys: idx_unique_manager
key: idx_unique_manager
key_len: 1
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
key – indicates the actual index used by MySQL. This column may contain an index that is not listed in the possible_key column. MySQL optimizer always look for an optimal key that can be used for the query. While joining many tables, it may figure out some other keys which is not listed in possible_key but are more optimal.
What does the ‘null’ in possible_keys mean?
mysql> explain select manager_staff_id from store limit 3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: store
partitions: NULL
type: index
possible_keys: NULL
key: idx_unique_manager
key_len: 1
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
Null indicates no relavant indexes could be found for this query, which could make sense seeing you have no where clause.
explain format=json select * from table;
The FORMAT option can be used to select the output format. TRADITIONAL presents the output in tabular format. This is the default if no FORMAT option is present. JSON format displays the information in JSON format.
This extra formatting gives more information.
mysql> explain format=json select manager_staff_id from store limit 3 \G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.40"
},
"table": {
"table_name": "store",
"access_type": "index",
"key": "idx_unique_manager",
"used_key_parts": [
"manager_staff_id"
],
"key_length": "1",
"rows_examined_per_scan": 2,
"rows_produced_per_join": 2,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.40",
"prefix_cost": "1.40",
"data_read_per_join": "32"
},
"used_columns": [
"manager_staff_id"
]
}
}
}
1 row in set, 1 warning (0.00 sec)Which privledges must a user have to run ‘explain’ on a query?
a) Must have SUPER
b) Must have privilege’s to select/delete/insert/update/replace on the objects being explained
c) Must have show view privileges
d) Must have explain privileges
b) and c)
EXPLAIN requires the same privileges required to execute the explained statement. Additionally, EXPLAIN also requires the SHOW VIEW privilege for any explained view.
How can you run the explain on another session?
a) Explain session
b) Explain thread_id
c) Explain for connection
d) Explain connection
c)
EXPLAIN [options] FOR CONNECTION connection_id;
EXPLAIN FOR CONNECTION returns the EXPLAIN information that is currently being used to execute a query in a given connection. Because of changes to data (and supporting statistics) it may produce a different result from running EXPLAIN on the equivalent query text. This difference in behavior can be useful in diagnosing more transient performance problems. For example, if you are running a statement in one session that is taking a long time to complete, using EXPLAIN FOR CONNECTION in another session may yield useful information about the cause of the delay.
You have forgotten the root user account password. You decide to reset the password and execute the following:
Shell> /etc/init.d/mysql stop
Shell> /etc/init.d/mysql start –skip-grant tables
Which additional argument makes this operation safer?
a) –old-passwords, to start MySQL to use the old password format while running without the grant tables
b) –read-only, to set all data to read-only except for super users
d) –skip-networking, to prohibit access from remote locations
c)
. –skip-networking, to prohibit access from remote locations
You started your server with the parameter: –skip-grant-tables
/usr/local/mysql/bin/mysqld –user=_mysql –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –skip-grant-tables
Who will have access to the server?
a) All users
b) Only root
c) SUPER users
a)
All users
What does skip grant tables mean?
Using –skip-grant-tables
In this method, you stop the server and start it by specifying –skip-grant-tables , which will not load the grant tables. You can connect to the server as root without a password and set the password.
If the server is started with the –skip-grant-tables option, it does not read the grant tables or implement any access control. Any user can connect and perform any operation, which is insecure. To cause a server thus started to read the tables and enable access checking, flush the privileges.
Which two methods will show the count of partitions for the Country table? (Choose two.)
A. SHOW CREATE TABLE COUNTRY;
B. SELECT * FROM information_schema.PARTITIONS WHERE TABLE_NAME=’COUNTRY’;
C. SELECT * FROM performance_schema.PARTITIONS WHERE TABLE_NAME=’COUNTRY’;
D. SHOW STATUS TABLE COUNTRY;
A) and B)
A. SHOW CREATE TABLE COUNTRY;
B. SELECT * FROM information_schema.PARTITIONS WHERE TABLE_NAME=’COUNTRY’;
You have created a new user with this statement:
CREATE USER ‘erika’@’localhost’ IDENTIFIED BY ‘first#1Pass’ PASSWORD EXPIRE; What is the outcome?
A. When ‘erika’@’localhost’tries to log in with the MySQL command-line client, the user will be permitted to log in but will not be able to issue any statements until the user changes the password.
B. You receive a syntax error that indicates that you cannot set a password and expire it at the same time.
C. When ‘erika’@’localhost’tries to log in with the MySQL command-line client, the user will have to change the password before seeing the mysql>prompt.
D. When ‘erika’@’localhost’tries to log in with the MySQL command-line client, the user will not be permitted to log in because the password is expired.
answer A
To satisfy a security requirement, you have created or altered some user accounts to include REQUIRE X509.
Which additional task needs to be performed for those user accounts to fulfill the requirement to use X509?
A. Restart the server with the –require-x509option.
B. Set the X509 option in the [client] section of the MySQL server’s configuration file.
C. Distribute client digital certificates to the client computers being used to log in by the user accounts.
D. Install the X509plug-in on the server.
E. Provide users access to the server’s private key.
Correct Answer: B
but research this as x509 needs certificate
