1
tedsmith
How to restore .sql backup files?
  • 2005/4/21 13:02

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


Luckily, last week, I made a backup of my MySQL database using 'XOOPS Backup' for our internal Intranet. I now have a single .sql file with the entire content of my database in it. I need to restore it.

Since the backup we've had a server wipeout and the backup of the server did not (it seems) backup the MySQL folder properly, and thus the sql database. All now relies on my own backup that I made 'just in case'!!

Can anyone tell me how I go about restoring a *.sql file? Can I do it using phpMyAdmin (once the server is back online)? Any other ways?

2
ackbarr
Re: How to restore .sql backup files?

phpmyadmin is probably the easiest option (use the "SQL" tab), and should work with all but the largest MySQL databases. You can also do this from a server shell account using the following command:
mysql -[username] -[dbname] < [backup_script_path]

Of course, this assumes that you have the ability to connect to a shell, and that the sql backup script is on the server.

Your last resort is to send the host the sql script and direct them to install it for you if neither of the first two alternatives are workable.

3
LazyBadger
Re: How to restore .sql backup files?

Never used phpMyAdmin, thus - can say only about command-line possibilities
You have to have shell-access to server to do it...
If you sql-file have CREATE DATABASE statement, you can ignore p.1

If server has DBA admin, you can ignore p.2 also
0. See at mainfile.php for database name, db-user and db-pass. Remember it
1. >mysqladmin create
2.
>mysql -u -p
>GRANT ALL PRIVILEGES ON database_name.* TO db_username@localhost IDENTIFIED BY 'password';
>\q
3. >mysql -u -p < file.sql

4
LazyBadger
Re: How to restore .sql backup files?

Quote:

mysql -u [username] -p [dbname] < [backup_script_path]

Work only if database eas created and privileges to user was granted

5
tedsmith
Re: How to restore .sql backup files?
  • 2005/4/22 12:39

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


I've had a nightmare of a 24 hours but, at last, got MySQL server up and running. It's not the latest version but itr is the version I was using before (and at the time of the backup) i.e. ver 4.0.20. Anyway, it's up and running and I can access it using phpMyAdmin.

However, when I got to the SQL section of my database and select my backup file to run it says that 'The file cannot be read'. Remeber that I used the XOOPS Backup module so the format must be OK. I notice that it states a max file size of 2,048 KB. My backup file is only 1.5Mb (1,500Kb) so I am assuming it's not that?

Does anyone know what else could be the problem? I'm very close now to getting things working again (IIS, php and MySQL all up and running again) and this is the final herdle. I'd really apreciate some help. If I'm honest, the MySQL command line terrifies me so I'd rather avoid that if I can.

Thanks

Ted

6
jdseymour
Re: How to restore .sql backup files?

The MySQL command line is by far the better and more powerful way to restore a backup. phpmyadmin I sometimes have problems with, but my SQL file is almost 8MB, so I get timeout problems.

If you have another site that is working, export an sql from there and compare syntax and see if you see any problems that could cause the file not being read.

7
romu
Re: How to restore .sql backup files?
  • 2005/4/22 13:45

  • romu

  • Theme Designer

  • Posts: 22

  • Since: 2002/12/5


Here is a complete solution.

Going further with our new host and new restrictions I now use win32 backup solution : Backup Watcher with BigDump restore...

8
tedsmith
Re: How to restore .sql backup files?
  • 2005/4/22 17:11

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


OK, I'm not having much success here and getting increasingly worried.

phpMyAdmin is failing to restore it, full stop. It just says 'Can not read file'. Whether it could be an IIS or Windows Server issues I don't know? I've tried restoring the backups that were created using the 'Xoops Backup' module, and also the native phpMyAdmin backups. Neither work. Both say 'Can not read file'.

So I had a go at the command line using what Lazbadger suggested :

He wrote :
mysql -<db_user> -<database_name> < file.sql


So I wrote the following at the c:\mysql DOS shell :

mysql -u root -p jurrasic5 cms Backup_05_04_05_.sql


where root is the user, jurrasic5 the password, cms the name of the database and Backup_05_04_05_.sql the name of the backup file. All it did was display the help pages to me. Don't worry by the way - we are on an internal intranet that is not connected to the outside world or even another office!

So I tried the same from the c:\mysql\bin folder. Same problem. So I copied the backup file into the bin folder and tried again. Same problem.

In other words, it's not working! Where should my backup file be located? I assume the use of '<' tells MySQL to do a backup? If not, how does it know what I am asking it to do?

Lastly, here is a snippet of my backup file from the top. Does it look normal and OK? I can e-mail the whole thing to someone if they want (in case the problem is further down)

# Backup for MySQL
#
# --------------------------------------------------------

#
# Table structure for table `xoops_avatar`
#

CREATE TABLE xoops_avatar (
  
avatar_id mediumint(8unsigned NOT NULL auto_increment,
  
avatar_file varchar(30NOT NULL default '',
  
avatar_name varchar(100NOT NULL default '',
  
avatar_mimetype varchar(30NOT NULL default '',
  
avatar_created int(10NOT NULL default '0',
  
avatar_display tinyint(1unsigned NOT NULL default '0',
  
avatar_weight smallint(5unsigned NOT NULL default '0',
  
avatar_type char(1NOT NULL default '',
  
PRIMARY KEY  (avatar_id),
  
KEY avatar_type (avatar_type,avatar_display)
TYPE=MyISAM;

#
# Dumping data for table `xoops_avatar`
#

INSERT INTO xoops_avatar VALUES (4'savt411cb177b93e5.jpeg''Han Solo''image/pjpeg'109239947910'S');
INSERT INTO xoops_avatar VALUES (2'savt411cae8408fb4.jpeg''Wolfy''image/pjpeg'109239872410'S');
INSERT INTO xoops_avatar VALUES (3'savt411cae9d681d2.jpeg''Funny_Birds''image/pjpeg'109239874910'S');
INSERT INTO xoops_avatar VALUES (5'savt411cb186a5610.jpeg''Neo1''image/pjpeg'109239949410'S');
INSERT INTO xoops_avatar VALUES (6'savt411cb19f27394.gif''Mr Smith''image/gif'109239951910'S');

And 
on and on and on....


I would REALLY appreciate your help guys. If this backup cannot be restored we've lost a years worth of forum postings, news articles, the lot!

Thanks

Ted

9
jdseymour
Re: How to restore .sql backup files?

The backup is called from the home directory, not the root directory. In other words on my server I have /home/username/public_html, XOOPS is in the public_html directory my backup is in /home/username. One level up from public_html.

Not sure how it would be on windows, but maybe it helps you.

Also if your tables are already created you will get an error and the CLI will exit because it cannot create tables that already exist.

Find the backup first, apparently the CLI cannot find it with your error, then we can work from there. The backup will work it just sometimes takes time to find the proper method.

10
Dave_L
Re: How to restore .sql backup files?
  • 2005/4/22 18:33

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


What happens if you type:

mysql --version

Login

Who's Online

391 user(s) are online (302 user(s) are browsing Support Forums)


Members: 0


Guests: 391


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits