• Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

Card Range To Study

through

image

Play button

image

Play button

image

Progress

1/125

Click to flip

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