Find the answer to your Linux question:
Results 1 to 9 of 9
Hi, i have a MySQL database on my server, i was just wondering if there was a way to setup a script to run daily that would dump the database ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Linux Guru sdousley's Avatar
    Join Date
    Feb 2004
    Posts
    1,790

    Dumping MySQL


    Hi, i have a MySQL database on my server, i was just wondering if there was a way to setup a script to run daily that would dump the database structure out of the database into a file called structure<time>.sql and then dump the data out of the database into a similar file called data<time>.sql

    I'm sure there's guna b a way to dump the WHOLE lot into a file called dump<time>.sql but not sure about how to seperate the data structure with the content.

    I guess this is guna b a bash script that runs on a cron job? Though i have never bash scripted like this (inface never done any bash scripting!)

    Any help would be cool.

    Thanks.
    "I am not an alcoholic, alcoholics go to meetings"
    Registered Linux user = #372327

  2. #2
    Linux Guru sarumont's Avatar
    Join Date
    Apr 2003
    Location
    /dev/urandom
    Posts
    3,682
    IIRC, you can just copy the database files from wherever they are stored. That will include the data and the structure of the database. I'm not sure where they are stored, though, as I don't have mysql running anywhere right now.
    "Time is an illusion. Lunchtime, doubly so."
    ~Douglas Adams, The Hitchhiker's Guide to the Galaxy

  3. #3
    Linux Guru sdousley's Avatar
    Join Date
    Feb 2004
    Posts
    1,790
    ok, i have found a folder in /var/lib/mysql which has a folder the same name as all the databases on my MySQL server.

    How would i go about copying the directory for a specific database to a file/folder named by what it's originally called, then had the time added to the filename/folder name?

    As i mentioned, i never done any bash scripting or cron jobs, so haven't a clue where to start wiv em!!
    "I am not an alcoholic, alcoholics go to meetings"
    Registered Linux user = #372327

  4. $spacer_open
    $spacer_close
  5. #4
    Just Joined!
    Join Date
    Jun 2004
    Location
    London, UK
    Posts
    53
    Copying the files "hot" is not recommended. If you wish to do this please shut down MySQL first.

    Alternativly use the mysqldump command.

    Something like this is what you need, modify it after your system need.
    Code:
    #!/bin/bash
    DATETAG=`date %y%d%M`
    
    mysqldump --username=user --password=password databasename > /backup_dir/mysql_database_$DATETAG.sql

    Use the -A option to dump all the databases instead of "databasename".

  6. #5
    Linux Newbie
    Join Date
    Apr 2004
    Posts
    158
    Hi,

    this might work for you...

    for i in `ls /var/lib/mysql`
    do mysqldump -d -B $i > myfile`date +%y%m%d`_no-data_$i.sql
    mysqldump -n B $i > myfile`date +%y%m%d`_only_data_$i.sql
    done

    Please forgive any errors in this small program, I am on customer sight and have only a wintendo to play with...

    Let me know how it goes, I can verify this for you later today!

    Cheers

    Jonas
    --
    in Linux Computing we Trust

  7. #6
    Linux Guru sdousley's Avatar
    Join Date
    Feb 2004
    Posts
    1,790
    That seems to come back with quite a lot of errors saying that access is denied for user: root@localhost (using password: NO)

    It's ok, i'll just have to do it all manually, not a huge problem, i may just end up leaving everything in one sql file anyway. I only need one file that's a blank database so i can hand the work in.

    Thanks for all the help anyway.
    "I am not an alcoholic, alcoholics go to meetings"
    Registered Linux user = #372327

  8. #7
    Linux Engineer Giro's Avatar
    Join Date
    Jul 2003
    Location
    England
    Posts
    1,219
    Code:
    #!/bin/bash
    DATE=`date --iso-8601`
    mysqldump -u <username> -p <password> <dbname> > $&#123;DATE&#125;.sql

  9. #8
    Linux Newbie
    Join Date
    Apr 2004
    Posts
    158
    Hi,

    Here is a script that I am running today, it works fine for me and should do the same for you. :o)

    Code:
    #!/bin/bash
    
    # backup mysql databases with full create syntax
    
    TARGETDIR=/var/backup/
    DATE=`date +%Y%m%d`
    for i in `ls /var/lib/mysql`
    do mysqldump -uroot -ppassword -a --add-drop-table -B $i > $&#123;TARGETDIR&#125;$&#123;i&#125;_full_$&#123;DATE&#125;.sql 
    done
    
    # gzip all files
    #for i in `ls /var/backup`
    #do cd /var/backup
    #gzip -9 $i
    #done
    If you want gzipped backups just uncomment the last lines...

    Also remember to set TARGETDIR and user/password to the correct values.

    there is no typo in the -uroot and -ppassword, there should be no space between the -u/-p and the username/password.

    Regards

    Jonas
    --
    in Linux Computing we Trust

  10. #9
    Linux Guru
    Join Date
    Mar 2003
    Location
    Wisconsin
    Posts
    1,907
    Code:
    #!/bin/bash
    
    # backup mysql databases with full create syntax
    
    TARGETDIR=/var/backup/
    DATE=`date +%Y%m%d`
    for i in `ls /var/lib/mysql`
    do mysqldump -uroot -ppassword -a --add-drop-table -B $i > $&#123;TARGETDIR&#125;$&#123;i&#125;_full_$&#123;DATE&#125;.sql
    done
    So, I tried using this last night and I came across a few stubmling blocks. First, I have other files in /var/lib/mysql besided database folders. I had to add a little work around to get only the db names.

    Code:
    cd /var/lib/mysql
    for i in `ls -d */`
    do TMP=`cat $i | sed 's/\///'`
    mysqldump -uroot -ppassword -a --add-drop-table -B $TMP > $&#123;TARGETDIR&#125;$&#123;TMP&#125;_full_$&#123;DATE&#125;.sql
    done
    That seemed to work, ie I then only had a listing of the db's, but then I get an error: /var/backup/filename_full_20051115.sql doesn't exist and it doesn't create it for some reason.

    While we're at it, I have one more q. Do I have to physically backup the /var/lib/mysql folder structure using tar in addition to using mysqldump?

    EDIT: Another thought, can I just use --all-databases rather then looping thru each db?

    Thanks guys,
    Jeremy
    Registered Linux user #346571
    "All The Dude ever wanted was his rug back" - The Dude

Posting Permissions

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