Interview Q and A for MySQL Part - 2

51. Have you used this command “mysql> HELP contents;”?

Yes, you can get server-side help from this command.

mysql> HELP contents;

You asked for help about help category: “Contents”

For more information, type ‘help <item>’, where <item> is one of

the following categories:

Administration
Column Types
Data Definition
Functions
Geographic features
Transactions

52. Which engine would you prefer for production OLTP environment and why?
  InnoDB (to be transaction safe)

53. What are the best installation practices for MySQL?
there are following installation methods available
A. Binary Installation
B. RPM Installation
C. Source Code compilation
after installation you should change the location of bin logs and datafiles on the different physical disks.

54. Which RAID level is best suited for MySQL?
RAID 10

55. How do you upgrade from one mysql version to another mysql version?
create a slave on newer version and change it to MASTER.

56. How many types of logs are there in mysql?
General Log, Error Log, Binary Log and Slow Query Log

57.How do you find out slow queries in mysql?
By enabling the slow query log as follows
SET GLOBAL slow_query_log = 1;

58. How do you go through the MySQL slow query log?
slow query log might be very huge in size and query could be listed thousand times. to summarize the slow query log in a very informative way there is third party tool available 'pt-query-digest' which is a part of percona tool kit freely downloadable.

59.How do you check the uptime of a mysql server?
following command gives you the system status
status

60. If the mysql server is performing slow than how to find out the process which is causing problem.
show processlist

61. What do you do if the end user complains about the performance of the DB?
show processlist

will show the processes which taking resources at db server and the do the rest diagnosis.

62. What do you do about the slow queries?
study the explain plan and create necessary indexes if required.

63. Where do you change the performance parameters of mysql and by default where is the file located on a unix system?
 my.cnf. this file is available under /etc/my.cnf

64. Which are the important performance parameters for MyISAM and InnoDB?

For MyISAM
key_cache_size
thread_cache_size
tmp_table_size
max_heap_table_size
read_buffer_size
query_cache
For InnoDB
innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_file_size
innodb_log_buffer_size
tmp_table_size
max_heap_table_size
table_open_cache

65. Should we have Query_cache enabled?
in many caches yes.

66. what are the disadvantages of having a big Query cache size?
query cache puts an additional overhead on the database. It required the db to work on invalidating the queries from the query cache.

67. what should be the optimum size of InnoDB buffer cache?
it should be the 70-80% of the memory available.

68. How do you backup InnoDB tables?
there are two ways of taking backup
1. mysqldump with --single-transaction
2. xtrabackup (part of percona)

69. How to take incremental backup in MySQL?
 Using percona xtrabackup

70. Can the database be renamed in MySQL?
  No.

71. How to check the table fragmentation and resolve if the fragmentation is found?
following query will list all the fragmented tabes
SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), 'MB') DATA, CONCAT(ROUND(data_free  / ( 1024 * 1024 ), 2), 'MB')FREE from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema','mysql') and Data_free < 0;
then run the following command on tables given by the query
alte table < table returned from pervious query > engine=innodb;

72. What do you do if the data disk is full?
if the data disk is full then create a soft link and move the .frm and .idb files to the linked location.

73. How to take consistent backup in mysql?
 take the backup at slave after stopping the slave.

74. How do you restrict the users in mysql?
by grant and revoke commands.

75. What’s the default port for MySQL Server?

 3306

76.What does tee command do in MySQL?

 tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.

77. Can you save your connection settings to a conf file?

Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.

78. Use mysqldump to create a copy of the database?

mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql

79. What are some good ideas regarding user security in MySQL?

There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.

80. Explain the difference between MyISAM Static and MyISAM Dynamic.

In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.

81. What are HEAP tables in MySQL?

HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.

82. How do you control the max size of a HEAP table?

MySQL config variable max_heap_table_size.

83. What are CSV tables?

Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.

84. Explain federated tables.

Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.

85. What is SERIAL data type in MySQL?

BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT

86. What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?

It stops incriminating. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.

87. Explain the difference between BOOL, TINYINT and BIT.

Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.

88. Explain the difference between FLOAT, DOUBLE and REAL.

FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.

89. If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?

999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.

90. What happens if a table has one column defined as TIMESTAMP?

That field gets the current time stamp whenever the row gets altered.

91. But what if you really want to store the timestamp data, such as the publication date of the article?

Create two columns of type TIMESTAMP and use the second one for your real data.

92. Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

 The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.

93. What does TIMESTAMP  do ON UPDATE CURRENT_TIMESTAMP data type do?

On initialization places a zero in that column, on future updates puts the current value of the timestamp in.

TIMESTAMP column is updated with Zero when the table is created.  UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to  current time whenever there is a change in other fields of the table.

94. Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44′ ON UPDATE CURRENT_TIMESTAMP.

A default value is used on initialization, a current timestamp is inserted on update of the row.

95. If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table?

CHAR(3), since MySQL automatically adjusted the data type.

96. Explain the difference between mysql and mysqli interfaces in PHP?

mysqli is the object-oriented version of mysql library functions.

97. Backup & Recovery – explain various types & scenarios for restore

Backups come in a few different flavors that the DBA should be familiar with.

Cold backups involve shutdown down the database server (mysqld) and then backing up all the data files by making a copy of them to another directory. To be really thorough, the entire datadir including binlogs, log files, /etc/my.cnf config file should also be backed up. The cold backup is a database in itself, and can be copied to an alternate server and mounted as-is.

Logical backups involve using the mysqldump tool. This locks tables while it runs to maintain consistency of changing data, and can cause downtime. The resulting dump file contains CREATE DATABASE, CREATE TABLE & CREATE INDEX statements to rebuild the database. Note the file itself is not a database, but rather a set of instructions which can tell a MySQL server *HOW* to reconstruct the database. Important distinction here.

Hot backups are a great addition to the mix as they allow the physical database data files to be backed up *WHILE* the server is up and running. In MySQL this can be achieved with the xtrabackup tool, available from Percona. Despite the name, it works very well with MyISAM and InnoDB tables too, so don’t worry if you’re not using xtradb tables.

There are a few different restore scenarios, and the candidate should be able to describe how these various backups can be restored, and what the steps to do so would be. In addition, they should understand what point-in-time recovery is, and how to perform that as well. After restoring one of the above three backup types, the DBA would use the mysqlbinlog utility to apply any subsequent transactions from the binary logs. So if the backup was made at 2am last night, and you restore that backup, the mysqlbinlog tool would be used to dig up transactions since 2am, and apply them to that restored database.

98. Troubleshooting Performance

Typically, first steps involve mitigating the immediate problem by finding out what changed in the environment either operationally or code changes. If there is a bug that was hit, or other strange performance anomaly, the first stop is usually looking at log files. MySQL server error logs, and the slow query log are key files. From there, analyzing those files during the time frame where problems occurred should yield some clues.

99. How To take a database Backup in MySQL
Before taking a dump a backup has to select the name of the database.

I have chosen the database name is testmysqldb and  dumps the output to testmysqldb.sql  and just read the below options.
-u is user name
-p is password
-A is All Databases
-d is Do not write any row information
Syntax:
mysqldump -u user_name -p password  database_name > /destination/path/backup.sql

100. Multiple database backup in a command
Below example takes a multiple databases (mysqlbackup, mysql_db, db_backup ) backup in single command and dumps the output to  mysqlbackup.sql

#  mysqldump -u root -p password mysqlbackup mysql_db db_backup > mysqlbackup.sql

OR

mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

101. Backup a specific tables
Below example takes one table  id_test from testmylsqldb database

mysqldump -u root -p password  testmysqldb  id_test > /tmp/testmysqldb_id_test.sql

OR

mysqldump -c -u username -ppassword databasename tablename > databasename.tablename.sql

102. Dump ALL MySQL Databases
Below example to take a backup of all databases dump the output to  mysqlbackup_all.sql ,  use any one option --all-databases or -A
#  mysqldump -u root -p password -A > mysqlbackup_all.sql

103. Dump mysql database structure only
if you want to get a dump of only the  database structure without any data,
 #mysqldump -u root -p password -d mysqldb > mysqldb.sql

104. Restore MySQL database
To restore the backup file into another server testmysqldb, execute mysql  with ‘ < ’ symbol (less than). Before restored that backup need to create a new database.
 #  mysql -u root -p password testmysqldb < testmysqldb.sql

105. version of mysqldump

If you want to see your version of mysqldump run the command:
# mysqldump -V
mysqldump  Ver 10.13 Distrib 5.5.34, for Linux (x86_64)
will displaying full details about mysqldump usages and Options
root@host [~]# mysqldump –help

106. How might you hack a MySQL server?

This is a good opportunity for the candidate to show some creativity with respect to operations and Linux servers.  There are all sorts of ways into a database server:

A. bad, weak or unset passwords
b. files with incorrect permissions – modifying or deleting file-system files can take a database down or corrupt data
c. intercepting packets – could reveal unencrypted data inside the database
d. unpatched software – bugs often reveal vulnerabilities that allow unauthorized entry
e. moving, disabling or interrupting the backup scripts – a possible timebomb until you need to restore
f. DNS spoofing, could allow login as a different user
g. generous permissions – may allow an unprivileged user access to protected data

There are endless possibilities here.  Listening for creative thinking here, reveals how much that person will think thoroughly and effectively about protecting your systems from those same threats.

107. How are Users & Grants different in MySQL than other DBs?

Creating a grant in MySQL can effectively create the user as well.  MySQL users are implemented in a very rudimentary fashion.  The biggest misunderstanding in this area surrounds the idea of a user.  In most databases a username is unique by itself.  In MySQL it is the *combination* of user & hostname that must be unique.  So, for example if I create user sean@localhost, sean@server2 and sean@server3, they are actually three distinct users, which can have distinct passwords, and privileges.  It can be very confusing that seen logging in from the local command line has different privileges or password than seen logging in from server2 and server3.  So that’s an important point.

108. How would you setup master/slave & master/master replication?

A basic replication setup involves creating a full dump of the primary database, while it’s tables are locked.  The DBA should capture the master status, logfile & position at that time.  She should then copy the dump file to the secondary machine & import the full dump.  Finally the CHANGE MASTER TO statement should be run to point this database instance to it’s master.  Lastly START SLAVE should be issued.  If all goes well SHOW SLAVE STATUS should show YES for both of these status variables:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Master-master replication is similar, except one additional step.  After the above steps have run, you know that your application is not pointing at the slave database.  If you’re not sure, verify that fact first.  Now determine the logfile name & position on the slave with SHOW MASTER STATUS.  Return to the primary box, and run the CHANGE MASTER TO command to make it slave from the secondary box.  You’ve essentially asked MySQL to create a circular loop of replication.

How does MySQL avoid getting into an infinite loop in this scenario?  

The server_id variable must be set, and be unique for all MySQL instances in your replication topology.

For extra credit, ask the candidate about replication integrity checking.  As important as this piece is to a solid reliable replication setup, many folks in the MySQL world are not aware of the necessity.  Though replication can be setup, and running properly, that does not mean it will keep your data clean and perfect.  Due to the nature of statement based replication, and non-deterministic functions and/or non-transactional tables, statements can make their way into the binary logs, without completing.  What this means is they may then complete on the slave, resulting in a different row set on the same table in master & slave instance.

109. How to search second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load?
SELECT DISTINCT(salary) FROM employee order by salary desc limit 1 , 1 ;
(This way we will able to find out 3rd highest , 4th highest salary so on just need to change limit condtion like LIMIT 2,1 for 3rd highest and LIMIT 3,1 for 4th
someone may finding this way using below query that taken more time as compare to above query SELECT salary FROM employee where salary < (select max(salary) from employe) order by salary DESC limit 1 ;

110. How to Load a CSV file into a table
mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

111. how you will Create a database on the mysql server with unix shell
mysql> create database databasename;

112. Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with r.
mysql> SELECT * FROM tablename WHERE rec RLIKE “^r”;

113. How to Create a new user.

 Login as root. Switch to the MySQL db. Make the user. Update privs.
# mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));

mysql> flush privileges;

114. Change column name and Make a unique column so we get no dupes.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
mysql> alter table [table name] add unique ([column name]);

115. How to Update database permissions/privilages.
mysql> flush privileges;

116. Can MySQL 5.7 perform multiple-table inserts, updates, and deletes?

Yes. A multiple-table insert can be accomplished using a trigger who’s FOR EACH ROW clause contains multiple INSERT statements within a BEGIN ... END block

117. Does MySQL 5.7 have a Query Cache? Does it work on Server, Instance or Database?

Yes. The query cache operates on the server level, caching complete result sets matched with the original query string. If an exactly identical query is made (which often happens, particularly in web applications), no parsing or execution is necessary; the result is sent directly from the cache.

118. Does MySQL 5.7 have Sequences?

No. However, MySQL has an AUTO_INCREMENT system, which in MySQL 5.7 can also handle inserts in a multi-master replication setup. With the auto_increment_increment and auto_increment_offset system variables, you can set each server to generate auto-increment values that don't conflict with other servers.

119. Does MySQL 5.7 have a NOW() function with fractions of seconds?

No. This is on the MySQL roadmap as a “rolling feature”. This means that it is not a flagship feature, but will be implemented, development time permitting. Specific customer demand may change this scheduling.

However, MySQL does parse time strings with a fractional component.

120. Does MySQL 5.7 work with multi-core processors?

Yes. MySQL is fully multi-threaded, and will make use of multiple CPUs, provided that the operating system supports them.