Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Montréal
    Posts
    84

    Need to backup MYSQL each day/week

    Hi,

    I have a server with plesk 7.5 installed
    I would like to know if there is a way to backup all my Data bases on the server one's a week.

    Actualy i have like 20 DB that are own by 20 differents users. and each DB have x number of tables.

    So i would like to do a full backup every day or week of MYSQL Database.

    Is there a mysql command that i can do?

    one's that done i would like to gzip the file.

    Thanks in advance for any help.

  2. #2
    There are various ways to do this with the mysqldump command. I have a shell script that does something like this, and runs on cron every night:

    Code:
    outfile='/backup-directory-here/db-'`date +%m`'-'`date +%d`'-'`date +%y`'.tar.gz'
    mkdir /backup-directory-here/temp
    
    mysqldump -uUSERNAME -pPASSWORD --databases DB_NAME_1 > /backup-directory-here/temp/DB_NAME_1.sql
    mysqldump -uUSERNAME -pPASSWORD --databases DB_NAME_2 > /backup-directory-here/temp/DB_NAME_2.sql
    mysqldump -uUSERNAME -pPASSWORD --databases DB_NAME_3 > /backup-directory-here/temp/DB_NAME_3.sql
    
    tar -czf $outfile /backup-directory-here/temp/*
    rm -rf /backup-directory-here/temp
    rm -f temptar
    Hopefully I didn't cut out anything important when editing out most of my stuff. It dumps the DBs I specified to .sql files (easy re-importing) and tars/gzips it all up with a dated filename.
    Dan Grossman - dan @ awio.com
    My Blog | Affiliate Program for Web Hosts

  3. #3
    Join Date
    Feb 2004
    Posts
    634
    Depending on your needs and your table types; you may want to check out:

    MySQLHotCopy (ISAM/MyISAM tables only)
    MySQLSnapshot (MyISAM tables only)
    InnoDB Hot Backup (InnoDB tables only; not open source)

    None of these do a SQL dump (i.e. a list of SQL statements to rebuild the DB) like mysqldump; they back up the database in binary form so you would simply need to copy it back over instead of rebuilding from the SQL file for a restore. MySQLHotCopy is probably the least useful for higher volume sites (it needs to use a table lock to copy each table), but it comes with the MySQL distro. MySQLSnapshot works well for backing up an entire DB, and there's a command line argument to gzip the resulting tarball. InnoDB Hot Backup is the only tool I'm aware of that let's you take hot backups (i.e. the server can be running and no table locks are necessary) of InnoDB tables.

Posting Permissions

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