MySql Database Administrator Interview Questions..

on

1. How would you check if MySql service is running or not?
Answer : Issue the command “service mysql status” in ‘Debian’ and “service mysqld status” in RedHat. Check the output, and all done.
root@localhost:/home/avi# service mysql status
/usr/bin/mysqladmin  Ver 8.42 Distrib 5.1.72, for debian-linux-gnu on i486
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.1.72-2
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 1 hour 22 min 49 sec
Threads: 1  Questions: 112138  Slow queries: 1  Opens: 1485  Flush tables: 1  Open tables: 64  Queries per second avg: 22.567.
2. If the service is running/stop how would you stop/start the service?
Answer : To start MySql service use command as service mysqld start and to stop use service mysqld stop.
root@localhost:/home/avi# service mysql stop
Stopping MySQL database server: mysqld.
root@localhost:/home/avi# service mysql start
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
3. How will you login to MySQL from Linux Shell?
Answer : To connect or login to MySQL service, use command: mysql -u root -p.
root@localhost:/home/avi# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 207 
Server version: 5.1.72-2 (Debian) 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
mysql>
4. How will you obtain list of all the databases?
Answer : To list all currently running databases run the command on mysql shell as: show databases;
mysql> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| a1                 | 
| cloud              | 
| mysql              | 
| phpmyadmin         | 
| playsms            | 
| sisso              | 
| test               | 
| ukolovnik          | 
| wordpress          | 
+--------------------+ 
10 rows in set (0.14 sec)
5. How will you switch to a database, and start working on that?
Answer : To use or switch to a specific database run the command on mysql shell as: use database_name;
mysql> use cloud; 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
Database changed 
mysql>
6. How will you get the list of all the tables, in a database?
Answer : To list all the tables of a database use the command on mysql shell as: show tables;
mysql> show tables; 
+----------------------------+ 
| Tables_in_cloud            | 
+----------------------------+ 
| oc_appconfig               | 
| oc_calendar_calendars      | 
| oc_calendar_objects        | 
| oc_calendar_repeat         | 
| oc_calendar_share_calendar | 
| oc_calendar_share_event    | 
| oc_contacts_addressbooks   | 
| oc_contacts_cards          | 
| oc_fscache                 | 
| oc_gallery_sharing         | 
+----------------------------+ 
10 rows in set (0.00 sec)
7. How will you get the Field Name and Type of a MySql table?
Answer : To get the Field Name and Type of a table use the command on mysql shell as: describe table_name;
mysql> describe oc_users; 
+----------+--------------+------+-----+---------+-------+ 
| Field    | Type         | Null | Key | Default | Extra | 
+----------+--------------+------+-----+---------+-------+ 
| uid      | varchar(64)  | NO   | PRI |         |       | 
| password | varchar(255) | NO   |     |         |       | 
+----------+--------------+------+-----+---------+-------+ 
2 rows in set (0.00 sec)
8. How will you delete a table?
Answer : To delte a specific table use the command on mysql shell as: drop table table_name;
mysql> drop table lookup; 
Query OK, 0 rows affected (0.00 sec)
9. What about database? How will you delete a database?
Answer : To delte a specific database use the command on mysql shell as: drop database database-name;
mysql> drop database a1; 
Query OK, 11 rows affected (0.07 sec)
10. How will you see all the contents of a table?
Answer : To view all the contents of a particular table use the command on mysql shell as: select * from table_name;
mysql> select * from engines; 
+------------+---------+----------------------------------------------------------------+--------------+------+------------+ 
| ENGINE     | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA   | SAVEPOINTS | 
+------------+---------+----------------------------------------------------------------+--------------+------+------------+ 
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        | 
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         | 
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         | 
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         | 
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         | 
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       | 
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         | 
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         | 
+------------+---------+----------------------------------------------------------------+--------------+------+------------+ 
8 rows in set (0.00 sec)
11. How will you see all the data in a field (say, uid), from table (say, oc_users)?
Answer : To view all the data in a field use the command on mysql shell as: select uid from oc_users;
mysql> select uid from oc_users; 
+-----+ 
| uid | 
+-----+ 
| avi | 
+-----+ 
1 row in set (0.03 sec)
12. Say you have a table ‘xyz’, which contains several fields including ‘create_time’ and ‘engine’. The field ‘engine’ is populated with two types of data ‘Memory’ and ‘MyIsam’. How will you get only ‘create_time’ and ‘engine’ from the table where engine is ‘MyIsam’?
Answer : Use the command on mysql shell as: select create_time, engine from xyz where engine=”MyIsam”;
12. mysql> select create_time, engine from xyz where engine="MyIsam";
+---------------------+--------+ 
| create_time         | engine | 
+---------------------+--------+ 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
+---------------------+--------+ 
132 rows in set (0.29 sec)
13. How will you show all the records from table ‘xrt’ where name is ‘tecmint’ and web_address is ‘tecmint.com’?
Answer : Use the command on mysql shell as: select * from xrt where name = “tecmint” and web_address = “tecmint.com”;
mysql> select  * from xrt where name = "tecmint" and web_address = “tecmint.com”;
+---------------+---------------------+---------------+ 
| Id                  | name                   | web_address | 
+---------------+---------------------+----------------+ 
| 13                 |  tecmint               | tecmint.com  |
+---------------+---------------------+----------------+ 
| 41                 |  tecmint               | tecmint.com  |
+---------------+---------------------+----------------+
14. How will you show all the records from table ‘xrt’ where name is not ‘tecmint’ and web_address is ‘tecmint.com’?
Answer : Use the command on mysql shell as: select * from xrt where name != “tecmint” and web_address = “tecmint.com”;
mysql> select * from xrt where name != ”tecmint” and web_address = ”tecmint.com”;
+---------------+---------------------+---------------+ 
| Id            | name                | web_address   | 
+---------------+---------------------+----------------+ 
| 1173          |  tecmint            | tecmint.com   |
+---------------+---------------------+----------------+
15. You need to know total number of row entry in a table. How will you achieve it?
Answer : Use the command on mysql shell as: select count(*) from table_name;
mysql> select count(*) from Tables; 
+----------+ 
| count(*) | 
+----------+ 
|      282 | 
+----------+ 
1 row in set (0.01 sec)











1. How do you start and stop MySQL on Windows? - net start MySQL, net stop MySQL
How do you start MySQL on Linux? - /etc/init.d/mysql start

2. Explain the difference between mysql and mysqli interfaces in PHP?
mysqli is the object-oriented version of mysql library functions.

3. What’s the default port for MySQL Server? - 3306

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

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

6. How do you change a password for an existing user via mysqladmin? - mysqladmin -u root -p password "newpassword"

7. Use mysqldump to create a copy of the database? - mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql

8. Have you ever used MySQL Administrator and MySQL Query Browser? Describe the tasks you accomplished with these tools.

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

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

11. What does myisamchk do? - It compressed the MyISAM tables, which reduces their disk usage.
Explain advantages of InnoDB over MyISAM? - Row-level locking, transactions, foreign key constraints and crash recovery.

12. Explain advantages of MyISAM over InnoDB? - Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.

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

14. How do you control the max size of a HEAP table? - MySQL config variable max_heap_table_size.

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

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

17. What is SERIAL data type in MySQL? - BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT

18. What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table? - It stops incrementing. 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.

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

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

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

22. What happens if a table has one column defined as TIMESTAMP? - That field gets the current timestamp whenever the row gets altered.

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

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

25. What does TIMESTAMP 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.

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

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














Posted by at  2 comments: 



































Tuesday, October 30, 2012









MySQL Interview Question and Answers Part - I











1. What’s MySQL?
  MySQL the most popular Open Source SQL database management system, is developed, distributed, and supported by MySQL AB. MySQL AB is a commercial company, founded by the MySQL developers, that builds its business by providing services around the MySQL database management system.

2. Why use the MySQL Database Server?
  The MySQL Database Server is very fast, reliable, and easy to use. it is possible for anyone to use and modify the software. Anybody can download the MySQL software from the Internet and use it without paying anything.

3. What is the technical features of MySQL Server?
  The MySQL Database Software is a client/server system that consists of a multi-threaded SQL server that supports different backends, several different client programs and libraries, administrative tools, and a wide range of application programming interfaces (APIs).

4. What are the column comparisons operators?
  The = , ‹›, ‹=, ‹, ›=, ›,‹‹,››, ‹=›, AND, OR, or LIKE operators may be used in column comparisons to the left of the FROM in SELECT statements.

5. How do you get the number of rows affected by query?
  SELECT COUNT (user_id) FROM users;

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

7. How do you return the a hundred books starting from 25th?
  SELECT book_title FROM books LIMIT 25, 100;

8. How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
  SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8).

9. What is the default port for MySQL Server?
  The default port is 3306.

10. How would you select all the users, whose phone number is null?
  SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

11. What are ENUMs used for in MySQL?
  You can limit the possible values that go into the table.
CREATE TABLE months (month ENUM ’January’, ’February’, ’March’,); INSERT months VALUES (’April’).

12. What are the advantages of Mysql comparing with oracle?
  MySql is Open source, which can be available any time. Provides Gui with Command Prompt. Supports the administration using MySQL Admin,MySQL Query Browser.Oracle is best database ever in Software development.

13. What is the difference between CHAR_LENGTH and LENGTH?
  The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

14. How are ENUMs and SETs represented internally?
  As unique integers representing the powers of two, due to storage optimizations.

15. How do you change a password for an existing user via mysqladmin?
  mysqladmin -u root -p password "newpassword"

16. If the value in the column is repeatable, how do you find out the unique values?
  SELECT DISTINCT user_firstname FROM users;

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

18. How do you get the current version of mysql?
  SELECT VERSION();

19. Is Mysql query has LETTERCASE?
  No.
Ex :
SELECT VERSION(), CURRENT_DATE;
select version(), current_date;
SeLeCt vErSiOn(), current_DATE;

20. What is the LIKE?
  A LIKE pattern match, which succeeds only if the pattern matches the entire value.

21. Differentiate the LIKE and REGEXP operators?
  SELECT * FROM pet WHERE name REGEXP "^b";
  SELECT * FROM pet WHERE name LIKE "%b";

22. What are the String types are available for a column?
  The string types are CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET.

23. What is the REGEXP?
  A REGEXP pattern match succeed if the pattern matches anywhere in the value being tested.

24. What is the difference between CHAR AND VARCHAR?
  The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved.
The length of a CHAR column is fixed to the length that you declare when you create the table.
The length can be any value between 1 and 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

25. How quoting and escaping work in SELECT QUERY?
  SELECT ‘hello’, ‘“hello”’,‘““hello””’, ‘hel‘‘lo’, ‘\‘hello’.

26. What is the difference between BLOB AND TEXT?
  A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB differ only in the maximum length of the values they can hold.
The four TEXT types TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT correspond to the four BLOB types and have the same maximum lengths and storage requirements. The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive fashion for BLOB values and case-insensitive fashion for TEXT values. In other words, a TEXT is a case-insensitive BLOB.

27. How we get Sum of column?
SELECT SUM(*) FROM [table name];

28. How do you get current user in mysql?
  SELECT USER();

29. How would you change a table to InnoDB?
  ALTER TABLE name_file ENGINE innodb;

30. How do you concatenate strings in MySQL?
  CONCAT (string1, string2, string3)

31. what is difference between primary key and candidate key?
  Primary Key
- are used to uniquely identify each row of the table. A table can have only one primary Key.
Candidate Key
- primary key is a candidate key. There is no difference. By common convention one candidate key is designated as a “primary” one and that key is used for any foreign key references.

32. How do you get the month from a timestamp?
  SELECT MONTH(january_timestamp) from tablename;

33. What do % and _ mean inside LIKE statement?
  % corresponds to 0 or more characters, _ is exactly one character.

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

35. How do you get the current date in Mysql?
  SELECT CURRENT_DATE();

36. What is the difference between mysql_fetch_array and mysql_fetch_object?
  mysql_fetch_array(): - returns a result row as a associated array, regular array from database.
mysql_fetch_object: - returns a result row as object from database.

37. You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?
  SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS();

38. What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id)?
  It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

39. How do you display the list of database in mysql?
  SHOW DATABASES;

40. How do you display the structure of the table?
  DESCRIBE table_name;

41. How do you find out which auto increment was assigned on the last insert?
  SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.

42. What does TIMESTAMP 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.

43. How many drivers in Mysql? 
There are eleven drivers in MYSQL .Six of them from MySQL AB and five by MYSQL Communities.They are
PHP Driver
ODBC Driver
JDBC Driver
ado.net5.mxj
CAPI1PHP DRIVER
PERL DRIVER
PYTHON DRIVER
RUBY DRIVER
C WRAPPER

44. How do you run batch mode in mysql?
  mysql < batch-file >;
mysql < batch-file > mysql.out

45. What Storage Engines do you use in MySQL?
  Storage engines used to be called table types.
Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employs different storage mechanisms, indexing facilities, locking levels and ultimately provides a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.

46. Where MyISAM table is stored?
Each MyISAM table is stored on disk in three files.
The ‘.frm’ file stores the table definition.
The data file has a ‘.MYD’ (MYData) extension.
The index file has a ‘.MYI’ (MYIndex) extension

47. Define Primary key?
  MYSQL allows only one primary key. A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself.A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key.









Question 1:



What does ACID stand for?







Response should include some of the following :







A: atomicity.

    

  •     Autocommit setting.
    • 

  •     COMMIT statement.
    • 

  •     ROLLBACK statement.
    • 


C: consistency.

    

  •     InnoDB doublewrite buffer.
    • 

  •     InnoDB crash recovery.
    • 


I:: isolation.

    

  • Autocommit setting.
    • 

  • SET ISOLATION LEVEL statement.
    • 

  • InnoDB locking.
    • 


D: durability.

    

  •     InnoDB doublewrite buffer, turned on and off by the innodb_doublewrite
    • 

  •     Configuration option innodb_flush_log_at_trx_commit.
    • 

  •     Configuration option sync_binlog.
    • 

  •     Configuration option innodb_file_per_table.
    • 

  •     Write buffer in a storage device, such as a disk drive, SSD, or RAID array.
    • 






Question 2:



What can you tell me about the MySQL Architecture?







Response should reference some of the following : 










What can you tell me MySQL and Disk I/O ?







Let them explain their RAID level  preferences and why. 



Additional reading for you with this can be found here.




Question 4:



What are the steps involved in taking over a MySQL server when you do not have any credentials?



Response should be similar to some of the following : 




    

  • stop the service: # /etc/init.d/mysql stop
    • 

  • Restart with skip grand: # mysqld_safe ... --skip-grant-tables &
    • 

      

    • /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin -- user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/kdlarson-pc.pid
      • 

    

  • Connect as root:    
    • 

      

    • # mysql -u root
      • 

    

  • To Setup a new password :
    • 

      

    • use mysql;
      • 

    • mysql> update user set password=PASSWORD("NEW-ROOT- PASSWORD") where User='root';
      • 

    • mysql> flush privileges; 
      • 

    • mysql> quit
      • 

    















Question 5:








How can you define MySQL Indexes to people who do not know what they are? 














Let them explain their definition of an index. The point is not to be overly technical but to explain the point and reason for them.








A common example for index to think of a phone book or to think of an index card in the library. 



They are used to find something quickly and logically. If a phone book had everyone listed by firstname instead of lastname it would be harder to find and make updates. 







People used to use the library card catalog to find books in the library. This was useful because they stored information on index cards with location information so it was easy to location the related information. 











Follow up question:



Is it better to have a lot of indexes across different types of data ?








Response could include some of the following : 








More indexes mean more work but it also means the more indexes mean more ways of looking up data fast. A DBA needs to determine the required amount of indexes per the related application and data needs.  







Question 6:





What is a MySQL Query

 

 and what makes a good query?











Allow them explain what a query is and how they prefer to write a query. 







It could include some of the following:






    

  • SQL queries are like little programs in and of themselves. 
    • 

  • They ask the database server to collect selections of records, cross tabulate them with other collections of records, then sort them, and slice and dice them. 
    • 

  • All of this requires MySQL to build temporary tables, perform resource intensive sorts and then organize the output in nice bite size chunks.
    • 













Question 7:









What storage engines have they used?
















They are likely to reference some of the following. These are all links to the mysql.com website so you can read more about them, 









Bonus points if they can talk about these as well














Question 8:





What is has been the hardest problem you ever had to solve with MySQL?











Allow them explain and go into detail about the problem as well as the solution. 








    

  • Why was it a problem ?
    • 

  • How did you fix it? 
    • 

  • Was it a revenue dependent emergency fix? 
    • 

  • Did the problem reappear or stay fixed ?
    • 










Question 9:






What do they prefer MySQL, Percona or MariaDB?












Allow them to talk and explain what they like and why. This will allow you to see what they hold dear as values. Some might prefer MariaDB because they are dedicated to the open source message, others might prefer Percona because if offers open source tools while others prefer MySQL because that is the source and original. 










Question 10:






What is their experience with MySQL replication?












Allow them to talk about their different experiences and replicated setups. 



Some of their information could be like the following or these could be follow up questions.








Follow up questions:








How would you setup master/slave:

    

  •     full dump of the primary database,while it’s tables are locked.
    • 

  •     capture the master status, logfile & position at that time   (  --master-data[=#]   )
    • 

  •     import data on new machine
    • 

  •     CHANGE MASTER TO
    • 

  •     SHOW SLAVE STATUS
    • 

  •     Slave_IO_Running: Yes
    • 

  •     Slave_SQL_Running: Yes
    • 


How would you setup master/master replication:

    Master-master replication is similar to master/slave replication, except one additional step.

    

  •         On Slave SHOW MASTER STATUS
    • 

  •         Return to the primary box, and run the CHANGE MASTER TO
    • 

  •             | auto_increment_increment    | 1     |
    • 

  •             | auto_increment_offset       | 1     |
    • 


What is the command to set the Master on a Slave:
CHANGE MASTER TO
  -> MASTER_HOST='master_host_name',
  -> MASTER_USER='replication_user_name',  
 ->  MASTER_PASSWORD='replication_password',
 ->  MASTER_LOG_FILE='recorded_log_file_name', 
 ->  MASTER_LOG_POS=recorded_log_position;


Do you know of a tool that helps with replication integrity checking?
        Percona’s pt-table-checksum is the preventative tool to use
            It can build checksums of all your tables, and then propagate those checksums through replication to the slave.

How to install Semisynchronous Replication

    

  • INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
    • 

  • master> SET GLOBAL rpl_semi_sync_master_enabled = on;
    • 

  • slave> SET GLOBAL rpl_semi_sync_slave_enabled = on;
    • 

  • slave> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
    • 

  • master> SHOW STATUS LIKE 'Rpl_semi_sync_master_clients';
    • 


+------------------------------+-------+ 
| Variable_name    | Value | 
+------------------------------+-------+
| Rpl_semi_sync_master_clients | 1    | 
+------------------------------+-------+







How can you stop  replication on all slave servers at the same point in time? 







This will show if they use tools or hack methods. 



Normally, replication stops when an error occurs on the slave, so if they force an error that is replicated  it would stop the all slaves at the same point in time, an "old school" method to stoping the slaves. 


















Question 11:













What else do they consider themselves, a sysadmin or a developer?
























Allow them to talk about their experiences and how it has shaped their career. 





Historically MySQL DBA are created out of their work experience. Some are focused on System administration while others are Web Developers, sometimes they are all of the above. Either way they now have a focus on the MySQL database and you might be able to use that experience to your advantage. 

 









Question 12:
What are the major differences between MySQL 5.1 and 5.5?

    

  • InnoDB Becomes Default Storage Engine
    • 

  • Semi-Synchronous Replicatio
    • 

  • Improved Recovery Performance
    • 

  • InnoDB Stats in Performance Schema
    • 

  • Multiple Buffer Pool Instances
    • 

  • More Partitioning Options
    • 


  If you do not know the answers to these features then review more here.

 
Question 13:
What are the major differences between MyISAM and InnoDB?
 InnoDB follows the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data. -- Manual









. Describe MySQL architecture.

Answer: MySQL has got the logical architecture as follows

A. Connection Manager
B. Query Optimizer
C. Pluggable Engines.


2. What are the major differences between MySQL 5.1 and 5.5?

Answer: The Major difference I know is that the default engine was myisam in 5.1 and innodb in 5.5

3. What are the different database engines available in MySQL? (explain)

Answer: Following are the highly used engines available in mysql

A. MyISAM
B. INNODB
C. Memory
D. Federated
E. CSV

4. What are the major differences between MyISAM and InnoDB?

Answer: Following are the differences between InnoDB and MyISAM

A. MyISAM does not support transactions whereas InnoDB does.
B. InnoDB supports multi versioning concurrency control.
C. MyISAM uses table level locks and InnoDB uses row level locking.


5. Which engine would you prefer for production OLTP environment and why?

Answer:  InnoDB (to be transaction safe)

6. What are the best installation practices for MySQL?

Answer: 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.

7. Which RAID level is best suited for MySQL?

Answer: RAID 10

8. How do you upgrade from one mysql version to another mysql version?

Answer:  create a slave on newer version and change it to MASTER.

9. How many types of logs are there in mysql?

Answer: General Log, Error Log, Binary Log and Slow Query Log


10. How do you find out slow queries in mysql?

Answer : By enabling the slow query log as follows
SET GLOBAL slow_query_log = 1;


11. How do you go through the MySQL slow query log?

Answer : 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.

12. How do you check the uptime of a mysql server?

Answer : following command gives you the system status

status


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

Answer : show processlist

14. What do you do if the end user complains about the performance of the DB?

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

15. What do you do about the slow queries?

Answer : study the explain plan and create necessary indexes if required.

16. Where do you change the performance parameters of mysql and by default where is the file located on a unix system?

Answer : my.cnf. this file is available under /etc/my.cnf

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

Answer : 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

18. Should we have Query_cache enabled?

Answer : in many caches yes.
19. what are the disadvantages of having a big Query cache size?

Answer : query cache puts an additional overhead on the database. it required the db to work on invalidating the queries from the query cache.

20. what should be the optimum size of InnoDB buffer cache?

Answer : it should be the 70-80% of the memory available.

21. How do you backup InnoDB tables?

Answer : there are two ways of taking backup

1. mysqldump with --single-transaction
2. xtrabackup (part of percona)

22. How to take incremental backup in MySQL?

Answer : Using percona xtrabackup

23. Can the database be renamed in MySQL?

Answer :  No.

24. How to check the table fragmentation and resolve if the fragmentation is found?

Answer : 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;

25. How to change the root password if the root password is lost?

Answer : start the Db with --skip-grants-table. change the password and restart the db in  normal mode.

26. What do you do if the data disk is full?

Answer : if the data disk is full then create a soft link and move the .frm and .idb files to the linked location.

27. How to take consistent backup in mysql?

Answer : take the backup at slave after stopping the slave.

28. How do you restrict the users in mysql?

Answer :  by grant and revoke commands.

29. what is advantage of having file per table parameter enabled?

Answer :  point # 26 can only be done in file per table is enabled at innodb level.

30. How do you setup replication?













    

  • what RAID configuration should be used
    • 

  • backup strategies for databases.
    • 

  • MySQL specific things like differences between MyISAM and InnoDB
    • 

  • backup strategies and recovery
    • 

  • Let them do some SQL queries and some query optimization (usage of explain etc. even when using hibernate. Sometimes it's good to bypass hibernate to gain performance)
    • 

  • did I mention backup strategies
    • 

  • For scalability a DBA should know about different replication modes (RBR, SBR, mixed mode replication, replication maintenance such as observing replication lag and binlog maintenance)
    • 

  • InnoDB tuning
    • 

  • What kind of files are being written by the DB (eg, ibdata and log files) and how they can be arranged (eg, one ibdata file per table, move them around on different partitions, InnoDB compression)
    • 

  • Discuss monitoring tools. What are you using, do they have experience with that tool or a similar one?
    • 

  • I'd also look into system tools like iostat/memstat/vmstat/whatever your OS provides. Give them a system with some load and let them find the cause
    • 




































































Comments















Post a Comment


Oracle DBA Information