How to import a mysql database from backup

WindowBreaker · 03-15-2006, 05:13 PM

I'm trying to import a mysql database backup into a new computer.

I used webmin's mysql module to create the backup file. The name of the database I backed up was 'rt3'. The name of my backup file is 'rt3.sql'. I've already moved it over to the new machine.

I searched the mysql documentation and tried doing:

Code:

mysqlimport rt3 rt3.sql

It thinks I'm trying to import the entire file into a table called rt3, and quits with an error.
The problem is that this is a backup of an entire dB, not just a single table.

I'm no mysql expert, so any suggestions would be appreciated.

mdarby · 03-15-2006, 05:18 PM

Code:

mysql -p -D rt3 < rt3.sql

WindowBreaker · 03-15-2006, 08:32 PM

I tried that - here's the output:

Code:

mysql -p -D rt3 < rt3.sql

Quote:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rt3.sql
(44,'Group',23,'WatchAsAdminCc','RT::System',1,0,0),(47,'Group',4,'Admin' at line 1

The backup is coming from a mysql v 4.1.16 (which I manually compiled), and the new system is using mysql v4.1.14 (ships with slack 10.2).

I'm assuming the version mismatch is causing the problem.

Is there any way around this - such as a platform/version independent backup process, after which I can import the entire dB into the new system?

willysr · 03-15-2006, 11:55 PM

try using PHPMyAdmin

WindowBreaker · 03-16-2006, 12:01 PM

thanks for the suggestion willysr. However, I already tried it with phpmyadmin, and the following error below is given (pretty long):

Quote:

Error
There is a chance that you may have found a bug ...[cut]

-- Host: localhost Database: rt3
-- ------------------------------------------------------
-- Server version 4.1.16-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

----END RAW----

SQL query:

rt3.sql0000644
MySQL dump 10.9 -- -- Host: localhost Database: rt3 -- ------------------------------------------------------ -- Server version 4.1.16-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

MySQL said:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rt3.sql' at line 1

willysr · 03-16-2006, 06:59 PM

it seems that your query was not accepted by 4.1.14
maybe you can post your query file?

WindowBreaker · 03-16-2006, 11:08 PM

The problem was being caused by non-identical files. I ran an md5sum on both the original and copy, and they didn't match (even though their sizes did).

[shame on me - rookie mistake]

Then I ended up using phpmyadmin to import the file into the dB without a hitch.

Thanks to everyone for your suggestions.