MySQL DBA backups, replication

Question 1

Which program copies the databases from one server to another?

a) mysqldbcopy
b) mysqlcopydb
c) mysqlflushdb
d) mysqldbflush

Answer 1

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.

Question 2

To use ‘mysqldbcopy’ which privileges are required on the source server?

a) CREATE
b) INSERT
c) UPDATE
d) SELECT

Answer 2

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.

Question 3

The program that performs logical backups is _____________

a) mysqlimport
b) mysqldump
c) myslqpit
d) mysqllogic

Answer 3

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.

Question 4

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 4

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.

Question 5

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 5

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.

Question 6

The MySQL server is poorly configurable.

a) True
b) False

Answer 6

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.

Question 7

Multiple MySQL servers can be easily run on the same machine.

a) True
b) False

Answer 7

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.

Question 8

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 8

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.

Question 9

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 9

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.

Question 10

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 10

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.

Question 11

To reload a delimited text data file use ______________

a) mysqldump
b) mysqld
c) mysqlimport
d) mysqlnaive

Answer 11

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.

Question 12

Replication enables data from one MySQL database server to be copied to one or more MySQL database servers.

a) True
b) False

Answer 12

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.

Question 13

SBR replication is ______________

a) Statement based
b) Row based
c) Column based
d) Table based

Answer 13

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.

Question 14

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 14

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.

Question 15

The header that should be included first is ______________

a) my_global.h
b) my_sys.h
c) mysql.h
d) my_local.h

Answer 15

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.

Question 16

Which of the following use ‘NULL’ to indicate failure?

mysql_init(), mysql_real_connect()

a) 0
b) 1
c) 2
d) 3

Answer 16

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.

Question 17

The option that specifies the data directory location at server startup is ______________

a) –data_dir
b) –data
c) –data-dir
d) –datadir

Answer 17

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.

Question 18

The datadir variable value can be seen using ______________

a) SHOW VARIABLES
b) DISP VARIABLES
c) CONNECT VARIABLES
d) SHOW VARIABLE

Answer 18

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’.

Question 19

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 19

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.

Question 20

Which of the following is NOT part of the MySQL shutdown process?

1. The shutdown process is initiated.
2. The server creates a shutdown thread if necessary.
3. The server stops accepting new connections.
4. The server terminates current activity.
5. The server shuts down or closes storage engines.
6. The server exits.

a) 5
b) 4
c) 2 and 5
d) They are all part of the shutdown process

Answer 20

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.

Question 21

Encrypted connections can be established using ______________

a) exec_ssl_stmt
b) exec_stmt_ssl
c) exec_stmnt_ssl
d) exec_ssl_stmnt

Answer 21

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.

Question 22

On UNIX, statements entered in ‘MySQL’ are saved in which file?

a) .mysql_queries
b) .queries
c) .mysql_history
d) .history

Answer 22

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.

Question 23

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 23

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.

Question 24

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 24

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.

Question 25

Which option prints output in table-format when MySQL is run interactively?

a) -tf
b) -p
c) -pf
d) -t

Answer 25

Answer: d

Explanation: MySQL produces output in tab-delimited format when it is run in batch mode. Suppose a table-format output is desired, the -t option is used along with the mysql command. -tf is not a valid option.

Question 26

In UNIX, the name of the option file is __________

a) .my.cnf
b) .my.ini
c) .my.opt
d) .my.opc

Answer 26

Answer: a
Explanation: Under Unix, an option file is set up by creating a file named ‘~/.my.cnf’ in the home directory. ‘C:\my.ini’ is the option file that is setup in Windows. An option file stores the connection parameters.

Question 27

Which of the following MySQL programs will allow you to perform a logical backup?

a) mysqlpump
b) mysqlimport
c) mysqlcheck
d) mysqlschema_backup

Answer 27

Answer: a

The mysqlpump 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.

Question 28

Which of the following is FALSE

a) It is possible to use replication in a way where the storage engine on the replica is not the same as the storage engine on the source.
b) Transactions that fail on the source do not affect replication.
c) MySQL replication is based on the history log where MySQL writes SQL statements that modify data.
d) If you are running MySQL Enterprise backup you can make a new replica without taking down the source or an existing replica

Answer 28

Answer : c

MySQL replication is based on the binary log where MySQL writes SQL statements that modify data.

Question 29

Which Authentication plugin is enabled by default in MySQL 8.0?

a) mysql_native_password
b) caching_sha2_password
c) sha256_password
d) mysql_no_login

Answer 29

Answer: b

In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password.

Question 30

Which Authentication plugin would be best to use if you want to set up proxied accounts that should never permit direct login but are intended to be accessed only through proxy accounts

a) mysql_native_password
b) caching_sha2_password
c) sha256_password
d) mysql_no_login

Answer 30

Answer : d

The mysql_no_login server-side authentication plugin prevents all client connections to any account that uses it. Use cases for this plugin include:

Accounts that must be able to execute stored programs and views with elevated privileges without exposing those privileges to ordinary users.

Proxied accounts that should never permit direct login but are intended to be accessed only through proxy accounts.

Question 31

You need to use the mysql_no_login to set up proxy accounts in MySQL. Which of the following will you need to do first:

a) run the following command with MySQL up and running
INSTALL PLUGIN mysql_no_login SONAME ‘mysql_no_login.so’;

b) place the mysql_no_login plugin in in the MySQL base directory, edit the option file my.cnf to include plugin-load-add=mysql_no_login.so, restart mysqld.
c) both a and b
c) mysql_no_login has been depreciated in 8.0, you cannot use it

Answer 31

Answer: a

Answer b is incorrect because

To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

to load the plugin at runtime, use this statement, adjusting the .so suffix for your platform as necessary:

INSTALL PLUGIN mysql_no_login SONAME ‘mysql_no_login.so’;