QUESTION 35
The ‘applicationdb’ is using InnoDB and consuming a large amount of file system space. You have a /backup partition available on NFS where backups are stored.
You investigate and gather this information:
* The table data_current has 1,000,000 rows.
* The table data_reports has 1,500,000 rows.
* The table data_archive has 4,500,000 rows.

Three tables are stored in the InnoDB shared tablespace and the details are as follows:

You attempt to free space from ibdata1 by taking a mysqldump of the data_archive table and storing it on your backup partition.

Unfortunately, this action does not free any actual disk space back to the file system and the server disk space is running out.
Which set of actions will allow you to free disk space back to the file system?

QUESTION 36
You are using the mysqldumpslow utility to view the contents of the slow query log.
You notice the letter N and the character string `S’ in a number of locations in the output.
What does the N indicate?

QUESTION 37
You have a MySQL instance with the following variables in the /etc/my.cnf file:

You issue these statements:
USE prices;
UPDATE sales.january SET amount=amount+1000;
An hour after excluding the statements, you realize that you made a mistake and you want to go to the binary log and look at the statements again.
Which statement is true? (Choose two.)

QUESTION 38
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.)

QUESTION 39
A particular government’s security policy is to have very strict data encryption and safety settings. This is done by restricting access based on their own CA authority and limiting access to particular users within a department. Which method could be used to restrict access as required?

QUESTION 40
Which statement is correct about how InnoDB storage engine uses disk space?

QUESTION 41
To query two system variables, you execute this statement and get the results shown:

When you execute the following two statements, only the first one succeeds:
SET @@autocommit=0;
SET @@max_connections=200;
Why does the second statement fail?

QUESTION 42
You will configure a MySQL Server to act as a replication master. Which two options must be configured correctly to allow this?

QUESTION 43
Consider the ANALYZE TABLE command.
In which two situations should this command be used?

QUESTION 44
Consider these global status variables:

Which two conclusions can be made from the output?

QUESTION 45
Which two methods accurately monitor the size of your total database size over time? (Choose two.)

QUESTION 46
You attempt to connect to a MySQL Server by using the mysql client program. However, you receive this notice:

What would you run to fix the issue?

QUESTION 47
Which three options are most likely to be changed for production form their default values?

QUESTION 48
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?

QUESTION 49
Which two statements are true regarding MySQL security? (Choose two.)

QUESTION 50
Host slave1 has ip address 192.0.2.10.
Host slave2 has ip address 203.0.113.50
Examine these commands:

Why did this error occur?

QUESTION 51
When you examine a new MySQL installation with default configuration, you find a file called ibdata1in the database directory. Which two statements are true about this file? (Choose two.)