Saturday, September 1, 2012

MySQL Interview Questions & Answers


By : Mohd.Umar Khan

What is MySQL?
MySQL is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for accessing, and processing data in a database. 
Since it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. 

What’s the default port for MySQL Server? 
The default port for Mysql server is 3306

How to use MySQLdump to create a copy of the database?
To create a backup run below command:-
Mysqldump –h hostname –u username –p mydatabasename > dbdump.sql

What is the use of myisamchk? 
MyISAM is the default storage engine for MySQL database. MyISAM table gets corrupted very easily. 
myisamchk will identify and fix table corruption in MyISAM.

How to create backup of a Mysql Database
mysqldump 
Use Mysqldump to create a .sql file for the database. 
mysqldump --opt database > backup-file.sql

Example:
mysqldump database_name > database_name.sql

To restore the .sql dump file
mysql database < backup-file.sql

Example:
mysql database_name < database_name.sql

How can we take a backup of mysql table and restore it?
These are the simplest method to backup and restore the MySQl table
For taking the backup of all the databases
mysqldump --user {user} --password {password} -A > {file name to dump}
To take the backup of a specific database
mysqldump --user {user} --password {password} dbname > {filename to dump}
To restore a SQL dump please use this
mysql --user {user} --password {password} dbName < {filename to restore}

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,

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

How to check whether MySQL Server is up and running?

# mysqladmin -u root -p ping
Enter password:
mysqld is alive

How do I find out what version of MySQL I am running?

Apart from giving the ‘Server version’, this command also displays the current status of the mysql server.
# mysqladmin -u root -ptmppassword version
mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version          5.1.25-rc-community
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 107 days 6 hours 11 min 44 sec
Threads: 1  Questions: 231976  Slow queries: 0  Opens: 17067
Flush tables: 1  Open tables: 64  Queries per second avg: 0.25

What is the current status of MySQL server?

# mysqladmin -u root -ptmppassword status

Uptime: 9267148
Threads: 1  Questions: 231977  Slow queries: 0  Opens: 17067
Flush tables: 1  Open tables: 64  Queries per second avg: 0.25


[root@server ~]# mysqladmin -u root -p status
Enter password:
Uptime: 412  Threads: 1  Questions: 5  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.12


  • Uptime: Uptime of the mysql server in seconds
  • Threads: Total number of clients connected to the server.
  • Questions: Total number of queries the server has executed since the startup.
  • Slow queries: Total number of queries whose execution time waas more than long_query_time variable’s value.
  • Opens: Total number of tables opened by the server.
  • Flush tables: How many times the tables were flushed.
  • Open tables: Total number of open tables in the database.
How to display all the running process/queries in the mysql database?

# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 36   |       |                  |
| 23 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
 
You can use this command effectively to debug any performance issue and identify the query that is causing problems, by running the command automatically every 1 second as shown below.
 
# mysqladmin -u root -ptmppassword -i 1 processlist
 
 

How to Delete/Drop an existing MySQL database?

# mysqladmin -u root -ptmppassword drop testdb


How to reload/refresh the privilege or the grants tables?

# mysqladmin -u root -ptmppassword reload;
Refresh command will flush all the tables and close/open log files.
# mysqladmin -u root -ptmppassword refresh
 

How to kill a hanging MySQL Client Process?
First identify the hanging MySQL client process using the processlist command.
# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 64   |       |                  |
| 24 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
 
Now, use the kill command and pass the process_id as shown below. To kill multiple process you can pass comma separated process id’s.
 
# mysqladmin -u root -ptmppassword kill 20
 

How to start and stop MySQL replication on a slave server?

# mysqladmin  -u root -ptmppassword stop-slave
 

How to combine multiple mysqladmin commands together?
In the example below, you can combine process-list, status and version command to get all the output together as shown below. 
# mysqladmin  -u root -ptmppassword process status version
You can also use the short form as shown below:
# mysqladmin  -u root -ptmppassword pro stat ver
Use the option -h, to connect to a remote MySQL server and execute the mysqladmin commands as shown below.
# mysqladmin  -h 192.168.1.112 -u root -ptmppassword pro stat ver
 
 
 
 
 
 










2 comments: