Results 1 to 29 of 29
  1. #1
    Join Date
    Aug 2009
    Posts
    48

    Simplest way to auto cron a mysql database

    Hi,
    I use a php script usually but that does not work for large files.

    I have a 130mb mysql database on linux using cPanel.
    What is the easiest way to set up say a monthly cron to back this up to the root server?

    Any help appreciated.

  2. #2
    Join Date
    Oct 2002
    Location
    Langley, BC
    Posts
    2,046
    mysqldump -u username -ppassword dbname > dbname.sql would do the trick I suppose?

    To load it
    mysql -u username -ppassword dbname < dbname.sql
    We Have Generated Over 7 Million cPanel Backups Come Dance Together With Us Y'all!
    Offer Your Own Backup Hosting Service - SiteAutoBackup.Com (Private Label / WHMCS Ready)
    WebHostingBusinessBook.Com | YouTube.com/WebHostingTutorial | NowOpenOnline.Com

  3. #3
    Join Date
    Aug 2009
    Posts
    48
    Is that complete?
    Just that in a cron.
    I have had something similar which didn't work.
    If that is complete I will try again thanks

  4. #4
    Join Date
    Oct 2001
    Location
    United States
    Posts
    203
    If you ae using cPanel, you can setup cronjob from your control panel. It is much easier. There are tutorials on how to setup a cronjob on cPanel.

  5. #5
    Join Date
    Aug 2009
    Posts
    48
    yes there are but none show the exact syntax to use.
    The tuts just show how to enter info in the cron control panel.
    I am fine with that.
    I have found many syntax examples but have probs with them not working.
    For instance this one does not work for me...
    /usr/bin/mysqldump –opt –all-databases -u root -pPASSWORD > /foo/bar/db-`date +%Y-%m-%d`.sql

    That's why I am asking for the exact syntax to simply do this...
    Backup database to server.


    I am currently trying this one...
    mysqldump -u DBUSER -pDBPASSWORD --all-databases | gzip -9 -c > /home/yourlogin/randombackupnamehere.`date +%d-%m-%Y`.sql.gz

    Thanks
    Last edited by Pete eteP; 10-03-2009 at 08:01 PM.

  6. #6
    Join Date
    Aug 2009
    Posts
    48
    and this is the error I get via email for that one

    /bin/sh: -c: line 0: unexpected EOF while looking for matching ``'
    /bin/sh: -c: line 1: syntax error: unexpected end of file

  7. #7
    Join Date
    May 2007
    Location
    Canada
    Posts
    183
    Its more than likely your naming that is messing things up:

    .`date +%d-%m-%Y`

    Remove that section, and see what happens. I've used and recommended to others similar syntax:

    mysqldump -u username --password='password' db_name > /home/user/file.sql

    Make sure you place the single quotes around the password field if your password contains any type of special character.
    Quality Hosting - http://www.robohostingsolutions.com
    My Blog - http://solidservers.ca
    Freelance Server Administration (decent rates!) - Chris(at)SolidServers.ca

  8. #8
    Join Date
    May 2009
    Location
    Atlanta,GA
    Posts
    85
    this one works for me.i have a vbulletin forum

    # mysqldump --opt -Q -u dbusername -p dbname > backupname.sql

  9. #9
    Join Date
    Aug 2009
    Posts
    48
    Gearbox..but that doesn't seem to designate a folder on server to save to?
    Does it do this by default?

    RSH that could be my prob...I didn't use the quotes.

    I WILL get this and report when successful...
    :-)

  10. #10
    Join Date
    Aug 2009
    Posts
    48
    RHS,
    This is the error I get.
    Username and password I am using are the ones to access cPanel.

    mysqldump: Got error: 1045: Access denied for user 'cpanelusernamehere'@'localhost' (using password: YES) when trying to connect

  11. #11
    Join Date
    May 2009
    Location
    Atlanta,GA
    Posts
    85
    Quote Originally Posted by Z Web Design View Post
    RHS,
    This is the error I get.
    Username and password I am using are the ones to access cPanel.

    mysqldump: Got error: 1045: Access denied for user 'cpanelusernamehere'@'localhost' (using password: YES) when trying to connect
    you should be having a separate database username and dbase password.it is different from your cpanel account.

  12. #12
    Join Date
    May 2009
    Location
    Atlanta,GA
    Posts
    85
    Quote Originally Posted by Z Web Design View Post
    Gearbox..but that doesn't seem to designate a folder on server to save to?
    Does it do this by default?


    :-)
    this will create the backup in the directory you are presently in.

    you can use the following one.

    mysqldump --opt -Q -u dbaseusername -p dbasename > /path/to/backupname.sql

  13. #13
    Join Date
    Aug 2009
    Posts
    48
    oh ok
    and do i add it is this form
    cpanelusername_databaseusername
    cpanelusername_databasepassword

    or like this?
    databaseusername
    databasepassword

    thanks

  14. #14
    Join Date
    May 2007
    Location
    Canada
    Posts
    183
    Like this:

    cpanelusername_databaseusername
    cpanelusername_databasename
    dbpassword
    Quality Hosting - http://www.robohostingsolutions.com
    My Blog - http://solidservers.ca
    Freelance Server Administration (decent rates!) - Chris(at)SolidServers.ca

  15. #15
    Join Date
    Aug 2009
    Posts
    48
    This is what I had. No joy.
    mysqldump -u cpaneluser_cpaneluser_dbname --password='cpanelpassword' Cpaneluse_dbname > /home/cpaneluser/backups/file.sql

  16. #16
    Join Date
    Aug 2009
    Posts
    48
    Please spell it out exactly using say
    cpanel username of cpanelusername
    database password as dbpassword
    cpanel password as cpanelpassword
    dbusername as dbusername

    Sorry guys.
    There are quite a few interpretations of this.. so I want to get it right.

  17. #17
    Join Date
    Aug 2009
    Posts
    48
    /cpanleusername/bin/mysqldump ľopt ľall-databases -u cpanelusername -pcpanelpassword > /cpanelusername/backups/db.sql

    This one give an email message:
    /bin/sh: /cpanelusername/backups/db.sql: No such file or directory

    !!!
    argggggggggggggggggggg

  18. #18
    Join Date
    Jun 2006
    Location
    Devon, UK
    Posts
    1,307
    For accessing the database, you won't be able to use the cPanel password/username. cPanel doesn't insert that data into the MySQL username tables.

    If you're running cPanel, your files will be located within /home/<your cpanel username>/ so backups will need to go there as well

    Try this:
    mysqldump -u [database username] -p [database password] [database name] > /home/[your cpanel username]/backup.sql
    Remember to replace the parts in [] with the required bits, removing the brakets. Your database username will be your cpanel username_dbuser ie cold_user your password will be whatever you used when you made the database. The database name will be your cPanel username_dbname ie cold_db1.

    An example:
    mysqldump -u cold_user -p mypass cold_db1 > /home/cold/backup.sql
    Hope that helps,
    Mike

  19. #19
    Join Date
    Aug 2009
    Posts
    48
    appreciate your perseverance.
    stay tuned

  20. #20
    Join Date
    Aug 2009
    Posts
    48
    mysqldump -u paperflo_pete -p databasepassword paperflo_database > /home/paperflo/backups/backup.sql

    error:
    Enter password: mysqldump: Got error: 1045: Access denied for user 'paperflo_pete'@'localhost' (using password: NO) when trying to connect

    Maybe its blocked or not allowed by my reseller host?

  21. #21
    Join Date
    May 2007
    Location
    Canada
    Posts
    183
    mysqldump -u paperflo_pete --password='password' paperflo_database > /home/paperflo/backups/backup.sql

    Make sure to change the password to your current db pass.
    Quality Hosting - http://www.robohostingsolutions.com
    My Blog - http://solidservers.ca
    Freelance Server Administration (decent rates!) - Chris(at)SolidServers.ca

  22. #22
    Join Date
    Aug 2009
    Posts
    48
    I will contact my reseller host and ask them.
    I will report back the outcome.
    Appreciate the help.

  23. #23
    Join Date
    Aug 2009
    Posts
    48
    Quote Originally Posted by RHS-Chris View Post
    mysqldump -u paperflo_pete --password='password' paperflo_database > /home/paperflo/backups/backup.sql

    Make sure to change the password to your current db pass.
    Still get this...
    mysqldump: Got error: 1045: Access denied for user 'paperflo_pete'@'localhost' (using password: YES) when trying to connect

  24. #24
    Join Date
    May 2007
    Location
    Canada
    Posts
    183
    Sounds like you have the wrong password then. If you have forgot the password, you should be able to locate it in your database connection settings for the script you are using.
    Quality Hosting - http://www.robohostingsolutions.com
    My Blog - http://solidservers.ca
    Freelance Server Administration (decent rates!) - Chris(at)SolidServers.ca

  25. #25
    Join Date
    Jun 2006
    Location
    Devon, UK
    Posts
    1,307
    Or just add a new username in cPanel.

    Z Web: That error is actually telling you that your password or username are wrong. You need to make sure that you've applied the user account to the database you want to use.

  26. #26
    Join Date
    Aug 2009
    Posts
    48
    yes i did apply and did set a new user and database.
    Reseller host also says should work so I will persist.
    We'll get there....:-)

  27. #27
    Join Date
    Aug 2009
    Posts
    48
    FINALLY!
    This worked
    mysqldump -u DATABASEUSER -pDATABASEPASSWORD DATABASENAME > DATABASEBACKUPFILE.sql

    I think I was putting in the database password as databasename_password instead of just password.
    But to be honest I have tried so many variables I'm not sure.
    Now I will mod this slowly adding date to backup file etc until I get it how I want it.
    I have noted this working one down for future reference too!

    1000 thanks for your patience!
    :-)

  28. #28
    Join Date
    Jun 2006
    Location
    Devon, UK
    Posts
    1,307
    Glad to see you got it sorted .

  29. #29
    Join Date
    Aug 2009
    Posts
    48
    I gave up on this 12 mths ago.
    I think it was worth the pain this time.
    It's an essential I should know how to do.

    Thanks again for all your help


    And I just got the gzip version to work now too. :-)

Similar Threads

  1. How set up cron job auto empty a tables of an database
    By dotham in forum Hosting Security and Technology
    Replies: 11
    Last Post: 12-25-2008, 01:18 PM
  2. Auto Creation of MYSQL Database With New CPanel Account
    By qualityinterfaces in forum Hosting Software and Control Panels
    Replies: 4
    Last Post: 08-03-2007, 02:17 AM
  3. Auto submit a form via cron
    By keliix06 in forum Programming Discussion
    Replies: 3
    Last Post: 01-10-2007, 02:12 PM
  4. How to auto backup Mysql database ?
    By Abdo-sa in forum Hosting Security and Technology
    Replies: 2
    Last Post: 11-05-2006, 07:39 AM
  5. Auto MySQL database backups
    By Jim_UK in forum Hosting Security and Technology
    Replies: 0
    Last Post: 10-21-2002, 08:37 PM

Posting Permissions

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