QNAP NAS Community Forum

Having been running this script successfully for many months but a week or so ago it started giving this error:

ERROR compressing backup

Having modified the script to remove the redirect of stderr on the tar command and then running the script using putty I found it was giving the error:

/bin/tar: databasename.sql: file changed as we read it

where databasename is the last database.

I think the timings must have changed and it is trying to tar the file before it has properly closed.

I've added a sleep 5 after the line cd $BKUP_P/mysql/ and it now runs without error via putty - hopefully the overnight cron will now also run correctly!

Posting this in case it helps others.

Edit: Confirmed overnight runs without error.

I see, that is odd, thankyou for this input, I'll add it to the next update!

Hi,
i am very grateful for this script. Is there a way to backup specific mysql base? Not all bases always, but to choose witch one I want to backup. To define in scrip what I want to backup.
Maybe you discus it, but I could not find that information.

Thank you.

Hi Telekksna

Thankyou for your kind word, well currently it's not designed to only take a specific DB, and I don't think I'll change the code for it, but it's quite easy to modify it.
In the section 'Listing all the databases...'
You could just change the databases=
to
databases="my database" ;

Best regards
Kenneth

jelv1 wrote:I've added a sleep 5 after the line cd $BKUP_P/mysql/ and it now runs without error via putty - hopefully the overnight cron will now also run correctly

Hi jelv1

Did it solve the problem for good? Then I'll add the bit to the code.

History
Can now be found in the file
changes.zip

Changes.zip is not there any longer. do you have a current change log?

Also:

fribse wrote: ↑Wed Aug 16, 2017 5:33 pm @Toxic17: Sure, good suggestion, I'll consider that, I'll probably build in a switch for those that don't want to use it

did you ever build in a switch to autoreapir/optimise databases before backup in your script?

@Toxic17: Very odd about the changes.zip, I've removed the two files, and added them back in. No I haven't built that in yet. I've tried to build a QPKG, but I must be stupid, because I can't figure out how to make it work

fribse wrote: ↑Sat Jan 05, 2019 7:04 pm I've tried to build a QPKG, but I must be stupid, because I can't figure out how to make it work

Can I help?

Thanks fribse - woohoo finally it works!
-------------------------------------------------- --------------
I got itto work after a few failed attempts on my TS-470 PRO, but had to read almost all of the posts in this thread - hence my brief guide for noobs like me:
1. created a share "backup" with QNAP-shares within my raid6
2. created a database user "nas_backup" with global "Select, Show View and Show Databases" rights
2. copied mysqlbackup.sh untouched to the share and set with WINSCP chmod 700
3. edited mysqlbackup.conf with PSPAD and Format UNIX (LF) - for no carrier return marks in the lines (took me an hour to find the problem )!!!
-> share=backup
-> user=nas_backup
-> pw = **********
->folder=MYSQL
4. copied with WINSCP in etc/conf and set chmod 600
5. went with console "cd /share/backup/" into the directory and tested it with command ./mysqlbackup.sh
6. If an error "/ bin / bash ^ M: bad interpreter: No such file or directory" is reported via the console:
6.1 vi /share/backup/mysqlbackup.conf
6.2: set fileformat = unix
6.3: wq
6.4 back to 5.
7. When the manual backup goes through creating the cronjob through the console
7.1 echo "10 1 * * * /share/backup/mysqlbackup.sh" >> / etc / config / crontab
7.2 crontab / etc / config / crontab
7.2 /etc/init.d/crond.sh restart

Everything should work now

Hi there ,

I am working with the QNAP MySQL backup script, unfortunately, the script is not working as I got this error

Backing up database
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
MySQL Backup: ERROR: creating new backup when trying to access the database



anyone, please explain
I am very thankful if someone can help me.

Kind Regards,
Adnan

@OneCD: You would be more than welcome to help me figure this out

@adnanbutt050: Did you create a user for the database with select rights? Did you create a share for the backup?

@fribse: to get you started, please checkout this generic QPKG template. It can be compiled into a new QPKG, but being a template, won't do anything useful.

Examine the initpackage.sh shell-script file. There's not much to it. It's run by QTS whenever the NAS starts-up and shuts-down and when the user 'starts' or 'stops' a QPKG.

To customise the package, change the values in qpkg.cfg, and edit the package init script.

The package_routines file is used to add extra actions that are only performed when the package is first installed or is about to be removed from the target NAS.

If I was to guess, I'd say you'll need your package to make your dB backup script available for execution and add a new crontab entry - correct?

Yes, that was my idea. Copy the config file, install the script, and create a crontab entry.

No worries, that’s easy enough to do.

Have you picked a package name yet? The internal package name can not contain any whitespace, but the package display name (shown in the App Center) can.

The package init script should also be named after your package. It must be unique, as QTS will create a symlink to it in [/etc/init.d] along with all other installed QPKGs. Your init script name should also be entered in the [qpkg.cfg] file so QTS knows which script filename it should call to start and stop your package.

Hi,

thanks for the great backup script. It's working fine so far but since a few days I'm getting the error: The backup seems to be created fine as far as I can tell. Also I got the following message:
I didn't get any messages before and I didn't change anything realted to mysql or folder permissions.

Any idea where this i comming from?`
Thanks a lot
Dennis