Results 1 to 10 of 10

Thread: Backing Up MySQL

  1. #1
    Will.Spencer's Avatar
    Will.Spencer is offline Retired
    Join Date
    Dec 2008
    Posts
    5,033
    Blog Entries
    1
    Thanks
    1,010
    Thanked 2,327 Times in 1,258 Posts

    Backing Up MySQL

    When I want to backup all of the MySQL databases on my server, I have been using this command:
    Code:
    mysqldump --all-databases -u MYSQL_USERNAME -h MYQL_SERVER -pMYSQL_PASSWORD   > mysql-backup-`date +'%Y%m%d-%H%M%S'`
    The trouble is that this backs up the DB's all into one large file. That makes restoring an individual DB kind of annoying.

    Presence uses mysqlhotcopy to backup each database into it's own file:
    Code:
    #!/bin/bash
    BACKUP_DIR=/tmp/backup
    for i in `/usr/bin/find /var/lib/mysql/* -type d -printf "%f\n"`;do /usr/local/bin/mysqlhotcopy --allowold -u root $i $BACKUP_DIR; done
    Submit Your Webmaster Related Sites to the NB Directory
    I swear, by my life and my love of it, that I will never live for the sake of another man, nor ask another man to live for mine.

  2. Thanked by:

    Coastercraze (27 March, 2009), Shenron (26 March, 2009), XinProxy (30 March, 2009)

  3. #2
    Shenron's Avatar
    Shenron is offline Administrator
    Join Date
    Dec 2008
    Location
    Portugal
    Posts
    1,900
    Blog Entries
    2
    Thanks
    561
    Thanked 548 Times in 368 Posts
    Hurray!
    Now that's an awesome tip, specially when I have a few 200+ Mb DB's to backup everyday.

    Thx Will!

  4. #3
    presence's Avatar
    presence is offline Unknown Net Builder
    Join Date
    Jan 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This actually makes a hot copy of the data intact. You can just then tar the files up individually, or the whole directory.

  5. #4
    Coastercraze's Avatar
    Coastercraze is offline Net Builder Legend
    Join Date
    Jan 2009
    Location
    Under powerlines
    Posts
    499
    Blog Entries
    3
    Thanks
    94
    Thanked 59 Times in 48 Posts
    Hooray you just made work a bit easier for me
    Webmaster Forums
    Host Mist | Shared | Reseller | VPS | Dedicated
    Arcade Master - Rule the arcade!

  6. #5
    badtux is offline Unknown Net Builder
    Join Date
    Jul 2009
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Smile help

    Sir i am a newbie in PHP, how can i execute your backup script in .php? i dont know how to start it. hope you dont mind but i need this script.

    thanks!




    Quote Originally Posted by Will.Spencer View Post
    When I want to backup all of the MySQL databases on my server, I have been using this command:
    Code:
    mysqldump --all-databases -u MYSQL_USERNAME -h MYQL_SERVER -pMYSQL_PASSWORD   > mysql-backup-`date +'%Y%m%d-%H%M%S'`
    The trouble is that this backs up the DB's all into one large file. That makes restoring an individual DB kind of annoying.

    Presence uses mysqlhotcopy to backup each database into it's own file:
    Code:
    #!/bin/bash
    BACKUP_DIR=/tmp/backup
    for i in `/usr/bin/find /var/lib/mysql/* -type d -printf "%f\n"`;do /usr/local/bin/mysqlhotcopy --allowold -u root $i $BACKUP_DIR; done

  7. #6
    dtkguy's Avatar
    dtkguy is offline Still Learnin'
    Join Date
    Dec 2008
    Posts
    391
    Thanks
    7
    Thanked 164 Times in 108 Posts
    Quote Originally Posted by badtux View Post
    Sir i am a newbie in PHP, how can i execute your backup script in .php? i dont know how to start it. hope you dont mind but i need this script.

    thanks!
    It isn't in PHP, this is just something you'd run from shell.

  8. #7
    badtux is offline Unknown Net Builder
    Join Date
    Jul 2009
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    i see.

    So do you have a php scripts that i can backup my remote database into my local drive?

  9. #8
    Will.Spencer's Avatar
    Will.Spencer is offline Retired
    Join Date
    Dec 2008
    Posts
    5,033
    Blog Entries
    1
    Thanks
    1,010
    Thanked 2,327 Times in 1,258 Posts
    Quote Originally Posted by badtux View Post
    So do you have a php scripts that i can backup my remote database into my local drive?
    I believe that phpMyBackupPro does that.
    Submit Your Webmaster Related Sites to the NB Directory
    I swear, by my life and my love of it, that I will never live for the sake of another man, nor ask another man to live for mine.

  10. #9
    NameStand is offline Newbie Net Builder
    Join Date
    Jan 2009
    Location
    Naples, FL, US
    Posts
    21
    Thanks
    1
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Will.Spencer View Post
    When I want to backup all of the MySQL databases on my server, I have been using this command:
    Code:
    mysqldump --all-databases -u MYSQL_USERNAME -h MYQL_SERVER -pMYSQL_PASSWORD   > mysql-backup-`date +'%Y%m%d-%H%M%S'`
    The trouble is that this backs up the DB's all into one large file. That makes restoring an individual DB kind of annoying.
    You could just dump one database at a time.
    Code:
    mysqldump -u username -p database-name > output_filename.sql

  11. #10
    Will.Spencer's Avatar
    Will.Spencer is offline Retired
    Join Date
    Dec 2008
    Posts
    5,033
    Blog Entries
    1
    Thanks
    1,010
    Thanked 2,327 Times in 1,258 Posts
    Quote Originally Posted by NameStand View Post
    You could just dump one database at a time.
    Code:
    mysqldump -u username -p database-name > output_filename.sql
    I could, but the list of DB's is large and growing rapidly.

    I've actually asked Siva to look into Automatic MySql Backup Script Dagon Design for us.
    Submit Your Webmaster Related Sites to the NB Directory
    I swear, by my life and my love of it, that I will never live for the sake of another man, nor ask another man to live for mine.

Similar Threads

  1. Replies: 4
    Last Post: 23 January, 2010, 09:52 AM
  2. How to merge 2 MySQL dbs
    By ProxySites.ca in forum Proxy List Support Forum
    Replies: 1
    Last Post: 14 September, 2009, 15:16 PM
  3. ASP + MySQL
    By dodolls in forum Databases
    Replies: 0
    Last Post: 6 September, 2009, 23:12 PM
  4. I Want Your PHP/MySQL Epiphanies!
    By zac439 in forum Programming
    Replies: 1
    Last Post: 30 April, 2009, 16:48 PM

Tags for this Thread

Bookmarks

Posting Permissions

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