The main differences between MySQL 4.1/5.0/5.1/5.5/5.6

Some command differences between versions:

show innodb status\G mysql-5.1
show engines innodb status\G mysql-5.5

About grant authorization mysql-5.5 user@'%' does not include localhost

-----------------------------------------------------------------------

The main differences between MySQL 4.1/5.0/5.1/5.5/5.6

1. 4.1 adds support for subqueries, adds UTF-8 to the character set, adds ROLLUP to the GROUP BY statement, and uses a better encryption algorithm for the mysql.user table.

2. 5.0 adds support for Stored procedures, Views, Cursors, Triggers, XA transactions, and adds the INFORATION_SCHEMA system database.

3. 5.1 Added Event scheduler, Partitioning, Pluggable storage engine API, Row-based replication, and support for dynamic modification of general query log and slow query log at the global level.

4. New features of 5.5

1) The default storage engine is changed to InnoDB

2) Improve performance and scalability

a. Increased the default thread concurrency (innodb_thread_concurrency)

b. Background input/output thread control (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, users can turn off the adaptive hashing function.

f. Insert Buffering control, users can turn off the insert buffering function of InnoDB.

g. Improve scalability through fast locking algorithm. InnoDB does not use proxy (POSIX) threads, but uses native independent operations to complete mutual exclusion and read-write locking.

h. Restored Group Commit

i. Improve recovery performance

j. Multiple buffer pool examples

k. Multiple Rollback Segments. The previous version of InnoDB can handle up to 1023 concurrent operations. Now MySQL 5.5 can handle up to 128K concurrent transactions.

l. The Linux system has inherent asynchronous input/output, and the MySQL 5.5 database system also increases the number of concurrent input and output requests of the Linux system.

m. Extended change buffer: added delete buffer and clear buffer

n. Improved the mutual exclusion of the log system and the mutual exclusion of the single flush list

o. Improve the progress of the purge program. In MySQL 5.5, the purge operation thread is an independent thread and supports concurrency. You can use the innodb_purge_treads configuration.

p. Improve metadata locking in transactions. For example, if a statement in a transaction needs to lock a table, the table will be released at the end of the transaction instead of at the end of the statement as before.

3) Improve practicality

a. Semi-synchronous Replication

b. Copy Heartbeat

c. Automatic Relay Log Recovery

d. Replication Per Server Filtering

e. Data Type Conversions Supported by Replication Slave Side

4) Improve manageability and efficiency

a. Faster Index Creation

b. Efficient Data Compression

c. Provide efficient storage for large objects and variable-length columns

d. Added INFORMATION_SCHEMA tables. The new tables provide detailed information about InnoDB compression and transaction locking.

5) Improve usability

a. New SQL syntax for SIGNAL/RESIGNAL

b. New table/index partitioning options. MySQL 5.5 extends table and index RANG and LIST partitioning ranges to non-integer columns and dates, and adds the ability to partition on multiple columns.

6) Improved detection and diagnosis

MySQL 5.5 introduces a new performance architecture (performancn_schema, P_S) to monitor the performance of the MySQL monitoring server while it is running.

5. New features in 5.6

1) InnoDB can now limit the problem of excessive memory usage when a large number of tables are opened (such as mentioned here) (third-party patches are available)

2)InnoDB performance enhancement. Such as splitting kernel mutex; separating flush operation from main thread; multiple perge threads; large memory optimization, etc.

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

4) MySQL 5.6 supports delayed replication, which allows the slave and master to control a time interval, facilitating data recovery in special circumstances.

5) Enhanced table partitioning function

6) MySQL row-level replication is enhanced to reduce disk, memory, network and other resource overhead (only fields that can determine the row record are recorded)

7) Binlog implements crash-safety

8) CRC32 verification is used for replication events to enhance the consistency of master/slave replication data

9) Added log_bin_basename (previously, there was no binlog location information in variables, which was inconvenient for database supervision)

Summarize

The above is the main difference between MySQL 4.1/5.0/5.1/5.5/5.6. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time!