Udemy- Test #4 Q's Flashcards
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. –read-only, to set all data to read-only except for super users
B. –old-passwords, to start MySQL to use the old password format while running without the grant tables
c. –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. Only root
B. All users
B. All users
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. SHOW CREATE TABLE COUNTRY;
B. SELECT * FROM information_schema.PARTITIONS WHERE TABLE_NAME=’COUNTRY’;
Consider two tables that are equally defined, except for the partitioning method, and have two million records in each table.
The following query is executed and takes 2 minutes and 38 seconds to complete.
ALTER TABLE orders_hash ADD PARTITION p6;
You then execute the following query, which takes 13 seconds to complete;
ALTER TABLE orders_linear_key ADD PARTITION p6;
What could explain the difference in completion time?
A. The HASH algorithm is computationally harder than the LINEAR KEY algorithm.
B. The LINEAR KEY method changes fewer partitions
C. The HASH algorithm requires a file sort of the index before updating.
B. The LINEAR KEY method changes fewer partitions
Which two statement are true about the mysql upgrade command?
A. The mysql_upgade command is a utility that patches the mysql binary from its base version to a new version
B. The mysql upgrade command is run to check and attempt to fix tables for certain incompatibilities with the current version of MySQL
C. The mysql upgrade command executes on a stopped MySQL server data directory to ensure that it is prepared for upgrades.
D. The mysql upgrade command also executes the mysqlcheck command in order to provide all of its functionality.
B. The mysql upgrade command is run to check and attempt to fix tables for certain incompatibilities with the current version of MySQL
D. The mysql upgrade command also executes the mysqlcheck command in order to provide all of its functionality.
How does the InnoDB storage engine handle deadlocks when they are detected? (Choose one.)
A. The affected transaction wait for innodb_lock_wait_timeout seconds, and then roll back.
B. The transaction isolation level determines which transaction is rolled back.
C. One of the affected transactions will be rolled backed, the other is allowed to proceed.
D. Both the affected transactions will be rolled back.
E. The innodb_locks_unsafe_for_binlog setting determines which transaction is rolled back.
C. One of the affected transactions will be rolled backed, the other is allowed to proceed.
Explanation
When deadlock detection is enabled (the default) and a deadlock does occur, InnoDB detects the condition and rolls back one of the transactions (the victim). If deadlock detection is disabled using the innodb_deadlock_detect configuration option, InnoDB relies on the innodb_lock_wait_timeout setting to roll back transactions in case of a deadlock.
You want to lock the three MYISAM tables a,b and c.
You issue the following statements:
Mysql> LOCK TABLES a READ;
Mysql> LOCK TABLES b READ;
Mysql> LOCK TABLES c READ;
What is the result? (Choose one)
A. Table a, b, c are all locked.
B. Only the lock on table a takes effect.
C. Only the lock on table c takes effect.
D. None of the tables are locked.
C. Only the lock on table c takes effect.
Why should you be selective when granting the PROCESS privilege to an account?
A. It allows a client to process scripts.
B. It allows the use of stored routines.
C. It allows a client to see another user’s queries with the SHOW PROCESSLIST command.
D. It allows a client to control running processes on a server.
C. It allows a client to see another user’s queries with the SHOW PROCESSLIST command.
Explanation
The PROCESS privilege controls access to information about threads executing within the server (that is, information about statements being executed by sessions).
Thread information available using the SHOW PROCESSLIST statement, the mysqladmin processlist command, the INFORMATION_SCHEMA.PROCESSLIST table, and the Performance Schema processlist table is accessible as follows:
With the PROCESS privilege, a user has access to information about all threads, even those belonging to other users.
Without the PROCESS privilege, nonanonymous users have access to information about their own threads but not threads for other users, and anonymous users have no access to thread information.
You want to dump only data from the userdata table.
Which mysqldump command argument is required to accomplish this?
A. –no-create-info to skip writing CREATE TABLE statements
B. –single-transaction as this obtains a consistent view of data only
C. –data-only as this specifies that only data is to be dumped
D. –table=userdata in order to dump only the data from the userdata table
A. –no-create-info to skip writing CREATE TABLE statements
You need to dump the data from the master server and import it into a new slave server.
Which mysqldump option can be used when dumping data from the master server in order to include the master server’s binary log information?
A. include-log-file
B. master-binlog
C. master-data
C. master-data
While attempting to set up a new replication slave on host ‘192.168.0.25’ with the user ‘replication’, you encounter this error:
ERROR 1218 (08S01): error connecting to master: Host ‘192.168.0.25’ is not allowed to connect to this MySQL server.
What should you do to resolve this error?
A. Add the user replication@192.168.0.25 with the correct password to the master.
B. Edit the DNS table on the master to include the domain name for the IP address of 192.168.0.25
C. Edit the my.ini file on the slave so that the master-host variable is equal to the IP address of the master, and restart the slave.
D. Add the user replication@192.168.0.25 with the correct password to the slave.
A. Add the user replication@192.168.0.25 with the correct password to the master.
Which two capabilities are granted with the SUPER privilege?
A. allowing change of the server runtime configuration
B. allowing a client to shut down the server
C. allowing client accounts to take over the account of another user
D. allowing a client to kill other client connections
A. allowing change of the server runtime configuration
D. allowing a client to kill other client connections
You are remotely logged in to MySQL as the user “backup”.
The account has temporarily been granted full privileges WITH GRANT to perform the actions required. You want to change the password for the locally existing “backup” user.
Select the three commands which will perform the required action. (Choose three.)
A. SET PASSWORD FOR ‘backup’@’localhost’=’password’;
B. SET PASSWORD =’password’;
C. SET PASSWORD FOR ‘backup’@’localhost’= PASSWORD (‘password’);
D. ALTER USER USER() INDENTIFIED BY ‘password’;
E. CREATE USER ‘backup’@’localhost’ IDENTIFIED BY ‘password’;
F. ALTER USER ‘backup’ IDENTIFIED BY ‘password’;
G. ALTER USER ‘backup’@’localhost’ IDENTIFIED BY ‘password’;
A. SET PASSWORD FOR ‘backup’@’localhost’=’password’;
D. ALTER USER USER() INDENTIFIED BY ‘password’;
G. ALTER USER ‘backup’@’localhost’ IDENTIFIED BY ‘password’;
Multiversion Concurrency Control (MVCC) enables what type of backup?
A. hot
B. binary
C. logical
D. incremental
A. hot
Consider:
mysql> EXPLAIN SELECT Name from Country WHERE Population BETWEEN 1 AND 100\G
****** 1. Row ******
id: 1
Select type: SIMPLE
table: Country
type: range
possible_keys: i_pop
key: i_pop
key_len: 4
ref: NULL
rows: 10
Extra: Using where
What does the range value in the type column mean?
A. You can use an index and return rows that fall within a range of values.
B. The table will be scanned over a certain range of values.
C. This type of index uses the range hash.
D. There is a range of indexes that can be used.
A. You can use an index and return rows that fall within a range of values.
Which MySQL utility copies the master instance to a slave instance on the same host?
A. mysqlfallover
B. mysqldbcopy
C. mysqlrplsync
D. mysqlserverclone
D. mysqlserverclone
Explanation
This utility enables you to clone an existing MySQL server instance to create a new server instance on the same host.
The utility does not copy any data. It merely creates a new running instance of the cloned server with the same options (or additional options if specified). Thus, to create a copy of a server, you must copy the data after the server is cloned.
Which statement is true about tablespaces?
A. All tablespace files must be in the directory specified by the –datadir option.
B. General tablespaces can be configured to span multiple files.
C. All tables must be in either the system tablespace or a general tablespace.
D. The system tablespace can be configured to span multiple files.
D. The system tablespace can be configured to span multiple files.
Which statement is correct about how InnoDB storage engine uses disk space?
A. It stores data in .MYD files and its index and undo information in the common tablespace.
B. It stores data in the .MYD files, index information in the .MYI files, and undo information in the common tablespace.
C. It stores its data in the tablespace file(s). Index and data dictionary details are stored in .FRM files.
D. It stores its data, index and undo information in the .MYD and .MYI files.
E. It stores data, index and undo information in the tablespace files(s).
E. It stores data, index and undo information in the tablespace files(s).
Which two methods will provide the total number of partitions on a table? (Choose two.)
A. Query the INFORMATION_SCHEMA.PARTITIONS table
B. Use the command: SHOW TABLE STATUS
C. Query the INFORMATION_SCHEMA.TABLES tables for the partition_count.
D. Query the performance_schema.objects_summary_global_by_type
E. Use the command: SHOW CREATE TABLE
A. Query the INFORMATION_SCHEMA.PARTITIONS table
E. Use the command: SHOW CREATE TABLE
Where does MySQL Linux RPM install the mysqld binary?
A. /usr/libexec/
B. /usr/local.mysql/bin/
C. /usr/sbin/
D. /usr/bin/
E. /opt/mysql/server/bin/
C. /usr/sbin/
Default locations client programs and scripts = /usr/bin mysqld server = usr/sbin data directory = /var/lib/mysql error log file = /var/log/mysql/error.log (linux) \ProgramData\MySQL\MySQL Server 8.0\Data\filename.err (windows)
You are investigating the performance of the server and see this information: events_waits_summary_global_by_event_name in the Performance Schema shows that the wait/synch/mutex/sql/LOCK_table_cache event is dominating other wait events. The Table_open_cache_overflows status variable is 0.
Which action should be taken to remove the performance bottleneck described here?
A. Increase the value of table_open_cache_instances.
B. Decrease the value of table_definition_cache.
C. Decrease the value of table_open_cache.
D. Increase the value of table_definitnion_cache.
E. Increase the value of table_open_cache.
A. Increase the value of table_open_cache_instances.
Explanation
To improve scalability by reducing contention among sessions, the open tables cache can be partitioned into several smaller cache instances of size table_open_cache / table_open_cache_instances .
You have successfully provisioned the latest MySQL 8.0 database instance on a physical host, to be added to an existing farm for use in a modern, high volume, ACID-compliant, OLTP website, which serves hundreds of DML transactions per second.
The default values of which two key variables do you change to ensure seamless operation of the database? (Choose two.)
A. Key Buffer Size
B. InnoDB Redo Log Size
C. Binary Log Size
D. Buffer Pool Size
E. Sort Buffer size
F. Query Cache size
D. Buffer Pool Size
E. Sort Buffer size
cbryll aug/2021 - seems odd that soft buffer size is the answer. # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a disk # based merge sort is used instead - See the "Sort_merge_passes" # status variable. Allocated per thread if sort is needed.
You are creating a strategy for backing up MySQL using a cold binary backup. The MySQL instance is a replication master with global transaction identifiers (GTIDs) enabled and it uses Transparent Data Encryption (TDE). Other than the configuration required to make the instance a replication master and enabled GTIDs and TDE, the instance is using all default settings.
The requirements for the backup are:
It must be possible to rebuild the instance using the backup.
It must be verified.
It must allow for a catastrophic hardware failure.
Which four steps must be included in the backup strategy? (Choose two.)
A. Include the keyring data and/or configuration in the backup.
B. Restore the backup to a clean MySQL instance.
C. Copy the backup to a remote host.
D. Include the MySQL socket file in the backup.
A. Include the keyring data and/or configuration in the backup.
B. Restore the backup to a clean MySQL instance.
You are creating a strategy for backing up MySQL using a cold binary backup. The MySQL instance is a replication master with global transaction identifiers (GTIDs) enabled and it uses Transparent Data Encryption (TDE). Other than the configuration required to make the instance a replication master and enabled GTIDs and TDE, the instance is using all default settings.
The requirements for the backup are:
It must be possible to rebuild the instance using the backup.
It must be verified.
It must allow for a catastrophic hardware failure.
Which four steps must be included in the backup strategy? (Choose two.)
A. Include the ibtmp1 file in he backup
B. Include the relay log in the backup.
C. Include the operating system disk encryption key in the backup.
D. Include the MySQL PID file in the backup.
E. Include the binary logs in the backup.
B. Include the relay log in the backup.
C. Include the operating system disk encryption key in the backup.
Which storage option for MySQL data directory typically offers the worst performance in a highly concurrent, OLTP-heavy, IO-bound workload?
A. battery-backed locally-attached RAID 5 array
B. ISCSI Lun
C. SAN (Fibre Channel) Lun
D. NFS (Network File System) mount
D. NFS (Network File System) mount
Which two statements are true regarding MySQL security? (Choose two.)
A. The mysql user needs to have sudo priviledges.
B. The mysql process owner should own all files and directories to which the server writes.
C. The root or administrator users should own all files and directories to which tje server writes.
D. The mysql process should be run as a root or administrator
E. The mysql process should not be run as root or administrator.
B. The mysql process owner should own all files and directories to which the server writes.
E. The mysql process should not be run as root or administrator.
You have installed the validate_password plug-in and set the validate_password_policy variable. Which validation is affected by the validate_password_policy setting?
A. whether a new password is rejected if it contains a word found in a dictionary file.
B. whether a new password is rejected if it contains the current user’s username.
C. the amount of delay after an incorrect password is entered
D. the length of time before a newly created password expires.
A. whether a new password is rejected if it contains a word found in a dictionary file.
Which statement describes how the relay log works?
A. when a slave receives a change from the master, it is processed first, and then recorded in the relay log.
B. It maintains a record of available master binary logs and the current executed log position.
C. It stores changes on the master, and relays them to the slave.
D. When a slave receive a change from the master, it is recorded in the relay log first and processed later.
D. When a slave receive a change from the master, it is recorded in the relay log first and processed later.
The MySQL user ‘adam’ currently has USAGE permissions to the database.
The football database is transactional and has non-stop updates from application users. The ‘adam’ user needs to be able to take consistent backups of the football database by using the –single-transaction option.
Which extra GRANT permissions are required for adam to take mysqldump backups?
A. The ‘adam’ user must also have SINGLE TRANSACTION global grant to take a consistent backup.
B. The ‘adam’ user needs the PROCESS privilege to be able to take a consistent backup while other users are connected.
C. The ‘adam’ user must have the SUPER privilege in order to take data backups.
D. The ‘adam’ user must also have SELECT on the football databases for the backup to work.
D. The ‘adam’ user must also have SELECT on the football databases for the backup to work.
You have forgotten the root user account password. You decide to reset the password and execute:
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 grants tables
B. –reset-grant-tables, to start the server with only the mysql database accessible.
C. –read-only, to set all data to read-only except for super users
D. –skip-networking, to prohibit access form remote locations
D. –skip-networking, to prohibit access form remote locations
You are receiving complaints from your application administrators that they are seeing periodic stalls in database response (no queries to any table are returning results for several seconds or longer). You monitor your system and notice that the durations of those stalls correspond to peaks in disk I/O.
Which 2 things should you investigate?
A. Check the rate of change in the status value Aborted_connects and compare to the rate of change in Connections.
B. Check the difference between the InnoDB status values “Log Sequence number” and “Last Checkpoint” positions then compare that to the total size of the redo log.
C. Check the rate of change in the status value select_scan and compare to the rate of change in Com_select.
D. Check the difference between the InnoDB staus values “Trx id counterr” and “Purge done for” and compare to the state substatus of the main “Main thread”
E. Check the rate of change in the status value Qcache_hits and that to the rate of change of Qcache_not_cached.
B. Check the difference between the InnoDB status values “Log Sequence number” and “Last Checkpoint” positions then compare that to the total size of the redo log.
E. Check the rate of change in the status value Qcache_hits and that to the rate of change of Qcache_not_cached.
You have created a backup of the ‘sales’ database with the command:
mysqldump -u root -p –tab=/backup sales
Which two procedures can be used to restore the ‘orders’ table from the backup?
A. shell$ mysqldump -u root -p –tab=/backup –restore sales –tables orders
B. shell$ mysql -u root -p sales < /backup/orders.sql
shell$ mysql -u root -p sales < /backup/orders.txt
C. mysql> use sales;
mysql> source /backup/orders.sql;
mysql> load data local in file ‘/backup/orders.txt’ into table orders;
D. shell$ mysql -u root -p sales < /backup/orders.sql
shell$ mysqlimport -u root -p –local sales /backup/orders.txt
C. mysql> use sales;
mysql> source /backup/orders.sql;
mysql> load data local in file ‘/backup/orders.txt’ into table orders;
D. shell$ mysql -u root -p sales < /backup/orders.sql
shell$ mysqlimport -u root -p –local sales /backup/orders.txt
