Monday 4 September 2017

Interview Q and A for MySQL Part - 1

1. How would you check if MySql service is running or not?
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

2. If the service is running/stop how would you stop/start the service?
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.
start and stop MySQL on Windows? - net start MySQL, net stop MySQL
start MySQL on Linux? - /etc/init.d/mysql start
3. How will you login to MySQL from Linux Shell?
use command: mysql -u root -p.
root@localhost:/home/avi# mysql -u root -p
Enter password:
mysql -h hostname -u root -p pass
4. How will you obtain list of all the databases?
To list all currently running databases run the command on mysql shell as: show databases;
mysql> show databases;

5. How will you switch to a database, and start working on that?
To use or switch to a specific database run the command on mysql shell as: use database_name
mysql> use cloud;

6. How will you get the list of all the tables, in a database?
To list all the tables of a database, use the command on mysql shell as: show tables
mysql> show tables;

7. How will you get the Field Name and Type of a MySql table?
To get the Field Name and Type of a table use the command on mysql shell as: describe table_name
mysql> describe oc_users;

8. How will you delete a table?
To delete a specific table use the command on mysql shell as: drop table table_name
mysql> drop table lookup;

9. What about database? How will you delete a database?
To delete a specific database use the command on mysql shell as: drop database database-name
mysql> drop database a1;

10. How many TRIGGERS are possible in MySql?
There are only six triggers are allowed to use in MySQL database and they are
1. Before Insert
2. After Insert
3. Before Update
4. After Update
5. Before Delete
6. After Delete

11. What can you tell me about the MySQL Architecture?
Response should reference some of the following: 

 MySQL has got the logical architecture as follows

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

12. What are the steps involved in taking over a MySQL server when you do not have any credentials? OR
What do I do if I forget the MySQL root password?
Response should be similar to some of the following: 
  • stop the service: # /etc/init.d/mysql stop
  • Restart with skip grant: # 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

OR
First log in to the system as the same person who is running the mysqld daemon (probably root). Kill the process, using the kill command.
Restart MySQL with the following arguments:
bin/mysqld Skip-grant
USE mysql;
UPDATE user SET password = password(‘newpassword’) WHERE User = ‘root';
Exit
bin/mysqladmin reload
The next time you log in, you will use your new password
13. 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, 
The InnoDB Storage Engine
·       The MyISAM Storage Engine
Bonus points if they can talk about these as well

14. 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 ?

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

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

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

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

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

20. 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 stopping the slaves. 

21. What are the major differences between MySQL 5.1 and 5.5?
  • InnoDB Becomes Default Storage Engine
  • Semi-Synchronous Replication
  • 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.

 22. 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
Advantages of InnoDB over MyISAM
Row-level locking, transactions, foreign key constraints and crash recovery.
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.

23. Where is the data stored in a MySQL database?
MySQL uses files to store data. These files are under the data/databasename directory, where databasename is the name of the database. There are three file types: ISM, .FRM, and. ISD. The .FRM file contain the table schema. The. ISD is the file that actually holds the data. The. ISM file is the file that provides quick access between the two of them.

24. What are features of MYSQL?
MySQL is a full-featured relational database management system. It is very stable and has proven itself over time. MySQL has been in production for over 10 years.
- MySQL is a multithreaded server. Multithreaded means that every time someone establishes a
connection with the server, the server program creates a thread or process to handle that client’s
requests. This makes for an extremely fast server. In effect, every client who connects to a MySQLserver gets his or her own thread.
- MySQL is also fully ANSI SQL92-compliant. It adheres to all the standards set forth by the American National Standards Institute.
- another feature of MySQL is its portability—it has been ported to almost every platform. This means that you don’t have to change your main platform to take advantage of MySQL. And if you do want to switch, there is probably a MySQL port for your new platform.
- MySQL also has many different application programming interfaces (APIs). They include APIs for Perl, TCL, Python, C/C++, Java (JDBC), and ODBC.

25. What is MySQL Query Browser?
The MySQL Query Browser is a graphical tool designed to provide a user-friendly environment in which to construct and execute SQL statements.

26. Explain “AUTO_INCREMENT” attribute?
AUTO_INCREMENT attribute may be added to an integer column definition to create a column for which MySQL automatically generates a new sequence number each time you create a new row. There may be only one AUTO_INCREMENT column per table, the column must be indexed, and the column must be defined as NOT NULL.

27. What this command “mysqladmin status variables” will do?
This command will display a brief status message, followed by the list of server system variables.

28. What is REPLCAE statement, and how do I use it?
The REPLACE statement is the same as using an INSERT INTO command. The syntax is pretty much the same. The difference between an INSERT statement and a REPLACE statement is that MySQL will delete the old record and replace it with the new values in a REPLACE statement, hence the name REPLACE.

29. MySQL has a lot of neat functions. What if I need one that isn’t there?
MySQL is so flexible that it allows you to create your own functions. These user-defined functions act the same way that MySQL’s own intrinsic functions operate. It is also possible to recompile your functions into the application so that you will always have them, no matter how many times you install.

30. Do all unique keys have to be primary keys?
No. MySQL permits only one primary key per table, but there may be a number of unique keys. Both unique keys and primary keys can speed up the selecting of data with a WHERE clause, but a column should be chosen as the primary key if this is the column by which you want to join the table with other tables.

31. How many databases can one MySQL RDBMS contain?
Because MySQL uses the file system of the operating system, there really is no limit to the number of databases contained within a single MySQL RDBMS. The size of the database is limited by the operating system. The database tables can only be as big as the OS’s file system will allow.

32. I want to sort the values of my ENUM and SET columns. How do I do this?
The sort order depends on the order in which the values were inserted. ENUM and SET types are not case sensitive. The value that is inserted reverts to the value that you used when you created the ENUM or SET.

33. What can I do with the contents of a mysqldump file?
This file is a complete replica of your database in SQL format. You can do a lot of things with this data. You could re-create your database in Microsoft SQL Server or Sybase by simply cutting and pasting the contents of the file. You could also restore your database by using the dump file and the batching ability of the mysql program.

34. Explain “CHECK TABLE” statement?
The CHECK TABLE statement performs an integrity check on table structure and contents. It works for MyISAM and InnoDB tables. For MyISAM tables, it also updates the index statistics. If the table is a view, CHECK TABLE verifies the view definition.

35. Explain “REPAIR TABLE” statement?
The REPAIR TABLE statement corrects problems in a table that has become corrupted. It works only for MyISAM tables.

36. Explain “ANALYZE TABLE” statement?
The ANALYZE TABLE statement updates a table with information about the distribution of key values in the table. This information is used by the optimizer to make better choices about query execution plans. This statement works for MyISAM and InnoDB tables.

37. Explain “OPTIMIZE TABLE” statement?
The OPTIMIZE TABLE statement cleans up a MyISAM table by defragmenting it. This involves reclaiming unused space resulting from deletes and updates, and coalescing records that have become split and stored non-contiguously. OPTIMIZE TABLE also sorts the index pages if they are out of order and updates the index statistics.

38. What is “mysqlcheck Client Program”?
mysqlcheck checks, repairs, analyzes, and optimizes tables. It can perform all these operations on MyISAM tables, and can perform some of them on InnoDB tables. It provides a command-line interface to the various SQL statements that instruct the server to perform table maintenance, such as CHECK TABLE and REPAIR TABLE.

39. What is “myisamchk Utility”?
The myisamchk utility performs table maintenance on MyISAM tables.
It compressed the MyISAM tables, which reduces their disk usage.

40. What is the use of “INFORMATION_SCHEMA Database”?
The INFORMATION_SCHEMA database provides access to database metadata. INFORMATION_SCHEMA is a “virtual database” in the sense that it is not stored anywhere on disk. But like any other database, it contains tables, and its tables contain rows and columns that can be accessed by means of SELECT statements.

41. What is “binary backup”?
A binary backup is a copy of the files in which database contents are stored. Copying these files preserves the databases in exactly the same format in which MySQL itself stores them on disk. Restoration involves copying the files back to their original locations. Techniques for making binary backups include file copy commands.

42. What is “text backup”?
A text backup is a dump of database contents into text files. Restoration involves loading the file contents back into databases by processing them through the server. Techniques for making text backups include the SELECT … INTO OUTFILE SQL statement, mysqldump, and MySQL Administrator.

43. How you will grant the SELECT privilege for all tables in the TEST database to a user named Ravi, who must connect from the local host and use a password of Ravi123?
Use following command.
GRANT SELECT ON TEST.* TO ‘Ravi’@’localhost’ IDENTIFIED BY ‘Ravi123′;

44. Explain “REVOKE statement”?
The REVOKE statement to revoke privileges from an account.

45. Explain the command “SHOW GRANTS FOR ‘root’@’localhost';”
It will show the account has global, database-level, and table-level privileges.

46. Explain the terms “mysqlimport”, “mysqldump”, “mysqladmin” and “mysqlcheck”?
mysqlimport for importing data files, mysqldump for making backups, mysqladmin for server administration, and mysqlcheck for checking the integrity of the database files.

47. How you will determine the options which are used by mysql?
#mysql –help

48. How you will determine the version of MySQL?
mysql –version

49. How you will connect to the server at a specific IP address with username and password?
mysql –host=10.168.1.33 –user=NAME –password=PASSWORD

50. What do you think about this command “mysql> STATUS;”

It will display information about the current connection to the server, as well as status information about the server itself.
at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)