New updates in MySQL 5.1 applications, such as WordPress, Joomla, and other Content Management Systems, are requiring developers to move away from MySQL 4.1. With the move away from MySQL 4.1, it is best to have an understanding of what 5.1 offers you as a developer. Several new features of MySQL 5.1 provide a stronger and more secure database compared to MySQL 4.1. The following sections go over several of the new features provided to you in MySQL 5.1 and some other things to consider when upgrading to MySQL5.1.
MySQL 5 is considered more reliable than MySQL 4 and is compatible with the majority of third-party applications currently in use. When using MySQL 5, older database utilities and scripts designed to run on MySQL 4 may not work correctly after you have upgraded to MySQL 5. Our team highly recommends that after you complete your upgrade to MySQL 5, you immediately review your entire site and all functionality carefully after the upgrade is complete. For developers who have written scripts designed to run on MySQL, please review your code carefully before upgrading to anticipate any issues you may face with MySQL 5.
Please be aware that the majority of problems faced during the MySQL database upgrade is caused by the collation, charset encoding, and syntax of the database. We recommend that you refer to your software manual (Drupal/Wordpress etc.) since they typically have the suggested steps to resolve these issues before they happen.
Here are some of the new features in MySQL5.1:
Partitioning was an innovation in MySQL 5.1 that allows better control over how tables are physically stored. With partitioning, tables can be distributed across a filesystem and into multiple partitions.
MySQL 5.1 introduced a new plugin API. This API enabled components to be loaded and unloaded while the server is running. The plugin API uses the INSTALL PLUGIN and UNINSTALL PLUGIN statements allowing it to be a reliable replacement for the older user-defined function interface.
With the upgrade to MySQL 5.1.4, the community was introduced to MySQL slap. Mysqlslap is a load emulator that runs like any of the other MySQL binaries but has been designed to emulate the load on a server, and provide feedback on the timing of each stage.
MySQL 5.1 provided new backup changes allowing users to use MySQL dump. Mysqldump allows the dumping of tablespaces.
One of the more exciting additions in MySQL 5.1 is the addition of event scheduler. Event scheduler allows you to create and run SQL statements at a particular time or other set intervals.
MySQL 5.1 provided some new tables allowing better metadata to be retrieved. These tables included ENGINES, FILES, EVENTS, PARTITIONS, PLUGINS, and PROCESSLIST. This useful information can be queried, and searched quickly inside the database.
MySQL 5.1 also added a number of XML functions. The ExtractValue() function provides XML information according to an XPath expression. UpdateXML() allows you to find and replace some of the XML information.