MySQL Replication Setup

Mysql uses a Master-slave/Publisher-Subscriber model for Replication. MySQL replication is an asynchronous replication. In MySQL replication master keeps a log of all of the updates performed on the database. Then, one or more slaves connect to the Master(Publisher Server), read each log entry, and perform the indicated update on the slave (Subscriber) server databases. The master server is responsible for the track of log rotation and access control.

Each slave server has to keep the track of current position within the server's transaction log. As new transactions occur on the server, they get logged on the master server and downloaded by each slave. Once the transaction has been committed by each slave, the slaves update their position in the server's transaction log and wait for the next transaction.

In this article, I will show you the steps to configure the Master/Slave replication between two servers.

Step 1: Create a user on Master server which Slave server can use to connect. I have created the user named "repl_user".
[sourcecode language="sql"]
--Connect to MySQL Master server
mysql -u root -proot
--Execute the below code
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
[/sourcecode]

Step 2: We have to change the MySQL configuration file usually in the /etc/mysql.cnf location. Here we will add the replication configuration parameters.

log-bin – will be used to write a log on the desired location
binlog-do-db – will be used to enabled the database for writing log. I have used Publisher_Database, you have to specify your database name.
server-id – Specify the ID of the Master server

[sourcecode language="sql"]
log-bin = /home/mysql/logs/mysql-bin.log
binlog-do-db=publisher_database
server-id=1
[/sourcecode]

Step 3: Once you have added the above configuration parameters into the My.cnf, next step is restart the MySQL Master Instance.
You can use below command to restart the MySQl service.
[sourcecode language="sql"]
/etc/init.d/mysqld restart
service mysqld restart
[/sourcecode]

Step 4: We have to configure the /etc/my.cnf file on the slave server. Here we will add the below parameters in the configuration file.

server-id - gives the Slave its unique ID
master-host - tells the Slave the I.P address of the Master server for connection. You can get the IP address using IPConfig command.
master-connect-retry - Here we will specify the connection retry interval.
master-user - Specify the user which has permission access the Master server
master-password - Specify the password of the replication user mentioned above
replicate-do-db - Specify the subscriber database name
relay-log - direct slave to use relay log

[sourcecode language="sql"]
server-id=2
master-host=128.20.30.1
master-connect-retry=60
master-user=repl_user
master-password=password
replicate-do-db=subscriber_slave
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
[/sourcecode]

Step 5: Restart the slave MySQl instance
[sourcecode language="sql"]
/etc/init.d/mysqld restart
service mysqld restart
[/sourcecode]

Step 6: If your Master MySQL instance is live instance, you have to do the backup/restore using MySQLDump utility.
[sourcecode language="sql"]
--Connect to MySQL Master server
mysql -u root -proot

--Stop the write operation
FLUSH TABLES WITH READ LOCK;

--Generate the dump of the database (backup)
--gzip command will compress the file and create the zip file name backup.sql.gz
mysqldump publisher_master -u root -p > /home/my_home_dir/backup.sql;
gzip /home/my_home_dir/backup.sql;

--execute below copy command on slave to copy the backup file
scp root@128.20.30.1:/home/my_home_dir/database.sql.gz /home/my_home_dir/

--Once copied, extract the file using gunzip
gunzip /home/my_home_dir/backup.sql.gz

--restore the databsae
mysql -u root -p subscriber_slave </home/my_home_dir/backup.sql

[/sourcecode]

Step 7: Execute the SHOW MASTER STATUS command on Master server. It will give you the bin log file name and position which we will use specify the slave.
[sourcecode language="sql"]
SHOW MASTER STATUS;
[/sourcecode]

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 707 | exampledb | |
+------------------+----------+--------------+------------------+


Step 8: Execute the below commands on slave.
[sourcecode language="sql"]
--Connect to MySQL Slave server
mysql -u root -proot
--stop the slave
slave stop;
-- Execute the below command
CHANGE MASTER TO MASTER_HOST='128.20.30.1', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=707;
--Start the Slave
slave start;
[/sourcecode]

Step 9: Login to Master MySQL instance and unlock the tables.
[sourcecode language="sql"]
--Connect to MySQL Master server
mysql -u root -proot
-- unlock the tables if you have executed lock tables command
unlock tables;
[/sourcecode]

You are all set. Master to Slave replication has been started. Make sure while configuring the my.cnf file.
1. Take the copy of my.cnf file before starting the replication configuration.
2. Make sure skip-networking parameter is not enabled in the my.cnf file.

MySQL Replication

Problem/Error

Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave

Resolution Steps

You have to follow below steps to troubleshoot the error.

Execute the below command

SHOW MASTER STATUS

SHOW SLAVE STATUS

Check the error log for replication and its position.

Ideally there are three sets of file/position coordinates in SHOW SLAVE STATUS to identify the correct file

1) The position, ON THE MASTER, from which the I/O thread is reading: Master_Log_File/Read_Master_Log_Pos.

2) The position, IN THE RELAY LOGS, at which the SQL thread is executing: Relay_Log_File/Relay_Log_Pos

3) The position, ON THE MASTER, at which the SQL thread is executing: Relay_Master_Log_File/Exec_Master_Log_Pos

Next you have to check the error log for the log position to identify the correct binary log file and set the correct log file using below command.

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000480'

Once the problem is resolved you can use Maatkit tool to sync table to multiple slaves.

mk-table-checksum command is used to check what tables are out of sync and when use mk-table-sync command is used to resync them.

Memcached & MySQL

memcached (pronunciation: mem-cash-dee.) is a general-purpose distributed memory caching system that was originally developed by Danga Interactive for LiveJournal, but is now used by many other sites. It is often used to speed up dynamic database-driven websites by caching data and objects in memory to reduce the number of times an external data source (such as a database or API) must be read. Memcached is distributed under a permissive free software license. Memcached lacks authentication and security features, meaning it should only be used on servers with a firewall set up appropriately. By default, memcached uses the port 11211. Among other technologies, it uses libevent. Memcached's APIs provides a giant hash table distributed across multiple machines. When the table is full, subsequent inserts cause older data to be purged in least recently used (LRU) order. Applications using memcached typically layer memcached requests and additions into core before falling back on a slower backing store, such as a database. You can download memcached API from http://www.danga.com/memcached/

 

How to recover root password in MySQL?

1. As Linux system root user stop the database process: /etc/init.d/mysql stop
   (or: service mysql stop)


2. Start MySQL in safe mode and skip the use of the "grant tables": /usr/bin/mysqld_safe --user=mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --datadir=/var/lib/mysql --skip-grant-tables --skip-networking &


3. Reset the MySQL root password: mysqladmin -u root flush-privileges password newpassword


4. Stop MySQL running in safe mode: kill `cat /var/run/mysqld/mysqld.pid`


5. Start MySQL: /etc/init.d/mysql start


6. The new MySQL root password can now be used: mysql -u root -p
   Respond withthe password: newpassword

MySQL and Linux commands

Where are the MySQL binaries stored? Find the location? : which mysql

 

Find the version of MySQL: mysql -V

 

Start the MySQL server: /usr/bin/mysqld_safe --user=mysql & or /etc/init.d/mysql start

 

Validate MySQL process has started: ps -afe | grep mysqld

 

Validate MySQL server is up and running: mysql> SHOW VARIABLES LIKE "%version%";

 

Find the columns of the table in MySQL: mysql> SHOW COLUMNS FROM dept;

 

Find the table related information: SHOW TABLE STATUS LIKE 'dept' \G

 

Display all tables: Mysql> show tables

 

Display all databases: Myql> show databases

 

Display tables from the non current database: Mysql> SHOW TABLES FROM database_name;

 

List the filed of the tables

mySQL> describe tableName

 

Backup/Dump all databases

[mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

 

Backup/Dump particular database

[mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

 

Backup/Dump a table from a database

[mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

 

Restore database (or database table) from backup

[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

 

 

 

 

 

How to decide Primary Key column data type in MySQL? Expectation with the Primary Key column

Decide the Primary Key column is one of the important tasks. Typically, the Primary Key is must mitigate the below properties.

 

(1) Uniquely identify each row in a table

(2) Can be referenced by another column/row (foreign key)

(3) provide a "natural" sequencing of the data (alphabetical, numeric, and chronological)

(4) Partition the data horizontally for performance

 

If your interest is either "unique identifier" or "natural sequencing", you will be using whatever data type fits for the rows natural identifier. So that pretty much answers the question, and should be your first choice.

 

Remember that a primary key must be immutable -- never changes -- so using a natural key may not be possible. In that case, a surrogate key becomes a necessity.

 

A surrogate key will probably break your "natural sequencing", since the data will be indexed on the surrogate key value, not on the natural values. This doesn't mean to avoid it, but only to recognize that condition and address it in your design (an index, for example, restores the sequencing but adds overhead to update/delete/insert operations).

 

Also, if you are looking at this as a performance problem, you may want to substitute an integer data type as a "surrogate key" for the natural primary key.

 

If you use a surrogate key, use an integer that is sized to the native size of the platform (32-bit, 64-bit, 128-bit integer) up to the size needed to support uniqueness.

 

I would avoid integers which increment monotonically (by 1) -- this presents a "bottleneck" in the processing, making it complicated to run multiple, parallel, concurrent insertions. If you're running an OLTP system, that bottleneck will limit your performance and scalability.

 

So ultimately to decide the primary key data type we need to project the data growth, number of insertion, number of selection and performance issues.

 

For the normal table you can use primary key datatype as BIG INT with AUTO_INCREMENT option.

Index Covering

What is Index Covering?

Index covering means to add a non-clustered index on every column  which are used in Query. Easy solution to improve query performance

 

Scenario: Speed Up a Query for a Table with a Clustered Index

Consider a very typical scenario: you have a table EmployeeSalary table with a clustered index on Employee_ID column. You need to speed up a select query quickly:

SELECT Salary_DATE, SUM(AMOUNT) FROM EmployeeSalary GROUP BY Salary_DATE

 

The query's execution plan is quite simple: the database engine scans the whole clustered index, and then it sorts the intermediate result set to satisfy the GROUP BY clause.

 

Can a non-clustered index speed up the query? Definitely. Just create a non-clustered index that contains all the columns used in the query:

CREATE  INDEX sal_amt ON dbo.EmployeeDetail(Salary_DATE, AMOUNT)

 

Re-execute the query it will run faster than the previous query.  If you, look at the execution plan: the query accesses only the index salary_amt; it doesn't touch the table at all. In fact, there is no need to access the table because all the columns necessary to satisfy the query are already stored in the index. This is called index covering.

MySQL Optimization Tips

(for more database related articles)

MYSQL Optimization Tips
The MySQL database server performance depends on the number of factors. The Optimized Query is one of the factors for the MySQL robust performance.

The MySQL performance depends on the below factors.

1. Hardware (RAM, DISK, CPU etc)


2. Operating System (i.e. Linux OS will give the more performance compare to Windows OS )


3. Application


4. Optimization of MySQL Server & Queries

· Choose compiler and compiler options.

· Find the best MySQL startup options for your system (my.ini/my.cnf).

· Use EXPLAIN SELECT, SHOW VARIABLES, SHOW GLOBAL STATUS, SHOW GLOBAL STATUS and SHOW PROCESSLIST.

· Optimize your table formats.

· Maintain your tables (myisamchk, CHECK TABLE, OPTIMIZE TABLE).

· Use MySQL extensions to get things done faster.

· Write a MySQL UDF function if you notice that you would need some function in many places.

· Don't use GRANT on table level or column level if you don't really need it

· Use Index columns in joins

· Use better data types for the table design. (i.e. “INT” data type is better than “BIG INT” data type)

· Increase the use of “NOT NULL” at table level, that will save some bits

· Do not use UTF8 where you do not need it. UTF8 has 3 times more space reserved. Also UTF8 comparison and sorting is much more expensive. Only use UTF8 for mixed charset data

· Use staraight_join instead of inner join

· Use joins instead of “IN” or “Sub-Queries”

· Decide the database engine for the table by most effective way. (INNODB, MEMORY, ARCHIVE etc)

· INNODB database engine needs more performance and tuning for the MySQL Server & Query optimization.

· Try to create Unique Index. Avoid duplicate data in the index columns.

· Beware of Large Limit

• LIMIT 1000000, 10 can be slow. Even Google does not let you to page 100000. If large number of groups use

SQL_BIG_RESULT hint. Use FileSort instead of temporary table

· USE Index hints (INDEX/FORCE INDEX/IGNORE INDEX) (i.e SELECT * FROM Country IGNORE INDEX(PRIMARY)). This will give the advice to MySQL for the Index Use.

· Use “UNION ALL” instead of “UNION”

· Do not normalize the schema up to more than 3^rd Level NF

· Avoid the use of cursors in the stored procedure if not required.

· Avoid the use DDL statements in the stored procedure if not required.

· Use SQL for the things it's good at, and do other things in your application. Use the MySQL server to:

· Find rows based on WHERE clause.

· JOIN tables

· GROUP BY

· ORDER BY

· DISTINCT

Don't use MySQL server:

· To validate data (like date)

· As a calculator

· Use keys wisely.

· Keys are good for searches, but bad for inserts / updates of key columns.

· Keep by data in the 3rd normal database form, but don't be afraid of duplicating information or creating summary tables if you need more speed.

· Instead of doing a lot of GROUP BYs on a big table, create summary tables of the big table and query this instead.

· UPDATE table set count=count+1 where key_column=constant is very fast!

· For log tables, it's probably better to generate summary tables from them once in a while than try to keep the summary tables live.

· Take advantage of default values on INSERT.

· Use Index columns in joins

· Use the explain command
Use multiple-row INSERT statements to store many rows with one SQL statement.

The explain command can tell you which indexes are used with the specified query and many other pieces of useful information that can help you choose a better index or query.

Example of usage: explain select * from table

Explanation of row output:

o table—The name of the table.

o type—The join type, of which there are several.

o possible_keys—This column indicates which indexes MySQL could use to find the rows in this table. If the result is NULL, no indexes would help with this query. You should then take a look at your table structure and see whether there are any indexes that you could create that would increase the performance of this query.

o key—The key actually used in this query, or NULL if no index was used.

o key_len—The length of the key used, if any.

o ref—Any columns used with the key to retrieve a result.

o rows—The number of rows MySQL must examine to execute the query.

o extra—Additional information regarding how MySQL will execute the query. There are several options, such as Using index (an index was used) and Where (a WHERE clause was used).

· Use less complex permissions

The more complex your permissions setup, the more overhead you have. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements.

· Specific MySQL functions can be tested using the built-in “benchmark” command

If your problem is with a specific MySQL expression or function, you can perform a timing test by invoking the BENCHMARK() function using the mysql client program. Its syntax is BENCHMARK(loop_count,expression). The return value is always zero, but mysql prints a line displaying approximately how long the statement took to execute

· Optimize where clauses

o Remove unnecessary parentheses

o COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.

o If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table

· Run optimize table

This command de-fragments a table after you have deleted/inserted lots of rows into table.

· Avoid variable-length column types when necessary

For MyISAM tables that change frequently, you should try to avoid all variable-length columns (VARCHAR, BLOB, and TEXT). The table uses dynamic row format if it includes even a single variable-length column.

· Insert delayed

Use insert delayed when you do not need to know when your data is written. This reduces the overall insertion impact because many rows can be written with a single disk write.

· Use statement priorities

o Use INSERT LOW_PRIORITY when you want to give SELECT statements higher priority than your inserts.

o Use SELECT HIGH_PRIORITY to get retrievals that jump the queue. That is, the SELECT is executed even if there is another client waiting.

· Use multiple-row inserts

Use multiple-row INSERT statements to store many rows with one SQL statement.

· Synchronize data-types

Columns with identical information in different tables should be declared to have identical data types so that joins based on the corresponding columns will be faster.

· Optimizing tables

o MySQL has a rich set of different types. You should try to use the most efficient type for each column.

o The ANALYSE procedure can help you find the optimal types for a table: SELECT * FROM table_name PROCEDURE ANALYSE()

o Use NOT NULL for columns which will not store null values. This is particularly important for columns which you index.

o Change your ISAM tables to MyISAM.

o If possible, create your tables with a fixed table format.

o Don't create indexes you are not going to use.

o Use the fact that MySQL can search on a prefix of an index; If you have and INDEX (a,b), you don't need an index on (a).

o Instead of creating an index on long CHAR/VARCHAR column, index just a prefix of the column to save space. CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))

o Use the most efficient table type for each table.

o Columns with identical information in different tables should be declared identically and have identical names.

When MySQL uses indexes

o Using >, >=, =, <, <=, IF NULL and BETWEEN on a key.

o SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;

o SELECT * FROM table_name WHERE key_part1 IS NULL;

o When you use a LIKE that doesn't start with a wildcard.

o SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'

o Retrieving rows from other tables when performing joins.

o SELECT * from t1,t2 where t1.col=t2.key_part

o Find the MAX() or MIN() value for a specific index.

o SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

o ORDER BY or GROUP BY on a prefix of a key.

o SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3

o When all columns used in the query are part of one key.

o SELECT key_part3 FROM table_name WHERE key_part1=1

Pros and Cons of MySQL Table Types

Please visit http://sqldbpool.blogspot.com/

(for more database related articles)

Of all the positive things that MySQL brings to the table, probably the most overlooked is multiple table types. This facet of the application is overlooked as a feature and more importantly is overlooked at design time.

MySQL has six distinct table types.

• MyISAM


• MERGE


• ISAM


• HEAP


• InnoDB


• BDB or BerkeleyDB Tables

Finding a transaction table that's just my type

Two of these table types currently support transactions. Transactions give the user greater control when working with data. You would use syntax similar to the following for a manual transaction.

START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET summmary=@A WHERE type=1;

COMMIT;

Of the two commonly used transaction table types, the first is BerkeleyDB transactional tables provided by SleepyCat (www.sleepycat.com). In order to use BDB tables use a binary with BDB support or configure the source with the withberkeleydb option. If you don't want to use BDB tables, start the MySQL server with the skipbdb option. This will save a lot of memory, since the BDB library won't be included. However, you won't be able to use BDB tables. BDB is not used nearly as much as our second alternative which is InnoDB. InnoDB features rowlevel locking, consistent nonlocking read in SELECTs and common tablespace for all tables.

InnoDB Tables are made by Innobase Oy (www.innodb.com), and are distributed under the GPL as well as commercially. If you need commercial support or commercial licenses for your application and cost is a concern, not using InnoDB will save you about 20-50 % for licenses and support contracts. If data integrity is a concern InnoDB provides MySQL with a transactional storage engine and crash recovery capabilities. InnoDB has been designed for maximum performance when processing large data volumes and any other diskbased relational database engine does probably not match CPU efficiency. There are other transactional table types out there (such as Gemini), but they do not seem to be used any more than BDB. So, in a nutshell, most users prefer the speed and features of InnoDB.

A Database is no fun when you are locked out

The default table type for MySQL is MyISAM. It has table level locking, which means during an UPDATE, nobody can access any other record of the same table. BDB uses Page level locking, and during an UPDATE, nobody can access any other record residing in the same database page of that table, until the locking transaction issues a COMMIT.

InnoDB however, uses Row level locking. Row level locking ensures that during an UPDATE, nobody can access that particular row, until the locking transaction issues a COMMIT. Any of the above table types will probably be fine for a web server, but in a LAN application can cause unecessary issues.

Special circumstances call for special tools (or tables)

MERGE tables are a collection of identical MyISAM tables that can be used as one. You can only SELECT, DELETE, and UPDATE from the collection of tables. If you DROP the MERGE table, you are only dropping the MERGE specification. One reasons why you would use MERGE tables is to get more speed. You can split a big read-only table and then put the different table parts on different disks. You could do more efficient searches. If you know exactly what you are looking for, you can search in just one of the split tables for some queries and use a MERGE table for others. Repairs are more efficient. It's easier to repair the individual files that are mapped to a MERGE file than trying to repair a really big file. MyISAM and therefore MERGE tables are represented as individual files on the harddrive. You can go around the file-size limit for the operating system.

Some of the disadvantages of using MERGE tables are:

• You can only use identical MyISAM tables for a MERGE table.


• REPLACE doesn't work.


• Key reads are slower.

Also, you can't do DROP TABLE, ALTER TABLE, DELETE FROM table_name without a WHERE clause, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on any of the table that is mapped by a MERGE table that is "open". If you do this, the MERGE table may still refer to the original table and you will get unexpected results. The easiest way to get around this deficiency is to issue the FLUSH TABLES command, ensuring no MERGE tables remain "open".

Well, that should make you think twice about using MERGE tables. ISAM tables will disappear in MySQL version 5.0, so it wouldn't be a good idea to use them. Last but not least is the HEAP table type. HEAP tables use hashed indexes and are stored in memory. This makes them very fast, but if MySQL crashes you will lose all data stored in them. They are very useful for temporary tables. HEAP sounds cool but I don't think the risk justifies the performance.

The Lowdown on MySQL Table Types

Most people use MyISAM if they need speed and InnoDB for data integrity. You can use more than one or any combination of these table types in your database. Remember to asses the needs of your application before building it. Even though MyISAM is faster than InnoDB in the MySQL world, InnoDB is fast compared to any database engine. With InnoDB you get transactions, speed and integrity three features not usually used in the same sentence. Most of my customers want as much speed as they can get, but at the end of the day, good data integrity lets them sleep at night.

MySQL Question and Answers

1. How do you start and stop MySQL on Windows? - net start MySQL, net stop MySQL


2. How do you start MySQL on Linux? - /etc/init.d/mysql start


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


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


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


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


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


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


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


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


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


12. What does myisamchk do? - It compressed the MyISAM tables, which reduces their disk usage.


13. Explain advantages of InnoDB over MyISAM? - Row-level locking, transactions, foreign key constraints and crash recovery.


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


30. Explain MySQL architecture. - The front layer takes care of network connections and security authentications, the middle layer does the SQL query parsing, and then the query is handled off to the storage engine. A storage engine could be either a default one supplied with MySQL (MyISAM) or a commercial one supplied by a third-party vendor (ScaleDB, InnoDB, etc.)


31. Explain MySQL locks. - Table-level locks allow the user to lock the entire table, page-level locks allow locking of certain portions of the tables (those portions are referred to as tables), row-level locks are the most granular and allow locking of specific rows.


32. Explain multi-version concurrency control in MySQL. - Each row has two additional columns associated with it - creation time and deletion time, but instead of storing timestamps, MySQL stores version numbers.


33. What are MySQL transactions? - A set of instructions/queries that should be executed or rolled back as a single atomic unit.


34. What’s ACID? - Automicity - transactions are atomic and should be treated as one in case of rollback. Consistency - the database should be in consistent state between multiple states in transaction. Isolation - no other queries can access the data modified by a running transaction. Durability - system crashes should not lose the data.


35. Which storage engines support transactions in MySQL? - Berkeley DB and InnoDB.


36. How do you convert to a different table type? - ALTER TABLE customers TYPE = InnoDB


37. How do you index just the first four bytes of the column? - ALTER TABLE customers ADD INDEX (business_name(4))


38. What’s the difference between PRIMARY KEY and UNIQUE in MyISAM? - PRIMARY KEY cannot be null, so essentially PRIMARY KEY is equivalent to UNIQUE NOT NULL.


39. How do you prevent MySQL from caching a query? - SELECT SQL_NO_CACHE …


40. What’s the difference between query_cache_type 1 and 2? - The second one is on-demand and can be retrieved via SELECT SQL_CACHE … If you’re worried about the SQL portability to other servers, you can use SELECT /* SQL_CACHE */ id FROM … - MySQL will interpret the code inside comments, while other servers will ignore it.

How do I reclaim Space from the INNODB (Ibdata) file?

This is the major issue with the Innodb engine, the bulk delete/truncate operation will deleted the data but it is not reclaiming the space from the Innodb file. There are some techniques to reclaim the space but please make sure you must have the extra space on your servers.

1. Alter Database Set Engine = Innodb

2. Instead of truncate use the DROP statement.

3. Migrate your MySQL databases to another server MySQL server using MySQL dump utility and remove the ibdata file. Further migrate the backup database to orginal server which will makes the ibdata1 file fully de-fragmented.