MySQL DBA backups, replication Flashcards
Which program copies the databases from one server to another?
a) mysqldbcopy
b) mysqlcopydb
c) mysqlflushdb
d) mysqldbflush
Answer: a
Explanation: The utility program ‘mysqldbcopy’ is capable of copying databases from one server to another server. It can also prepare copies to make transfers on the same servers. This can be done simply by running the program.
To use ‘mysqldbcopy’ which privileges are required on the source server?
a) CREATE
b) INSERT
c) UPDATE
d) SELECT
Answer: d
Explanation: To use the utility program ‘mysqldbcopy’, the user must have SELECT privileges for the database(s) on the source server and have CREATE, INSERT, UPDATE on the destination server.
The program that performs logical backups is _____________
a) mysqlimport
b) mysqldump
c) myslqpit
d) mysqllogic
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.
In ‘mysqldump’ which option is used to make all tables in the destination databases to use a different storage engine?
a) –next-storage-engine
b) –new-storage-engine
c) –clear-storage-engine
d) –get-storage-engine
Answer: b
Explanation: While using the program ‘mysqldump’ in MySQL to copy databases from server to server, all tables in the destination databases can be directed to use a different storage engine with the –new-storage-engine option.
Which client-side authentication plugin is available that enables clients to send passwords to the server without hashing or encryption.
a) mysql_nohash_password
b) mysql_clear_password
c) mysql_native_password
d) None of these
Answer: b
A client-side authentication plugin is available that enables clients to send passwords to the server as cleartext, without hashing or encryption. This plugin is built into the MySQL client library.
The MySQL server is poorly configurable.
a) True
b) False
Answer: b
Explanation: The MySQL server is highly configurable. Some of the operational characteristics that you can control include which storage engines the server supports, the default character set, and its default time zone.
Multiple MySQL servers can be easily run on the same machine.
a) True
b) False
Answer: a
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.
Which is the log in which data changes received from a replication master server are written?
a) error log
b) general query log
c) binary log
d) relay log
Answer: d
Explanation: The Relay Log has the data changes received from a replication master server written in it. The problems encountered during the starting, running or stopping of ‘mysqld’ is written in error log.
The primary resource managed by a MySQL instance is the data directory. Which of the following allows you to set the data directory at start up?
a) –pid-file=file_name
b) –general_log_file=file_name
c) –basedir=dir_name
d) –datadir=dir_name
Answer: d
The primary resource managed by a MySQL instance is the data directory. Each instance should use a different data directory, the location of which is specified using the –datadir=dir_name option.
If you have multiple MySQL installations in different locations, you can specify the base directory for each installation with the –basedir=dir_name option. This causes each instance to automatically use a different data directory, log files, and PID file because the default for each of those parameters is relative to the base directory.
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) mysqlimport
d) mysqlnaive
Answer: c
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.
Replication enables data from one MySQL database server to be copied to one or more MySQL database servers.
a) True
b) False
Answer: a
Explanation: Replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default.
SBR replication is ______________
a) Statement based
b) Row based
c) Column based
d) Table based
Answer: a
Explanation: There are two main kinds of replication format: Statement Based Replication (SBR) replicates entire SQL statements and Row Based Replication (RBR) replicates only the changed rows.
The library file that contains various portability macros and definitions is ______________
a) my_global.h
b) my_sys.h
c) mysql.h
d) my_local.h
Answer: b
Explanation: The ‘my_sys.h’ header file contains a variety of portability macros and definitions required for structures and functions. These structures and functions are used by the client library.
The header that should be included first is ______________
a) my_global.h
b) my_sys.h
c) mysql.h
d) my_local.h
Answer: a
Explanation: The file ‘my_global.h’ takes care of including several other header files that are likely to be generally useful, like ‘stdio.h’. It also includes Windows compatibility information.
Which of the following use ‘NULL’ to indicate failure?
mysql_init(), mysql_real_connect()
a) 0
b) 1
c) 2
d) 3
Answer: c
Explanation: Both of the client library routines named ‘mysql_init()’ and ‘mysql_real_connect()’ return a pointer to the connection handler in order to indicate success and NULL to indicate failure.
The option that specifies the data directory location at server startup is ______________
a) –data_dir
b) –data
c) –data-dir
d) –datadir
Answer: d
Explanation: At the server startup the data directory location is specified by using a ‘–datadir=dir_name’ option. It is helpful for naming a location other than the compiled in default.
The datadir variable value can be seen using ______________
a) SHOW VARIABLES
b) DISP VARIABLES
c) CONNECT VARIABLES
d) SHOW VARIABLE
Answer: a
Explanation: The data directory location is checked by the ‘datadir’ variable. It can be obtained using a ‘SHOW VARIABLES’ statement or a ‘mysqladmin’ variables command, like: SHOW VARIABLES LIKE ‘datadir’.
Which data directory subdirectory provides the information used to inspect the internal execution of the server at runtime.
a) mysql
b) performance_schema
c) sys
d) nbdinfo
Answer: b
Explanation: The ‘performance_schema’ directory corresponds to the Performance Schema. It provides information used to inspect the internal execution of the server at runtime.
The ndbinfo directory corresponds to the ndbinfo database that stores information specific to NDB Cluster (present only for installations built to include NDB Cluster).
The sys directory corresponds to the sys schema, which provides a set of objects to help interpret Performance Schema information more easily.
Which of the following is NOT part of the MySQL shutdown process?
- The shutdown process is initiated.
- The server creates a shutdown thread if necessary.
- The server stops accepting new connections.
- The server terminates current activity.
- The server shuts down or closes storage engines.
- The server exits.
a) 5
b) 4
c) 2 and 5
d) They are all part of the shutdown process
Answer: d
The server shutdown process takes place as follows:
The shutdown process is initiated.
The server creates a shutdown thread if necessary.
The server stops accepting new connections.
The server terminates current activity.
The server shuts down or closes storage engines.
The server exits.
Encrypted connections can be established using ______________
a) exec_ssl_stmt
b) exec_stmt_ssl
c) exec_stmnt_ssl
d) exec_ssl_stmnt
Answer: b
Explanation: For the ‘exec_stmt_ssl’ to work properly, MySQL must have been built with SSL support, and the server must be started with the proper options that identify its certificate and key files.
On UNIX, statements entered in ‘MySQL’ are saved in which file?
a) .mysql_queries
b) .queries
c) .mysql_history
d) .history
Answer: c
Explanation: Statements entered in ‘MySQL’ are stored in the file named ‘.mysql_history’. This file is located in the home directory itself. The SQL statements can be directly pasted into this file.
Suppose run_me.sh is a script file. Which command is used to make it executable?
a) chmod +e run_me.sh
b) chmod +a run_me.sh
c) chmod +y run_me.sh
d) chmod +x run_me.sh
Answer: d
Explanation: The command ‘chmod +x file_name’ makes a script file executable. mysql supports reading from a script file and executing queries from it. Before a script is run, it is necessary for it to be made executable.
To execute the contents of a query file ‘exec.sql’ by feeding it to mysql, which command is used?
a) mysql exec.sql > sampdb
b) mysql sampdb < exec.sql
c) mysql exec.sql
d) mysql exec
Answer: b
Explanation: mysql queries can be run after placing the queries in a file, and then executing it by feeding it to mysql. By default, mysql prints output in a tab-delimited format when it is running in noninteractive mode.
