MySQL5.0 and MySQL5.5 master-slave Replication FAQ processing method

Package data from the 5.0 version of the database to version 5.5, then master the problems and solutions that are encountered when synchronizing

The following actions are performed on the 5.5 version of the data

1.mysql when performing flush privileges Refresh User Rights command to report error 1146 (42S02): Table ' mysql.servers ' doesn ' t exist such test errors

CREATE TABLE ' Servers ' (

' server_name ' char (+) is not NULL,

' Host ' char (+) is not null, ' Db ' char (+) NOT NULL,

' Username ' char (+) is not NULL,

' Password ' char (+) is not NULL,

' Port ' int (4) DEFAULT NULL,

' Socket ' char (+) DEFAULT NULL,

' Wrapper ' char (+) is not NULL,

' Owner ' char (+) is not NULL,

PRIMARY KEY (' server_name ')

) Engine=myisam DEFAULT Charset=utf8 comment= ' MySQL Foreign Servers table ';

2.unable to use slaves temporary directory/dev/shm/mysql_tmp

See if the file exists and give Mysql.mysql permission

3.mysql upgrade to 5.5 to make it compatible with data table PS in 5.0: change instance 3309 is using the Mysql5.5.25 version

#mysql-uroot-p-s/tmp/mysql-3309.sock

>repair TABLE Mysql.help_keyword;

>repair TABLE mysql.help_category;

>repair TABLE Mysql.help_topic;

>repair TABLE Mysql.proc;

>repair TABLE Mysql.procs_priv;

>repair TABLE Mysql.time_zone_name;

#/usr/local/mysql_3309/bin/mysql_upgrade-uroot-p-s/tmp/mysql-3309.sock--force

If the mysql_upgrade operation is not performed, the master-slave synchronization between version 5.0 and 5.5 will be error-

4. Modify the MY.CNF configuration file, mysql5.1 part of the parameter 5.5 is not recognized, 5.5 version of the default storage engine is InnoDB, we currently use the 5.0 storage engine is MyISAM, need to modify the configuration file

Default_storage_engine = MYISAM

#skip-locking in 5.5, changed to

Skip-external-locking

#log-long-format in 5.5, changed to

Log-short-format

#log_slow_queries have not been recognized in 5.5, changed to

Slow-query-log

#default-character-set = UTF8 has not been recognized in 5.5, changed to

Character-set-server = UTF8

5.mysql master-slave replication, often encounter errors resulting in slave-side replication interrupt, this time generally requires manual intervention, skipping errors to continue

There are two ways of skipping errors:

Vi/etc/my.cnf

[Mysqld]

#slave-skip-errors=1062,1053,1146 #跳过指定error No type error

#slave-skip-errors=all #跳过所有错误

This article from "Banging blog" blog, declined reprint!

MySQL5.0 and MySQL5.5 master-slave Replication FAQ processing method