Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I gave two machines which run on two entire different versions of MySQL server. I want to do the replication from 5.5.20 to 5.0 server. Will that be possible?

Because I remember when I transferred data from 5.0 to 5.5.20, I had to run the command upgrade table to work on on 5.5.20?

Please suggest what the best possible way, if replication is not possible.

share|improve this question

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
    
Thanks a lot, that was very explanatory! –  vinny Feb 8 '12 at 22:03
    
I'll flag those Q's for here –  gbn Feb 9 '12 at 10:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.