Results 1 to 11 of 11
  1. #1

    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.

  2. #2
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,849
    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. #3
    Hmm, but not only comments are there, do you know command that do not add nothing ? I just want to have clear backup.

  4. #4
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,849
    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. #5
    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. #6
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,849
    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. #7
    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. #8
    Join Date
    Nov 2006
    Location
    Karachi, Pakistan
    Posts
    1,359
    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 /dev/random; 02-10-2011 at 09:31 AM.

  9. #9
    Join Date
    May 2001
    Location
    HK
    Posts
    3,082

    *

    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. #10
    Hmmm, can I use multi options for example, --skip-opt and --compact
    ?

  11. #11
    Join Date
    Nov 2006
    Location
    Karachi, Pakistan
    Posts
    1,359
    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.

Similar Threads

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •