Differences between MySQL versions

Document directory

• Differences between MySQL versions
• Differences between MySQL versions

 

MySQL is an open-source relational database management system. The developers are MySQL AB in Sweden, which was acquired by Sun and finally merged into Oracle. Because of its high performance, low cost, and high reliability, MySQL has become the most popular open-source database and is widely used in small and medium websites on the Internet. As MySQL continues to mature, it is gradually used for more large-scale websites and applications, such as Wikipedia, Google, and Facebook. M in lamp, a popular open-source software combination, is MySQL.

Download: http://mysql.proserve.nl/Downloads/MySQL-5.1/
View updates:

Http://dev.mysql.com/doc/refman/5.1/en/news-5-1-50.html

Differences between MySQL versions

<The first MySQL Community server, this is not for money!
For the second MySQL Enterprise instance, you have to pay for it, but you can call to ask questions, that is, telephone technical support.
The third MySQL cluster, which cannot be used independently, should be used on the basis of 1 or 2. Of course, it is used to balance multiple databases.
The fourth MySQL workbench is a good thing for designing databases. Does Erwin know? This is the role.

MySQL Community Server Community version should not provide official technical support

MySQL Enterprise Server MySQL Enterprise Edition Server
The software is the most reliable, secure, and updated MySQL enterprise-level server database. It provides cost-effective e-commerce, online transaction processing (OLTP), and gigabit Data Warehouse applications. It supports ACID transaction processing and provides complete commit, rollback, crash recovery, and row-Level Lock functions. Because of its ease of use, scalability, and high performance, MySQL database has become the world's most popular open-source database.

MySQL cluster with more than 2 MySQL cluster servers

MySQL workbench has two versions:
MySQL workbench community edition (also known as MySQL workbench OSS, Community edition) and MySQL workbench Standard Edition (also known as MySQL workbench se, commercial edition ). MySQL workbench OSS is an open-source community version released under the GPL certificate, while MySQL workbench se is a commercial version charged on a yearly basis. The functional differences are shown in the table below (I have to say that database/model synchronization is only available in the charged MySQL workbench se, while dbdesigner4 is the basic function, this "Successor" approach is really disgusting );

I. Differences in Common commands of different versions

Show InnoDB status \ G mysql-5.1show engines InnoDB status \ G mysql-5.5 user @ '%' about grant authorization mysql-5.5 does not contain localhost

Ii. major differences between MySQL 4.1/5.0/5.1/5.5/5.6 versions mysql-server-4.1

Added support for subqueries, UTF-8 for character sets, Rollup for group by statements, and a better encryption algorithm for MySQL. User tables, InnoDB began to support separate tablespaces.

Mysql-server-5.0

Added support for stored procedures, views, cursors, triggers, and XA transactions, and added the inforation_schema system database.

Mysql-server-5.1

Added support for event scheduler, partitioning, pluggable storage engine API, row-based replication, global-level dynamic modification of general query log and slow query log.

Important features of minor versions:

5.1.2 starts to support slow queries in microseconds. For more information about slow queries, see http://linuxguest.blog.51cto.com/195664/721042

Mysql-server-5.5

1) Change the default storage engine to InnoDB
2) improve performance and scalability

A. Increase the default thread concurrency (innodb_thread_concurrency)
B. Background input/output thread control (innodb_read_io_threads, innodb_write_io_threads)
C. Master thread input/output rate control (innodb_io_capacity)
D. Operating System memory allocation program usage control (innodb_use_sys_malloc)
E. adaptive hash index control. You can disable the adaptive hash function.
F. Insert buffering control. You can disable InnoDB's insert buffer function.
G. Improve scalability through the quick lock algorithm. InnoDB does not use a proxy (POSIX) thread, but uses native independent operations to complete mutex and read/write locks.
H. Restore group commit (restored group commit)
I. Improved recovery performance
J. Multi-buffer pool instance
K. Multiple rollback segments (multiple rollback segments). The previous InnoDB version can handle a maximum of 1023 concurrent processing operations. Now mysql5.5 can handle up to concurrent transactions,
L. asynchronous input/output inherent in the Linux system, and the mysql5.5 database system also increases the number of input/output requests in the Linux system.
M. Extended change Buffer: added Delete buffer and clear Buffer
N. improves the mutex of the log system and the mutex of the flush list.
O. Improve the purge progress. In mysql5.5, it is clear that the Operation thread is an independent thread and supports concurrency. You can use the innodb_purge_treads configuration.
P. Improve metadata lock in transaction processing. For example, if a statement in a transaction needs to lock a table, it will release the table at the end of the transaction, rather than releasing the table at the end of the statement.

3) Improve Practicality

A. semi-synchronous replication (semi-synchronous replication)
B. Copy heartbeat
C. Automatic Recovery of relay logs (automatic relay Log Recovery)
D. Replication Based on server filtering items (replication per server filtering)
E. Replication slave side data type conversions)

4) improve ease of management and efficiency

A. Create a fast index (faster index creation)
B. Efficient Data Compression (efficient data compression)
C. efficient storage for large objects and variable length columns
D. added the information_schema table, which provides specific information related to InnoDB compression and transaction lock.

5) Improve availability

A. New SQL syntax for signal/resignal
B. New table/index partition options. Mysql5.5 extends the range of table and index rang and list partitions to non-integer columns and dates, and adds the ability to partition 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 servers.

Important features of minor versions:

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 excessive memory usage when a large number of tables are opened (as mentioned here) (a third-party patch already exists)
2) Enhanced InnoDB performance. Such as splitting kernel mutex, flush operations from the main thread, multiple perge threads, and large memory optimization.
3) InnoDB deadlock information can be recorded in the error log for easy analysis
4) mysql5.6 supports delayed replication, allowing the slave and master to control a time interval to facilitate data recovery in special circumstances.
5) Enhanced Table Partitioning
6) The Row-level replication function of MySQL is enhanced to reduce the overhead of disk, memory, network, and other resources (only record fields that can be determined by rows)
7) Implement crash-safe using BINLOG
8) the replication event adopts CRC32 verification to enhance data consistency during Master/Slave replication.
9) added log_bin_basename (previously, there was no BINLOG location information in variables and it was inconvenient to monitor the database)

 

MySQL is an open-source relational database management system. The developers are MySQL AB in Sweden, which was acquired by Sun and finally merged into Oracle. Because of its high performance, low cost, and high reliability, MySQL has become the most popular open-source database and is widely used in small and medium websites on the Internet. As MySQL continues to mature, it is gradually used for more large-scale websites and applications, such as Wikipedia, Google, and Facebook. M in lamp, a popular open-source software combination, is MySQL.

Download: http://mysql.proserve.nl/Downloads/MySQL-5.1/
View updates:

Http://dev.mysql.com/doc/refman/5.1/en/news-5-1-50.html

Differences between MySQL versions

<The first MySQL Community server, this is not for money!
For the second MySQL Enterprise instance, you have to pay for it, but you can call to ask questions, that is, telephone technical support.
The third MySQL cluster, which cannot be used independently, should be used on the basis of 1 or 2. Of course, it is used to balance multiple databases.
The fourth MySQL workbench is a good thing for designing databases. Does Erwin know? This is the role.

MySQL Community Server Community version should not provide official technical support

MySQL Enterprise Server MySQL Enterprise Edition Server
The software is the most reliable, secure, and updated MySQL enterprise-level server database. It provides cost-effective e-commerce, online transaction processing (OLTP), and gigabit Data Warehouse applications. It supports ACID transaction processing and provides complete commit, rollback, crash recovery, and row-Level Lock functions. Because of its ease of use, scalability, and high performance, MySQL database has become the world's most popular open-source database.

MySQL cluster with more than 2 MySQL cluster servers

MySQL workbench has two versions:
MySQL workbench community edition (also known as MySQL workbench OSS, Community edition) and MySQL workbench Standard Edition (also known as MySQL workbench se, commercial edition ). MySQL workbench OSS is an open-source community version released under the GPL certificate, while MySQL workbench se is a commercial version charged on a yearly basis. The functional differences are shown in the table below (I have to say that database/model synchronization is only available in the charged MySQL workbench se, while dbdesigner4 is the basic function, this "Successor" approach is really disgusting );

I. Differences in Common commands of different versions

Show InnoDB status \ G mysql-5.1show engines InnoDB status \ G mysql-5.5 user @ '%' about grant authorization mysql-5.5 does not contain localhost

Ii. major differences between MySQL 4.1/5.0/5.1/5.5/5.6 versions mysql-server-4.1

Added support for subqueries, UTF-8 for character sets, Rollup for group by statements, and a better encryption algorithm for MySQL. User tables, InnoDB began to support separate tablespaces.

Mysql-server-5.0

Added support for stored procedures, views, cursors, triggers, and XA transactions, and added the inforation_schema system database.

Mysql-server-5.1

Added support for event scheduler, partitioning, pluggable storage engine API, row-based replication, global-level dynamic modification of general query log and slow query log.

Important features of minor versions:

5.1.2 starts to support slow queries in microseconds. For more information about slow queries, see http://linuxguest.blog.51cto.com/195664/721042

Mysql-server-5.5

1) Change the default storage engine to InnoDB
2) improve performance and scalability

A. Increase the default thread concurrency (innodb_thread_concurrency)
B. Background input/output thread control (innodb_read_io_threads, innodb_write_io_threads)
C. Master thread input/output rate control (innodb_io_capacity)
D. Operating System memory allocation program usage control (innodb_use_sys_malloc)
E. adaptive hash index control. You can disable the adaptive hash function.
F. Insert buffering control. You can disable InnoDB's insert buffer function.
G. Improve scalability through the quick lock algorithm. InnoDB does not use a proxy (POSIX) thread, but uses native independent operations to complete mutex and read/write locks.
H. Restore group commit (restored group commit)
I. Improved recovery performance
J. Multi-buffer pool instance
K. Multiple rollback segments (multiple rollback segments). The previous InnoDB version can handle a maximum of 1023 concurrent processing operations. Now mysql5.5 can handle up to concurrent transactions,
L. asynchronous input/output inherent in the Linux system, and the mysql5.5 database system also increases the number of input/output requests in the Linux system.
M. Extended change Buffer: added Delete buffer and clear Buffer
N. improves the mutex of the log system and the mutex of the flush list.
O. Improve the purge progress. In mysql5.5, it is clear that the Operation thread is an independent thread and supports concurrency. You can use the innodb_purge_treads configuration.
P. Improve metadata lock in transaction processing. For example, if a statement in a transaction needs to lock a table, it will release the table at the end of the transaction, rather than releasing the table at the end of the statement.

3) Improve Practicality

A. semi-synchronous replication (semi-synchronous replication)
B. Copy heartbeat
C. Automatic Recovery of relay logs (automatic relay Log Recovery)
D. Replication Based on server filtering items (replication per server filtering)
E. Replication slave side data type conversions)

4) improve ease of management and efficiency

A. Create a fast index (faster index creation)
B. Efficient Data Compression (efficient data compression)
C. efficient storage for large objects and variable length columns
D. added the information_schema table, which provides specific information related to InnoDB compression and transaction lock.

5) Improve availability

A. New SQL syntax for signal/resignal
B. New table/index partition options. Mysql5.5 extends the range of table and index rang and list partitions to non-integer columns and dates, and adds the ability to partition 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 servers.

Important features of minor versions:

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 excessive memory usage when a large number of tables are opened (as mentioned here) (a third-party patch already exists)
2) Enhanced InnoDB performance. Such as splitting kernel mutex, flush operations from the main thread, multiple perge threads, and large memory optimization.
3) InnoDB deadlock information can be recorded in the error log for easy analysis
4) mysql5.6 supports delayed replication, allowing the slave and master to control a time interval to facilitate data recovery in special circumstances.
5) Enhanced Table Partitioning
6) The Row-level replication function of MySQL is enhanced to reduce the overhead of disk, memory, network, and other resources (only record fields that can be determined by rows)
7) Implement crash-safe using BINLOG
8) the replication event adopts CRC32 verification to enhance data consistency during Master/Slave replication.
9) added log_bin_basename (previously, there was no BINLOG location information in variables and it was inconvenient to monitor the database)