Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
125 Cards in this Set
- Front
- Back
What's MySQL query browser?
|
A general purpose graphical client to perform data analysis, not included in the distribution.
|
|
What's MySQL Administrator?
|
A graphical client used to administer the server, not included in the distribution.
|
|
Name 3 MySQL connectors.
|
MySQL/j, MySQL/Net, MySQL/ODBC
|
|
Name 4 supported networking protocols and the platforms on which they're supported.
|
TCP/IP - All
Unix socket - unix only Shared memory, named pipes - Windows |
|
What's the option to disable TCP/IP?
|
skip-networking
|
|
Which option enables named pipes?
|
enable-named-pipe
|
|
Which option enables shared memory?
|
shared-memory
|
|
How does JDBC performance compare to native?
|
Roughly the same on any platform
|
|
How does ODBC performance compare to native?
|
Native is better, as ODBC is layered on top of it
|
|
How do named pipes compare to TCP?
|
Much slower.
|
|
What are the two layers of a MySQL server?
|
- parser and optimizer
- storage engine |
|
Two index types supported only by MyISAM
|
Fulltext, spatial
|
|
How do you convert tables from one storage engine to the other?
|
ALTER TABLE ... ENGINE ...
|
|
What's a database directory?
|
A directory corresponding to a specific database, located in the data directory, containing table format files (.frm)
|
|
Name the default InnoDB tablespace file
|
ibdata1
|
|
Describe the servers in the Windows distribution
|
- mysqld (normal)
- mysqld-nt (supports named pipes) -mysqld-max and -max-nt (support addition storage engines) -mysqld-debug (has debugging support) |
|
How do you shutdown the server on Windows?
|
mysqladmin shutdown
|
|
What's the No-Install distribution?
|
A Windows distribution without installer, just unpack where you want to have it
|
|
Purpose of the --install option?
|
Install mysql as a service. Does not start the service.
|
|
How do you remove the mysql service on WIndows?
|
mysql -remove
|
|
Default data directory on Unix?
|
/var/lib/mysql
|
|
What's the purpose of mysql_install_db?
|
Initialize data directory and create system tables if they don't exist
|
|
What's the purpose of mysqld_safe?
|
Sets up error log, starts mysqld and monitors it. It restarts mysqld if it terminates abnormally.
|
|
What's the purpose of mysqld_multi?
|
Manage multiple mysqld servers on the same host
|
|
What's the default error log on Unix?
|
hostname.err in the data directory
|
|
What's mysqlmanager?
|
It was a commercial offering from mysql AB to manage multiple instances.
|
|
What's the --basedir option?
|
The installation directory
|
|
Standard location of options file on WIndows
|
my.ini or .conf in C:\Windows, then C:\
|
|
Standard location of options file on Unix
|
/etc/my.cnf, $MYSQL_HOME/my.cnf, ~/my.cnf
|
|
What's the option to set the default storage engine?
|
default-storage-engine
|
|
What's the general query log and how do you set it?
|
Logs the text of every query. Set by --log or --log=filename
|
|
What's the binlog?
|
Log of statements that modify data. No SELECT, but INSERT and UPDATE
|
|
How do you enable the binary log?
|
--log-bin or --log-bin=filename
|
|
What's the binary log index?
|
Records the names of current binlog files.
|
|
WHich variable determines what gets logged in the slow query log?
|
long-query-time, default 10
|
|
What's the purpose of the PID file and how do you set it?
|
--pid-file=filename. COntains the PID of the running instance so that it can be used to kill etc
|
|
What's the option to accept backward compatible authentication (from old clients)?
|
--old-password
|
|
How can you specify to accept only new style authentication?
|
--secure-auth
|
|
Basic steps to upgrading MySQL
|
Backup, stop server, upgrade, start server
|
|
What's the command to import a file?
|
mysqlimport
|
|
What's the command to dump database contents?
|
mysqldump
|
|
How can you check which character sets are available?
|
SHOW CHARACTER SET
|
|
Describe reader and writer locking behaviour.
|
Reader must block writers. Writers must block all.
|
|
How can locks be set explicitly?
|
By LOCK TABLE
|
|
Innodb vs MyISAM locking
|
row vs table
|
|
BDB locking occurs on which level?
|
Page.
|
|
Name the 3 files used for MyISAM tables
|
.frm, .myd, and myi
|
|
Describe MyISAM locking
|
At table level (good for reading, bad for mixed workload)
|
|
Can there be a deadlock with MyISAM tables?
|
No. It's not possible with table level locking.
|
|
Do MyISAM tables support concurrent inserts?
|
Yes, if the table has no holes. In that case, insert are done by appending at the end of the file.
|
|
How can you remove holes?
|
With OPTIMIZE TABLES
|
|
Name the 3 MyISAM row-storage formats
|
- Fixed row
- Dynamic row - Compressed |
|
Describe the MERGE engine
|
Several underlying tables with same structure. Like a virtual union. Can exceed normal size of MyISAM tables
|
|
What are the two components of a backup?
|
The database files and the binlog. The binlog can be used to replay statements made after the backup was created.
|
|
Two types of backups
|
Binary (copy the files) and textual (dump of the contents)
|
|
Advantages of binary vs textual backup
|
Binary is faster but textual is more portable
|
|
What's the effect of FLUSH TABLES?
|
Closes all open and in-use tables and clears the query cache
|
|
4 steps to binary backup MyISAM tables
|
1. Acquire read lock
2. Flush table 3. Copy files 4. Release lock |
|
3 steps to restore MyISAM tables
|
Stop server
Copy files to database dir Start server |
|
4 steps for binary InnoDB backup
|
1. Stop server
2. Verify clean shutdown 3. Copy files 4. Restart server |
|
4 types of files to copy in an InnoDB binary backup
|
.frm
tablespace file(s) + .ibd files if per-table tablespaces are used innoDB log files InnoDB configuration files |
|
3 steps for a binary InnoDB restore
|
Stop server, copy files, start server
|
|
mysqlhotbackup works for which storage engine?
|
MyISAM
|
|
What does mysqlhotcopy do?
|
Perform the steps of a MyISAM binary backup
|
|
Syntax for mysqlhotcopy?
|
mysqlhotcopy database/[table] /backup/path
|
|
Restrictions of mysqlhotcopy?
|
Must run on the DB host
|
|
What's InnoDB Hot Backup?
|
A commercial product (ibbackup)
|
|
What is binary portability?
|
Copying and restoring binary database backups on a different server
|
|
What are the conditions for binary portability?
|
- Both machines two-complement integer arithmetic
- Both machines IEEE floating point |
|
How can you warrant binary portability between Unix and Windows?
|
Use lower case for tables and database names
|
|
How can you enforce lower case for tables and database names?
|
--lower_case_table_names=1
|
|
SQL command to create text backup?
|
select ... into outfile 'filename' from tablename
|
|
Where does select into outfile store the file?
|
On the server
|
|
What does mysqldump do?
|
Dump database/tables and optionally more in SQL format
|
|
Besides mysqdump and select into outfile, how can you create a text backup
|
MySQL Administrator
|
|
3 steps to backup from a slave
|
STOP SLAVE SQL_THREAD
Backup START SLAVE SQL_THREAD |
|
How do you reprocess binlogs?
|
mysqbinlog --start-datetime <datetime> <binfiles>|mysql
|
|
Difference between table check, repair, analysis, and optimization
|
- Check for integrity
- Repair integrity issues - Analyze key distribution for the benefit of the optimizer - Optimize table contents so that it can be accessed faster |
|
Can you repair an InnoDB table?
|
No
|
|
What does table optimization do in detail
|
Defragment data file to reclaim gaps, sort indexes, update statistics
|
|
Difference between mysqlcheck and myisamchk
|
mysqlcheck sends SQL commands to the server, myisamchk operates on data files
|
|
Default mysqlcheck operation
|
check. Optionally, analyze, repair, optimize
|
|
Syntax to repair table with myisamchk
|
myisamchk --recover tablename
|
|
What does myisamchk --safe-recover do?
|
Similar to -recover, but slower and can recover more errors
|
|
What can you do if CHECK TABLE shows an InnoDB table with problems?
|
mysqldump and then restore it. This will rebuild the structure.
|
|
Option to enable MyISAM autorepair
|
--myisam-recover
|
|
INFORMATION_SCHEMA vs SHOW
|
schema is portable, show is mysql specific. schema starts in v5. Show more concise. Schema can do joins etc
|
|
Limitations of INFORMATION_SCHEMA in 5.0
|
- no parameters
- no constraints - no runtime information (processlist etc) (All addressed in later versions) |
|
./configure parameter to set default charset
|
--with-charset
|
|
./configure parameter to set available charsets
|
--with-extra-charset
|
|
How a MEMORY and MERGE tables locked?
|
at the table level
|
|
What's a READ LOCAL lock?
|
Like READ, but allows concurrent write. MyISAM only
|
|
What's advisory locking?
|
A feature for users, they can get_lock('lockname') and then check is_free_lock('lockname'). The locks are not enforced
|
|
2 operational modes of mysql Administrator
|
normal and configure-service
|
|
What does "server connections" show in MySQL Administrator?
|
Basically "show processlist"
|
|
What kind of backup can you do in MySQL Administrator?
|
text backups
|
|
Describe the FEDERATED engine
|
Makes tables on other server accessible. Comment contains the connection string. Server acts as client to remote server
|
|
What's mysql cluster?
|
Uses the NBD engine, which keeps all records on several nodes, giving HA. Storage in memory
|
|
What does INSERT LOW_PRIORITY do?
|
Delay the insert until nobody's reading from the table (potentially a long time). Disables concurrent inserts on MyISAM
|
|
What is INSERT DELAYED?
|
Basically an async insert. The server returns OK immediately, but inserts later
|
|
What's the command to unblock an advisory lock?
|
release_lock('lockname')
|
|
Define atomic
|
All the statements execute successfully or are canceled as a unit.
|
|
Define consistent
|
A database that is in a consistent state when a transaction begins is left in a consistent state by the transaction.
|
|
Define isolated
|
One transaction does not affect another.
|
|
Define durable
|
All the changes made by a transaction that completes successfully are recorded properly in the database. Changes are not lost.
|
|
Two ways to group statements into a transaction with InnoDB
|
- Disable autocmmit with set autocommit=0
- Explicitly start transaction with start transaction |
|
What are savepoints?
|
Allow you to roll back part of a transaction
|
|
Syntax for savepoints
|
- create with "savepoint savepointname;"
- rollback to savepoint savepointname |
|
Do reads require locks with InnoDB?
|
No, because of multiversioning.
|
|
How does InnoDB handle deadlocks?
|
It tries to detect them and to roll back the transaction modifying less rows. If not possible to detect, at some point the transaction will timeout
|
|
Two ways to set explicit locks with InnoDB
|
select ... lock in share mode
select ... lock for update |
|
What's a dirty read?
|
One transaction reads data that another one hasn't commited yet
|
|
What's a non-repeatable read?
|
Executing the same select twice returns differing data
|
|
What's a phantom?
|
A row that appears where it wasn't before
|
|
Name the 4 InnoDB isolation levels
|
read uncommited
read commited repeatable read serializable |
|
What's the default InnoDB isolation level?
|
repeatable read
|
|
Define "read uncommited"
|
Uncommitted changes from t2 can be seen in t1. Dirty reads, phantoms, and nonrepeatable reads occur
|
|
Define "read committed"
|
Only commited changes from t2 can be seen in t1. No dirty reads but phantoms and nonrepeatable reads
|
|
Define "repeatable read"
|
Same result will be obtained throughout the transaction, no matter what other tx do
|
|
Define the "seriezable" isolation level
|
like repeatable read, and in addition rows selected by t1 cannot be modified by t2 until t1 finishes
|
|
Option to define isolation level
|
transaction-isolation=NAME
|
|
Command to set tx isolation interactively
|
SET TRANSACTION ISOLATION LEVEL name
|
|
Which variable defines the file used by InnoDB for the tablespace, as well as its size and autoextend behaviour
|
innodb_data_file_path
|
|
How can you see the InnoDB status?
|
SHOW ENGINE INNODB STATUS\G
|
|
Describe InnoDB log buffers and files
|
Redo logs are stored in memory in a buffer, the flushed to log files, then recorded to tablespace, in a cascading overflow mechanism
|