+ Post New Thread
Results 1 to 7 of 7
*nix Thread, MySQL automated backup in Technical; Having just learned this mistake the hard way here's a useful script for any of you with CRON access to ...
  1. #1
    contink's Avatar
    Join Date
    Jul 2006
    Location
    South Yorkshire
    Posts
    3,791
    Thank Post
    303
    Thanked 327 Times in 233 Posts
    Rep Power
    118

    MySQL automated backup

    Having just learned this mistake the hard way here's a useful script for any of you with CRON access to backup your MySQL data on a regular basis.

    MySQL Automatic backup script
    http://www.debianhelp.co.uk/mysqlscript.htm

    Some tips to add:

    1. Create a specific db user with global select and lock table privs that nobody else knows about and use that to create your backups

    2. Download the file to your local PC and have it open to the area beneath the custom settings as this is where the real user manual lies.

    Other support/tips here:
    http://sourceforge.net/forum/forum.php?forum_id=350022

    Works very nicely and although this is a "stable door lock post horse depart" issue for me it's been worth the lesson.

  2. #2

    Ric_'s Avatar
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,592
    Thank Post
    109
    Thanked 770 Times in 598 Posts
    Rep Power
    182

    Re: MySQL automated backup

    You can also use MySQL Administrator to perform scheduled backups remotely if you don't have cron access.

  3. #3
    contink's Avatar
    Join Date
    Jul 2006
    Location
    South Yorkshire
    Posts
    3,791
    Thank Post
    303
    Thanked 327 Times in 233 Posts
    Rep Power
    118

    Re: MySQL automated backup

    Quote Originally Posted by Ric_
    You can also use MySQL Administrator to perform scheduled backups remotely if you don't have cron access.
    Didn't realise that existed to be honest so I've gone and grabbed that as well.. thanks for the tip Ric

    So I've some new tips regarding setting up a CPanel/WHM nic server so you can use the MySQL Administrator tool from your local machine.

    CSF users
    - make sure you're allowing port 3306 through as a TCP IN port.

    phpmyadmin
    - check and update the host privileges for the user(s) you intend to use so they aren't just "localhost" otherwise your user will be denied access

    WHM - Mysql
    - use the "Additional MySQL Access Hosts" to add your IP or hostname for the connection you wish to access the db from.


    Think that's about it...

  4. #4


    Join Date
    Jan 2006
    Posts
    8,202
    Thank Post
    442
    Thanked 1,032 Times in 812 Posts
    Rep Power
    339

    Re: MySQL automated backup

    Ideally you should only allow mysql 3306 access from the local machine (ip spoofing etc). Check this howto to tunnel MySQL administrator over ssh
    http://www.howtoforge.com/secure_mys...ion_ssh_tunnel

  5. #5
    contink's Avatar
    Join Date
    Jul 2006
    Location
    South Yorkshire
    Posts
    3,791
    Thank Post
    303
    Thanked 327 Times in 233 Posts
    Rep Power
    118

    Re: MySQL automated backup

    Quote Originally Posted by CyberNerd
    Ideally you should only allow mysql 3306 access from the local machine (ip spoofing etc). Check this howto to tunnel MySQL administrator over ssh
    http://www.howtoforge.com/secure_mys...ion_ssh_tunnel
    Good point... Worth noting that this only deals with the manual approach so if you really want to get into it, probably best to consider ssh2 keys and use of pagent to store them.

    Not sure how easy it is to get mysql admin to start the connection automatically as part of its scheduling system (something to find out about).

  6. #6


    Join Date
    Jan 2006
    Posts
    8,202
    Thank Post
    442
    Thanked 1,032 Times in 812 Posts
    Rep Power
    339

    Re: MySQL automated backup

    Not sure how easy it is to get mysql admin to start the connection automatically as part of its scheduling system (something to find out about).
    probably easier to schedule the original script over an ssh tunnel

  7. #7

    Join Date
    Apr 2006
    Posts
    138
    Thank Post
    0
    Thanked 19 Times in 10 Posts
    Rep Power
    38

    Re: MySQL automated backup

    Nice script, but remember a backup is completely useless unless you can restore from it. One gotcha to bear in mind when backing up with mysqldump, is that table columns are listed "as is" which might cause a problem on restore. For example, if you were to backup your Koha library system with mysqldump, one of the tables is dumped as
    Code:
    CREATE TABLE issues (
      borrowernumber int(11) NOT NULL default '0',
      itemnumber int(11) NOT NULL default '0',
      date_due date default NULL,
      branchcode char(4) default NULL,
      issuingbranch char(18) default NULL,
      returndate date default NULL,
      lastreneweddate date default NULL,
      return char(4) default NULL,
      renewals tinyint(4) default NULL,
      timestamp timestamp(14) NOT NULL,
      KEY issuesborridx (borrowernumber),
      KEY issuesitemidx (itemnumber),
      KEY bordate (borrowernumber,timestamp)
    ) TYPE=MyISAM;
    The line which will cause an error should be
    Code:
      `return` char(4) default NULL,

SHARE:
+ Post New Thread

Similar Threads

  1. Automated CMIS Backup
    By mark80 in forum MIS Systems
    Replies: 10
    Last Post: 30th September 2008, 12:40 PM
  2. MySQL Backup with phpMyAdmin
    By mark80 in forum How do you do....it?
    Replies: 5
    Last Post: 20th June 2007, 12:15 PM
  3. Automated PC Naming
    By Mr_M_Cox in forum Windows
    Replies: 8
    Last Post: 31st May 2007, 06:40 PM
  4. Automated Mac Address Retrieval
    By Kained in forum How do you do....it?
    Replies: 12
    Last Post: 27th March 2007, 03:25 PM
  5. Automated build testing
    By ITWombat in forum Windows
    Replies: 0
    Last Post: 20th January 2007, 11:54 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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