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} > 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<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > Grants.sql
STEP 03 load each file into a new MySQL 5.1 Instance
Login to MySQL as root and run
mysql> source Data.sql
mysql> source Grants.sql
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 releaseThe 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:\> mysqldump -uroot -ppassword --routines --triggers --database db1 db2 db3 > Data.sql
STEP 02 : Dump the Grants to a text file
C:\> mysql -uroot -ppassword -ANe"SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),''';') FROM mysql.user WHERE user<>''" > grant_cmds.sql
C:\> mysql -uroot -ppassword -AN < grant_cmds.sql > 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> source Data.sql
mysql> source Grants.sql
I have techniques for using MySQL for Windows with mysqldump along with user-defined variables in the Windows shell : MySQLdump on specific db date
