It is impossible. The reason?
The binary logs are incompatible.
To be more specific, the binary logs are not backwards compatible.
Here is why:
An empty binary in ...
- MySQL 5.0 and back is 98
- MySQL 5.1 is 106
- MySQL 5.5 is 107
I have made reference to this subtle difference in ServerFault
You can setup replication from an older MySQL version to a newer MySQL version, not the other way around.
If you ever need data from MySQL 5.5 ported back to an older version, your only recourse to mysqldump all databases EXCEPT the mysql schema.
DBLIST=`mysql -u... -p... -AN -e"select group_concat(schema_name separator ' ') from information_schema.schemata where schema_name not in ('information_schema','mysql')"`
MYSQLDUMP_OPTIONS="--add-drop-database"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers"
mysqldump -u... -p... ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > MySQLData.sql
You can extract the grants as SQL using pt-show-grants. Here is my personal emulation if it:
mysql -u... -p... --AN -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -u... -p... --skip-column-names -AN | sed 's/$/;/g' > MySQLUserGrants.sql
echo "flush privileges;" >> MySQLUserGrants.sql
The reason you have to dump the grants as SQL is the fact that the columns in the mysql.user table changes upon upgrade.
When you do desc mysql.user;
or select count(1) from information_schema.columns where table_schema='mysql' and table_name='user';
you get the following:
- 37 columns in MySQL 5.0
- 39 columns in MySQL 5.1
- 42 columns in MySQL 5.5
All the mysql upgrade does is morph mysql.user to accommodate new permissions. Performing the dump of the grants yields SQL that is independent of the mysql.user layout.
Once you have made these two dump files (MySQLData.sql
and MySQLUserGrants.sql
), simply load them into a new installation of an older MySQL version.