Note: for questions where you can select multiple answers, it will tell you how many to choose.
- Lots of InnoDB and transactional stuff
- Understand everything about locking
- Setting default storage engine:
storage_engine = MyISAM;
- READ UNCOMMITTED isolation level
This isolation level allows dirty reads. One transaction may see uncommitted changes made by some other transaction. You should pretty much never do this anyway.
- READ COMMITTED isolation level
A somewhat Oracle-like isolation level with respect to consistent (nonlocking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
- INFORMATION_SCHEMA.SCHEMATA
A schema is a database, so the SCHEMATA table provides information about databases.
INFORMATION_SCHEMA Name SHOW Name Remarks
CATALOG_NAME NULL
SCHEMA_NAME Database
DEFAULT_CHARACTER_SET_NAME
DEFAULT_COLLATION_NAME
SQL_PATH NULL
-
To repair an InnoDB table, you cannot use
REPAIR table. You must dump the table and reimport it. -
Importing multiple binary logs into MySQL:
mysqlbinlog binlog.001 binlog.002 binlog.003 | mysql
OR
mysqlbinlog binlog.001 | mysql
mysqlbinlog binlog.002 | mysql
mysqlbinlog binlog.003 | mysql
-
InnoDB tablespace files are machine independent.
-
To make a binary copy of the InnoDB tablespace, you need to copy:
- All tablespace files, including the files for the shared tablespace and .ibd files if you have con- figured InnoDB to use per-table tablespaces
- All InnoDB log files
- The .frm file for each of your InnoDB tables
- The InnoDB tablespace and log configuration information stored in your MySQL option file (that is, the settings for innodb_data_home_dir, innodb_data_file_path, and perhaps other InnoDB options)
- Configuration files are read in the following order:
- /etc/my.cnf
- /etc/mysql/my.cnf
- SYSCONFDIR/my.cnf
- $MYSQL_HOME/my.cnf
- defaults-extra-file The file specified with --defaults-extra-file=path, if any
- ~/.my.cnf
-
SELECT ... INTO OUTFILE 'filename'cannot overwrite (or append to) existing files, and the file will be world-readable. -
You cannot make a binary copy of InnoDB tables while the server is running. Unlike the options avail- able when using MyISAM tables (lock the tables, flush them to disk, and then copy them), you must tell the server to stop before making a copy of InnoDB files. This is necessary to ensure that InnoDB has completed any pending transactions before the copy is made.
-
When using InnoDB,
SELECT ... FOR UPDATEis used to select a set of rows that you later intend to update. -
If you install MySQL with a different service name on Windows, the server ignores the
[mysqld]option group and instead reads options from the group that has the same name as the service. For example, if you name the serviceMySQL5, it will read configuration options in the group[MySQL5]