How do I backup my db?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • HumanNature
    Member
    • Apr 2004
    • 41

    How do I backup my db?

    Hi, I have backed up my db for the first time using shell access and putty.

    I want to ask if someone can explain me how do I know if the backup is complete.

    To make the Backup I have written:

    mysqldump -u xxxxxxxxx -p xxxxxxxxxx > /path/to/backup.sql

    The backup starts with
    -- MySQL dump 9.10
    --
    -- Host: localhost Database: xxxxxxxxxxxxx
    -- ------------------------------------------------------
    -- Server version 4.0.1-standard
    --
    -- Table structure for table `xxxxx_access`
    --
    CREATE TABLE xxxxx_access (
    userid int(10) unsigned NOT NULL default '0',
    forumid smallint(5) unsigned NOT NULL default '0',
    accessmask smallint(5) unsigned NOT NULL default '0',
    PRIMARY KEY (userid,forumid)
    ) TYPE=MyISAM;
    --
    -- Dumping data for table `xxxxxx_access`
    --
    and ends with:

    INSERT INTO xxxxxx_word VALUES (543,'probando');
    INSERT INTO xxxxxx_word VALUES (544,'escribe');
    INSERT INTO xxxxxx_word VALUES (545,'erestonto');
    INSERT INTO xxxxxx_word VALUES (546,'firedevil');
    INSERT INTO xxxxxx_word VALUES (547,'enfado');
    INSERT INTO xxxxxx_word VALUES (548,'hgfhgfhfg');
    I don´t know why but I fell that this is wrong...

    Thanks
  • Steve Machol
    Former Customer Support Manager
    • Jul 2000
    • 154488

    #2
    That looks complete, although you really should use the --opt setting:

    mysqldump --opt -u xxxxxxxxx -p xxxxxxxxxx > /path/to/backup.sql
    Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
    Change CKEditor Colors to Match Style (for 4.1.4 and above)

    Steve Machol Photography


    Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


    Comment

    • HumanNature
      Member
      • Apr 2004
      • 41

      #3
      Originally posted by Steve Machol
      That looks complete, although you really should use the --opt setting:

      mysqldump --opt -u xxxxxxxxx -p xxxxxxxxxx > /path/to/backup.sql
      and which will be the difference?

      I also have seen that this backup create the table, insert the dates in it but don´t remove the pre-existing table. Is there anyway to to that?

      Thanks Steve

      Comment

      • Zachery
        Former vBulletin Support
        • Jul 2002
        • 59097

        #4
        OPT makes sure that the whole database isnt loaded into memory, it locks the tables while you are doing the backup, i believe it also otpmizes the table before putting it into the file.

        Comment

        • HumanNature
          Member
          • Apr 2004
          • 41

          #5
          Thanks Zachery

          Two more questions

          1) To restore de Backup .... ?????

          mysqldump --opt -u xxxxxxxx -p xxxxxxxxxx < /path/to/backup.sql

          2) And what about if I want to get a zip bakup? and to restore the zip backup?


          Thanks again

          3) Edit: Do I have to drop the tables by myself before restoring the db?


          Pd: My backup without --opt is 4,6MB and with --opt is 3,9M

          Comment

          • Steve Machol
            Former Customer Support Manager
            • Jul 2000
            • 154488

            #6
            The --opt command also adds the 'DROP IF EXIST' statement on all the tables it backs up.

            1. To restore:



            2. You can zip and unzip the backup just like any other file.

            3. Not if you use the --opt command.
            Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
            Change CKEditor Colors to Match Style (for 4.1.4 and above)

            Steve Machol Photography


            Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


            Comment

            • HumanNature
              Member
              • Apr 2004
              • 41

              #7
              Yes Steve, I know this, but I ask because if you see the manual´s intrucctions to backup the db --opt don´t appear, so I don´t Know if you backup the db with --opt, you have to restore it with --opt

              Thanks again.

              Comment

              • Steve Machol
                Former Customer Support Manager
                • Jul 2000
                • 154488

                #8
                No, you do not use the --opt for restoring. That command is for mysqldump only.
                Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
                Change CKEditor Colors to Match Style (for 4.1.4 and above)

                Steve Machol Photography


                Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


                Comment

                widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                Working...