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.
Best Answer
You use mysqldump.
Step 1. Dump your original 32 bit database. mysqldump my32bitdb [options...] > mydb.sql
Step 2. Copy the file mydb.sql to your new server
Step 3. Load the 32 bit database mysql -u root -p my64bitdb < C:\path\to\mydb.sql
With 10GB of data, it shouldn't take much more than ~ 1hr.
For reference, see here and here.