hosted by liquidweb


Go Back   Web Hosting Talk : Web Hosting Main Forums : Managed Hosting and Services : Mysqldump problem via SSH.
Reply

Forum Jump

Mysqldump problem via SSH.

Reply Post New Thread In Managed Hosting and Services Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old 02-10-2011, 05:31 AM
Mondo90 Mondo90 is offline
WHT Addict
 
Join Date: Feb 2011
Posts: 106
Unhappy

Mysqldump problem via SSH.


Hello, I have problem with mysqldump via SSH, I made backups using

Code:
mysqldump --add-drop-table -u dbusername -p dbname > backup.sql
// and
mysqldump --opt -u dbusername -p dbname > backup.sql
// and
mysqldump -u dbusername -p dbname > backup.sql
and always in my backups I can see a lot of unnecessary text like a ---- Dumping data for table `dle_banned`--, and other.


Please tell me what kind of commands I should use to make CLEAR copy of database without unnecessary text?


Thanks for help.



Sponsored Links
  #2  
Old 02-10-2011, 06:08 AM
foobic foobic is online now
Community Liaison 2.0
 
Join Date: Feb 2005
Location: Australia
Posts: 5,794
You can use the option --skip-comments to suppress these, but in practice the size difference will be minimal on a typical database.

__________________
Chris

"Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  #3  
Old 02-10-2011, 06:15 AM
Mondo90 Mondo90 is offline
WHT Addict
 
Join Date: Feb 2011
Posts: 106
Hmm, but not only comments are there, do you know command that do not add nothing ? I just want to have clear backup.

Sponsored Links
  #4  
Old 02-10-2011, 07:42 AM
foobic foobic is online now
Community Liaison 2.0
 
Join Date: Feb 2005
Location: Australia
Posts: 5,794
Pretty much anything that isn't a comment is database structure or data - without it you won't have a backup at all. What exactly do you want to do with this "clear backup"?

__________________
Chris

"Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  #5  
Old 02-10-2011, 07:50 AM
Mondo90 Mondo90 is offline
WHT Addict
 
Join Date: Feb 2011
Posts: 106
Take a look I always make backups by my cms , but now I can't so I using mysqldump and it add to my dbbackup garbage like a :
1.)-- Dumping data for table 'xxx'
2.)-- Table structure for table `dle_banners`
3.)
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;

and more important changing fragment from cms-backup

INSERT INTO `dle_banned` VALUES
(11, 8172, 'Reklama', '0', 0, ''),
(15, 14561, 'Ban for spam', '0', 0, ''),
itd

to this:

INSERT INTO `dle_banned` VALUES (11,8172,'Reklama','0',0,'');
INSERT INTO `dle_banned` VALUES (15,14561,'Ban for spam','0',0,'');
INSERT INTO `dle_banned` VALUES (22,15143,'Ban for: multi-account ','0',0,'');

So do you know any way to make backup without changes ?

  #6  
Old 02-10-2011, 07:59 AM
foobic foobic is online now
Community Liaison 2.0
 
Join Date: Feb 2005
Location: Australia
Posts: 5,794
1. and 2. are comments - they should disappear with --skip-comments. For the rest, character sets and table structure are important - with incorrect character set you'll get garbled text and without table structure you'll have nothing to insert data into.

Again, why does this matter to you?

__________________
Chris

"Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  #7  
Old 02-10-2011, 08:29 AM
Mondo90 Mondo90 is offline
WHT Addict
 
Join Date: Feb 2011
Posts: 106
I just worry abut mysql speed and working with unnecessary text.

I know, maybe I am worrying to much about deatail but my copy from cms looks better the it ;]

  #8  
Old 02-10-2011, 09:23 AM
Website themes Website themes is offline
Web Hosting Master
 
Join Date: Nov 2006
Location: Karachi, Pakistan
Posts: 1,195
You want a smaller backup then try this command:

mysqldump -p password -u username -ce --skip-comments dbname > dbbackup.sql

Also if you skip 'password' it will prompt you for one. This prevents the password from being saved in your bash history (i.e. it is more secure):

mysqldump -p -u username -ce --skip-comments dbname > dbbackup.sql


Last edited by Website themes; 02-10-2011 at 09:31 AM.
  #9  
Old 02-10-2011, 09:30 AM
YUPAPA YUPAPA is offline
Quant Trader
 
Join Date: May 2001
Location: HK
Posts: 2,998
*

Quote:
Originally Posted by Mondo90 View Post
and more important changing fragment from cms-backup

INSERT INTO `dle_banned` VALUES
(11, 8172, 'Reklama', '0', 0, ''),
(15, 14561, 'Ban for spam', '0', 0, ''),
itd

to this:

INSERT INTO `dle_banned` VALUES (11,8172,'Reklama','0',0,'');
INSERT INTO `dle_banned` VALUES (15,14561,'Ban for spam','0',0,'');
INSERT INTO `dle_banned` VALUES (22,15143,'Ban for: multi-account ','0',0,'');
This is a lot slower, the first one is optimized.

  #10  
Old 02-10-2011, 10:02 AM
Mondo90 Mondo90 is offline
WHT Addict
 
Join Date: Feb 2011
Posts: 106
Hmmm, can I use multi options for example, --skip-opt and --compact
?

  #11  
Old 02-11-2011, 11:57 AM
Website themes Website themes is offline
Web Hosting Master
 
Join Date: Nov 2006
Location: Karachi, Pakistan
Posts: 1,195
Quote:
Originally Posted by Mondo90 View Post
Hmmm, can I use multi options for example, --skip-opt and --compact
?
Yes see my post above. -c is the same as --compact.

Reply

Similar Threads
Thread Thread Starter Forum Replies Last Post
mysqldump (Backup using ssh) not completed, & please advice my.cnf basketmen Hosting Security and Technology 2 11-13-2010 06:23 AM
mysqldump problem nomankhn Hosting Security and Technology 7 05-06-2010 08:23 AM
mysqldump problem xserverx Hosting Security and Technology 13 09-06-2009 08:41 AM
mysqldump in ssh apacheMan Hosting Security and Technology 4 01-27-2008 03:45 PM
ssh problem snakey Hosting Security and Technology 7 08-31-2004 07:23 PM

Related posts from TheWhir.com
Title Type Date Posted
Microsoft Exchange Online Outage of Over 8 Hours Frustrates Customers Web Hosting News 2014-06-25 11:23:55
Google Releases Hosted Database Service Cloud SQL to General Availability Web Hosting News 2014-02-12 13:46:02
Lengthy Outages for Hacker News, FastHosts Web Hosting News 2014-01-06 09:56:36
Microsoft Azure Swap Deployment Feature Restored After Global Outage Web Hosting News 2013-10-31 11:16:53
Outbound Spam Causing Sleepless Nights? Blog 2013-05-13 09:52:21


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

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

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
Advertisement:
Web Hosting News:



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?