LinuxQuestions.org
Did you know LQ has a Linux Hardware Compatibility List?
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 08-16-2005, 03:34 AM   #1
dobriain
LQ Newbie
 
Registered: May 2002
Location: Ireland
Distribution: SuSE
Posts: 14

Rep: Reputation: 0
How do I restore a MySQL database simply


How do I restore a MySQL database simply

MySQL Backup Procedure

BACKUP

'mysqldump' will create a list of SQL statements which can recreate the database. It is best to redirect this output to a file.

linux:~ # mysqldump -uMyUser -pMyPass -A -q > dump_file.sql

-u Username
-p Password
-A All Databases (that the user -u has GRANT access to)
-q Quick

> Redirect console output to a file > dump_file.sql

RESTORE

What you have is a file of SQL Statements which can be restored as follows.

Start with the root user and redirect the input into the command from the file.

linux:~ # mysql -uroot -ppassword < dump_file.sql

-u Username
-p Password

< Redirect SQL file as input < RDF_Website.sql


Recreate the database user(s) with the GRANT options.

linux:~ # mysql -uroot -ppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 170 to server version: 4.1.10a

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all on MyDB01.* to 'MyUser'@'localhost' identified by 'MyPass' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on MyDB02.* to 'MyUser'@'localhost' identified by 'MyPass' with grant option;
Query OK, 0 rows affected (0.00 sec)

Check Grants

mysql> SHOW GRANTS for 'MyUser'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for MyUser@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'MyUser'@'localhost' IDENTIFIED BY PASSWORD '*A1361938C1D8A0E651E99019408F217F32F60078'|
| GRANT ALL PRIVILEGES ON `MyDB01`.* TO 'MyUser'@'localhost' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `MyDB02`.* TO 'MyUser'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Last edited by dobriain; 08-16-2005 at 03:36 AM.
 
Old 08-16-2005, 08:47 AM   #2
jtshaw
Senior Member
 
Registered: Nov 2000
Location: Seattle, WA USA
Distribution: Ubuntu @ Home, RHEL @ Work
Posts: 3,892
Blog Entries: 1

Rep: Reputation: 66
This looks pretty good, care to write a LinuxAnswer?
 
Old 04-19-2006, 12:05 PM   #3
mackdav
Member
 
Registered: Feb 2005
Distribution: RHEL, CentOS
Posts: 113

Rep: Reputation: 16
I have inherited a stone-aged RedHat 8.0 system running mysql 3.23.52 which cannot be upgraded for various reasons I won't go into here. However the powers that be are finally interested in backing this system up, so I have started looking into mysqldump. In the interests of recoverability, I have created a RedHat 9 system running mysql version 3.23.54 which I am now attempting to restore my dump file to.

So. To create my dump, I am doing this:

# mysqldump --opt --all-databases --user="root" -p > all-databases.dump

On the restore system, I do this:

# mysql -u root -p < all-databases.dump

...and it ticks along for a while and finally announces:

ERROR 1062 at line 6005: Duplicate entry '1' for key 1

Looking at line 6005, it says (line numbers added by me):

6004: LOCK TABLES groups WRITE;
6005: INSERT INTO groups VALUES (0,'Administrators'),(1,'Anonymous'),(2,'File Admin'),(3,'Company');

This does not appear to be a TINYINT problem; the table does not appear to be defined with a TINYINT.

I have googled around and the lack of an answer implies I'm doing something trivially incorrect.

One answer I found elswhere suggested that I replace this line

INSERT INTO groups VALUES (0,'Administrators'),(1,'Anonymous'),(2,'File Admin'),(3,'Company');

with

INSERT INTO groups VALUES ('','Administrators'),('','Anonymous'),('','File Admin'),('','Company');

This lets me import the database; however, I still have two questions:

First, when I dump the imported database back out, all my keys are shifted; ie instead of

INSERT INTO groups VALUES (0,'Administrators'),(1,'Anonymous'),(2,'File Admin'),(3,'Company');

...it says:

INSERT INTO groups VALUES (1,'Administrators'),(2,'Anonymous'),(3,'File Admin'),(4,'Company');

Is that going to be a problem if my group keys have all changed by one? Especially since no other tables have changed? (ie this, plus the server version, is the only line that is detected as a change when I diff the original dump and the dump of the restored database).

Secondly, is there some way to make mysqldump export the file in such a way that it will import back in cleanly? It seems wrong to have to mess with the dump file just to make it import again.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
I lost my urpmi database...how to restore it ? riccisit Mandriva 14 01-22-2006 03:14 AM
Writing an app that uses a mysql database without installing mysql server? QtCoder Programming 4 08-09-2004 02:43 PM
MySQL restore help, Please :) inspleak Linux - Software 5 07-12-2004 03:36 PM
MYSQL help - mysql database missing eloviyandhi Linux - Software 1 03-20-2004 09:20 PM
Had to restore database from backup - about 5 minutes lost jeremy LQ Suggestions & Feedback 0 01-21-2001 10:31 PM


All times are GMT -5. The time now is 08:09 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration