1Z0-883 - MySQL 5.6 Database Administrator

Go back to Oracle

Exam FAQ

How many questions are there on the 1Z0-883 exam? How long is the 1Z0-883 exam? How much does the 1Z0-883 exam cost? What is the best way to study for the 1Z0-883 exam?

Example Questions

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? Which three statements describe how the strict SQL mode provides added security? Which two statements describe the behavior of the server's SQL mode? 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? A user executes the statement; PURGE BINARY LOGS TO `mysql-bin.010'; What is the result? 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? 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? 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? 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? 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? 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? Which two statements are true about InnoDB auto-increment locking? 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? 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? 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? Which query would you use to find connections that are in the same state for longer than 180 seconds? What is true regarding innoDB locking? 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? 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? 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? 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? What are three actions performed by the mysql_secure_installation tool? 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? 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? Which statement is true about FLUSH LOGS command? 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? Which two statements are true about setting the per-thread buffers higher than required? Which two requirements would lead towards a high availability solution? 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? Consider typical High Availability (HA) solutions that do not use shared storage. Which three HA solutions do not use shared storage? Compare a typical Distributed Replicated Block Device (DRBD) with MySQL Standard Replication using master-slave replication. Which two statements are correct? When designing an InnoDB table, identify an advantage of using the BIT datatype Instead of one of the integer datatypes. 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 ? 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? 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? 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? Which hardware storage option, when set up with redundant disks, offers the least stability, availability, and reliability for Mysql data? 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? 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? 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? 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? 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? Which statement is true about using Microsoft Windows Cluster as a platform for Mysql? 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 `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? 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? In a design situation, there are multiple character sets that can properly encode your data. Which three should influence your choice of character set? Which two capabilities are granted with the SUPER privilege?

Study Guides