What's new in MySQL 5.1

There is not much time left until the release of MySQL 5.1. The article will review the changes and new features of this version.


1. Partitioning tables
Splitting tables is one of the important features that MySQL lacked. Although MySQL has the MERGE table type implemented, however, its functionality is quite different from partitioning tables.

Splitting tables will allow you to divide a large table into several and store them independently of each other, while maintaining logical commonality. This achieves an increase in data access speed and ease of disk management.
')
All table types support this partitioning.
MySQL 5.1 implements the following basic table partitioning types:

• Splitting range. Such a partition is suitable for tables with weakly related data, for example, a table with logs.
  

         CREATE TABLE logs (
             value VARCHAR (30) NOT NULL,
             create_date TIMESTAMP NOT NULL
         )
         PARTITION BY RANGE (YEAR (create_date)) (
             PARTITION p0 VALUES LESS THAN (2005),
             PARTITION p1 VALUES LESS THAN (2006),
             PARTITION p2 VALUES LESS THAN (2007),
             PARTITION P3 VALUES LESS THAN MAXVALUE
         );
  

  
  In this example, the data will be split into tables by the year the record was created.
• Split by list of values. This type of partitioning is very similar to the previous one. The main difference is that splitting the list of values ​​allows you to accurately specify the distribution of data for each value.
  

         CREATE TABLE employees (
             id INT NOT NULL,
             fname VARCHAR (30),
             lname VARCHAR (30),
             departament TINYINT (2),
         )
         PARTITION BY LIST (departament) (
           PARTITION management VALUES IN (1, 5, 6),
           PARTITION sales VALUES IN (2, 3),
           PARTITION technical VALUES IN (4, 7, 8)
         );
  

• Hash partitioning. In this type of partitioning, you can specify the function by which the data will be divided:
  

         CREATE TABLE logs (
             value VARCHAR (30) NOT NULL,
             create_date TIMESTAMP NOT NULL
         )
         PARTITION BY HASH (YEAR (create_date))
         PARTITIONS 4;
  

  
  

2. Line replication ( row-based replication )
During normal replication, the master slave sends the same command to the slaves each time the table is changed. In MySQL 5.1, the ability to create replication has been added, in which the wizard does not send the slave a command to change data, but writes to the log file how and in which lines the data was changed. This type of replication is the most reliable and is used in most commercial databases.

Enable line-by-line replication is possible with the command

SET GLOBAL binlog_format = 'ROW';

or in the configuration file.

3. Events
The new version adds the ability to create events. This functionality allows you to customize the execution of periodic SQL queries or procedures. For example, perform the necessary recalculation of data once a day.

 DELIMITER //
 CREATE EVENT RECALC_SUMM
 ON SCHEDULE EVERY 1 WEEK
 STARTS '2008-08-13 1:00:00'
 ON COMPLETION PRESERVE
 Do
 BEGIN
     UPDATE table1 SET sum = sum + today_amount
 END
 //

4. Ease of administration
Starting with the version of MySQL 5.1, it became possible to save server logs in tables. In previous versions, logs could be saved only in the file system, which added difficulty in processing them. General MySQL and slow logs can be written to the tables. You can configure this feature with the --log-output = TABLE parameter. When this option is selected, the general_log and slow_log tables are created in the mysql database.

In addition, the PROCESSLIST table appeared in MySQL 5.1. This table stores information about which processes MySQL is performing, identical to how they are displayed by the SHOW PROCESSLIST command.

5. XML support XPath
XPath support has been added to MySQL 5.1. Now the XML document saved to the table is available to the user as a tree. You can get any value from the tree and update only the desired node. For this, two functions were added: ExtractValue and UpdateXML. For example:

SELECT ExtractValue ('test', '/ a')

will give the result of the test.

SELECT UpdateXML ('test', '/ a', 'value')

will give the result value.

In addition, in MySQL 5.1 added:

• Support for plug-in architecture that allows you to connect and modify server components without rebooting.
• Replication cluster-cluster.
• Ability to store NDB cluster data on disk.
• The program mysqlslap, which allows to emulate the load on the MySQL server.

Read the original article at MySQL Consulting.

PS write in lichku topics on MySQL articles that you would like to read.