Backing up by script - Solved

4 posts / 0 new
Last post
Backing up by script - Solved

I need to set up regular cqrlog backups (preferably automatic).

The backup on exit doesn't work for me. It is not a priority to fix that because I rarely shutdown/reboot/exit-cqrlog.

I would prefer a backup method that allows me to automate a backup, maybe by middle of the night cron job, or similar.

I have tried this suggestion:

paul@paul:~$ (/usr/bin/mysql -B -N -e 'show databases like "cqr%"' | /usr/bin/xargs /bin/echo -n mysqldump --databases) > /tmp/mycqrlogsdump.sql

Which resulted in:

ERROR 1045 (28000): Access denied for user 'paul'@'localhost' (using password: NO)

So I tried:

paul@paul:~$ (/usr/bin/mysql -B -N -e --password 'show databases like "cqr%"' | /usr/bin/xargs /bin/echo -n mysqldump --databases) > /tmp/mycqrlogsdump.sql

And get the same output:

ERROR 1045 (28000): Access denied for user 'paul'@'localhost' (using password: NO)

So I tried this (password substituted for illustration purposes):

paul@paul:~$ (/usr/bin/mysql -B -N -e --password=pppp 'show databases like "cqr%"' | /usr/bin/xargs /bin/echo -n mysqldump --databases) > /tmp/mycqrlogsdump.sql

And still get the same output again.

So, I tried specifying both user and password in the command:

paul@paul:~$ (/usr/bin/mysql -B -N -e -upaul -ppppp 'show databases like "cqr%"' | /usr/bin/xargs /bin/echo -n mysqldump --databases) > /tmp/mycqrlogsdump.sql

and get the following output:

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database 'show databases like "cqr%"'

So to check I did the following:

paul@paul:~$ mysql -upaul -ppppp

mysql: [Warning] ...... Type '\c' to clear the current input statement.

mysql> show databases;

| Database |
| information_schema |
| cqrlog001 |
| cqrlog002 |
| cqrlog011 |
| cqrlog012 |
| cqrlog013 |
| cqrlog014 |
| cqrlog015 |
| cqrlog016 |
| cqrlog_common |
| mysql |
| performance_schema |
| phpmyadmin |
| sys |
14 rows in set (0.00 sec)

Does anyone know what I am doing wrong in the sql dump command, or is there a better way to backup the cqrlog databases?


Backing up by script

Hi Paul!

Just tried this:
$(/usr/bin/mysql -B -N -ucqrlog -pmypassword -e'show databases like "cqr%"' | /usr/bin/xargs /bin/echo -n mysqldump -ucqrlog -pmypassword --databases) > /tmp/mycqrlogsdump.sql

And it works for me. You dropped username and password between parameter -e and 'sqlcommand'
fix the place to be after -N

You can split command to pieces:
/usr/bin/mysql -B -N -ucqrlog -pmypassword -e'show databases like "cqr%"'

First create list of databases starting with "cqr":
[saku@hamtpad ~]$ /usr/bin/mysql -B -N -ucqrlog -pXXXXX -e'show databases like "cqr%"'
[saku@hamtpad ~]$

Then trying to make whole command based on database list created:

[saku@hamtpad ~]$ /usr/bin/mysql -B -N -ucqrlog -pXXXXX -e'show databases like "cqr%"' | /usr/bin/xargs /bin/echo -n mysqldump -ucqrlog -pXXXXX --databases

I have separated results here (normally they all appear right after commands last word"databases":

mysqldump -ucqrlog -pXXXXX --databases cqrlog001 cqrlog002 cqrlog003 cqrlog004 cqrlog005 cqrlog006 cqrlog007 cqrlog008 cqrlog_common

[saku@hamtpad ~]$

Then place everything between $( ) to get it run as command.

[saku@hamtpad ~]$ $(/usr/bin/mysql -B -N -ucqrlog -pmypassword -e'show databases like "cqr%"' | /usr/bin/xargs /bin/echo -n mysqldump -ucqrlog -pmypassword --databases)

Issuing this will produce whole dump as text to console. You will get lots of text.

Finally add redirecting to file instead adding this to end of command:

> /tmp/mycqrlogsdump.sql

Restoring is the just:

mysql -ucqrlog -pmypassword < /tmp/mycqrlogsdump.sql


Got it

Hi Saku.
Thank you for figuring out my mistake, and explaining how the command is broken down so that I can try the parts, and then understand when I am doing.
This worked for me so I can script is and will probably run it as a cron. Most likely daily with overwriting and weekly with keeping maybe 4 previous as archives.

Paul H
A65DR, G7SLP etc

Backing up by script

Hi Paul!

Now you have a tool to make backups.
Just remember to test restore to see that it really works, and afterwards do it again maybe few times a year.

Before restore test it is good to take a partition dump of HD to external HD. just in case. I have used system rescue cd ( ) to make backups from whole computer 3-5 times per year . And always before system upgrade.

Systemrescuecd is USB stick that boots tiny linux.

After that mount external HD: mount /dev/sdb1 /mnt/backup
Make dump of system HD organization with: fdisk -l >/mnt/backup//partitons.txt
Make copy of boot sectors: dd if=/dev/hda of=/mnt/backup/bootsect bs=512 count=10000
Then from every partiton of HD a fsarchiver dump: fsarchiver savefs /mnt/backup/myarchive1.fsa /dev/sda1

This is a bit "nerd" way. There are also other ways to do backups from whole computer. I think also every linux distro offers it's own backup program.

When it is done it is little bit safer to test mysql restore. Of course you can do restore to another PC running mysql server.

Remember also to save mysql dumps to external USB stick or HD to be saved from PC's HD failure.