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

[jm45]

Hi,
after NAS reboot is done, backuppass file is no more in my /etc
is it on purpose we have to re-create it?
context: TS-109 II firmware version: 3.1.0 Build 0708T

[fribse]

Hmm, no. Darned, now where should we put it?

[jm45]

inside the script itself ....

[fribse]

Move the password file to /etc/config, I'll update the script, and the instructions...

[jm45]

Hi,
new 2.1 script tested ok; "Backup Successfull"
(but i have not still done the reboot test to check if backuppass is kept)

just a remark about old comment remaining:
MYSQL_USER="backup" # User for accessing the databases, password stored in the file /etc/backuppass

[fribse]

Ok, fixed Didn't do a new version, just changed the comment. Thanks.

[techman]

Well done Job! Thanks.

[hellsblade]

Great script. At last. This should be integrated in the admin interface by default. Qnap guys... do the right thing.
I have a question also. For some reason the script claims there is an arc database, but it doesn't exist.
When i look with mysql admin there its not there. Any idea?

mysqldump: Got error: 1044: Access denied for user 'backup'@'localhost' to database 'arc' when selecting the database
ERROR creating new backup when trying to access the database arc

Rgrds,
hellsblade

[fribse]

Thankyou for the kind words, nice to know that somebody else than me, and a few select users, benefits of it.

You have a directory under the mysql database directory called arc.
There should only be databases under the mysql database directory!
If you've made a backup or archive and put it in a directory there, move it elsewhere.
It doesn't use a list of databases, it just looks for the available directories.

[hellsblade]

Thankyou for the kind words, nice to know that somebody else than me, and a few select users, benefits of it.

You have a directory under the mysql database directory called arc.
There should only be databases under the mysql database directory!
If you've made a backup or archive and put it in a directory there, move it elsewhere.
It doesn't use a list of databases, it just looks for the available directories.

There is indeed an arc directory in one of the database directories (PHPBB). It seems it has something to do with this bug. http://bugs.mysql.com/bug.php?id=17823, because i never put i there. I moved it out and i hope it does'nt come back.

Maybe its an idea to let the script send an email when there's an error encountered.

Rgrds,
Hellsblade

[fribse]

I'm looking into getting the list of databases from the mysql command, and I've so far found a way, but for some reason, the while loop fails, hmmm.

Code: Select all

# Listing all the databases

echo 'show databases;' | mysql -u username--password=password | sed '1d' | while read LINE
do
  echo "Backing up database $LINE to /mysql/$LINE.sql"
done

Anybody that can tell me what I'm doing wrong here?

I would love to be able to send a message to the log of the server, but I'm not aware of how to do that...

[Don]

Hi fribse,

Going to give your script a try. Great work BTW.

I would love to be able to send a message to the log of the server, but I'm not aware of how to do that...

Use the following command to write to the system log file

Code: Select all

/sbin/write_log "your message here" [1 | 2 | 4]

1 = an error message
2 = a warning message
4 = an informational message

Don

[Don]

Hi

I know you were looking for a way to determine the default volume. This will work.

Code: Select all

/sbin/getcfg Public path -f /etc/config/smb.conf -u | cut -f 3 -d '/'

Don

[Don]

hi,

Why does the destination have to be a share?

Don

[fribse]

Hi fribse,

Going to give your script a try. Great work BTW.

I would love to be able to send a message to the log of the server, but I'm not aware of how to do that...

Use the following command to write to the system log file

Hi Don

Excellent, just what I was looking for, I'll be incorporating it right away

Thanks