Udemy- Test #2 Q's Flashcards

1
Q

You enable binary logging on MySQL Server with the configuration: binlog-format=ROW log-bin

Which database updates are logged on the master server to the binary log by default?

A. al updates except to the TEMPDB db

B. all updates except to the PERFORMANCE _SCHEMA db

C. all updates not involving temporary tables

D. all updates to the default db, except temporary tables

E. all tables and all databases

A

D. all updates to the default db, except temporary tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

You are using GTID in replication. You need to skip a transaction with the GTID of aaa-bbb-ccc-ddd-eee : 3

Which command would you execute from a Mysql prompt?

A. STOP SLAVE; SETGTID_NEXT=”aaa-bbb-ccc-ddd-eee:3”; BEGIN; COMMIT; SET GTID_NEXT=”AUTOMATIC”; START SLAVE

B. STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

C. STOP SLAVE; BEGIN; SET GTID_IGNORE=”aaa-bbb-ccc-ddd-eee:3”; COMMIT; START SLAVE;

D. STOP SLAVE; RESET SLAVE; BEGIN; SKIP NEXT GTID; COMIMIT; START SLAVE;

E. STOP SLAVE;
SETGTID_SET="aaa-bbb-ccc-ddd-eee:3"; 
BEGIN;
COMMIT;
SETGTID_NEXT="AUTOMATIC"; 
START SLAVE;
A
E. STOP SLAVE;
SETGTID_SET="aaa-bbb-ccc-ddd-eee:3"; 
BEGIN;
COMMIT;
SETGTID_NEXT="AUTOMATIC"; 
START SLAVE;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

User A issues the command:

LOCK TABLES pets READ;

Which command can User B execute against the pets table?

A. UPDATE pets…
B. SELECT… FROM pets
C. INSERRT INTO pets…
D. ALTER TABLE pets…

A

B. SELECT… FROM pets

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

When backing up a replication slave, which three should also be backed up in addition to data?

A. The master.info and relay.info files
B. The relay log files
C. The relay index file
D. Mysql.slave_master_info table
E. Mysql.slave_relay_log_info table
F. Mysql.slave_worker_info table
A

A. The master.info and relay.info files
B. The relay log files
C. The relay index file

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

You want to shutdown a running Mysql Server cleanly.

Which three commands that are valid on either Windows or Linux will achieve this?

A. Shell> pkill -u mysql mysqld_safe
B. Shell> service mysql safe_exit
C. Shell> /etc/init.d/mysql stop
D. Shell> mysqladmin -u root -p shutdown
E. Mysql> STOP PROCESS mysqld;
F. Shell> net stop mysql
G. Shell> nmc mysql shutdown
A

C. Shell> /etc/init.d/mysql stop
D. Shell> mysqladmin -u root -p shutdown
F. Shell> net stop mysql

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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?

A. ALTER TABLE games REORGANIZE PARTITION (gMORES) INTO g01305 VAUES LESS THAN (TO_DAYS (2013-05-01 00:00:00) 0;

B. ALTER TABLE games ADD PARTITION g201350 VALUES LESS THAN (TO_DAYS (2013-05-01 00:00:00) 0;

C. ALTER TABLE games COALESCE PARTITION (gMORES) INTO g01305 VAUES LESS THAN (TO_DAYS (2013-05-01 00:00:00) 0; gMORES VALUES LESS THAN (MAXVALUE) );

A

A. ALTER TABLE games REORGANIZE PARTITION (gMORES) INTO g01305 VAUES LESS THAN (TO_DAYS (2013-05-01 00:00:00) 0;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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?

A. Use of soft links for db dir on the same physical disk

B. Use of the separate dir on the same physical disk for log files and data files

C. Placement of InnoDB log files and datadir on separate physical disks

D. Allocation of RAM to the buffer pool such that more of the data can fit in RAM

E. Use of delay_key_write=ON for batch index update

A

C. Placement of InnoDB log files and datadir on separate physical disks
D. Allocation of RAM to the buffer pool such that more of the data can fit in RAM
E. Use of delay_key_write=ON for batch index update

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

You want a record of all queries that are not using indexes.

How would you achieve this?

A. By enabling the Slow Query Log because all queries that are not using indexes will be logged automatically.

B. By enabling the Error Log because not using indexes is an error

C. By enabling the Slow Query Log and using the –log-queries-not-using-indexes option

D. By enabling the Error Log and using the –log-queries-not-

A

C. By enabling the Slow Query Log and using the –log-queries-not-using-indexes option

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

The validate_password plugin is loaded and displays the following settings in global variables:

Mysql> SHOW VARIABLES LIKE validate_password%;
Validate_password_dictionary_file
Validate_passworrd_length = 8
Validate_password_mixed_case_count = 1
Validate_password_number_count= 2
Validate_password_policy =MEDIUM
Validate_passworrd_special_char_count = 1

When attempting to set your password, you get the following error:
Mysql> SET PASSWORD = PASSWORD (Hoverl@%);
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
What is the cause of the error?

A. The password is eight characters long, but needs to exceed validaate_password_lengt to be valid.

B. All of the MEDIUM password policy requirements have not been honored.

C. The password does not match the validate_passworrd_number_count requirement.

D. There is no dictionary file defined, so password validation cannot work as expected.

A

B. All of the MEDIUM password policy requirements have not been honored.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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 connot be loaded: plugin not enabled

What would you run to fix the issue?

A. The msql client with the –ignore-password-hashing option

B. The mysql_secure_installation script to update server security settings

C. The mysql client with the –enable-cleartext-plugin option

D. The mysql_upgrade script

A

C. The mysql client with the –enable-cleartext-plugin option

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Consider the following statement on a RANGE partitioned table:
ALTER TABLE orders DROP PARTITION p1, p3;
What is the outcome of executing the above statement?

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

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

C. A syntax error will result as you cannot specify more than one partition in the same statement

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

A

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

Explanation
D is correct.
if you execute desc ; the result will be the same.
“Desc” does not show you the full definition of the table – it will not show you partition information.
need use: show create table orders;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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?

A. Execute SELECT * FROM mysql.routines WHERE DEFINER=’bob@localhost’;

B. Execute SHOW ROUTINES WHERE DEFINER=’bob@localhost’.

C. Execute SELECT * FROM INFORMATION_SCHEMA ROUTINES WHERE DEFINER=’bob@localhost’;

D. Execute SELECT * FROM INFORMATION_SCHEMA. PROCESSLIST WHERE USER=’bob’ and HOST=’LOCALHOST’;

A

C. Execute SELECT * FROM INFORMATION_SCHEMA ROUTINES WHERE DEFINER=’bob@localhost’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

ROW-based replication has stopped working. You investigate the error log file and find the following entries:

2019-08-27 14:15:47 9056 [ERROR] Slave SQL: Could not execute Delete_rows event on table test.t1; Cant find record in t1, Error_code: 1032; handler error

HA_ERR_KEY_NOT_FOUND; the events master log 56_master-bin. 000003, end_log_pos 851, Error_code: 1032

2019-08-27 14:15:47 9056 [warning] Slave: Cant find record in t1 Error_code: 1032

2019-08-27 14:15:47 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_master-bin. 000003 position 684

Why did you receive this error?

A. The slave SQL thread does not have DELETE privileges to execute on test.t1 tablets

B. The table definition on the slave -litters from the master

C. Multi-threaded replication slaves can have temporary errors occurring for cross database updates

D. The slave SQL thread attempted to remove a row from the test.t1 table, but the row did not exist

A

D. The slave SQL thread attempted to remove a row from the test.t1 table, but the row did not exist

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Mysqldump was used to create a single schema backup;

Shell> mysqldump -u root -p sakila > sakila2013.sql

Which two commands will restore the sakila database without interfering with other running database?

A. Mysql> USE sakila; LOAD DATA INFILE ‘sakila2013.sql’;

B. Shell> mysql -u root -p sakila < sakila2013.sql

C. Shell> mysqlimport -u root -p sakila sakila2013.sql

D. Shell> mysql -u root -p-e ‘use sakila; source sakila2013.sql’

E. Shell> mysql -u root -p -silent < sakila2013.sql

A

B. Shell> mysql -u root -p sakila < sakila2013.sql

D. Shell> mysql -u root -p-e ‘use sakila; source sakila2013.sql’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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 doesnt exist

Which subset of event attributes would indicate this error in the audit.log file?

A. NAME = “Query” STATUS= “1146” SQLTEXT=”select user, host from users” />

B. NAME=”Error” STATUS=”1146” SQLTEXT=”Error 1146 (42502): Table ‘mysql.users’ doesn’t exist”/>

C. NAME =”Query” STATUS=”1146” SQLTEXT=”Error 1146 (42502): Table ‘mysql.users’ doesn’t exist

D. NAME=”Error” STATUS =”1146” SQLTEXT=”select user; host from users”/>

E. NAME= “Error” STATUS=”0” SQLTEXT = “Error 1146 (42502): Table ‘mysql.users’ doesn’t exist” />

A

A. NAME = “Query” STATUS= “1146” SQLTEXT=”select user, host from users” />

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Which query would you use to find connections that are in the same state for longer than 100 seconds?

A. SHOW FULL PROCESSLIST WHERE Time > 100;

B. SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE STARTS < (DATE_SUB (NOW( ), INTERVAL 100 SECOND) );

C. SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 100;

A

C. SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 100;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

You are using replication and the binary log files on your master server consume a lot of disk space.

Which two steps should you perform to safely remove some of the older binary log files?

A. Ensure that none of the attached slaves are using any of the binary logs you want to delete

B. Use the command PURGE BINARY LOGS and specify a binary log file name or a date and time to remove unused files

C. Execute the PURGE BINARY LOGS NOT USED command

D. Remove all of the binary log files that have a modification date earlier than today

E. Edit the .index file to remove the files you want to delete

A

A. Ensure that none of the attached slaves are using any of the binary logs you want to delete

B. Use the command PURGE BINARY LOGS and specify a binary log file name or a date and time to remove unused files

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Which two statements are true about InnoDB auto-increment locking?

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

B. InnoDB never uses table-level locks

C. Some settings for innodb_autoiinc_lock_mode can help reduce locking

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

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

A

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

C. Some settings for innodb_autoiinc_lock_mode can help reduce locking

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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?

A. 102; top-level statements are logged, but LOAD DATA INFILE is logged as a separate event

B. 3; only the top-level statements are logged

C. 111; top-level statements and all lower-level statements are logged

D. 122; only top-level statements and stored procedure events are logged

A

B. 3; only the top-level statements are logged

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

You execute the following statement in a Microsoft Windows environment. There are no conflicts in the path name definitions.

C: > mysqld install Mysql8.0 defaults file = C : \my opts.cnf

What is the expected outcome?

A. Mysqld acts as an MSI installer and installs the Mysql 8.0 version, with the c: \my-opts.cnf as the configuration file

B. MySQL is installed as the Windows service name MySQL 8.0 and uses c: \my-opts.cnf as the configuration file

C. An error message is issued because - install is not a valid option for mysqld

D. A running MySQL 8.0 installation has its runtime configuration updated with the server variables set in c:\myy=opts.cnf

A

B. MySQL is installed as the Windows service name MySQL 8.0 and uses c: \my-opts.cnf as the configuration file

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Consider the events_% tables in performance Schema.

Which two methods will clear or reset the collected events in the tables?

A. Using DELETE statements, for example, DELETE FROM performance_schema.events_waits_current;

B. Using the statement RESET PERFORMANCE CACHE;

C. Using the statement FLUSH PERFORMANCE CACHE;

D. Using TRUNCATE statements, for example, TRUNCATE TABLE performance_schema.events_waits_current;

E. Disabling and re-enabling all instruments

F. Restarting mysql

A

D. Using TRUNCATE statements, for example, TRUNCATE TABLE performance_schema.events_waits_current;

F. Restarting mysql

Explanation
Tables in the performance_schema database are views or temporary tables that use no persistent on-disk storage.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What are four capabilities of the mysql client program? (Choose four.)

A. Creating and dropping databases

B. Creating, dropping, and modifying tables and indexes

C. Shutting down the server by using the SHUTDOWN command

D. Creating and administering users

E. Displaying replication status information

A

A. Creating and dropping databases

B. Creating, dropping, and modifying tables and indexes

D. Creating and administering users

E. Displaying replication status information

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Assume that you want to know which Mysql Server options were set to custom values.

Which two methods would you use to find out?

A. Check the configuration files in the order in which they are read by the MySQL Server and compare them with default values

B. Check the command-line options provided for the MySQL Server and compare them with default values

C. Check the output of SHOW GLOBAL VARIABLES and compare it with default values

D. Query the INFORMATION_SCHEMA.GLOBAL_VARIABLES table and compare the result with default values

A

C. Check the output of SHOW GLOBAL VARIABLES and compare it with default values

D. Query the INFORMATION_SCHEMA.GLOBAL_VARIABLES table and compare the result with default values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

In a design situation, there are multiple character sets that can properly encode your data.

Which three should influence your choice of character set? (Choose three.)

A. Disk usage when storing data

B. Syntax when writing queries involving JOINS

C. Comparing the encoded data with similar columns on other tables

D. Memory usage when working with the data

E. character set mapping index hash size

A

C. Comparing the encoded data with similar columns on other tables

D. Memory usage when working with the data

E. character set mapping index hash size

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Consider typical High Availability (HA) solutions that do not use shared storage.

Which three HA solutions do not use shared storage? (Choose three.)

A. Mysql Replication

B. Distributed Replicated Block Device (DRBD) and MySQL

C. Windows Cluster and MySQL

D. Solaris Cluster and MySQL

A

A. Mysql Replication

B. Distributed Replicated Block Device (DRBD) and MySQL

D. Solaris Cluster and MySQL (maybe… I need to check)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Which three statements are characteristic of the MEMORY storage engine? (Choose three.)

A. Each table is represented on disk as an.frm file

B. Each table has a corresponding.MYI and .MYD file

C. It can support foreign keys

D. It cannot contain text or BLOB columns

E. Table contents are not saved if the server is restarted

F. It can support transactions

A

A. Each table is represented on disk as an.frm file

D. It cannot contain text or BLOB columns

E. Table contents are not saved if the server is restarted

F. It can support transactions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Consider the Mysql Enterprise Audit plugin.

Which statement is true when you identify a connection event that has used external authentication?

A. The attribute “STATUS” is set to the string EXTERNAL_AUTH.

B. The attribute “PRIV_USER” coontains the username

C. The vent type that is given in the attribute “NAME” is EXTERNAL_AUTH.

D. There is no differentiation between native and external authentication events

E. External authentication is managed through external auditing logs.

A

E. External authentication is managed through external auditing logs.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

You are having problems with connections from a specific host (192.168.1.15) not closing down correctly. You want to find the state of the threads from that host check for long-running queries.

Which statement will accomplish this?

A. SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE HOST = ‘192.168.1.15’

B. SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE HOST =’192.168.1.15’

C. SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE HOST =’192.168.1.15’

D. SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE HOST = ‘192.168.1.15’

A

A. SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE HOST = ‘192.168.1.15’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Identify a performance impact when using the Performance Schema.

A. There is no impact on performance

B. There is an overhead for querying the Performance Schema but not for having it enabled

C. There is constant overhead regardless of settings and workload

D. The overhead depends on the settings of the performance schema

A

D. The overhead depends on the settings of the performance schema

Explanation
Performance Schema is designed to run with minimal overhead. The actual impact on server performance depends on how it is configured

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Which statement is true about FLUSH LOGS command?

A. It requires the RELOAD, FILE, and DROP privileges

B. It closes and reopens all log files

C. It closes and sends binary log files to slave servers

D. It flushes dirty pages in the buffer pool to REDO logs

A

B. It closes and reopens all log files

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

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) as total_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE =’BASE TABLE’ GROUP BY TABLE_SCHEMA, ENGINE

Why is this query slow to execute?

A. Counting and summarizing all table pages in the InnoDB shared tablespace is time consuming

B. Collecting information requires various disk-level operations and is time consuming

C. Aggregating details from various storage engine caches for the final output is time consuming

D. Collecting information requires large numbers of locks on various INFORMATION_SCHEMA tables

A

B. Collecting information requires various disk-level operations and is time consuming

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Which two events will cause a slave server to create a new relay log file?

A. Starting of the I/O thread
B. Execution of the FLUSH LOGS statement
C. Starting of the SQL thread
D. Reaching the slave_pending_jobs_size_max limit
E. Execution of FLUSH TABLES WITH READ LOCK

A

A. Starting of the I/O thread

B. Execution of the FLUSH LOGS statement

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

The InnoDB engine has a feature known as clustered indexes.

Which three statements are true about clustered indexes as used in InnoDB? (Choose three.)

A. A primary key must exist for creation of a cluster index

B. A primary key is used as a clustered index

C. A clustered index is a grouping of indexes from different tables into a global index for faster searching

D. If no indexes exist, a hidden clustered index is generated based on row IDs.

E. A clustered index provides direct access to a page containing row data

F. The first unique index is always used as a clustered index and not a primary key

G. A clustered index allows fulltext searching within InnoDB

A

B. A primary key is used as a clustered index

D. If no indexes exist, a hidden clustered index is generated based on row IDs.

E. A clustered index provides direct access to a page containing row data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

A Mysql instance is running on a dedicated server. Developers access the server from the same network subnet. Users access the database through an application that is running on a separate server in a DMZ.

Which two will optimize the security of this setup? (Choose two.)

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

B. Running the server with –skip-networking specified

C. Limiting logins to originate from the application server or the server’s subset

D. Starting the server with –bind-address=0.0.0.o specified

E. Installing MySQL on the application server, and running the database and application on the same server

F. Enabling and using SSL for connections to the MySQL database

A

C. Limiting logins to originate from the application server or the server’s subset

F. Enabling and using SSL for connections to the MySQL database

35
Q

Which hardware storage option, when set up with redundant disks, offers the least stability, availability, and reliability for Mysql data?

A. RAID 5
B. iSCSI
C. SAN (Storage Area Network)
D. NFS (Networked File System)

A

D. NFS (Networked File System)

36
Q

Which two statements are true regarding partitioning in Mysql? (Choose two.)

A. Tables with BLOB and TEXT columns cannot be partitioned

B. Partitioning allows easier management of smaller data sets for certain queries

C. Partitioning allows different columns to be stored in separate files

D. The partitioning expression is an integer or function that returns an integer value or NULL value

E. Partitioning is only available for those storage engines that implements it natively

A

B. Partitioning allows easier management of smaller data sets for certain queries

D. The partitioning expression is an integer or function that returns an integer value or NULL value

37
Q

The MySQL user ‘adam’ currently has USAGE permissions to the database.

The football database is transactional and has non-stop updates from application users The ‘adam’ user needs to be able to take consistent backups of the football database by using the — single-transaction option.

Which extra GRANT permissions are required for adam to take mysqldump backups?

A. The ‘admin’ user must also have SINGLE TRANSACTION global grant to take a consistent backup

B. The ‘adam’ user must have the SUPER privilege in order to take data backups

C. The ‘adam’ user must have also have SELECT on the football database for backups to work

D. The ‘adam’ user needs the PROCESS privilege to be able to take a consistent backup while other users are connected

A

C. The ‘adam’ user must have also have SELECT on the football database for backups to work

Explanation
mysql> show grants for ‘adam’@’localhost’;

+——————————————+

+——————————————+

| GRANT USAGE ON . TO ‘adam’@’localhost’ |

+——————————————+

1 row in set (0.01 sec)

mysqldump -u adam -p –single-transaction test1»123

Enter password:

mysqldump: Got error: 1044: Access denied for user ‘adam’@’localhost’ to database ‘test1’ when selecting the database

Grants for adam@localhost |

38
Q

You need to dump the data from the master server and import it into a new slave server.

Which mysqldump option can be used when dumping data from the master server In order to include the master server’s binary log information?

A. include-master-info

B. master-binlog

C. include-log-file

D. master-data

A

D. master-data

39
Q

Which two types of indexes are supported by the MEMORY storage engine? (Choose two.)

A. HASH indexes, which provide fast lookups that use a unique index

B. B-TREE indexes, which provide better performance with non-unique index values and other comparison operators

C. FULLTEXT indexes, which provide fast text searching

D. R-TREE indexes, which provide GIS data indexing

A

A. HASH indexes, which provide fast lookups that use a unique index

B. B-TREE indexes, which provide better performance with non-unique index values and other comparison operators

40
Q

On a master server that is using Statement-based replication a table of log data has become very large. You decide to delete 100,000 rows.

Which two methods can be independently invoked to ensure that the delete is properly propagated to the slave? (Choose two.)

A. Use the LIMIT clause to limit the deletion to 100,00 rows

B. Change the replication mode to MIXED before issuing any delete statements when the LIMIT clause is used

C. Use the LIMIT clause in conjunction with the ORDER BY clause

D. Use the data modification is non-deterministic, the query optimizer will resolve any potential issues

A

B. Change the replication mode to MIXED before issuing any delete statements when the LIMIT clause is used

C. Use the LIMIT clause in conjunction with the ORDER BY clause

41
Q

When the backup is taken, there are no errors.

Why are triggers missing from the backup?

A. The FILE privilege is required to back up the TRGG files stored in the database directory

B. The PROCESS privilege is required to back up triggers because triggers have a wide variety of abilities

C. The ALL privilege is required to back up triggers because triggers have a wide variety of abilities.

D. The TRIGGER privilege is required to create, execute, and show triggers in a database

A

D. The TRIGGER privilege is required to create, execute, and show triggers in a database

42
Q

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?

A. The system can only support one snapshot per volume, and you need to remove it to be able to take your next backup

B. The snapshot size will continue to grow as changes to the volume are made

C. The snapshots take a significant amount of disk space as they are a duplicate copy of the data

D. The system keeps a copy of changes in memory and can cause an out of memory event

A

B. The snapshot size will continue to grow as changes to the volume are made

43
Q

Which two statement are true about the mysql upgrade command? (Choose two.)

A. The mysql_upgrade command is a utility that patches the mysqld binary from its base version to a new version

B. The mysql upgrade command is run to check and attempt to fix tables for certain incompatibilities with the current version of MySQL

C. The mysql upgrade command executes on a stopped MySQL server data directory to ensure that it is prepared for upgrade

D. The mysql upgrade command also executes the mysqlcheck command in order to provide all of its functionality

A

B. The mysql upgrade command is run to check and attempt to fix tables for certain incompatibilities with the current version of MySQL

D. The mysql upgrade command also executes the mysqlcheck command in order to provide all of its functionality

44
Q

You have a server that has very limited memory but has a very large table.

You will use mysqldump to back up this table.

Which option will ensure mysqldump will process a row at a time instead of buffering a set of rows

A. quick
B. single-transaction
C. skip-buffer

A

A. quick

45
Q

What does the Performance Schema provide?

A. auto-tuning based on settings and ongoing workload
B. insight into the internal working of MySQL
C. recommendations for schema changes
D. a text-based version of MySQL Enterprise Monitor

A

B. insight into the internal working of MySQL

46
Q

Which two capabilities are granted with the SUPER privilege? (Choose two.)

A. Allowing a client to kill other client connections
B. Allowing change of the server runtime configuration
C. Allowing a client to shut down the server

A

A. Allowing a client to kill other client connections

B. Allowing change of the server runtime configuration

47
Q

Which three data components are needed for point-in-time recovery? (Choose three.)

A. The error log
B. The backup log
C. The general query log
D. Binary logs
E. The data backup
F. Configuration files
A

D. Binary logs
E. The data backup
F. Configuration files

48
Q

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?

A. Disconnecting idle localhost client sessions
B. Implementing storage engine data compression options
C. Relocating your data and log files to separate storage devices
D. Activating the MySQL Enterprise thread pool plugin

A

D. Activating the MySQL Enterprise thread pool plugin

49
Q

Consider the MySQL Enterprise Audit plugin,

You add the following lines to the my.cnf configuration tile:
[mysqld]
Plugin-load=audit_log.so
Audit-log=FORCE_PLUS_PERMANENT

You attempt to start up the MySQL service and notice that it fails to start.

Which two statements would explain why the service did not start? (Choose two.)

A. FORCE_PLUS_PERRMANENT is not valid for the audit-log option

B. The audit_log.so library does not exist

C. The audit_log.so library is in a location that is different from that defined by the plugin_dir option

D. The audit plugin must be loaded dynamically by using the INSTALL PLUGIN command

A

B. The audit_log.so library does not exist

C. The audit_log.so library is in a location that is different from that defined by the plugin_dir option

50
Q

Which three methods will show the storage engine for the Country table? (Choose three.)

A. SHOW CREATE TABLE Country;
B. SHOW ENGINE Country STATUS;
C. SHOW TABLE STATUS LIKE ‘Country’;
D. SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=’Country’;
E. SELECT ENGINE FROM INFORMATION_SCHEMA.ENGINES WHERE TABLE_NAAME=’Country’;

A

A. SHOW CREATE TABLE Country;

C. SHOW TABLE STATUS LIKE ‘Country’;

D. SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=’Country’;

51
Q

You are attempting to secure a MySQL server by using SSL encryption.

On starting MySQL, you get this error:
[ERROR] mysqld: unknown option ‘–ssl’

What is the cause of the error?

A. The –ssl level was not specified
B. The server was not started wit –enable-ssl-plugin option
C. –ssl is not a valid server option
D. The mysqld binary was not compiled with SSL support
E. The server’s SSL certificate was invalid

A

D. The mysqld binary was not compiled with SSL support

52
Q

An employee cannot access the company database. You check the connection variables:

Mysql> SHOW GLOBAL VARIABLES LIKE ‘%connect%’;
OUTPUT:
connect_timeout = 10
Init_connect 
max_connect_errors = 10
max_connections = 10
max_user_connections = 10

A look at the user privileges shows:
GRANT… TO ‘bob’@’%, example.com’ WITH MAX_USER_CONNECTIONS 0;
GRANT… TO ‘kay’@’%, example.com’ WITH MAX_USER_CONNECTIONS 1;
GRANT… TO ‘joe’@’%, example.com’ WITH MAX_USER_CONNECTIONS 50;

What is a valid explanation for why one of the users is unable to connect to the database?

A. Bob has max_user_connections set to zero, which blocks all his connections

B. Joe has exceeded the max_user_connections global limit

C. All users are blocked because max_user_connections is accumulated over the host account information

D. Kay is already connected elsewhere and attempting to log in again

E. Connect_timeout is too small to allow a connection to occur

A

D. Kay is already connected elsewhere and attempting to log in again

53
Q

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

mysqlbackup Cu root Cp C-socket=/tmp/my.sock C-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?

A. Backup State = Compressed Backup Operation = copy-back
B. Backup State = Prepared Backup Operation = validate
C. Backup State = Raw Backup Operation = backupdir-to-image

A

C. Backup State = Raw Backup Operation = backupdir-to-image

54
Q

You have a consistent InnoDB backup created with mysqldump, the largest table is 50 GB in size. You start to restore your backup with this command;

shell> mysql -u root -p < backup.sql

After 30 minutes, you notice that the rate of restore seems to have slowed down. No other processes or external factors are affecting server performance.

Which is the most likely explanation for this slowdown?

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

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

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

A

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

55
Q

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

A. supports native automatic failover

B. enables automatic resync of databases when discrepancies are detected

C. can easily add slaves for read scaling

D. synchronously guarantees identical slave copy

E. is easy to configure and has low performance overhead

A

C. can easily add slaves for read scaling

E. is easy to configure and has low performance overhead

56
Q

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?

A. Run the create_system_tables.sql file
B. Invoke mysql with the –initialize option
C. Invoke mysqld with the –initialize option

A

C. Invoke mysqld with the –initialize option

57
Q

Which two are considered good security practices when using passwords? (Choose two.)

A. Use one-way encryption for storage of passwords

B. Store password external to the database

C. Choose short passwords to save on storage space

D. Use simple keyboard actions that give mixed letters

E. Do not use dictionary-based words

A

A. Use one-way encryption for storage of passwords

E. Do not use dictionary-based words

58
Q

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?

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

B. Yes. Shut down, copy the UNDO tablespace to the new location, and change the innodb_undo_directory value in 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 tablespace is not supported on modern SSD block devices

A

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

59
Q

What are three methods to reduce MySQL server exposure to remote connections? (Choose three.)

A. using SSL when transporting data over remote networks

B. using the sql_mode=STRICT_SECURE after connection are established for encrypted communications

C. setting –skip-networking when connections are not required

D. setting specific grant privileges to limit remote authentication

E. setting –mysql_secure_configuration t enable paranoid mode

A

A. using SSL when transporting data over remote networks

C. setting –skip-networking when connections are not required

D. setting specific grant privileges to limit remote authentication

60
Q

You have a MySQL replication setup and you intentionally stop the SQL thread on the slave.

mysql> SHOW SLAVE STATUS\G
… Slave_IO_Running: YesSlave_SQL_Running: No

What are two reasons that you may stop the SQL thread on the slave while keeping the I/O thread running? (Choose two.)

A, to allow the remaining events to be processed on the slave while not receiving new events from the master

B. to allow a backup to be created under reduced load

C. to allow for point-in-time recovery on the slave

D. to prevent schema changes from propagating to the slave before they are validated

A

B. to allow a backup to be created under reduced load

C. to allow for point-in-time recovery on the slave

61
Q

Group Replication uses global transaction identifiers to track executed transactions and are fundamental in avoiding transaction conflict. Which additional three steps help in avoiding conflicts in group replication?

A. Set isolation level to be SERIALIZABLE
B. Use the binary log row format
C. Set isolation level to READ COMMITTED
D. Configure IPv6 network for hosts
E. Guarantee a secondary index on every table
F. Guarantee a primary key on every table
G. Set multiple slave parallel worker threads

A

A. Set isolation level to be SERIALIZABLE

B. Use the binary log row format

F. Guarantee a primary key on every table

62
Q

You are contacted by a user who does not have permission to access a database table. You determine after investigation that this user should be permitted to have access and so you execute a GRANT statement to enable the user to access the table.

Which statement describes the activation of that access for the user?

A. The access does not take effect until the user logs out and back in

B. The access does not take effect until the next time the server is started

C. The access is available immediately

D. The access does not take effect until you issue the FLUSH PRIVILEGES statement

A

C. The access is available immediately

63
Q

Suppose you are adding rows to a MyISAM table and the –datadir location runs out of disk space. What will happen when this occurs?

A. The server will crash

B. The server suspends that INSERT operation until space becomes available

C. An error message will be returned to the client> Server Error: ER_IO

D. The server suspends operations for all storage engines until space becomes available

A

B. The server suspends that INSERT operation until space becomes available

64
Q

In a design situation, there are multiple character sets that can properly encode your data.

Which three should influence your choice of character set? (Choose three.)

A. Disk usage when storing data

B. Syntax when writing queries involving JOINS

C. Comparing the encoded data with similar columns on other tables

D. Memory usage when working with the data

E. Character set mapping index hash size

A

C. Comparing the encoded data with similar columns on other tables

D. Memory usage when working with the data

E. Character set mapping index hash size

65
Q

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?

A. The system can only support one snapshot per volume, and you need to remove it to be able to take your next backup

B. The snapshot size will continue to grow as changes to the volume are made

C. The snapshots take a significant amount of disk space as they are a duplicate copy of the data

D. The system keeps a copy of changes in memory and can cause an out of memory event

A

B. The snapshot size will continue to grow as changes to the volume are made

66
Q

A user executes the statement;
PURGE BINARY LOGS TO ‘mysql-bin.010’;

What is the result?

A. It deletes all binary log files, except ‘mysql-in.010’.

B. It deletes all binary log files up to and including ‘mysql-in.010’.

C. It deletes all binary log files before ‘mysql-in.010’.

D. It deletes all binary log files after ‘mysql-in.010’.

A

C. It deletes all binary log files before ‘mysql-in.010’.

67
Q

You examine the output of SHOW GLOBAL STATUS and notice that the value of Created_tmp_disk_tables is consistently increasing.

Which two variables would likely fix this issue? (Choose two.)

A. Table_open_cache
B. Table_open_cache_instances
C. Table_definition_cache
D. Tmp_table_size
E. Max_heap_table_size
F.Max_tmp_ttables
A

D. Tmp_table_size

E. Max_heap_table_size

68
Q

You need to replicate a table from a master to a slave. The master and slave copies of the table will have different number of columns.

Which two conditions must be true? (Choose two.)

A. Each extra column in the copy with more columns must not have a default value

B. Columns that are common to both versions of the table must be defined n the same order on the master and the slave

C. The slave database cannot have more columns than the master. Only the master database can have more columns

D. Columns that are common to both versions of the table must come first in the table definition, before any additional columns are additional columns are defined on either server

A

B. Columns that are common to both versions of the table must be defined n the same order on the master and the slave

D. Columns that are common to both versions of the table must come first in the table definition, before any additional columns are additional columns are defined on either server

69
Q

Which three statements are true about memory buffer allocation by a MySQL Server? (Choose three.)

A. Global buffers such as the InnoDb buffer pool are allocated after the server starts, and are never freed

B. Thread buffers are allocated when a client connects, and are freed when the client disconnects

C. Buffers that are needed for certain operation are allocated when the operation starts, and freed when it ends

D. Use buffers are allocated at server startup and freed when the user is dropped

E. All dynamic buffers that are set with a SET GLOBAL statement immediately get allocated globally, and are never freed

A

A. Global buffers such as the InnoDb buffer pool are allocated after the server starts, and are never freed

C. Buffers that are needed for certain operation are allocated when the operation starts, and freed when it ends

E. All dynamic buffers that are set with a SET GLOBAL statement immediately get allocated globally, and are never freed

70
Q

How do you determine the server SQL mode? (Choose three.)

A. SET GLOBAL sql_mode='modes';
B. SET SESSION sql_mode='modes';
C. Update performance_schema
D. Update information_schema
E. Set the default SQL mode for the mysld startup with the --sql-mode option
A

A. SET GLOBAL sql_mode=’modes’;

B. SET SESSION sql_mode=’modes’;

E. Set the default SQL mode for the mysld startup with the –sql-mode option

71
Q

What are three actions performed by the mysql_secure_installation tool? (Choose three.)

A. It prompts you to set the root user account password

B. It checks whether file permissions are appropriate within datadir

C. It asks to remove the test database, which is generated at installation time

D. It can delete any anonymous accounts

E. It verifies that all users are configuration with the longer password hash

A

A. It prompts you to set the root user account password

C. It asks to remove the test database, which is generated at installation time

D. It can delete any anonymous accounts

72
Q

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? (Choose three.)

A. Use of soft links for database directories on the same physical disks

B. Use of separate directories on the same physical disk for log files and data files

C. Placement of InnoDB log files and datadir on separate physical disks

D. Allocation of RAM to the buffer pool such that more of the data can fit in RAM

E. Use of delay_key_write=ON for batch index update

A

C. Placement of InnoDB log files and datadir on separate physical disks

D. Allocation of RAM to the buffer pool such that more of the data can fit in RAM

E. Use of delay_key_write=ON for batch index update

73
Q

You have been notified that the ‘apps’ . ‘reports’ table has been accidentally truncated.

You have single file mysqldump backup available taken prior to the truncate. The backup contains all the tables from the instance, and the ‘apps’ . ‘reports’ table must be restored without affecting the other remaining databases and tables.

Which restore option is suitable in this scenario?

A. Restore the backup to another database instance and obtain a copy of the reports table individually

B. Extract the ‘apps’. ‘reports’ table from the backup using the SOURCE command

C. Execute LOAD DATA INFILE ‘backup.sql’ SCHEMA=’apps’ TABLE =’reports’

D. Execute mysqdump on the backup.sql file and apply -filter arguments to obtain only the apps’. ‘reports’ table

A

A. Restore the backup to another database instance and obtain a copy of the reports table individually

74
Q

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?

A. –skip-networking, to prohibit access from remote locations

B. –reset-grant-tables, to start the server with only the mysql database accessible

C. –read-only, too set all data to read-only except for super users

D. –old-passwords, to start MySQL to use the old password format while running without the grant tables

A

A. –skip-networking, to prohibit access from remote locations

75
Q

Which two requirements would lead towards a high availability solution? (Choose two.)

A. When uptime is critical
B. When data must be refractured
C. When application concurrency is status
D. When data loss is unacceptable
E. When application is a single point of failure

A

A. When uptime is critical

E. When application is a single point of failure

76
Q

Which statement is true about using Microsoft Windows Cluster as a platform for Mysql?

A. It is provided by means of IP-level disk replication

B. It is shared-nothing architecture

C. It implements High Availability by using the .NET Connector’s load balancing capabilities

D. It relies on the shared disk architecture being visible to both servers

A

D. It relies on the shared disk architecture being visible to both servers

77
Q

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?

A. shell> mysqlbinlog bin.00010 | mysql shell> mysqlbinlog bin,00011 | mysql shell> mysqlbinlog bin.00012 | mysql

B. shell> mysqlbinlog bin.00010 bin.00011 bin.00012 | mysql

C. shell> mysqlbinlog -restore bin.00010 bin.00011 bin.00012

D. shell> mysqlbinlog -include bin.00010 bin.00011 bin.00012 | mysql

A

B. shell> mysqlbinlog bin.00010 bin.00011 bin.00012 | mysql

78
Q

Which two statements are true about setting the per-thread buffers higher than required? (Choose two.)

A. More memory per thread is beneficial in all scenarios

B. It causes increased overhead due to initial memory allocation

C. It can affect system stability during peak load times, due to swapping

D. It requires increasing the thread_cache_size variable

A

C. It can affect system stability during peak load times, due to swapping

D. It requires increasing the thread_cache_size variable

79
Q

You are investigating the performance of the server and see the following 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?

A. Decrease the value of table_definition_cache
B. Increase the value of table_definition_cache
C. Decrease the value of table_open_cache
D. Increase the value of table_open_cache
E. Decrease the value of table_open_cache_instances
F. Increase the value of table_open_cache_instances

A

F. Increase the value of table_open_cache_instances

80
Q

Which statement is true about the log-output variable?

A. It is static variable and can be set only at MySQL serverr startup

B. It enables and starts the general query log

C. It sets the target location for the binary logs generated by the MySQL server

D. It specifies output destinations for the slow and general query logs

A

D. It specifies output destinations for the slow and general query logs

81
Q

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 sorting 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?

A. Execute OPTIMIZE TABLE so that the InnoDB engine frees unused pages on disk back on the file system:
Mysql> OPTIIMIZE TABLE data_currentt, data_reprts;

B. 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:
mysql> SET GLOBAL innodb_file_perr_table=1; mysql> ALTER TABLE data_current ENGINE=innoDB; mysql> ALTER TABLE data_reports ENGINE=InnoDB;

C. Take backup, stop the server, remove the data files, and restore the backup: shell> mysqldump -u root -p applicationdb /> /backup/applicationdb.sql Shell> /etc/init.d/mysql stop Shell> cd /var/lib/mysql/ Shell> rm iddata1 ib_logfile0 id_logfile 1 Shell> myysql -u root -p applicaationdb < backup/applicationdb.sql

A

C. Take backup,
shell> mysqldump -u root -p applicationdb > /backup/applicationdb.sql

stop the server,
Shell> /etc/init.d/mysql stop

remove the data files,
Shell> cd /var/lib/mysql/
Shell> rm idata1 ib_logfile0 id_logfile 1

and restore the backup:
Shell> myysql -u root -p applicaationdb < backup/applicationdb.sql

82
Q

A general purpose MySQL instance is configured with the following options:

    • log-slow-queries
    • long-query-time=1
    • log-slow-admin-queries
    • general-log
    • log-bin
    • binlog-format=STATEMENT
    • innodb-flush-log-at-trx-commit=1

Which three statements are true? (Choose three.)
A. The general query log records more data than the binary log
B. The binary log records more data than the general query log
C. The slow query log records more data than the general query log
D. The general query log records more data than the slow query log
E. The slow query log records more data than the binary log
F. the binary log records more data than the slow query log

A

A. The general query log records more data than the binary log

D. The general query log records more data than the slow query log

F. the binary log records more data than the slow query log

83
Q

Compare a typical Distributed Replicated Block Device (DRBD) with MySQL Standard Replication using master-slave replication.

Which two statements are correct? (Choose two.)

A. Both technologies use the TCP/IP stack as their primary transmission medium

B. DRBD uses shared-disk technology

C. Both technologies guarantee an identical copy of data on the secondary node

D. Only MySQL can maintain a time-delayed copy of data on the secondary node

A

A. Both technologies use the TCP/IP stack as their primary transmission medium

D. Only MySQL can maintain a time-delayed copy of data on the secondary node