LinuxQuestions.org
Go Job Hunting at the LQ Job Marketplace
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices

Reply
 
Search this Thread
Old 09-06-2005, 04:49 AM   #1
Swakoo
Member
 
Registered: Apr 2005
Distribution: Red Hat / Fedora / CentOS
Posts: 508

Rep: Reputation: 30
MySQL :: Restoring Dump


I am running 2 test servers, both RHEL4 with Nanhant 1
The SQL server on it is 4.1.10a

I used the mysqldump on the actual server, got the dump, then did a scp to the other server. I tried to restore the database on the latter server, but upon running the command, this prompts out

Quote:
[root@backupdb tmp]# mysqldump -uroot -p articles < articles2.dump
-- MySQL dump 10.9
--
-- Host: localhost Database: articles
-- ------------------------------------------------------
-- Server version 4.1.10a

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Database (empty) is already created. I tried to create the first table in it, the dump restoring ran furthur (ran past the "Drop Table if exist") and after that it stops again

what could be the reason?

please advice
 
Old 09-06-2005, 01:20 PM   #2
XavierP
Moderator
 
Registered: Nov 2002
Location: Kent, England
Distribution: Lubuntu
Posts: 19,174
Blog Entries: 4

Rep: Reputation: 428Reputation: 428Reputation: 428Reputation: 428Reputation: 428
Moved: This thread is more suitable in Linux-General and has been moved accordingly to help your thread/question get the exposure it deserves.
 
Old 09-07-2005, 06:20 AM   #3
cardy
Member
 
Registered: Jan 2005
Location: Shropshire, England, UK
Distribution: RedHat, Fedora, CentOS..........
Posts: 121

Rep: Reputation: 19
It has been a while since i did this but looking at what you have put the command your using to import is wrong.

From what i remember you need to use mysqldump to export the database as you have


scp the file to the new machine

then just use the mysql client (i.e. mysql not mysqldump) to re-import the data.

mysqldump -uroot -p articles < articles2.dump

should be

mysql -uroot -p articles < articles2.dump

the mysqldump command dumps the database as SQL, using the normal mysqlclient should then apply all the SQL statements in the new (empty) database and recreate the database.
 
Old 09-07-2005, 08:13 AM   #4
jayemef
Member
 
Registered: Aug 2005
Location: Juniata College, PA
Distribution: Ubuntu, Slackware
Posts: 67

Rep: Reputation: 15
To export a database into SQL, use
Code:
$ mysqldump -uroot -p db_name > ~/dbbackup.sql
Then, to import it back in (assuming the database is already created), use:
Code:
$ mysql -uroot -p -Ddb_name < dbbackup.sql
 
Old 09-07-2005, 10:25 PM   #5
Swakoo
Member
 
Registered: Apr 2005
Distribution: Red Hat / Fedora / CentOS
Posts: 508

Original Poster
Rep: Reputation: 30
ah... gosh.. silly me!

Now it works

3 questions:

Using --opt, it locks table etc.. whats the purpose of locking table? will it unlock on restore?

If I use mysqldump on database that is being written to, will it update itself, or would I risk having broken relation?

I used --opt, it adds "drop table if exist" to my file. (Without --opt it does too actually.. hmm)
Is there a way to dump it such that it creates the database too? So when I restore, I don't need to create the database, rather I just dump the file to mysql and it will create from the database downwards



thanks guys!
 
Old 09-08-2005, 08:21 AM   #6
cardy
Member
 
Registered: Jan 2005
Location: Shropshire, England, UK
Distribution: RedHat, Fedora, CentOS..........
Posts: 121

Rep: Reputation: 19
Locking the table simply means that whilst the table is being dumped it is locked so that users logged into the database at the time can not make changes to the table whilst its being dumped. This is done to give consistency of data and the tables are automatically unlocked after the dump is complete.

adding the command --databases or --all-databases will cause the create database statements to be included. see man mysqldump which shows you the options available.
 
Old 09-09-2005, 01:27 AM   #7
Swakoo
Member
 
Registered: Apr 2005
Distribution: Red Hat / Fedora / CentOS
Posts: 508

Original Poster
Rep: Reputation: 30
Quote:
Originally posted by cardy
Locking the table simply means that whilst the table is being dumped it is locked so that users logged into the database at the time can not make changes to the table whilst its being dumped. This is done to give consistency of data and the tables are automatically unlocked after the dump is complete.

adding the command --databases or --all-databases will cause the create database statements to be included. see man mysqldump which shows you the options available.
Yup I just realised that. We did a test to cron the job, to do a mysqldump with gzip, then scp over to another server to store it.

Now its time to bring it to the datacentre and test with the actual data.

Whats the best way to ensure the output usually from scp gets thrown away and not stored.. or best still .. write to a file.

I got a darn simple script running.. you guys got any tips?
 
  


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 On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Restoring MySQL backups with mysql-administrator pnellesen Programming 0 04-25-2005 09:53 AM
Mysql with 10.2 dump barrythai Mandriva 2 04-11-2005 07:18 PM
Help restoring file permissions from a Dump bminish Linux - Newbie 3 12-12-2004 01:34 PM
How do I get a dump file (.sql) into MySql? TreeDragon60 Linux - Software 4 03-21-2004 05:19 AM
restoring mysql databases? and mysql permissions... armegeden Linux - Software 0 03-13-2003 11:04 AM


All times are GMT -5. The time now is 02:52 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