1z0-888 MySQL 5.7 Database Administrator Questions and Answers

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 ’’@’’;

Implement a failover strategy to another geographic location.

Create a master-master pair for each service.

Have a second data centre in a different region or country.

Keep software updated to the latest version.

Use RAID 10 storage for data.

Use on-site network-attached storage to separate service from data.

SET GTID_NEXT=”CONSISTENCY”;

BEGIN; COMMIT;

SET GTID_NEXT=”AUTOMATIC”;

SET GTID_NEXT=”5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8”;

BEGIN; COMMIT;

SET GTID_NEXT=”AUTOMATIC”;

SET GLOBAL SQL_SKIP_SLAVE_COUNTER=1

SET GLOBAL enforce_gtid_consistency=ON

SET GTID_EXECUTED=”5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8”;

The host on the command line is not defined in the login path.

The mysqld instance has not been restarted after creating the login path.

There is no password defined in the login path.

The DNS is not configured correctly for slave1 host.

The .mylogin.cnf file is not readable.

They are not affected by this configuration change.

They all connect via the secure sha256_password algorithm without any configuration change.

They will have their passwords updated on start-up to sha256_password format.

They will have to change their password the next time they login to the server.

All tablespace files must be in the directory specified by the –-datadir option.

General tablespaces can be configured to span multiple files.

All tables must be in either the system tablespace or a general tablespace.

The system tablespace can be configured to span multiple files.

InnoDB never uses table-level locks.

InnoDB always protects auto-increment updates with a table-level lock

InnoDB does not use locks to enforce auto-increment uniqueness.

The auto-increment lock can be a table-level lock.

Some settings for innodb_autoinc_lock_mode can help reduce locking.

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.

Yes, because binary backups only contain data, and not statements required to insert data into the tables.

No, because text backups can have optimizations, which make them smaller, such as updating many rows at once.

No, because if InnoDB tables contain many empty pages, they could take more space than the INSERT statements.

Yes, because even if InnoDB tables contain many empty pages, text backups have empty INSERT statements for them.

using stored procedures to validate values that are input

using SQL variables to secure input values

using prepared statements to handle unsecured values

installing the SQL Protection plugin to catch such attempts

starting the server with the –-injection-protection command-line option.

the PERFORMANCE_SCHEMA tables

the SHOW PROCESSLIST command

the INNODB Lock Monitor details

the SHOW STATUS command

the INFORMATION_SCHEMA tables

Physical backups can be used to recover from data corruption.

Logical backups are human-readable whereas physical backups are not.

Logical backups are always larger than physical backups.

Physical backups are usually slower than text backups.

Physical backups are usually faster than text backups.

The MySQL server has stopped inserting data to check index consistency.

InnoDB is doing CRC32 checks over the tablespace data as it grows.

The MySQL server is taking a periodical snapshot of data so it can resume the restore if it is interrupted mid-way.

InnoDB has filled the redo log and now must flush the pages.

Secondary indexes no longer fit into the buffer pool.

all updates except to the TEMPDB database

all updates except to the PERFORMANCE_SCHEMA database

all updates not involving temporary tables

all updates to the default database, except temporary tables

all updates to all databases

a backup created with mysqldump --all-databases

a backup created using FLUSH TABLES … FOR EXPORT

an up-to-date replication slave

a file system-level snapshot

a file system copy created while MySQL was shut down.