Move a MySQL 5.5 database to a server running MySQL 5.1 I have a backup of a MySQL database (using InnoDB) from a Windows machine that was running MySQL 5.5. The Windows machine is no longer available and now I need to import the backup into an Ubuntu box running MySQL 5.1.73. Is this possible? Any hints on how/where to copy the files?

Most of the info I find is about "upgrading" from 5.1 to 5.5 but in my case I need to go the opposite way.
I have a script to move MySQL data from one version to another as long as it is MySQL 5.x

If you look at my old post MySQL upgrade 5.0.88 to latest, I handle this situation going to a higher version. With slight modifications, I can make this process go backwards.

STEP 01 : mysqldump all databases except mysql, information_schema, performance_schema

MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQLSTMT="${SQLSTMT} FROM information_schema.schemata WHERE schema_name"
SQLSTMT="${SQLSTMT} NOT IN ('information_schema','mysql','performance_schema')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --databases ${DBLIST}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} &gt; Data.sql


STEP 02 : dump the grants as pure SQL commands

MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user&lt;&gt;''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' &gt; Grants.sql


STEP 03 load each file into a new MySQL 5.1 Instance

Login to MySQL as root and run

mysql&gt; source Data.sql
mysql&gt; source Grants.sql


Give it a Try !!!

CAVEAT #1

You cannot mysqldump everything and load it into a different version of MySQL because the mysql schema has columns in the mysql.user table specific to a version.

You cannot run mysql_upgrade to fix it because you are doing a downgrade.

I have mentioned splitting the grants from the data before


Oct 17, 2014 : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?
Apr 11, 2013 : MySQL upgrade 5.0.88 to latest (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release


CAVEAT #2

The solution I posted would be for Linux, but your database is on a Windows machine. So let me rephrase the solution with Windows in mind ...

STEP 01 : Get the name of every database in the Windows machine (except mysql, information_schema, performance_schema) and dump them. For example, if your databases as db1, db2 and db3, dump them to a text file like this:

C:\&gt; mysqldump -uroot -ppassword --routines --triggers --database db1 db2 db3 &gt; Data.sql


STEP 02 : Dump the Grants to a text file

C:\&gt; mysql -uroot -ppassword -ANe"SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),''';') FROM mysql.user WHERE user&lt;&gt;''" &gt; grant_cmds.sql
C:\&gt; mysql -uroot -ppassword -AN &lt; grant_cmds.sql &gt; Grants.sql


STEP 03 : Edit the Grants.sql

Open Grants.sql in notepad and append a semicolon to every line

STEP 04 load each file into a new MySQL 5.1 Instance

Login to MySQL as root and run

mysql&gt; source Data.sql
mysql&gt; source Grants.sql


CAVEAT #3

I have techniques for using MySQL for Windows with mysqldump along with user-defined variables in the Windows shell : MySQLdump on specific db date