MySQL

Question 1

How do you start MySQL on Linux?

Answer 1

/etc/init.d/mysql start

Question 2

Explain the difference between mysql and mysqli interfaces in PHP?

Answer 2

mysqli is the object-oriented

version of mysql library functions.

Question 3

What does tee command do in MySQL?

Answer 3

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

Question 4

How do you change a password for an existing user via mysqladmin?

Answer 4

mysqladmin -u root -p password

“newpassword” or mysql command: set password for ‘username’@’localhost’ = password(‘newpassword’);

Question 5

Use mysqldump to create a copy of the database?

Answer 5

mysqldump -h mysqlhost -u username -p

mydatabasename > dbdump.sql

Question 6

Explain advantages of InnoDB over MyISAM?

Answer 6

Row-level locking, transactions, foreign key constraints and crash recovery.

Question 7

Explain advantages of MyISAM over InnoDB?

Answer 7

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. InnoDB COUNT(*)s execute slower than in MyISAM due to tablespace complexity.

Question 8

Explain federated tables.

Answer 8

Introduced in MySQL 5.0, federated tables allow access to the tables located on other
databases on other servers. CREATE TABLE t1 ( a int, b varchar(32)) ENGINE=FEDERATED
CONNECTION=’mysql://user@hostname/test/t1’