Results 1 to 14 of 14
-
08-16-2009, 01:29 PM #1Junior Guru Wannabe
- Join Date
- Sep 2008
- Posts
- 38
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?
-
08-16-2009, 03:32 PM #2WebHostingTalk Lover
- 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 ...
-
08-16-2009, 04:07 PM #3Junior Guru Wannabe
- 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.
-
08-16-2009, 04:38 PM #4Disabled
- Join Date
- Oct 2008
- Posts
- 13
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.
-
08-16-2009, 05:14 PM #5Junior Guru Wannabe
- 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.
-
08-16-2009, 05:34 PM #6Disabled
- Join Date
- Oct 2008
- Posts
- 13
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.
-
08-16-2009, 05:36 PM #7Retired Moderator
- 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
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
-
08-16-2009, 05:44 PM #8Junior Guru Wannabe
- 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?
-
08-16-2009, 05:57 PM #9Retired Moderator
- 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
-
08-16-2009, 06:04 PM #10Junior Guru Wannabe
- 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.
-
08-16-2009, 06:10 PM #11Junior Guru Wannabe
- 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
-
08-16-2009, 06:22 PM #12Junior Guru Wannabe
- 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.
-
08-16-2009, 06:26 PM #13Web Hosting Evangelist
- 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 luckExceptional VPS Hosting. With love, 6sync.
-
08-17-2009, 01:27 AM #14Web Hosting Master
- 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!
ThomasGPLHost:>_ 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
-
How to get copy of all mysql databases into individual tar.gz files?
By mrzippy in forum Hosting Security and TechnologyReplies: 4Last Post: 03-27-2009, 11:16 AM -
Backup and Restore Mysql databases.
By hosseinrz in forum Hosting Security and TechnologyReplies: 0Last Post: 05-10-2008, 04:24 PM -
How to restore mysql databases from /var/lib/mysql/user_database/ files only?
By bjdea1 in forum Hosting Security and TechnologyReplies: 2Last Post: 02-11-2008, 05:44 AM -
Databases: Flat files vs MySQL
By krissauquillo in forum Programming DiscussionReplies: 26Last Post: 12-02-2007, 05:49 AM -
Mysql database backup for all databases in individual files
By dandanfirema in forum Hosting Security and TechnologyReplies: 5Last Post: 07-14-2002, 03:43 AM