Results 1 to 10 of 10

Thread: Backing Up MySQL

Hybrid View

  1. 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. #2
    Hurray!
    Now that's an awesome tip, specially when I have a few 200+ Mb DB's to backup everyday.

    Thx Will!

  3. #3
    This actually makes a hot copy of the data intact. You can just then tar the files up individually, or the whole directory.

  4. #4
    Hooray you just made work a bit easier for me
    Webmaster Forums
    Host Mist | Shared | Reseller | VPS | Dedicated
    Arcade Master - Rule the arcade!

  5. #5

    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

  6. #6
    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.

  7. #7
    i see.

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

  8. 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.

  9. #9
    Join Date
    Jan 2009
    Location
    Naples, FL, US
    Posts
    21
    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

  10. 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, 10: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
  •