1Z0-888 Online Practice Questions and Answers
Consider the key buffer in a MySQL server. Which two statements are true about this feature? (Choose two.)
A. It caches index blocks for MyISAM tables only.
B. It caches index blocks for all storage engine tables.
C. It is a global buffer.
D. It is set on a per-connection basis.
E. It caches index blocks for InnoDB tables only.
A MySQL replication slave is set up as follows:
Uses all InnoDB tables Receives ROW-based binary logs Has the read-only option
The replication slave has been found in an error state. You check the MySQL error log file and find these entries:
What are two possible causes for this error to occur? (Choose two.)
A. The applications have the SUPER privilege, which allows them to update rows.
B. The root user on the slave has executed FLUSH LOGS, causing the relay-log to doublewrite.
C. For tables with UNIQUE keys, statement-based replication must be used to maintain integrity.
D. The slave was created with mysqldump –u root –p --skip-lock-tables –all-databases > /data/data.sql
E. The slave user does not have INSERT, UPDATE, or DELETE permission and cannot execute the Write_rows function.
Consider the table people with the 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.)
A. Change the WHERE clause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.
B. Add a functional index for YEAR(Birthday).
C. Execute ANALYZE TABLE to update the index statistics.
D. Add a generated column calculating YEAR(Birthday) and index that column.
E. Add FORCE INDEX (Birthday) to the query.
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:
Three tables are stored in the InnoDB shared tablespace and the details are as follows:
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.
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?
A. Option A
B. Option B
C. Option C
D. Option D
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
Multiversion Concurrency Control (MVCC) enables what type of backup?
A. Hot
B. Binary
C. Logical
D. Incremental
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() IDENTIFIED 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';
While attempting to set up a new replication slave on host `192.168.0.25' with the user `replication', you encounter this error:
What should you do to resolve this error?
A. Add the user [email protected] 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 [email protected] with the correct password to the slave.
You are looking at these details:
Why does the message "Waiting for an event from Coordinator" show up on the slave_worker threads?
A. The Event Schedular is not enabled on the slave host.
B. The slave is waiting for an event to come into the relay log.
C. The slave thread is waiting for another event to finish in another slave thread.
D. The binary log on the master is not being updated.
You are installing MySQL Server 5.7 from a tarball package. You have already set up the correct PATH environment variable and configured your /etc/my.cnf file.
Which command will correctly prepare a data directory?
A. service mysqld prepare-init
B. mysqld_safe --initialize ...
C. mysql -u root -p < support-files/mysqldb.sql
D. mysqld_safe --user=mysql
E. mysqld --initialize ...