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

[Mondo90]

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

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.

[foobic]

You can use the option --skip-comments to suppress these, but in practice the size difference will be minimal on a typical database.

[Mondo90]

Hmm, but not only comments are there, do you know command that do not add nothing ? I just want to have clear backup.

[foobic]

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"?

[Mondo90]

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 ?

[foobic]

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?

[Mondo90]

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 ;]

[Website themes]

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

[YUPAPA]

Originally Posted by Mondo90

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.

[Mondo90]

Hmmm, can I use multi options for example, --skip-opt and --compact
?

[Website themes]

Originally Posted by Mondo90

Hmmm, can I use multi options for example, --skip-opt and --compact
?

Yes see my post above. -c is the same as --compact.