March Sale Special Limited Time 65% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: netbudy65

1z0-888 MySQL 5.7 Database Administrator Questions and Answers

Questions 4

After rebooting the host, you attempt to start the mysqld service. You get the following error:

Can’t start the server: Bind on TCP/IP port: Address already in use

What is the most likely cause of this error?

Options:

A.

The mysqld service has already been started on the same port.

B.

The network service process in the server is frozen, so all TCP/IP connections are paused and cannot be reused.

C.

You failed to specify the port number 3306 to the command to start the server, so it is defaulting to port 80, which is in use by the built-in web server.

D.

The /etc/hosts file does not have a valid IP entry for mysqld localhost, so it is binding to 127.0.0.1, which is already in use.

E.

The mysql.sock file in the MySQL /tmp directory was not removed after the reboot, so mysqld still thinks there is an active server running.

Buy Now
Questions 5

A MySQL server was initialized with separate UNDO tablespaces. Users complain that when they roll back large transactions, the time to process the request takes too long. The DBA would like to move the MySQL InnoDB UNDO tablespace to a solid-state drive (SSD) for better performance. Is this possible and how?

Options:

A.

Yes. Shut down the mysqld process, enable the transportable_tablespace option, and move the UNDO directory to the SSD.

B.

Yes. Shut down, copy the UNDO tablespaces to the new location, and change the innodb_undo_directory value in your my.cnf.

C.

No. The UNDO tablespaces must remain on the same file system as the system tablespaces.

D.

No. The sequential write pattern of the UNDO tablespaces is not supported on modern SSD block devices.

Buy Now
Questions 6

Consider the join_buffer_size parameter in MySQL Server.

Which two statements are true about the join buffer? (Choose two.)

Options:

A.

The value should be increased if the client performs several SELECT operations.

B.

The join buffer is set per connection.

C.

The join buffer is used to process sorts when complex joins are being performed.

D.

The value should be increased from the default if the query joins large rows without using an index.

E.

The join buffer is global and can be changed only by restarting the server.

Buy Now
Questions 7

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

Options:

A.

log-master-updates

B.

rpl-recovery-rank

C.

server-id

D.

enable-master-start

E.

log-bin

F.

master-logging

Buy Now
Questions 8

Which three are key advantages of standard MySQL replication? (Choose three.)

Options:

A.

supports native automatic failover

B.

enables automatic resync of databases when discrepancies are detected

C.

provides arbitrary geographic redundancy with minimal overhead to master

D.

synchronously guarantees identical slave copy

E.

is easy to configure and has low performance overhead

F.

can easily add slaves for read scaling

Buy Now
Questions 9

You have installed MySQL Server for the first time on your system. However, the data directory along with the tables in the mysql system database are missing. Which step do you perform to create the contents of the data directory?

Options:

A.

Run the create_system_tables.sql file

B.

Run the mysql_unpack.sql file

C.

Invoke mysqld with the --initialize option.

D.

Invoke mysql with the --initialize option.

Buy Now
Questions 10

A single InnoDB table has been dropped by accident. You are unable to use an additional intermediate MySQL instance to restore the table. Which two backup methods can be used to restore the single table without stopping the MySQL instance? (Choose two.)

Options:

A.

a backup created with mysqldump --all-databases

B.

a backup created using FLUSH TABLES … FOR EXPORT

C.

an up-to-date replication slave

D.

a file system-level snapshot

E.

a file system copy created while MySQL was shut down.

Buy Now
Questions 11

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?

Options:

A.

Enable compression on the table, causing InnoDB to release unused pages on disk to the file system:

B.

Execute OPTIMIZE TABLE so that the InnoDB engine frees unused pages on disk back to the file system:

C.

Set the server to use its own tablespace, and then alter the table so that data is moved from the shared tablespace to its own:

D.

Take a backup, stop the server, remove the data files, and restore the backup:

Buy Now
Questions 12

Consider:

Which statement best describes the meaning of the value for the key_len column?

Options:

A.

It shows how many bytes will be used from each index row.

B.

It shows the number of characters indexed in the key.

C.

It shows the total size of the index row.

D.

It shows how many columns in the index are examined.

Buy Now
Questions 13

old_alter_table is disabled as shown.

mysql> SELECT @@old_alter_table;

Consider this statement on a RANGE-partitioned table:

mysql> ALTER TABLE orders DROP PARTITION p1, p3;

What is the outcome of executing this statement?

Options:

A.

All data in p1 and p3 partitions is removed and the table definition is changed.

B.

All data in p1 and p3 partitions is removed, but the table definition remains unchanged.

C.

Only the first partition (p1) will be dropped because only one partition can be dropped at any time.

D.

It results in a syntax error because you cannot specify more than one partition in the same statement.

Buy Now
Questions 14

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?

Options:

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_definition_cache.

E.

Increase the value of table_open_cache.

F.

Decrease the value of table_open_cache instances.

Buy Now
Questions 15

You have just executed a manual backup by using this command:

mysqlbackup –u root –p –-socket=/tmp/my.sock –-backup-dir=/my/backup/ backup

The operation completed without error.

What is the state of this backup and operation required before it is ready to be restored?

Options:

A.

Backup State = Compressed Backup

Operation = copy-back

B.

Backup State = Raw Backup

Operation = apply-log

C.

Backup State = Prepared Backup

Operation = validate

D.

Backup State = Prepared Backup

Operation = apply-log

E.

Backup State = Raw Backup

Operation = backupdir-to-image

Buy Now
Questions 16

You are using the Performance Schema to investigate replication on a slave which has a single master. The option slave-parallel-type is set to DATABASE.

Assume that all instruments and consumers are enabled and all threads are instrumented.

Which two facts can be concluded from the given output? (Choose two.)

Options:

A.

The slave has two intermediate relay slaves connected to it.

B.

The slave is configured with slave_parallel_workers = 4

C.

At most, two schemas are being updates concurrently.

D.

THREAD_ID 21 has stopped running.

E.

The slave cannot process the relay log fast enough to use all threads.

F.

The server needs more cores to use all slave threads.

Buy Now
Questions 17

MySQL is installed on a Linux server and has this configuration:

[mysqld]

user=mysql

datadir=/data/mysql/

As the ‘root’ user, you change the datadir location by executing:

shell> cp –R /var/lib/mysql /data/mysql/

shell> chown –R mysql /data/mysql

What is the purpose of changing ownership of datadir to the ‘mysql’ user?

Options:

A.

MySQL needs to be run as the root user, but files cannot be owned by it.

B.

The mysqld process requires all permissions within datadir to be the same.

C.

MySQL cannot be run as the root user.

D.

MySQL requires correct file ownership while remaining secure.

Buy Now
Questions 18

How does the InnoDB storage engine handle deadlocks when they are detected?

Options:

A.

Both the affected transactions will be rolled back.

B.

The affected transactions wait for innodb_lock_wait_timeout seconds, and then roll back.

C.

One of the affected transactions will be rolled back, the other is allowed to proceed.

D.

The transaction isolation level determines which transaction is rolled back.

E.

The innodb_locks_unsafe_for_binlog setting determines which transaction is rolled back.

Buy Now
Exam Code: 1z0-888
Exam Name: MySQL 5.7 Database Administrator
Last Update: Mar 29, 2024
Questions: 124

PDF + Testing Engine

$140

Testing Engine

$105

PDF (Q&A)

$90