Share the differences between the major versions of MySQL

Because the version of MySQL is more and is acquired by Oracle Company, all a lot of friends are not very clear about the difference of each version, here is a simple introduction, easy to need friend one, each version of the common command difference

Show InnoDB STATUSG mysql-5.1

Show Engines InnoDB STATUSG mysql-5.5

The user@ '% ' on grant authorization mysql-5.5 does not contain localhost

Second, MySQL 4.1/5.0/5.1/5.5/5.6 the main differences between the versions

mysql-server-4.1

Increased support for subqueries, character set addition Utf-8,group by statement added Rollup,mysql.user table with better encryption algorithm, INNODB began to support a separate tablespace.

mysql-server-5.0

Increased support for stored procedures, views, Cursors, triggers, XA transactions, and increased Inforation_schema system databases.

mysql-server-5.1

Added event scheduler,partitioning,pluggable storage engine API, row-based replication, global level dynamically modifying general query log and slow Support for query log.

Important features of the iteration:

5.1.2 began to support microsecond-level slow queries. For more information about slow queries please refer to http://linuxguest.blog.51cto.com/195664/721042

mysql-server-5.5

1) The default storage engine is changed to InnoDB

2 Improve performance and scalability

A. Increased default thread concurrency (innodb_thread_concurrency)

B. Background input/Output line program control system (innodb_read_io_threads, Innodb_write_io_threads)

C. Main thread input/output rate control (innodb_io_capacity)

D. Operating system memory allocation Program usage control (INNODB_USE_SYS_MALLOC)

E. Adaptive hash Index (hash index) control, the user can turn off the adaptive hashing function.

F. Insert buffering (inserts buffering) control, the user can turn off the InnoDB insertion buffering function.

G. Enhanced scalability through a fast locking algorithm, INNODB does not use proxy (POSIX) threads, but instead uses native independent operations to complete mutexes and read-write locks.

H. Recovery Group submission (restored group Commit)

I. Improving recovery performance

J. Multi-buffer pool instance

K. Multiple rollback segments (multiple Rollback segments), the previous InnoDB version can handle up to 1023 concurrent processing operations, now mysql5.5 can handle up to 128K of concurrent things,

L. The inherent asynchronous input/output of the Linux system, the mysql5.5 database system also increases the number of concurrent input and output requests from the Linux system.

M. Extended change buffering: Added delete buffer and clear buffer

N. Improved mutual exclusion and separate refresh (Flush) list mutexes for the log system

O. Improve the progress of the cleaner, clear in mysql5.5 that the operation thread is a separate thread and supports concurrency, and can use the Innodb_purge_treads configuration.

P. Improve metadata locking in transaction processing. For example, one statement in a thing needs to lock a table, releasing the table at the end of the thing, rather than releasing the table as it was before the end of the statement.

3) Improve practicality

A. Semi-synchronous replication (semi-synchronous Replication)

B. Replication Heartbeat

C. Relay Log AutoRecover (Automatic Relay log Recovery)

D. Replication based on server filtering (Replication per filtering)

E. Data type conversions supported from server replication (Replication Slave Side data types conversions)

4. Improve manageability and efficiency

A. Establishing a quick index (faster index creation)

B. Efficient data compression (efficient-Compression)

C. Providing efficient storage for large objects and variable-length columns

D. The INFORMATION_SCHEMA table has been added, and the new table provides specific information about INNODB compression and transaction locking.

5) Improve Usability

A. New SQL syntax for signal/resignal

B. New Table/Index partitioning options. MySQL5.5 extends the table and index rang and list partition ranges to non integer series and dates, and increases the ability to partition on multiple columns.

6) Improve detection and diagnosis

MYSQL5.5 introduces a new performance architecture (performancn_shema,p_s) to monitor the performance of MySQL monitoring Server runtime.

Important features of the iteration:

PERCONA-SERVER-5.5.18.23 Support Group Commit reference: http://www.orczhou.com/index.php/2011/12/time-to-group-commit-2/

mysql-server-5.6

1) InnoDB can now limit the large number of tables open when the memory footprint problem (such as mentioned here) (third party already has a patch)

2) InnoDB performance enhancement. such as split kernel Mutex;flush operation from main thread separation; multiple perge threads; large memory optimization, etc.

3 InnoDB deadlock information can be recorded to the error log for easy analysis

4) MySQL5.6 Support delay replication, can let slave and master control a time interval, convenient special circumstances of data recovery.

5) Table Partitioning enhancements

6 MySQL row-level replication enhancements, you can reduce the disk, memory, network and other resource costs (only record can determine the field of Row Records)

7) Binlog realize Crash-safe

8 Replication events using CRC32 checksum, enhanced Master/slave replication data consistency

9) Add Log_bin_basename (previously variables without binlog location information, the monitoring of the database is very inconvenient)