LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
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 02-10-2006, 04:21 PM   #1
johnMG
Member
 
Registered: Jul 2003
Location: CT, USA
Distribution: Debian Sarge (server), Etch (work/home)
Posts: 601

Rep: Reputation: 31
MySQL, dumping, restoring, the "mysql" db


When dumping a db, I see that some folks use
Code:
mysqldump -u username -p -l enchilada_db > whole_enchilada.sql
while others use that "--opt" option:
Code:
mysqldump -u username -p --opt enchilada_db > whole_enchilada.sql
I see that --opt actually is a shortcut for the following options:
Code:
--add-drop-table
--add-locks
--create-options
--extended-insert
--lock-tables
--quick
1. Not sure the purpose of --add-locks... What's the difference between that and -l (--lock-tables)?

2. What's the point of --create-options (aka --all)? I'm just going from one MySQL install to another. (v4.0 to v4.x on another machine).

3. The --quick option would seem to slow down the dump job, rather than speed it up... The docs say that using that option tells mysqldump to *not* use buffering, so if it shoots out text to stdout after each sql command, wouldn't that take *longer* than if we'd used buffering?

Also, what I'm really concerned most about is, do I dump the db named "mysql" along with the others? If so, then when I'm restoring that db into the mysql install on the other computer, I need to DROP that database first? (Hm. Does the --add-drop-table take care of this for me?)

Finally, is there a rule-of-thumb to give me an idea how large my mysqldump file is going to be? My db has a good amount of binary data in it.

So far, I'm putting my notes on using mysql here: http://www.simisen.com/jmg/mysql.html
 
Old 02-11-2006, 12:23 AM   #2
leandean
Member
 
Registered: Oct 2005
Location: Burley, WA
Distribution: Sabayon
Posts: 276

Rep: Reputation: Disabled
I don't worry about all the option gobbleydegook nor does the .sql extension mean anything to MySQL. I usually just name the db 'something descriptive and easy to remember'.txt. mysqlimport strips the extension. I copy as opposed to dumping the mysql db to the new machine as that's where all the user and db grant info is. Then 'flush privileges'.

The dump size will be about the same size as the original db. You can pipe it to gzip if size is an issue.
 
Old 02-12-2006, 01:20 PM   #3
johnMG
Member
 
Registered: Jul 2003
Location: CT, USA
Distribution: Debian Sarge (server), Etch (work/home)
Posts: 601

Original Poster
Rep: Reputation: 31
> I usually just name the db 'something descriptive and easy to remember'.txt.

Right. I see that the output is just the sql commands that would re-create the db(s).

Regarding "flush privileges", I see that that's only required when you make changes
to the users table manually, i.e. when you don't use the grant command instead.

Regarding the dump size, I was guessing that, since the mysqldump command gives
you text, any binary data in the db will have to be encoded, and so will take up
more room.

Any more info anyone can offer about backing-up/restoring the mysql table is most
appreciated.
 
Old 02-13-2006, 04:36 PM   #4
johnMG
Member
 
Registered: Jul 2003
Location: CT, USA
Distribution: Debian Sarge (server), Etch (work/home)
Posts: 601

Original Poster
Rep: Reputation: 31
Ok, regarding the numbered questions I asked above, it looks like:

1. Not any difference worth worrying about.

2. I think this is used when you're dumping from a MySQL db to a MySQL db.

3. There may be a difference between MySQL buffering and stdout buffering. Dunno. Either way, it's not a big deal.

Regarding the db named mysql, I think I can dump and restore it just like any other db.

It also looks like "--opt" is pretty standard usage, and should be used when moving data between MySQL servers.
 
  


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
Buzilla issue: "Can't connect to local MySQL server through socket '/tmp/mysql.sock'" vitopn Linux - General 3 05-21-2007 11:13 AM
MySQL/PHP/Horde "Failed to write session data" Big Money Linux - Software 0 11-23-2004 02:33 PM
"my mysql sever is nopt accesible through other computers on network" pathak Linux - Networking 3 10-07-2004 02:51 AM
Php or Mysql upgrade on Fedora Core 2 "Uptodate" Program geninblaze Fedora 1 07-12-2004 10:56 PM
MySQL setup stops at command "./configure --prefix=/usr/local/mysql" k41184 Linux - Software 1 05-20-2004 03:44 PM


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