Announcement

Collapse
No announcement yet.

Mysql Database Backup Question

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mysql Database Backup Question

    Hi
    i need to backup my MYSQL DB

    i found the following at http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
    but i dont realy understand it.

    Example: mysqldump --add-drop-table -u dbuser -pPW DBNAME > /tmp/examp-back.sql


    1. what is "--add-drop-table"? what does it do? do i need it?
    2. is it possible to just stop the DB and then copy the MYSQL DB folder? and how is the restore if i just copy?
    3.is there a switch to backup all DB's? like "--alldatabases"?

    thanks
    MCSE 2000 Done
    RHCE Done

  • #2
    Re: Mysql Database Backup Question

    Hi,

    http://www.tizag.com/mysqlTutorial/mysqlbackup.php

    http://dev.mysql.com/doc/refman/5.0/en/backup.html

    http://jeremy.zawodny.com/mysql/mysq...-recovery.html

    There a few options for backup databases. In case of small database (Up to
    10 GB) I recommanded to use dump.

    Also, please note the information in:


    If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped.

    http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

    So shell> mysqldump [options] --all-databases can be useful to backup all
    the databases.

    * Its recommanded to call for MySQL DBA and look for other tools for quick restore and server optimize.

    Regards,

    Yuval
    Best Regards,

    Yuval Sinay

    LinkedIn: https://www.linkedin.com/in/yuval14, Blog: http://blogs.microsoft.co.il/blogs/yuval14

    Comment


    • #3
      Re: Mysql Database Backup Question

      ok i have done it like this...

      #REMOVE FOLDERS
      #
      #
      rm -fr /tmp/mysql_backup/
      mkdir /tmp/mysql_backup/
      mkdir /tmp/mysql_backup/mysql_hot_copy/



      #MYSQLDUMP START
      #
      #
      mysqldump --opt -u root -p "" --host=localhost --all-databases | gzip > /tmp/mysql_backup/mysq
      l_backup_alldb_dump.gz
      mysqldump -uroot -p "" --opt --all-databases > /tmp/mysql_backup/all_db_dump.sql

      #MYSQL_HOT_COPY START
      #
      #
      cd /etc/rc.d/init.d/
      ./mysqld stop
      cp -frP /var/lib/mysql/AI /tmp/mysql_backup/mysql_hot_copy/AI
      cp -frP /var/lib/mysql/bugs /tmp/mysql_backup/mysql_hot_copy/bugs
      cp -frP /var/lib/mysql/bugtracker /tmp/mysql_backup/mysql_hot_copy/bugtracker
      cp -frP /var/lib/mysql/mysql /tmp/mysql_backup/mysql_hot_copy/mysql
      cp -frP /usr/local/mantis /tmp/mysql_backup/mantis
      cd /etc/rc.d/init.d/
      sleep 5
      ./mysqld start
      sleep 2

      #prepare the files for scp using TAR
      #
      #
      cd /tmp/
      tar -cvzf mysqlbackup.tgz mysql_backup/

      #SCP PART
      #
      #
      scp /tmp/mysqlbackup.tgz [email protected]:/home/yanivf





      i have tested it cuple of time and it works
      i just have to wait and see if it works with the cronjob i have created to run at 2 in the morning.
      MCSE 2000 Done
      RHCE Done

      Comment


      • #4
        Re: Mysql Database Backup Question

        You should run test on another machine with the same settings that the restore process is working & the application can use the database as before.

        Yuval
        Best Regards,

        Yuval Sinay

        LinkedIn: https://www.linkedin.com/in/yuval14, Blog: http://blogs.microsoft.co.il/blogs/yuval14

        Comment


        • #5
          Re: Mysql Database Backup Question

          Here is a quick and dirty script I sometimes use in cron jobs:

          Code:
          #!/bin/bash
          
          DB_LIST="mysql db1 db2"
          MYSQL_PWD="secret"
          MYSQL_USR="[email protected]"
          MYSQLDUMP_BIN="/usr/bin/mysqldump"
          DEST_DIR="/backup/mysql"
          SUFFIX=`date +%a`
          
          for db in $DB_LIST
           do
           ${MYSQLDUMP_BIN} --password=$MYSQL_PWD --databases $db > ${DEST_DIR}/${db}_${SUFFIX}.sql
          done
          Guy Teverovsky
          "Smith & Wesson - the original point and click interface"

          Comment

          Working...
          X