2

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.

Improve this question
3
  • Is mysql still up and running on the Windows machine ?
    – RolandoMySQLDBA
    Commented Oct 31, 2014 at 16:10
  • The Windows machine is no longer available.
    – Grodriguez
    Commented Nov 3, 2014 at 15:31
  • If you can move that folder to another Windows and fire up MySQL on it, you could then apply my answer (dba.stackexchange.com/a/81602/877)
    – RolandoMySQLDBA
    Commented Nov 3, 2014 at 15:51

1 Answer 1

Reset to default
1

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

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

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:\> 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

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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