Backup strategies Flashcards
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. — skip-buffer
C. — single-transaction
D. — tab
Correct Answer: A
Explanation/Reference:
Reference: http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html
Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the –quick option (or –opt, which enables –quick). The –opt option (and hence –quick) is enabled by default, so to enable memory buffering, use –skip-quick.
Which of the following will allow you to use mysqldump to backup ALL databases?
a) –all option
b) do not name any tables following db_name
c) use the –databases and list all databases
d) –all-databases option.
e) do not include any databases or tables
c) and d)
To dump entire databases, do not name any tables following db_name, or use the –databases or –all-databases option.
mysqldump [options] db_name [tbl_name …]
mysqldump [options] –databases db_name …
mysqldump [options] –all-databases
The program that performs logical backups is _____________
a) mysqlimport
b) mysqldump
c) mysqlslap
d) xtrabackup
Answer: b
Explanation: The ‘mysqldump’ performs logical backups. It produces a set of SQL statements that are executed to reproduce the original database object definitions. It dumps one or more MySQL databases for backup.
Multiple MySQL servers can be easily run on the same machine.
a) False
b) True
Answer: b
Explanation: It is useful to run multiple servers on the same machine under certain circumstances. A new MySQL release can also be tested while leaving the current production server in place.
For InnoDB tables in mysqldump an online backup that takes no locks on tables can be performed by ______________
a) –multiple-transaction
b) –single-transaction
c) –double-transaction
d) –no-transaction
Answer: b
Answer: b
Explanation: For InnoDB tables it is possible to perform an online backup that takes no locks on tables using the option ‘–single-transaction’ to ‘mysqldump’. The ‘mysqldump’ can make backups.
To reload a delimited text data file use ______________
a) mysqldump
b) mysqld
c) mysqldump –csv
d) mysqlimport
Answer: d
Explanation: A way to create text data files along with files containing ‘CREATE TABLE’ statements for the backed up tables is to use ‘mysqldump’ with –tab. To reload a delimited text data file ‘mysqlimport’ is used.
In MySQL databases, the structure representing the organizational views of the entire databases is ____________
a) View
b) Instance
c) Schema
d) Table
Answer: c
Explanation: The schema in a database gives a blueprint of the structure. A view is an object that can be generated with a query. A table is a collection of records. An instance is analogous to a class object.
MySQL server can operate in different SQL modes, depending on the value of the sql_mode system variable. Which mode changes syntax and behavior to conform more closely to standard SQL?
a) TRADITIONAL
b) ANSI
c) MSSQL
d) STRICT
Answer: b
ANSI - This mode changes syntax and behavior to conform more closely to standard SQL.
Which option of most MySQL command-line programs can be used to get a description of the program’s different options?
a) –options
b) ?
c) –help
d) -h
Answer: c
–help Display help message and exit
mysql -? and mysql –help both display help messages, but you need the ‘-‘ character
You need to export the entire database, including the database objects, in addition to the data. Which command-line tool do you use?
a) mysqlexport
b) mysqladmin
c) mysqldump
d) mysqld
Answer: c
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.
mysqlexport - no such program
mysqladmin is a client for performing administrative operations. You can use it to check the server’s configuration and current status, to create and drop databases, and more. mysqladmin [options]
mysqld - the database server executable
The following command was entered in mysql shell
CREATE USER IF NOT EXISTS ‘test’@’localhost’ IDENTIFIED BY ‘Password’ PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Which of the following can be used to troubleshoot why the command failed?
a) show error;
b) show error();
c) show warning;
d) show warning();
Answer : c
SHOW WARNINGS [LIMIT [offset,] row_count]
you have to run it immediately after a query that had warnings attached to it.
show warning(); is incorrect syntax
SHOW ERRORS, shows only error conditions (it excludes warnings and notes),