Home / server / Questions / 250964
Accepted
Stann
Asked: 2011-03-24 10:16:34 +0800 CST

Using mysqldump from 5.1 package to backup data from 5.0 server?

  • 772

I've ran into problems trying to backup data from table City in world database from mysql 5.0 server on local network:

mysqldump --tab=/home/developer/Desktop -u developer -h 192.168.10.196 -p world City

I'm getting this error:

Not dumping tablespaces as no INFORMATION_SCHEMA.FILES table on this server

My mysqldump version is 10.13 (from Mysql server 5.1.49) While server on 192.168.10.196 has Mysql 5.0.77 installed (default mysqldump version that comes with that package is 10.11).

Does mysqldump version makes a difference? Why can't I use mysqldump?

Thanks!

mysql

2 Answers

  1. Best Answer

    Here are the tables in the information_schema for MySQL 5.1.37

    +---------------------------------------+
    | Tables_in_information_schema |
    +---------------------------------------+
    | CHARACTER_SETS |
    | COLLATIONS |
    | COLLATION_CHARACTER_SET_APPLICABILITY |
    | COLUMNS |
    | COLUMN_PRIVILEGES |
    | ENGINES |
    | EVENTS |
    | FILES |
    | GLOBAL_STATUS |
    | GLOBAL_VARIABLES |
    | KEY_COLUMN_USAGE |
    | PARTITIONS |
    | PLUGINS |
    | PROCESSLIST |
    | PROFILING |
    | REFERENTIAL_CONSTRAINTS |
    | ROUTINES |
    | SCHEMATA |
    | SCHEMA_PRIVILEGES |
    | SESSION_STATUS |
    | SESSION_VARIABLES |
    | STATISTICS |
    | TABLES |
    | TABLE_CONSTRAINTS |
    | TABLE_PRIVILEGES |
    | TRIGGERS |
    | USER_PRIVILEGES |
    | VIEWS |
    +---------------------------------------+
    28 rows in set (0.01 sec)

    Here are the tables in the information_schema for MySQL 5.0.83

    +---------------------------------------+
    | Tables_in_information_schema |
    +---------------------------------------+
    | CHARACTER_SETS |
    | COLLATIONS |
    | COLLATION_CHARACTER_SET_APPLICABILITY |
    | COLUMNS |
    | COLUMN_PRIVILEGES |
    | KEY_COLUMN_USAGE |
    | PROFILING |
    | ROUTINES |
    | SCHEMATA |
    | SCHEMA_PRIVILEGES |
    | STATISTICS |
    | TABLES |
    | TABLE_CONSTRAINTS |
    | TABLE_PRIVILEGES |
    | TRIGGERS |
    | USER_PRIVILEGES |
    | VIEWS |
    +---------------------------------------+
    17 rows in set (0.00 sec)

    Evidently, mysqldump for 5.1 presupposes that all 5.1 information_schema tables will be present. Your best bet it to install mysql 5.0 somewhere else on the box and call that binary executable mysqldump with the absolute path from where you installed MySQL 5.0, or, you could use another Linux machine that has MySQL 5.0 installed and mysqldump it remotely.

    • 1

  2. oh - I see. If I use --tab option - that actually creates 2 files: sql with raw tab-delimited data and txt with just sql schema.

    And also - sql file goes onto your computer and owned by you. While txt stays in the data directory on the server and owned by the server.

    so.....there... we have different ownerships.

    and Mysql manual advises you to simply ssh onto server and set directory that is accessible by server and you like tmp: So - this works (over ssh):

    mysqldump --tab=/tmp -p world City
    • 0