1z0-888 MySQL 5.7 Database Administrator Questions and Answers

The mysql user needs a login and its home directory must be the base directory of the installation.

The OS needs to allow logging in as mysql so that administrative tasks can be performed.

This prevents mysqld from starting when standard startup scripts are used.

This prevents creation of a command shell with the mysql account, while allowing mysqld to run.

Increase the value of table_open_cache_instances.

Decrease the value of table_definition_cache.

Decrease the value of table_open_cache.

Increase the value of table_definition_cache.

Increase the value of table_open_cache.

Decrease the value of table_open_cache instances.

mysql> CREATE USER ‘employee’@’localhost’ IDENTIFIED BY ‘more_secrets’;

mysql> CREATE USER ’’@’’ IDENTIFIED BY ‘valid_password’ WITH PROXY ‘employee’@’localhost’;

mysql> CREATE USER ‘employee’@’localhost’ IDENTIFIED BY ‘more_secrets’;

mysql> GRANT PROXY ON ‘employee’@’localhost’ TO ‘robert’@’localhost’;

mysql> CREATE USER ‘robert’@’localhost’ IDENTIFIED BY ‘secret_password’;

mysql>CREATE USER ‘employee’@’localhost’ IDENTIFIED BY ‘more_secrets’;

mysql> CREATE_USER ’’@’’ IDENTIFIED WITH authentication_pam ACCOUNT LOCK;

mysql> CREATE USER ‘employee’@’localhost’ IDENTIFIED BY ‘more_secrets’;

mysql> GRANT PROXY ON ‘employee’@’localhost’ TO ’’@’’;

The cluster can be operated in multimaster mode with conflict detection for DML statements.

All MySQL client programs and connectors can be used for executing queries.

It provides fully synchronous replication between the nodes.

There is support for automatic failover when one node fails.

The data is automatically shared between the nodes.

Each query will be executed in parallel across the nodes.

There are three indexes on the table.

There is a redundant index on the dept_no column.

The secondary indexes are optimized for unique key look-ups.

The values on the emp_no column must be unique.

The selectivity of the dept_no column is the best of the indexed columns.

There is a redundant index on the emp_no column.

--restore=2

--copy-back-from-log

--backup-instance=/var/lib/mysql/instance2

--instance=/var/lib/mysql/instance2

--defaults-file=/etc/mysql/instance2.cnf

SET PASSWORD FOR ‘backup’@’localhost’ = ‘password’;

SET PASSWORD = ‘password’;

SET PASSWORD FOR ‘backup’@’localhost’ = PASSWORD (‘password’)

ALTER USER USER() IDENTIFIED BY ‘password’;

CREATE USER ‘backup’@’localhost’ IDENTIFIED BY ‘password’;

ALTER USER ‘backup’ IDENTIFIED BY ‘password’;

ALTER USER ‘backup’@’localhost’ IDENTIFIED BY ‘password’;

- -no-create-info to skip writing CREATE TABLE statements

- -single-transaction as this obtains a consistent view of data only

- -data-only as this specifies that only data is to be dumped

- -table=userdata in order to dump only the data from the userdata table

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

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

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

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

The log_bin variable is set to OFF on the slave.

server_id is not unique.

The max_connections variable on the slave needs to be increased.

The shared_memory_base_name variable must match the master.

The version of the slave is newer that the version of the master.

it contains the binary log.

it contains a general tablespace.

it is the default location for all new tables that you create.

it contains the system tablespace.

it contains the redo log.

it contains the undo log.

It allows a client to process scripts.

It allows the use of stored routines.

It allows a client to see another user’s queries with the SHOW PROCESSLIST command.

It allows a client to control running processes on a server.

to find out why a query takes a long time to execute on a given table

to make sure a table has no structural problems

to improve performance by updating index distributing statistics on InnoDB tables

to repair table structure problem

to make sure that no table indexes are corrupted

Change the WHERE clause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.

Add a functional index for YEAR(Birthday).

Execute ANALYZE TABLE to update the index statistics.

Add a generated column calculating YEAR(Birthday) and index that column.

Add FORCE INDEX (Birthday) to the query.

enabling and using SSL for connections to the MySQL database

running the server with –-skip-networking specified

disabling connections from named pipes or socket files (depending on the operating system of the server)

starting the server with –-bind-address=0.0.0.0 specified

limiting logins to originate from the application server or the server’s subnet

installing MySQL on the application server, and running the database and application on the same server