MySQL automated backup

[contink]

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.

[Ric_]

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

[contink]

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...

[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

[contink]

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).

[CyberNerd]

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

[unixman]

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

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

  `return` char(4) default NULL,