1Z0-883 - MySQL 5.6 Database Administrator
Go back to Oracle
You have forgotten the root user account password. You decide to reset the password and execute the following: Shell> /etc/init.d/mysql stop Shell> /etc/init.d/mysql start skip-grant tables Which additional argument makes this operation safer?
--reset-grant-tables, to start the server with only the mysql database accessible
Which three statements describe how the strict SQL mode provides added security?
It limits the operations that the server can perform.
It rejects queries that produce out-of-range values.
It rejects dates with zero day or month values.
Which two statements describe the behavior of the server's SQL mode?
The server's SQL mode determines how the server should behave when performing data validation check and interpreting different forms of syntax.
The server's SQL mode can be changed at the session level with a SET SESSION sql_mode="new_value" command.
You want to create a temporary table named OLD_INVENTORY in the OLD_INVENTORY database on the master server. This table is not to be replicated to the slave server. Which two changes would ensure that the temporary table does not propagate to the slave?
Use the replicate-do-db, -- replicate-do-table, or replicate-wild-do-table option with the value equal to OLD_INVENTORY.
Set binlog_format=MIXED with the replicate-ignore-temp-table option.
A user executes the statement; PURGE BINARY LOGS TO `mysql-bin.010'; What is the result?
It deletes all binary log files up to and including `mysql-bin.010'.
You have enabled the Slow Query Log for a short period. When you process the Slow Query Log, you receive the following snip of output: Count: 100 Time=0 .22a (22s) Lock=0.00s (0s) Rows=0.0 (0), root[root] @localhost CREATE TABLE `t1' (id serial,id0 varchar(N) unique key,intcaoll INT (N) ,intco12 INT(N) ,intco13 INT(N) ,intco14 INT(N) ,intco15 INT(N) ,charcol1 VARVHAR(N) ,charcol2 VARCHAR(N) charcol3 VARCHAR (N) ,charcol4 VARVHAR(N) ,charcol5 VARCHAR(N) charcol6 VARCHAR (N) ,charcol7 VARVHAR(N) ,charcol8 VARCHAR(N) charcol9 VARCHAR (N) .charcol 10 VACHAR (N) ) Count: 64000 Time-0.02s (1213s) Lock=0.00s (6s) Rows=1.0 (64000), root [root]@ localhost SELECT intocl1, intco12, intco13, intco14, intco15, intco16,intco17, intco18 ,intcol9, intcol10, charcol1, charcol2, charcol3, charcol4, charcol5, charcol6 ,charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = `s' Count: 1 Time=0.02s (0s) Lock=0.00s (0s) Rows=1.0 (1) agent [agent] @localhost SELECT Select_priv, Repl_client_priv, Show_db_priv, Super_priv, Process_priv FROM mysql.user WHERE CONCAT (user, `s', host) = CURRENT_USER () Count: 48000 Time=0.02s (778s) Lock=0.00 (3s) Rows=1.0 (48000), root[root]@localhost SELECT intocl1,intcol2,intcol3, intcol4, intcol5, charcol1, charcol2, charcol3 ,charcol4, charcol5, charcol6, charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = `s' You want to tune the query such that it provides the greatest overall time savings. Which query will accomplish this?
CHEATE TABLE `t1' (id serial, id0 varchar (N) unique key, intcol1 INT (N) ,intcol2 INT (N), intcol3 INT(N) ,intcol4 INT(N), intcol5 INT(N), charool1 VARCHAR (N) ,charcol2 VARCHAR (N), charcol3 VARCHAR(N), charcol4 VARCHAR(N), charcol5 VARCHAR (N) ,charcol6 VARCHAR (N), charcol7 VARCHAR(N), charcol8 VARCHAR(N), charcol9 VARCHAR (N) ,charcol10 VARCHAR (N);
Your developers have created table to store some of their program's data. After examining the slow Query Log, you see that they are using the LIKE operator and SUBSTER () functions against a VARCHAR (10000) column quite often. An example of the start of one row of data: `GREEN01020495888331993-12-10/2...' What should you do to improve the overall performance?
Redesign the table so that the most commonly searched for string patterns are in their own columns.
Consider the Mysql Enterprise Audit plugin. A CSV file called data.csv has 100 rows of data. The stored procedure prepare_db ( ) has 10 auditable statements. You run the following statements in the mydb database: Mysql> CALL prepare_db ( ); Mysql> LOAD DATA INFILE `/tmp/data.cav' INTO TABLE mytable; Mysql> SHOW TABLES; How many events are added to the audit log as a result of the preceding statements?
3; only the top-level statements are logged.
You want to start monitoring statistics on the distribution of storage engines that are being used and the average sizes of tables in the various databases. Some details are as follows: The Mysql instance has 400 databases. Each database on an average consists of 25-50 tables. You use the query: SELECT TABLE_SCHEMA, `ENGINE', COUNT (*), SUM (data_length) total_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = `BASE TABLE' GROUP BY TABLE_SCHEMA, `ENGINE' ; Why is this query slow to execute?
Collecting information requires various disk-level operations and is time consuming.
You have taken a Logical Volume Manager (LVM) snapshot backup of a volume that contains the MySQL data directory. Why is it important to remove snapshots after completing a RAW backup in this way?
The snapshots take a significant amount of disk space as they are a duplicate copy of data
You have a login-path named "adamlocal" that was created by using the mysql_config_editor command. You need to check what is defined for this login_path to ensure that it is correct for you deployment. You execute this command: $ mysql_config_editor print login-path=adamlocal What is the expected output of this command?
The command prints all parameter for the login-path. The password is replaced with stars.
Which two statements are true about InnoDB auto-increment locking?
The auto-increment lock can be a table-level lock.
InnoDB always protects auto-increment updates with a table-level lock.
In a test database, you issue the SELECT...INTO OUTFILE statement to create a file with your t1 table data. You then TRUNCATE this table to empty it. mysql>SELECT*INTO OUTFILE '/tmp/t1.sql' fromt1; mysql>TRUNCATE t1; Which two methods will restore data to the t1 table?
mysql>LOAD DATA INFILE '/tmp/t1.sql INTO TABLE t1;
While reviewing the MySQL error log, you see occasions where MySQL has exceeded the number of file handles allowed to it by the operating system. Which method will reduce the number of file handles in use?
Disconnecting idle localhost client sessions
You adjust a default configuration to the following /etc/my.cnf on a Linux installation: [mysqld] Loq-bin Binrylog_format=ROW You do not notice the spelling error in binrylog_format and restart your production server. How does the MySQL server behave with incorrectly spelled options?
Mysqld prints to the error log about an unknown variable, and then exits.
Which query would you use to find connections that are in the same state for longer than 180 seconds?
SHOW FULL PROCESSLIST WHEER Time > 180;
What is true regarding innoDB locking?
InnoDB uses row-level or table-level locks depending on the number of rows affected
You have table `apps','userdata' on server that uses MyISAM storage engine. You want to transfer this data to server but use InnoDB engine instead. You execute the following commands: ServerB commands: Shell> mysqldump u root h server no-data apps userdata | mysql u root p apps Shell> mysql u root p h server e `ALTER TABLE `apps','userdata' ENGINE=InnoDB;' Shell> mysqldump u root p h server no-create-info order-by-primary apps userdata | mysql u root p apps What effect does the order-by-primary argument have on the mysqldump command?
It orders by primary key to assist in speeding up importing to InnoDB tables.
In a test database, you issue the SELECT ... INTO OUTFILE statement to create a file with your t1 table data. You then TRUNCATE this table to empty it. Mysql> SELECT * INTO OUTFILE `/tmp/t1.sql' from t1; mysql> TRUNCATE t1; Which two methods will restore data to the t1 table?
Mysql> LOAD DATA INFILE `/tmp/t1.sql' INTO TABLE t1;
$ mysqlimport u root p h localhost test /tmp/t1.sql
Consider the Mysql Enterprise Audit plugin. You are checking user accounts and attempt the following query : Mysql> SELECT user, host, plugin FROM mysql.users; ERROR 1146 (42S02): Table ‘mysql.users’ doesn’t exist Which subset of event attributes would indicate this error in the audit.log file?
NAME=”Query” STATUS=”1146” SQLTEXT=” Error 1146 (42S02): Table ‘mysql.users’ doesn’t exist”/>
MySQL is installed on a Linux server and has the following configuration: [mysqld] User=mysql Datadir=/data/mysql As the `root' user, 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?
MySQL requires correct file ownership while remaining secure.
What are three actions performed by the mysql_secure_installation tool?
It prompts you to set the root user account password.
It asks to remove the test database, which is generated at installation time.
It can delete any anonymous accounts.
A database exists as a read-intensive server that is operating with query_cachek_type = DEMAND. The database is refreshed periodically, but the resultset size of the queries does not fluctuate. Note the following details about this environment: - A web application uses a limited set of queries. - The Query Cache hit rate is high. - All resultsets fit into the Query Cache. - All queries are configured to use the Query Cache successfully. The response times for queries have recently started to increase. The cause for this has correctly been identified as the increase in the number of concurrent users accessing the web service. Based solely on the information provided, what is the most likely cause for this slowdown at the database level?
Mutex contention on the Query Cache is forcing the queries to take longer due to its single- threaded nature.
A database exists as a read-intensive server that is operating with query_cachek_type = DEMAND. The database is refreshed periodically, but the resultset size of the queries does not fluctuate. Note the following details about this environment: A web application uses a limited set of queries. The Query Cache hit rate is high. All resultsets fit into the Query Cache. All queries are configured to use the Query Cache successfully. The response times for queries have recently started to increase. The cause for this has correctly been identified as the increase in the number of concurrent users accessing the web service. Based solely on the information provided, what is the most likely cause for this slowdown at the database level?
Mutex contention on the Query Cache is forcing the queries to take longer due to its single-threaded nature.
Which statement is true about FLUSH LOGS command?
It closes and reopens all log files.
Consider the three binary log files bin.00010, bin.00011, and bin.00012 from which you want to restore data. Which method would use mysqlbinlog for greater consistency?
shell> mysqlbinlog bin.00010 | mysql shell> mysqlbinlog bin.00011 | mysql shell> mysqlbinlog bin.00012 | mysql
Which two statements are true about setting the per-thread buffers higher than required?
It can affect system stability during peak load times, due to swapping.
It requires increasing the thread_cache_size variable.
Which two requirements would lead towards a high availability solution?
When uptime is critical
When data loss is unacceptable
You inherit a legacy database system when the previous DBA, Bob, leaves the company. You are notified that users are getting the following error: mysql> CALL film_in_stock (40, 2, @count); ERROR 1449 (HY000): The user specified as a definer (`bon'@'localhost') does not exist How would you identify all stored procedures that pose the same problem?
Execute SELECT * FROM INFORMATION_SCHEMA. PROCESSLIST WHERE USER='bob' and HOST=' localhost';.
Consider typical High Availability (HA) solutions that do not use shared storage. Which three HA solutions do not use shared storage?
Windows Cluster and Mysql
Solaris Cluster and Mysql
Compare a typical Distributed Replicated Block Device (DRBD) with MySQL Standard Replication using master-slave replication. Which two statements are correct?
Both technologies use the TCP/IP stack as their primary transmission medium.
Only MySQL can maintain a time-delayed copy of data on the secondary node.
When designing an InnoDB table, identify an advantage of using the BIT datatype Instead of one of the integer datatypes.
Multiple BIT columns pack tightly into a row, using less space.
You attempt to connect to a Mysql Server by using the mysql program. However, you receive the following notice: ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' can't be loaded: plugin not enabled What would you run to fix the issue ?
The mysql client with the - enable-cleartext-plugin option
You are using CTIDS in replication. You need to skip a transaction with the CTID of aaa-bbb-ccc- ddd-eee : 3 on a slave. Which command would you execute from a Mysql prompt?
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
User A issues the command: LOCK TABLES pets READ; Which command can User B execute against the pets table?
A MySQL replication slave is set up as follows: User 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 the following entries: 2013-08-27 13:55:44 9056 [ERROR] Slave SQL: Could not execute Write_rows event on table test.tl; Duplicate entry `3' for key'PRIMARY' , Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log 56_master-bin.000003, end_log_pas 653, Error_code: 2013-08-27 13:55:44 9056 [Warning] Salve: Duplicate entry `3' for key `PRIMARY' Error_code: 1062 2013-08-27 13:55:44 9056 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START", We stopped at log `56_masterbin. 000003' position 496 What are two possible causes for this error to occur?
The slave was created with mysqldump u root p skip-lock-table--all-databases > /data/data.sql
The applications have the SUPER privilege, which allows them to update rows.
Consider the following table: CREATE TABLE `game' ( `id' int (10) unsigned NOT NULL AUTO_INCREMENT, `keyword' varchar (45) DEFAULT NULL, `date' datetime NOT NULL, PRIMARY KEY (`id' , `date'), UNIQUE KEY `keyword_idx' (`keyword' , `date') ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY RANGE (TO_DAYS (date) ) ( PARTITION g201301 VALUES LESS THAN (TO_DAYS (`2013-01-01 00:00:00') ), PARTITION g201302 VALUES LESS THAN (TO_DAYS (`2013-02-01 00:00:00') ), PARTITION g201303 VALUES LESS THAN (TO_DAYS (`2013-03-01 00:00:00') ), PARTITION g201304 VALUES LESS THAN (TO_DAYS (`2013-04-01 00:00:00') ), PARTITION gMORES VALUES LESS THAN (MAXVALUE) ); Which method should used to add a new g201305 partition to the table?
ALTER TABLE games ADD PARTITION g201350 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') );
Which hardware storage option, when set up with redundant disks, offers the least stability, availability, and reliability for Mysql data?
SAN (Storage Area Network)
Which High Availability solution can provide a consistent, time-delayed (for example, one hour) snapshot of the live production database?
You execute the following statement in a Microsoft Windows environment. There are no conflicts in the path name definitions. C: \> mysqld install Mysql56 defaults file = C : \my opts.cnf What is the expected outcome?
Mysql is installed as the Windows service name Mysql56, and uses c: \my-opts.cnf as the configuration file
You are attempting to secure a mysql server by using SSL encryption. On starting MYSQL, you get this error: 130123 10:38:02 [ERROR] mysqld: unknown optio '-ssl' What is the cause of the error?
The server was not started with the - enable--ssl-plugin option
Full Atomicity, Consistency, Isolation, Durability (ACID) compliance is a necessity for a new application, which heavily reads and writes data. This requires the following config file options: Sync_binlog=1 Innodb_flush_log_at_trx_commit=1 Innodb_doublewrite=1 However, this configuration is expected to introduce disk I/O overhead. What three changes will reduce disk I/O overheads?
Placement of InnoDB log files and datadir on separate physical disks
Allocation of RAM to the buffer pool such that more of the data can fit in RAM
Use of delay_key_write=ON for batch index update
Consider the Mysql Enterprise Audit plugin. A CSV file called data.csv has 100 rows of data. The stored procedure prepare_db ( ) has 10 auditable statements. You run the following statements in the mydb database: Mysql> CALL prepare_db ( ); Mysql> LOAD DATA INFILE '/tmp/data.cav' INTO TABLE mytable; Mysql> SHOW TABLES; How many events are added to the audit log as a result of the preceding statements?
3; only the top-level statements are logged.
You install a copy of Mysql 5.6.13 on a brand new Linux server by using RPM packages. The server starts successfully as verified by the following commands: $ pidof mysqld $tail - n2 /var/lib.mysql/hostname.err 2013-08-18 08:18:38 3132 [Note] /usr/sbin/mysqld: ready for connections. Version: `5.6.13-enterprise-commercial-advaced' socket: `/tmp/mysql.sock' port; 3306 Mysql Enterprise Server Advanced Edition (Commercial) You attempt to log in as the root user with the following command: $mysql u root ERROR 1045 (28000): Access denied for user `root'@'localhost' (using password: NO) Which statement is true about this scenario?
The local root user must log in with a blank password initially: mysql u root p.
Which statement is true about using Microsoft Windows Cluster as a platform for Mysql?
It relies on the shared disk architecture being visible to both servers.
You are attempting to secure a MySQL server by using SSL encryption. On starting MySQL, you get this error: 130123 10:38:02 [ERROR] mysqld: unknown option `--ssl' What is the cause of the error?
The server was not started with the enable--ssl-plugin option.
The `allplicationdb' 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 the following information: [mysqld] Datadir=/var/lib/mysql/ Innodb_file_per_table=0 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. Shell> is -1 /var/lib/mysql/ -rw-rw---- 1 mysql mysql 744G Aug 26 14:34 ibdata1 -rw-rw---- 1 mysql mysql 480M Aug 26 14:34 ib_logfile0 -rw-rw---- 1 mysql mysql 480M Aug 26 14:34 ib_logfile1 ... You attempt to free space from ibdata1 by taking a mysqldump of the data_archive table and storting it on your backup partition. Shell> mysqldump u root p applicationdb data_archive > /backup/data_archive.sql Mysql> DROP TABLE data_archive; Which set of actions will allow you to free disk space back to the file system?
Enable compression on the table, causing InnoDB to release unused pages on disk to the file system: Mysql> SET GLOBLE innodb_file_per_table=1; Mysql> SET GLOBLE innodb_file_format=Barramcuda; Mysql> ALTER TABLE data_current ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; Mysql> ALTER TABLE data_history ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
You are using CTIDS in replication. You need to skip a transaction with the CTID of aaa-bbb-cccddd- eee : 3 on a slave. Which command would you execute from a Mysql prompt?
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
In a design situation, there are multiple character sets that can properly encode your data. Which three should influence your choice of character set?
Comparing the encoded data with similar columns on other tables
Memory usage when working with the data
Character set mapping index hash size
Which two capabilities are granted with the SUPER privilege?
Allowing a client to kill other client connections
Allowing a client to shut down the server