New Features and improvements of MySQL5.5 replication/synchronization

I had the honor to attend the MySQL2010 User Conference six months ago. At the Conference, OracleSun announced the new features of MySQL5.5. There were many improvements in MySQL5.5, including the Semi-synchronous Semi-synchronousReplication introduced in GooglepatchforMySQL, A new feature for high-availability solutions. Tan junqing @ MySQL lab MyS

I had the honor to attend the MySQL2010 User Conference six months ago. Oracle/Sun announced the new features of MySQL5.5 at the Conference. This time, many improvements were made to MySQL5.5, semi-synchronous Replication introduced in Google patch for MySQL, a new feature for high availability solutions. Tan junqing @ MySQL lab MyS

I had the honor to attend the MySQL2010 User Conference six months ago. Oracle/Sun announced the new features of MySQL5.5 at the Conference. This time, many improvements were made to MySQL5.5, semi-synchronous Replication introduced in Google patch for MySQL, a new feature for high availability solutions. Tan junqing @ MySQL lab

Improvement on replication/synchronization of MySQL5.5:

• Ensure data consistency (synchronization) on the master and slave servers)
• The replication exception can be detected immediately.
• Crashed Salve can automatically restore synchronization from errors
• In ring replication, you can specify to skip an instance event.
• Automatically adapts to field type conversion in master-slave Replication

MySQL semi-synchronous replication (semi-synchronous replication)

By default, MySQL replication is asynchronous. After all update operations on the Master node are written into the Binlog, It is not ensured that all updates are copied to the Slave. Although asynchronous operations are highly efficient, there is a high risk of data synchronization or even data loss when the Master/Slave encounters problems.

MySQL5.5 introduces the semi-synchronous replication function to ensure that at least one Slave data is complete when the master node fails. In case of timeout, you can also temporarily transfer to asynchronous replication to ensure normal service usage. After a salve catches up with it, the semi-Sync Mode will continue to be switched.

Master:

Install plugin rpl_semi_sync_master SONAME 'semisync _ master. so ';

Set global rpl_semi_sync_master_enabled = 1;

Set global rpl_semi_sync_master_timeout = 1000; (1 s, default 10 s)

Slave:

Install plugin rpl_semi_sync_slave SONAME 'semisync _ slave. so ';

Set global rpl_semi_sync_slave_enabled = 1;

Replication HEARTBEAT (the user checks whether the replication is interrupted)

The new master_heartbeat_period configuration provided by MySQL5.5 can help us quickly find problems when replication stops working and network interruptions occur.

Enabling method:

Stop slave;

Change master to master_heartbeat_period = milliseconds;

Start slave;

Slave automatically resumes Synchronization

Before MySQL 5.5, the MySQL Slave instance may interrupt replication after the service is terminated abnormally, and the relay binlog may be damaged. After MySQL is started again, the replication cannot be resumed normally. In MySQL5.5, this problem is solved. MySQL can discard the corrupted and unprocessed data, obtain the source data from the master again, and then reply to the replication.

Skips a specified replication event.

In the case of multi-Master or circular replication, the server in the middle of the replication chain is abnormal.

Change master to MASTER_HOST = xxx IGNORE_SERVER_IDS = y

Skip the problematic MySQL instance.

Automatically convert the field type

MySQL supports partial field conversion under statement-based replication, but row-level errors are reported. MySQL5.5 statements and row-level replication are supported. You can also use SLAVE_TYPE_CONVERSIONS to control the conversion direction.

Related posts:

1. MySQL 5.5 Released (non-GA)
2. New Features of MySQL5.5
3. MySQL5.6 release and new features
4. Some disputes caused by MySQL5.4
5. Performance Comparison and test of MySQL5.5 and MySQL5.1

? Tan junqing published a study on MySQL performance, MySQL Cluster, and MySQL HA High Availability-MySQL laboratory, 2010.
The source and author information of an article can be reproduced at will.
Link: http://www.mysqlab.net/blog/2010/12/mysql5-5-replication-new-future/

Tags: MySQL HA, MySQL Replication, MySQL5.5