MySQL database Administrator interview question and Answer

Question: How to start MySQL Service?
Start MySQL Server using service

sudo service mysql start

Start MySQL Server using using init.d

sudo /etc/init.d/mysql start

Start MySQL Server using systemd

Start MySQL Server using systemd

Question: List the MySQL Users

select user from mysql.user;

Question: List all databases

show databases

Question: Grant insert,UPDATE,delete privileges on the mydb (DB) to dbuser (User)

GRANT INSERT, UPDATE, DELETE ON mydb.* TO bob@localhost;

Question: Grant all privileges on the mydb (DB) to dbuser(User)

grant all privileges on mydb.* to dbuser@localhost;

Question: Show all the privileges to dbuser(User)

SHOW GRANTS FOR dbuser;

Question: Revoke insert,UPDATE,delete privileges on the mydb (DB) from dbuser (User)

REVOKE INSERT, UPDATE ON mydb.* FROM dbuser@localhost;

Question: List all the Proesslist in MySQL

SHOW Full PROCESSLIST

Question: What are different MySQL storage engines

1. MyISAM: Before MySQL version 5.5, MyISAM is the default storage engine. MyISAM extends the former ISAM storage engine. MyISAM tables are optimized for compression and speed. MyISAM tables can be compressed into read-only tables to save spaces. The MyISAM tables are not transaction-safe. MySQL also checks and repairs InnoDB tables.
2. InnoDB: InnoDB tables fully support ACID-compliant and transactions. InnoDB table supports foreign keys, commit, rollback, roll-forward operations. MySQL also checks and repairs InnoDB tables.
3. MERGE: A MERGE table is a virtual table that combines multiple MyISAM tables that have a similar structure to one table. MERGE table does not have its own indexes;
4. MEMORY: The memory tables are stored in memory and use hash indexes so that they are faster than MyISAM tables The lifetime of the data of the memory tables depends on the uptime of the database server.

Why use MySQLi instead of mysql?

Question: Why use MySQLI instead of MySQL?

1. MySQL is Not in Active Development
2. Officially deprecated as of PHP 5.5
3. Will fully removed in PHP 7.0
4. OOP is absent
5. Doesn't Support asynchronous queries
6. Doesn't Support Prepared statements
7. Doesn't Support Multiple Statements
8. MySQL leads to SQL injection vulnerabilities
9. In MySQL, we must use mysql_real_escape_string to avoid attacks where as in MySQLI its inbuilt.

Question: What is replacement of MySQL
You can use one of below:

1. MySQLi
2. PDO - PHP Database Object

Question: How to checked MySQLi is installed OR Not?

if(function_exists('mysqli_connect')){
 echo "MySQLi enabled";
}else{
 echo "MySQLi NOT enabled";
}

OR
you can check in phpinfo.


Question: How to enable MySQLi in php.ini? If not already enabled?
open php.ini file and find php_mysqli.dll
Replace

;extension=php_mysqli.dll

With

extension=php_mysqli.dll

Basically removed the colon (i.e ;)


Question: How to connect to MySQLi?
Connect to MySQLI with procedural programming

$mysqliObj = mysqli_connect("localhost", "root", "", "mydb");

Connect to MySQLI with OOP programming

$mysqliObj = new mysqli("localhost", "root", "", "mydb");

Question: What minimum PHP Version required for MySQLI?
PHP5.0


Question: Give Example of Database Connection?

$conn = new mysqli('localhost', 'root', '', 'mydb');
if ($conn->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqliObj->connect_errno . ") " . $mysqliObj->connect_error;
}

Question: Give Example of Insert Record?

$sql="INSERT INTO users (name, email) VALUES ('jonny','jonny@no-spam.ws')"; 
if($conn->query($sql) === false) {
  echo "Error: ".$conn->error;
} else {
  echo "Insert Successfully";
}

Question: Give Example of Fetch Record from database?

$sql="select * from users "; 
$rs=$conn->query($sql);
 
if($rs === false) {
  echo "Error: ".$conn->error;
} else {
  echo $rs->num_rows.' Total Rows found';
}

Question: Give Example of Free the Memory after getting data?

$rs->free();

Question: Give Example of Delete Record?

$sql="delete from users where id=10"; 
if($conn->query($sql) === false) {
  echo "Error: ".$conn->error;
} else {
  echo "Insert Successfully";
}

Question: Give Syntax for Transactions in MySQLi

/*starts transaction*/
try {

  /* switch autocommit status to FALSE. Actually */
  $conn->autocommit(FALSE);
 
  $res = $conn->query($sql1);
  if($res === false) {
    throw new Exception('Wrong SQL: ' . $sql . ' Error: ' . $conn->error);
  }
 
  $res = $conn->query($sql2);
  if($res === false) {
    throw new Exception('Wrong SQL: ' . $sql . ' Error: ' . $conn->error);
  }
 
  /** You can use number of SQL Query **/
 
  $conn->commit();
  echo 'Transaction completed successfully!';
 
} catch (Exception $e) {
 
  echo 'Transaction failed: ' . $e->getMessage();
  $conn->rollback();
}
 
/* switch back autocommit status */
$conn->autocommit(TRUE);

Question: Give an example to close the MySQLi connection?

$conn->close();

Best Related Posts are Following:

1. What is MySQLI technology in php.
2. Using mysqli and parameterized statements in PHP with Example.

What is MySQLI technology in PHP?

The MySQLi(MySQL Improved) is a relational database driver used in the PHP programming language to provide an interface with MySQL databases.

There are three main API options when considering connecting to a MySQL database server:
1. PHP's MySQL Extension
2. PHP's MySQLI Extension
3. PHP Data Objects (PDO)





Following is SQL Query for test the PHP Code:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `type` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
INSERT INTO `mydb`.`users` (`id`, `first_name`, `last_name`, `email`, `type`) VALUES (NULL, 'Arun', 'kumar', 'arun.compute@domain.com', 'Admin');

The MySQLI extension supports dual interface procedural and object-oriented programming paradigm.
MySQL database connection with PHP's MySQLI driver using procedural programming.

$mysqliObj = mysqli_connect("localhost", "root", "", "mydb");
$result = mysqli_query($mysqliObj, "select * from users");
$row = mysqli_fetch_assoc($result);
print_r($row);

MySQL database connection with PHP's MySQLI driver using object-oriented programming.

$mysqliObj = new mysqli("localhost", "root", "", "mydb");
if ($mysqliObj->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqliObj->connect_errno . ") " . $mysqliObj->connect_error;
}
$result = $mysqliObj->query("SELECT * from  users");
$row = $result->fetch_assoc();
print_r($row);

Following are MySQLI Functionalities.
1. An object-oriented interface

$mysqliObj = new mysqli("localhost", "root", "", "mydb");
$result = $mysqliObj->query("SELECT * from  users");
$row = $result->fetch_assoc();
print_r($row);

2. Supports server-side Prepared Statements

$mysqliObj = new mysqli("localhost", "root", "", "mydb");
$userType = "Admin";
$stmt = $mysqliObj->prepare("SELECT * FROM users WHERE type = ?");
$stmt->bind_param("i", $userType);
$stmt->execute();
$resObj = $stmt->get_result();
while ($row = $resObj->fetch_assoc()) {
    print_r($row);echo "\n";
}

3. Supports Stored Procedures

$mysqliObj = new mysqli("localhost", "root", "", "mydb");
$mysqliObj->query("CALL p(1)");

4. Support for multiple statements

$mysqliObj = new mysqli("localhost", "root", "", "mydb");
$sql = "SELECT COUNT(*) AS _num FROM users; ";
$sql.= "INSERT INTO users(name) VALUES ('name'); ";
$sql.= "SELECT COUNT(*) AS _num FROM users; ";
if (!$mysqliObj->multi_query($sql)) {
    echo "Multi query failed: (" . $mysqliObj->errno . ") " . $mysqliObj->error;
}

5. Support for transactions

$mysqliObj = new mysqli("localhost", "root", "", "mydb");
$sql1="INSERT INTO users (first_name, email) VALUES ('name','email@web-technology-experts-notes.in')";
$sql2="INSERT INTO users (first_name, email) VALUES ('name','email@web-technology-experts-notes.in')";
try {
  /* switch autocommit status to FALSE. Actually, it starts transaction */
  $mysqliObj->autocommit(FALSE);
  
  $res1 = $mysqliObj->query($sql1);
  if($res1 === false) {
    throw new Exception('Wrong SQL: ' . $sql1 . ' Error: ' . $mysqliObj->error);
  }
  
  $res2 = $mysqliObj->query($sql2);
  if($res2 === false) {
    throw new Exception('Wrong SQL: ' . $sql2 . ' Error: ' . $mysqliObj->error);
  } 
  $mysqliObj->commit();
  echo 'Transaction completed successfully!';
  
} catch (Exception $e) {
  
  echo 'Transaction failed: ' . $e->getMessage();
  $mysqliObj->rollback();
} 

6. Enhanced debugging support
7. Embedded server support
8. API supports Charsets
9. Result set contains metadata: It have information about table along with result.

Best Related Posts are Following:

1. What is MySQLI technology in php.
2. Using mysqli and parameterized statements in PHP with Example.

How to use MySQLi parameterized statements?

Here I will provide code snippet for the followings

• How to make Database Connection with PHP-MysQLI.
• How to fetch the records from database using MysQLI parameterized/prepared statements.
• How to insert the record in database using MysQLI parameterized/prepared statements.

Create New Table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `type` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 
INSERT INTO `users` (`id`, `first_name`, `last_name`, `email`, `type`) VALUES
(1, 'Micheal', 'Steve', 'micheal@georama.com', 'Guide'),
(2, 'Vt', 'B2B', 'vt@georama.com', 'Guide'),
(5, 'Jana', 'Peter', 'jana@gmail.com', 'Guide'),
(6, 'Ram', 'kumar', 'ramkumar@no-spam.ws', 'Guide');

How to make Database Connection with PHP-MysQLI.

$host = 'localhost';
$user = 'root';
$password = '';
$dbName = 'arun';
$mysqliObj = new mysqli($host, $user, $password, $dbName);
if ($mysqliObj->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqliObj->connect_errno . ") " . $mysqliObj->connect_error;
}

How to fetch the records from database using MysQLI parameterized/prepared statements.

$userType = "Guide";
$stmt = $mysqliObj->prepare("SELECT * FROM users WHERE type = ?");
$stmt->bind_param("i", $userType);
$stmt->execute();
$resObj = $stmt->get_result();
while ($row = $resObj->fetch_assoc()) {
    print_r($row['id']);echo "\n";
}

How to insert the record in database using MysQLI parameterized/prepared statements.

/** Insert into database with mysql * */
//the data
$id = null;
$firstname = "Web";
$lastname = "Technoogy";
$email = "arun.c@no-spam.ws ";
$type = "Guide";

$stmt = $mysqliObj->stmt_init();
if (!($stmt->prepare("INSERT INTO users(first_name, last_name, email,type) VALUES (?,?,?,?)"))) {
    echo "Prepare failed: (" . $mysqliObj->errno . ") " . $mysqliObj->error;
}
$stmt->bind_param("ssss", $firstname, $lastname, $email, $type);
$stmt->execute();
$stmt->close();

Best Related Posts are Following:

1. What is MySQLI technology in php.
2. Using mysqli and parameterized statements in PHP with Example.