You have a config file for a running DB with this excerpt:
[mysqld]
tmp_table_size=16M
sort_buffer_size=256k
To address a query performance problem of connecting to the DB from an application on another host, you log in and make these changes to the DB:
mysql> SET GLOBAL tmp_table_size=32000000;
mysql> SET sort_buffer_size=2000000;
This solves the problem with your queries. However, later the DB instance is restarted and the performance problem returns.
Which three best describe this scenario?
A MySQL database uses all InnoDB tables and is configured as follows;
You will be setting up a replication slave by using mysqldump. You will need a consistent backup taken from your running production server. The process should have minimal impact to active database connections.
Which two arguments will you pass to mysqldump to achieve this? (Choose two.)
Consider the table people with this definition:
The application uses a query such as:
SELECT * FROM people WHERE YEAR(Birthday) = 1980;
The query is not using an index.
Which two methods can be used to allow the query to use an index? (Choose two.)
Which three allocate memory per thread in MySQL? (Choose three.)
When you examine a new MySQL installation with default configuration, you find a file called ibdata1 in the database directory. Which two statements are true about this file? (Choose two.)
Examine the mysqldumpslow output:
Which two options could explain the slow query? (Choose two.)
What does the possible_keys column in this output denote?
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?
You are using GTIDS in replication. You need to skip a transaction with the GTID of aaa-bbb-ccc-ddd-eee:3 on a slave.
Which procedure would you execute from a MySQL prompt?
You want to immediately stop access to a database server for remote user ‘mike’@’client.example.com’. This user is currently not connected to the server.
Which two actions can you take to stop any access from the user?
