Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2008
    Posts
    38

    Exclamation Backup All MYSQL Databases To Seperate FIles

    I want to be able to backup all MySQL databases on my cPanel server. I know i can do this via mysqldump -u username -ppassword –all-databases > dump.sql which will put all mysql databases in 1 file but what I want preferably is to have all the mysql databases sent over to a remote ftp. then put in like /cpaneluser/mysqldb_1.sql, /cpaneluser/mysqldb_2.sql, /cpaneluser2/mysqldb_1.sql etc. But how would I do this?

  2. #2
    Join Date
    Mar 2003
    Location
    New York City
    Posts
    7,406
    There is the backup option in cPanel/WHM but try emailing cPanel via their website or their helpdesk, they might have a script for you to do this.

    Good luck
    █• Taskade - To-Do List & Tasks • All-in-One To-Do List & Mind Map App for Remote Teams
    █• Simple and shareable to-do lists for web, mobile, and desktop
    █• To-Do List Templates • 300+ shareable templates and productivity workflows
    █• Get things done, faster and smarter! • I eat penguins for breakfast ...

  3. #3
    Join Date
    Sep 2008
    Posts
    38
    Ye i hate the backup option ive made my own backup script but I wanna do MySQL seperately and im not sure how to do it like I want it.

  4. #4
    Your best option probably is to write a script that runs from cron and does a mysqldump and then an scp of ftp transfer to the remote site. And then on the remote site, you could setup logrotate to rotate the backup files automatically. If you can't do any log rotation on the remote side, then you would need to make your cron script on the local side doing the logic for backup rotation, but it's going to be easier to do it with logrotate on the remote side.

    Also, to do scp from a cron script automatically, you'll want to setup SSH keys so that the login is automatic. If you only have FTP available, then you'll have to do something like this in your backup script:

    # login to remote server
    ftp -n -i $SERVER <<EOF
    user $USERNAME $PASSWORD
    cd $BACKUPDIR
    mput $FILE
    quit
    EOF

    There are other options too and some ftp clients will allow you to pass the user and pass all in one command, but hopefully this gets you in the right direction.

  5. #5
    Join Date
    Sep 2008
    Posts
    38
    Ye I can do that bit but i need to know how to write each database to its own file thats the problem im having atm.

  6. #6
    Oh, sorry, missed that detail.. Only way I can think of is to use a perl script to do a mySQL query to list the databases, and then do each dump separately based on the output of that query. There may be something in the cPanel API to do it on a per-user basis, but I'm not sure off-hand.

  7. #7
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,849
    You should be able to do something like this:
    Code:
    for db in `echo "show databases" |mysql --column-names=false`
    do
        mysqldump $db >/path/to/backups/$db.sql
    done
    Note - no passwords on the command lines. For automated login you can put the password in a .my.cnf file in /root.
    Code:
    [client]
    password = secret
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  8. #8
    Join Date
    Sep 2008
    Posts
    38
    Thats brilliant thanks. It creates them but i do get some errors on certain databases all the same error.

    mysqldump: Got error: 1033: Incorrect information in file: './horde/horde_sessionhandler.frm' when using LOCK TABLES

    what causes that?

  9. #9
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,849
    Sounds like a corrupted database but a quick Google finds this, suggesting a change in settings may cause it. Have you made config changes recently?
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  10. #10
    Join Date
    Sep 2008
    Posts
    38
    Erm well i know for a fact the tables with the errors still work. As one is my WHMCS database i know for a fact that it works. But erm well ive changed .my.cnf last night i added the root username and password for MySQL.

    Ah w8 its not my WHMCS Db and when i go into PHPMyAdmin the tables say In Use. But theres no data in the tables so how can it be corrupt lol.
    Last edited by truevision; 08-16-2009 at 06:09 PM.

  11. #11
    Join Date
    Jan 2008
    Posts
    55
    You can always repair your tables:

    myisamchk -r /var/lib/mysql/DATABASE/*.MYI

    or
    /usr/bin/myisamchk -r DATABASE

  12. #12
    Join Date
    Sep 2008
    Posts
    38
    Ok that didnt work but after some further reading up i found

    mysqldump db_name > dump.sql
    mysql db_name < dump.sql

    which repairs them.

  13. #13
    Join Date
    Apr 2009
    Posts
    481
    You may want to look at: AutoMySQLBackup

    Relevant features:
    * Backup all databases to a single backup file or to a seperate directory and file for each database.
    * Can be set to run PRE and POST backup commands. [[ ie ftp commands ]]

    Pretty easy to get going Good luck
    Exceptional VPS Hosting. With love, 6sync.

  14. #14
    Join Date
    Aug 2004
    Location
    Shanghai
    Posts
    1,475
    Hi,

    I was about to talk about automysqlbackup!!!

    In fact, I'm the Debian (and ubuntu) maintainer for this package, so it's available as Debian package. However, you should take a great care that, by default, it will create world readable backups, which might be dangerous if you have some users on the server.

    If you are not using a Debian or Ubuntu server, I suggest you to STILL get my package and maybe work with alien to convert it to a RPM. You can as well get the source package to see what we have patched.

    Next, if you want to backup a folder recursively I can only suggest you to use lftp. It's a very good tool!

    Thomas
    GPLHost:>_ open source hosting worldwide (I'm founder, CEO & official Debian Developer)
    Servers & our leading control panel and our Xen VPS hosting, which are already included in Debian and Ubuntu
    Available in: Kuala Lumpur, Singapore, Sydney, Seattle, Atlanta, Paris, London, Barcelona, Zurich, Israel

Similar Threads

  1. How to get copy of all mysql databases into individual tar.gz files?
    By mrzippy in forum Hosting Security and Technology
    Replies: 4
    Last Post: 03-27-2009, 11:16 AM
  2. Backup and Restore Mysql databases.
    By hosseinrz in forum Hosting Security and Technology
    Replies: 0
    Last Post: 05-10-2008, 04:24 PM
  3. How to restore mysql databases from /var/lib/mysql/user_database/ files only?
    By bjdea1 in forum Hosting Security and Technology
    Replies: 2
    Last Post: 02-11-2008, 05:44 AM
  4. Databases: Flat files vs MySQL
    By krissauquillo in forum Programming Discussion
    Replies: 26
    Last Post: 12-02-2007, 05:49 AM
  5. Mysql database backup for all databases in individual files
    By dandanfirema in forum Hosting Security and Technology
    Replies: 5
    Last Post: 07-14-2002, 03:43 AM

Posting Permissions

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