[HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

[fribse]

The find command can take quite a lot of time, so it's not viable to run it every time the backup is to be done.
But there might be another way, look in your pm

[jm45]

ok, i should have thought about the time needed at every launch
PM answered

[fribse]

Hi jm45

Thanks for the feedback, it is much appreciated, the script is updated again, please note the change in the archive name of old backups.

[jm45]

everything is fine, nothing to change in the script,

every paths are well found.
nice job

[jm45]

now:
once the backup is done, i get files.sql as
(example with the database of my forum )

/share/HDA_DATA/Qbackup/mysql/forum.sql

then what to do with this file if i wish to restore ?

a cp should no fit ...

hence /share/HDA_DATA/.@mysql/forum/ contains a lot of files like x.frm x.MYD x.MYI

[fribse]

You are absolutely right.
The restore procedure was a relick of previous methods to do backup.
You import the database via phpMyAdmin, the procedure has been updated.

[jm45]

brillant! fribse.

[fribse]

I updated the script to a bit better error handling, and also cleaned up the code a bit.

[jm45]

latest v1.8 tested,
all working fine

just a cosmetic about output log:
maybe it would be usefull to change text, and remind the full path for oldmysql folder
changing:
"Compressing old backup to 090826.tar.gz"
..
"Done compressing old backup"
as
Compressing previous backup to /share/HDA_DATA/Qbackup/oldmysql/090826.tar.gz
..
Done compressing previous backup

-------------------------------------------------------------------------------------
Cleaning out old backups. Keeping the last 15 backups

Compressing old backup to 090826.tar.gz

/share/HDA_DATA/Qbackup/mysql/
/share/HDA_DATA/Qbackup/mysql/mysql.sql
/share/HDA_DATA/Qbackup/mysql/test.sql
/share/HDA_DATA/Qbackup/mysql/forum.sql

Done compressing old backup

Backing up current databases

Backing up MySQL database mysql
Backing up MySQL database test
Backing up MySQL database forum

All databases backed up

#

[bramschats]

Hello,

I have installed the script as discribed but since it all is nearly out of my league i have a few questions.
1. how can manually start the script to see if the backup is working?
2. how can i see in the eventlog or something if everything is backed up correctly?

I use wordpress and i need to backup the databases!

Hope someone can help me with this one.

[fribse]

No entry in the eventlog.

But you can run it from a SSH session, like any other command.

Log in with Putty username admin and your admin password.
type the full path, and commandname (including .sh), and it will show what happens on the screen, and also give errors out.
If you see .sql files in the share you have chosen to backup to (under the mysql folder), then the backup is running.

[bramschats]

What is wrong if i am getting the following message.


I think i have done everything right.

Code: Select all

[~] # /opt/bin/mysqlbackup.sh
ERROR mysqldump command not found. Please use the command 'find / -name mysqldump' and send the result to the author

Code: Select all

[~] # find / -name mysqldump
/mnt/ext/opt/mysql/bin/mysqldump

what do i have to do to solve this.....

One other thing i do not understand:
"Create the mysqlbackup.sh file with pspad.exe on one of your normal shares"

thanks in advance.

[fribse]

Answered in PM

[fribse]

The script was updated to take care of the TS-219 without raid, and it will also create the dir's under the share itself now, including some error checking.

[jm45]

Hi,
V2.0 tested ok
on my TS-109 II firmware version: 3.1.0 Build 0708T

Backup Successfull