|Exam Name||:||Sun Certified MySQL 5.0 Database(R) Administrator Part II|
|Questions and Answers||:||138 Q & A|
|Updated On||:||February 21, 2018|
|PDF Download Mirror||:||310-811 Brain Dump|
|Get Full Version||:||Pass4sure 310-811 Full Version|
Which of the following disk characteristics are most important to a database server and why?
Disk size, because a database needs a lot of storage space.
Transfer rate, because the database needs to be able to transfer the data off the disks at a very high rate.
Disk seek time, because it is more important for the heads of the disk to move quickly from track to track.
Which of the following best describe some possible benefits of using RAID?
Better user-level security.
Read performance gains.
Write performance gains.
Hot-swappable disk drives.
Better memory and processor utilization.
Suppose you have a server whose log files, data directory, and temporary files are distributed on different disks. How would this affect overall performance, compared to a server where all components reside on a single disk?
Performance would remain about the same, because the same amount of disk usage would be needed.
You would expect a performance gain because the disk related activities would be split more evenly.
Performance would drop because the server would have to do extra work to read information off of the different disks.
Which of the following are methods to improve performance?
Spreading single tables to different disks pointed to by a symbolic link.
Spreading whole databases to different disks pointed to by a symbolic link.
Moving the MySQL utilities such as mysqladmin to different disks pointed to by a symbolic link.
Moving less needed parts of the MySQL install such as the documentation or the test files to different disks pointed to by a symbolic link.
The type of file system you chose may affect MySQL use and/or performance with regard to...
Table opening time.
Size limits of files.
Time to recover from a crash.
The number of users MySQL can handle.
The type of storage engines that can be used.
Which of the following steps are used to create database symbolic links on Windows?
Stop the server.
Restart the server.
Use the ln -s command.
Update your my.ini with the path to the new location.
Move the database directory from the data directory to a new location.
Create a file in the data directory with the database name with an extension of .sym that contains the full path to the new location.
Which of the following best describes the effect that the per-process limit of open file descriptors in an operating system has on MySQL, and what can be done to increase it?
The --open-files-limit option can be used to increase it.
It limits the amount of operations MySQL can perform at one time.
It limits the maximum size of the table cache that holds file descriptors.
The operating system may be able to be reconfigured to increase this limit.
Which of the following best describes what may limit the number of simultaneous connections to the server? What are possible ways to increase it?
It is limited by the maximum size of the table cache.
The --key_cache option may be used to increase the limit.
The maximum number of threads per-process of the operating system.
The operating system may be able to be reconfigured to increase this limit.
Which of the following best describes what the connection backlog is and why it may need to be increased?
It may need to be increased on a server with a high rate of transactions.
The backlog is the queue of processes waiting to be processed on a server.
The backlog is the queue of network connections of clients waiting to connect.
It may need to be increased on a server with a high rate of client connections.
Which of the following are requirements for running more than one MySQL server on the same machine?
On Windows each server that runs as a service must have a different service name.
Each server must have its own log files.
Each server must share the same error log.
Each server must run on a different disk.
Each server must have its own network interfaces.
There must be more than one processor on the machine.
InnoDB tablespace files must be separate between servers.
Which of the following are general features of replication?
Can be used to distribute the load caused by servicing many clients.
Provides complete redundancy and high availability at all times.
Multiple copies are available in case a server crashes or goes offline.
Allows the databases on one server to be made available on another server.
What kind of replication is supported by the MySQL server?
Master to slave replication
Single file based clustering
MySQL doesn't support replication
Can you have several MySQL servers running on the same computer, using the same InnoDB tablespace files?
Yes, provided their permissions are set to read-only
Which of the following is/are required on the master server for replication to work?
Issue the START MASTER command.
Binary logging must be turned on.
Tell the master which slaves to connect to.
Make a backup of all databases to be replicated.
Which of the following best describes what MASTER_LOG_FILE and MASTER_LOG_POS provides?
The path of the log file.
The file to log replication actions to.
The masters binary log name used for replication.
The position in the masters binary log in which to start replication.
Which of the following best describes what the master.info file contains and how it is used?
It contains the values from the CHANGE MASTER statement.
When the slave restarts it looks for which master to use from this file.
It contains information about the master server, its slaves and its configuration.
It is used by an administrator to determine what slaves connect to the master, and other information about the master server.
Nature fans all over have good reason to love the historic Natchez Trace Parkway. This National Scenic Byway is perfect place to experience the beautiful outdoors! Out of the 52,000 acres of Trace, almost 39,000 of those are maintained in a “natural” condition. That means you’ll find bountiful forests, non-agricultural fields and sparkling open water.
Article by ArticleForge
About This Book.
Other Required Reading
Study Guide Errata
Certification Information at .mysqlm
The MySQL Certification Candidate Guide
The Certification Mailing List
Conventions Used in This Book
Running MySQL on Microsoft Windows
About the Exams
Registering for an Exam
Going to the Exam
Taking the Exam
After the Exam
Interpreting DESCRIBE Output
MYSQL DEVELOPER EXAMS.
MySQL Developer I Exam.
1. ClientServer Concepts.
General MySQL Architecture
Invoking Client Programs
General Command Option Syntax
Connection Parameter Options
Using Option Files
Selecting a Default Database
Establishing a Connection with a GUI Client
Server SQL Modes
2. The mysql Client Program.
Using mysql Interactively
The mysql Prompts
Using Editing Keys in mysql
Using Script Files with mysql
mysql Output Formats
Client Commands and SQL Statements
Using Server-Side Help
Using the --safe-updates Option
3. MySQL Query Browser.
MySQL Query Browser Capabilities
Using MySQL Query Browser
Using the Query Window
The Result Area
The Script Editor
Stored Routine Management
The Object and Information Browsers
The MySQL Table Editor
Using the Connection Dialog
Editing Connection Profiles
The Options Dialog
4. MySQL Connectors.
MySQL Client Interfaces
5. Data Types.
Data Type Overview
Numeric Data Types
Integer Data Types
Floating-Point Data Types
Fixed-Point Data Types
The BIT Data Type
String Data Types
Character Set Support
Non-Binary String Data Types: _CHAR, VARCHAR, TEXT
Binary String Data Types: BINARY, VARBINARY, BLOB
The ENUM and SET Data Types
Temporal Data Types
The DATE, TIME, DATETIME, and YEAR Data Types
The TIMESTAMP Data Type
Per-Connection Time Zone Support
Numeric Column Attributes
String Column Attributes
General Column Attributes
Using the AUTO_INCREMENT Column Attribute
Handling Missing or Invalid Data Values
Handling Missing Values
Handling Invalid Values in Non-Strict Mode
Handling Invalid Values in Strict Mode
Enabling Additional Input Data Restrictions
Overriding Input Data Restrictions
Case Sensitivity of Identifiers
Using Qualified Names
Using Reserved Words as Identifiers
Obtaining Database Metadata
8. Tables and Indexes.
Creating Tables Using an Explicit Definition
Specifying the Storage Engine for a Table
Creating Tables Based on Existing Tables
Using TEMPORARY Tables
Adding and Dropping Columns
Modifying Existing Columns
Renaming a Table
Specifying Multiple Table Alterations
Types of Indexes
Choosing an Indexing Algorithm
Obtaining Table and Index Metadata
9. Querying for Data.
Using SELECT to Retrieve Data
Specifying Which Columns to Retrieve
Renaming Retrieved Columns
Identifying the Database Containing a Table
Specifying Which Rows to Retrieve
Using ORDER BY to Sort Query Results
The Natural Sort Order of Data Types
Limiting a Selection Using LIMIT
Using DISTINCT to Eliminate Duplicates
The MIN() and MAX() Aggregate Functions
The SUM() and AVG() Aggregate Functions
The COUNT() Aggregate Function
The GROUP_CONCAT() Function
Aggregation for NULL Values or Empty Sets
GROUP BY and Sorting
Selecting Groups with HAVING
Using GROUP BY and WITH ROLLUP
10. SQL Expressions.
Components of SQL Expressions
Case Sensitivity in String Comparisons
Using LIKE for Pattern Matching
Functions in SQL Expressions
Control Flow Functions
Comments in SQL Statements
11. Updating Data.
The INSERT Statement
Adding Multiple Records with a Single INSERT Statement
Handling Duplicate Key Values
Using INSERT ... ON DUPLICATE KEY UPDATE
The REPLACE Statement
The UPDATE Statement
Using UPDATE with ORDER BY and LIMIT
Preventing Dangerous UPDATE Statements
Multiple-Table UPDATE Statements
The DELETE and TRUNCATE TABLE Statements
Using DELETE with ORDER BY and LIMIT
Multiple-Table DELETE Statements
Privileges Required for Update Statements
MySQL Developer II Exam.
Writing Inner Joins
Writing Inner Joins with the Comma Operator
Writing Inner Joins with INNER JOIN
Writing Outer Joins
Writing LEFT JOIN Queries
Writing RIGHT JOIN Queries
Resolving Name Clashes Using Qualifiers and Aliases
Qualifying Column Names
Qualifying and Aliasing Table Names
Multiple-Table UPDATE and DELETE Statements
Types of Subqueries
Subqueries as Scalar Expressions
Comparing Subquery Results to Outer Query Columns
Using ALL, ANY, and SOME
Comparison Using Row Subqueries
Using Subqueries in the FROM Clause
Converting Subqueries to Joins
Converting Subqueries to Inner Joins
Converting Subqueries to Outer Joins
Using Subqueries in Updates
Reasons to Use Views
Restrictions on Views
Obtaining View Metadata
Privileges Required for Views
15. Importing and Exporting Data.
Import and Export Operations
Importing and Exporting Using SQL
Importing Data with LOAD DATA INFILE
Exporting Data with SELECT ... INTO OUTFILE
Data File Format Specifiers
Importing and Exporting NULL Values
Importing and Exporting Data from the Command Line
Importing Data with mysqlimport
Exporting Data with mysqldump
16. User Variables.
User Variable Syntax
User Variable Properties
17. Prepared Statements.
Benefits of Prepared Statements
Using Prepared Statements from the mysql Client
Preparing a Statement
Executing a Prepared Statement
Deallocating Prepared Statements
18. Stored Procedures and Functions.
Benefits of Stored Routines
Differences Between Stored Procedures and Functions
The Namespace for Stored Routines
Defining Stored Routines
Creating Stored Routines
The DECLARE Statement
Variables in Stored Routines
Conditions and Handlers
Retrieving Multiple Result Sets
Altering Stored Routines
Dropping Stored Routines
Invoking Stored Routines
Obtaining Stored Routine Metadata
Stored Routine Privileges and Execution Security
Reasons to Use Triggers
Creating a Trigger
Restrictions on Triggers
Referring to Old and New Column Values
Destroying a Trigger
Privileges Required for Triggers
20. Obtaining Database Metadata.
Overview of Metadata Access Methods
Using INFORMATION_SCHEMA to Obtain Metadata
Using SHOW and DESCRIBE to Obtain Metadata
Using mysqlshow to Obtain Metadata
21. Debugging MySQL Applications.
Interpreting Error Messages
The SHOW WARNINGS Statement
The SHOW ERRORS Statement
The perror Utility
22. Basic Optimizations.
Overview of Optimization Principles
Using Indexes for Optimization
Types of Indexes
Principles for Index Creation
Indexing Column Prefixes
Leftmost Index Prefixes
General Query Enhancement
Query Rewriting Techniques
Using EXPLAIN to Obtain Optimizer Information
Optimizing Queries by Limiting Output
Using Summary Tables
Choosing Appropriate Storage Engines
MYSQL DBA EXAMS.
MySQL DBA I Exam.
23. MySQL Architecture.
The SQL Parser and Storage Engine Tiers
How MySQL Uses Disk Space
How MySQL Uses Memory
24. Starting, Stopping, and Configuring MySQL.
Types of MySQL Distributions
MySQL Binary Distributions
MySQL Source Distributions
Starting and Stopping MySQL Server on Windows
Server Startup Prerequisites on Windows
Running MySQL Server Manually on Windows
Running MySQL Server as a Windows Service
Starting and Stopping MySQL Server on Unix
Server Startup Prerequisites on Unix
Choosing a Server Startup Method on Unix
Runtime MySQL Configuration
Log and Status Files
The General Query Log
The Binary Log
The Slow Query Log
The Error Log
Loading Time Zone Tables
Setting the Default SQL Mode
25. Client Programs for DBA Work.
Overview of Administrative Clients
Client Program Limitations
26. MySQL Administrator.
MySQL Administrator Capabilities
Using MySQL Administrator
Starting MySQL Administrator
Selecting an Operational Mode
Server Monitoring Capabilities
Backup and Restore Capabilities
MySQL Administrator System Tray Monitor
27. Character Set Support.
Choosing Data Types for Character Columns
Explicit Table Locking
29. Storage Engines.
MySQL Storage Engines
The MyISAM Engine
MyISAM Locking Characteristics
MyISAM Row-Storage Formats
The MERGE Engine
MERGE Locking Characteristics
The InnoDB Engine
The InnoDB Tablespace and Logs
InnoDB and ACID Compliance
The InnoDB Transaction Model
InnoDB Locking Characteristics
InnoDB Isolation Levels, Multi-Versioning, and Concurrency
Using Foreign Keys
Configuring and Monitoring InnoDB
The MEMORY Engine
MEMORY Indexing Options
The FEDERATED Engine
The Cluster Storage Engine
Other Storage Engines
30. Table Maintenance.
Types of Table Maintenance Operations
SQL Statements for Table Maintenance
Client and Utility Programs for Table Maintenance
The mysqlcheck Client Program
The myisamchk Utility
Options for mysqlcheck and myisamchk
Repairing InnoDB Tables
Enabling MyISAM Auto-Repair
31. The INFORMATION_SCHEMA Database.
INFORMATION_SCHEMA Access Syntax
INFORMATION_SCHEMA Versus SHOW
Limitations of INFORMATION_SCHEMA
32. Data Backup and Recovery Methods.
Binary Versus Textual Backups
Making Binary Backups
Making Binary MyISAM Backups
Making Binary InnoDB Backups
Other Binary Backup Tools
Conditions for Binary Portability
Making Text Backups
Making Text Backups via SQL
Making Text Backups with mysqldump
Making Text Backups with MySQL Administrator
Backing Up Log and Status Files
Replication as an Aid to Backup
MySQL Cluster as Disaster Prevention
Reloading mysqldump Output
Reloading Dumps with MySQL Administrator
Processing Binary Log Contents
33. Using Stored Routines and Triggers for Administration.
Using Stored Routines and Triggers for Security Purposes
Using Stored Routines to Enhance Performance
MySQL DBA II EXAM.
34. User Management.
User Account Management
Types of Privileges That MySQL Supports
The Grant Tables
Approaches to Account Management
Creating and Dropping User Accounts
Specifying Account Names
Changing Account Passwords
When Privilege Changes Take Effect
Specifying Resource Limits
Privileges Needed for Account Management
Client Access Control
Connection Request Checking
Statement Privilege Checking
Resource Limit Checking
Disabling Client Access Control
35. Securing the MySQL Installation.
Operating System Security
Log Files and Security
Securing the Initial MySQL Accounts
General Privilege Precautions
MySQL Cluster Network Security
FEDERATED Table Security
36. Upgrade- Security Issues.
Upgrading the Privilege Tables
Security- SQL Mode Values
37. Optimizing Queries.
Identifying Candidates for Query Analysis
Using EXPLAIN to Analyze Queries
How EXPLAIN Works
Analyzing a Query
EXPLAIN Output Columns
Using SHOW WARNINGS for Optimization
MyISAM Index Caching
38. Optimizing Databases.
General Table Optimizations
MyISAM Row-Storage Formats
Keep Optimizer Information Up to Date
Specifying MyISAM Maximum Row Count
39. Optimizing the Server.
Interpreting mysqld Server Information
Accessing Server System Variables
Accessing Server Status Variables
Measuring Server Load
Tuning Memory Parameters
Global (Server-Wide) Parameters
Using the Query Cache
Enabling the Query Cache
Measuring Query Cache Utilization
40. Interpreting Diagnostic Messages.
Sources of Diagnostic Information
Using the Error Log for Diagnostic Purposes
Using The Slow Query Log for Diagnostic Purposes
41. Optimizing the Environment.
Choosing Hardware for MySQL Use
Configuring Disks for MySQL Use
Moving Databases Using Symbolic Links
MyISAM Table Symlinking
Optimizing the Operating System for MySQL Use
42. Scaling MySQL.
Using Multiple Servers
Setting Up Replication
The Binary and Relay Logs
Replication Compatibility and Upgrading
B. Other Offers.