Tag Archives: backup

23 Mar

MySQL Backup setup, automation using Holland, mysqldump on Ubuntu

This post will guide you to set up and automate the MySQL logical backups using mysqldump on Ubuntu Linux. We will set-up MySQL backup using mysqldump and automate it with Holland backup framework on Ubuntu.

Towards the end of the post, you have the steps to be executed in a single block which includes steps for Xtrabackup configuration. Though this post is for Ubuntu Linux with holland + mysqldump while we already have an old post to set-up Holland with Xtrabackup on Redhat / CentOS.

Let’s begin the execution.

Installations for Holland Backup Framework on Ubuntu

Read More
27 May

xtrabackup_51: not found & no ‘mysqld’ group in MySQL options

Recently I happen to setup a new MySQL instance with my tools – a standard MySQL 5.1+, xtrabackup setup and last-hotbackup.tar.gz.

To restore from the backup we used xtrabackup binaries and ran into issues following standard commands (assuming no changes):

To prepare the backup I used apply-log as follows:

$] innobackupex-1.5.1 --defaults-file=/usr/local/mysql/data/backup-my.cnf --apply-log  /usr/local/mysql/data --ibbackup xtrabackup_51

Continue Reading…

17 Dec

Is your backup complete? – backup MySQL users privileges / grants – pt-show-grants

Everybody knows the criticality of backup. You might have had your database dumped, data directory copied, flat files exported and even config and binary log files backed-up. But did you backup your MySQL user privileges?

One can easily forget to backup the user privileges but it is much easier to do and you should backup your MySQL server user privileges / grants regularly.

1. Backup MySQL user privileges using mysqldump:

$] mysqldump -uUSER -pPASS --databases mysql > mysql.sql
Continue Reading…

12 Nov

Scheduled Backup MySQL Administrator & Windows Scheduler – Odd

Creating scheduled backup using administrator is an easy task.

Follow the GUI and you’re done. But I observed something different!

MySQL Version: 5.0.83 Community

MySQL Administrator: 1.2.17

Machine: Intel P4, Windows XP, Enough RAM 🙂

Logs: General, Error & irrelevant here though Slow query.

Prologue: Administrator performs scheduled backup properly in normal scenario.

What I was watching: Administrator’s Auto-Backup behaviour with windows Scheduler service and tracing steps out.

Some facts:

MySQL Administrator stores backup project’s details in <PROJECT-NAME>.mbd files in xml format under directory

C:\Documents and Settings\<USER-NAME>\Application Data\MySQL

Along with it it also adds a scheduled task with name <PROJECT-NAME>.

“C:\Program Files\MySQL\MySQL Tools for 5.0\MySQLAdministrator.exe” “-UDC:\Documents and Settings\<USER-NAME>\Application Data\MySQL\” “-cbkupconn” “-bp1” “-btC:\” “-bx1”

If you find trouble getting automated backup work, make sure your Scheduler [Task Scheduler] service is running.

Start >> Run >> services.msc

MySQL Administrator does not log these events unless specified explicitly.

To enable logging:

Menu: Tools >> Options
Category: Administrator
In Backup Section:
  • Tick checkbox “Write log file for scheduled backup” and spcify path.
  • Tick checkbox “Log Row Processing Progress” and specify number.

To view Scheduled task’s log you may:

Start  >>  program  >>  accessasory  >>  System Tools  >>  Scheduled Tasks
Click on menu: Advance >> View Logs
Open Windows/SchedLgU.Txt

My observation:

Whenever I keep task scheduler Enabled & Started, I could see updates in logs of Scheduled task and also in Event Viewer. For backups you get logged in General Query Logs.

But with my surprise I’m not able to get the MySQL Administrator’s log file (with default setting).

Also if scheduler is Disabled & Stopped, Nothing is getting logged. Neigher in logs of Scheduled task nor in Event Viewer.

And again there is no MySQL Administrator’s log file getting generated. Also this even is not getting logged in to Error Log!

Later I read the bug: http://bugs.mysql.com/bug.php?id=28821

I tried to change the path from default, C:\ to D:\SOMEDIRECTORY. And with my surprise MySQL Administrator logged successfully executed scheduled tasks to log.

Next observation after disabling Scheduler(Task Scheduler) Windows Service I made was, backup had failed but Administrator haven’t logged it into C:\MySQLAdminBackupLog.txt !!

Even now I have “Log file path” as D:\ and I’m reading log updates in C:\MySQLAdminBackupLog.txt. There is nothing in D:\!

Is it the problem with way Administrator writes to .mdb file (bug?) or am I doing something wrong!

-- Kedar Vaijanapurkar --