Difference between revisions of "AutoMysqlBackup"

From SME Server
Jump to navigationJump to search
 
(72 intermediate revisions by 6 users not shown)
Line 1: Line 1:
 +
{{Languages|AutoMysqlBackup}}
 
==Automysqlbackup==
 
==Automysqlbackup==
−
AutoMySQLBackup with a DB configuration will create Daily, Weekly and Monthly backups of one or more of your MySQL databases from one or more of your MySQL servers.
+
[http://sourceforge.net/projects/automysqlbackup/ AutoMySQLBackup] associates with DB configuration will create Daily, Weekly and Monthly backups of your MySQL databases. At least there is no panel in the server-manager but the configuration is quite simple with few commands listed above, handle by "config setprop automysqlbackup". I'm fairly sure that you do not have to modify huge options except the mail where logs and files are sent.
−
At least there is no panel in the server-manager but the configuration is quite simple with few commands listed above, handle by "config setprop automysqlbackup".
+
With this contribs you save your mysql databases in a simple way.
  
 +
Other Features include:
 +
*Email notification of backups
 +
*Databases split and sent by mails
 +
*Backup Compression and Encryption
 +
*Configurable backup rotation (no db command yet, you have to do it manualy)
 +
*Incremental database backups (no db command yet, you have to do it manualy)
 +
 +
=== Version ===
 +
{{ #smeversion: smeserver-automysqlbackup}}
 +
{{ #smeversion: automysqlbackup}}
 +
 +
==Maintainer==
 +
[mailto:stephdl@de-labrusse.fr stephdl] Stéphane de Labrusse AKA [[User:stephdl|Stephdl]]<br />
 +
 +
==Installation==
 +
====For SME8====
 +
This contrib is in the [[Stephdl|'''stephdl''']] repository, you have to enable it before to install the contrib
 +
 +
</noinclude>
 +
db yum_repositories set stephdl repository \
 +
BaseURL http://mirror.de-labrusse.fr/smeserver/\$releasever \
 +
EnableGroups no GPGCheck yes \
 +
Name "Mirror de Labrusse" \
 +
GPGKey http://mirror.de-labrusse.fr/RPM-GPG-KEY \
 +
Visible yes status disabled
 +
 +
 +
<noinclude>
 +
After adding it to the database updating the configuration file is required:
 +
signal-event yum-modify
 +
 +
yum install --enablerepo=stephdl smeserver-automysqlbackup
 +
config set UnsavedChanges no
 +
 +
====For SME9 and SME10====
 +
 +
yum install --enablerepo=smecontribs smeserver-automysqlbackup
 +
config set UnsavedChanges no
 +
 +
You can also install pigz for multi CPU support which can speed up your backups and remove the warning from the status email:
 +
 +
yum --enablerepo=epel install pigz
 +
 +
==smeserver Panel==
 +
 +
There is no panel for manage this contrib, you  have to play with db configuration command but normally the options by default could be sufficient.
 +
 +
==How to launch the backup==
 +
You can launch manually the database backup by a command line with automysqlbackup in a root terminal.
 +
automysqlbackup
 +
otherwise every night, a cron job start à 4h00 AM to save your databases.
 +
==DB command option==
 +
If you want to see the db configuration of automysqlbackup.
 +
config show automysqlbackup
 +
===Time===
  
−
Other Features include:
+
You can adjust the time when start the cronJob
−
- Email notification of backups
 
−
- Backup Compression and Encryption
 
−
- Configurable backup rotation
 
−
- Incremental database backups
 
  
−
===Maintainer===
+
* Each 30 Minutes
−
[[User:stephdl|Stephane de Labrusse]]
+
  config setprop automysqlbackup Hour '*' Minute '*/30'
 +
  signal-event automysqlbackup-update
  
−
mailto:stephdl@de-labrusse.fr
+
* Each Hour
 +
  config setprop automysqlbackup Hour '*/1' Minute 1
 +
  signal-event automysqlbackup-update
  
−
===Installation===
+
* At 3 Hour Am
−
for the instant you have to download the rpm here and install it by the yum localinstall command
+
  config setprop automysqlbackup Hour 3 Minute 1
 +
  signal-event automysqlbackup-update
  
−
  wget http
+
===Backup directory===
−
  yum localinstall
+
It is the folder where you save you database backup '''(default is /root/backup/db)'''. This is the architecture of your backup folder.
 +
{{note box|keep in mind that the /root folder is saved with the e-smith-backup of your server-manager}}  
 +
  #ls /root/backup/db/
 +
daily  fullschema  latest  monthly  status  tmp  weekly
  
−
===DB command option===
+
If you want to save in another place, you have to do the command below.
  
−
====backup directory====
+
  config setprop automysqlbackup Backupdir /path/to/other/folder
−
default is /root/backup/db
+
you have to create manually your new backup directory
−
  config setprop automysqlbackup Backupdir "path/to/other/folder"
 
−
you have to create your new backup directory
 
 
  mkdir -p /path/to/other/folder
 
  mkdir -p /path/to/other/folder
  
−
====ENCRYPTION====
+
===Backup a local directory===
−
If you wish to encrypt your backups using openssl (no is default)
+
the possibility is given to you to save a local directory by automysqlbackup. I suppose that you can not use it for large folder but for a web site with its database it could be useful. not activated by default
 +
config setprop automysqlbackup Backup_local_files /path/to/folder
 +
 
 +
===Mysql53, Mysql55, Mysql57, MariaDB===
 +
 
 +
With SCL on SME you may be running other database versions.
 +
 
 +
You must make sure you have a backup user for the newer databases
 +
 
 +
Creating backup user for default mysql53 database
 +
done
 +
*** If you add mysql55/mysql57 you must add a backup user ***
 +
You can use the password from config show automysqlbackup
 +
e.g.:
 +
mysql55 -e " GRANT EVENT,SELECT,LOCK TABLES ON *.* TO backupuser@'localhost' "
 +
mysql55 -u root -e "SET PASSWORD FOR backupuser@localhost = PASSWORD( ' -from config show automysqlbackup DbPassword- ' ) "
 +
 
 +
We can now choose other databases to backup:
 +
 
 +
mysql53, mysql55, mysql57, mariadb
 +
 
 +
You can enable or disable as follows:
 +
 
 +
config setprop automysqlbackup Mysql55 enabled
 +
signal-event automysqlbackup-update
 +
 
 +
====For Koozali SME v10====
 +
 
 +
The default Maria 5.5 is included. To add Maria 10.5 added via the contrib:
 +
 
 +
config setprop automysqlbackup Mymaria105 enabled
 +
signal-event smeserver-automysqlbackup-update
 +
 
 +
===Encryption===
 +
If you wish to encrypt your backups using openssl '''(no is default)'''
 
  config setprop automysqlbackup Encrypt yes
 
  config setprop automysqlbackup Encrypt yes
−
you need to set a password (default is 01234567899876543210)
+
you need to set a password '''(default is 01234567899876543210)'''
−
  config setprop automysqlbackup Dbencrypt_password "your-password"
+
  config setprop automysqlbackup Dbencrypt_password your-password
  
−
=====Decryption=====
+
{{Note box| This option may be useful when you want to save  your databases outside of your server by sending them by mails. keep in mind to change the default password}}
−
To decrypt run (replace gz with bz2 if using bzip2):
+
 
 +
===Decryption===
 +
To decrypt run :
  
 
  openssl enc -aes-256-cbc -d -in encrypted_file_name(ex: *.enc.gz) -out outputfilename.gz -pass pass:your-password
 
  openssl enc -aes-256-cbc -d -in encrypted_file_name(ex: *.enc.gz) -out outputfilename.gz -pass pass:your-password
Line 42: Line 135:
 
example :  
 
example :  
  
−
  openssl enc -aes-256-cbc -d -in daily_horde_2013-04-26_01h41m_Friday.sql.gz.enc -out daily_horde_2013-04-26_01h41m_Friday.sql.gz -pass pass:01234567899876543210
+
  openssl enc -aes-256-cbc -d -in daily_horde_2013-04-26_01h41m_Friday.sql.gz.enc -out daily_horde_2013-04-26_01h41m_Friday.sql.gz \
−
 
+
-pass pass:01234567899876543210
−
====Mailcontent====
 
−
you can choose your type of mail send to the admin (log by default)
 
−
config setprop automysqlbackup Mailcontent "option"
 
  
 +
===Mailcontent===
 +
you can choose your type of mail send to the admin '''(log by default)'''
 +
config setprop automysqlbackup Mailcontent option
 +
option :
 
*log  : send only log file (default)
 
*log  : send only log file (default)
 
*files : send log file and sql files as attachments
 
*files : send log file and sql files as attachments
Line 53: Line 147:
 
*quiet : Only send logs if an error occurs to the MAILADDR.
 
*quiet : Only send logs if an error occurs to the MAILADDR.
  
−
====Mailto====
+
{{Tip box|Use the "'''files'''" option with the Mailcontent db to save your mysql databases in a mailbox outside of your server, moreover they are saved too in a local folder of your server (by default/root/backup/db)}}
−
you can choose the mail  account where you sent your mails (default is admin).
 
  
−
config setprop automysqlbackup Mailto "foo@foo.com"
+
===Mailto===
  
−
====Sizemail====
+
You can choose the mail  account where you sent your mails '''(default is the mailbox admin of your server)'''.
  
−
you can determine the size of mails sent (default is 8000 bytes).
+
Note that this may not work to places like Gmail or Microsoft as it bypasses DKIM etc and their checking is extremely strict.
 +
 
 +
config setprop automysqlbackup Mailto foo@foo.com
 +
 
 +
===Sizemail===
 +
 
 +
you can determine the size of mails sent '''(default is 8000 KB)'''.
 
  config setprop automysqlbackup Sizemail 8000
 
  config setprop automysqlbackup Sizemail 8000
 +
{{note box|keep in mind that a lot of smtp server reject mail bigger than 10 000 KB or 10MB}}
 +
 +
 +
===Debug===
 +
 +
This should enable some logging to be emailed when it runs:
 +
 +
config setprop automysqlbackup debug enabled
 +
signal-event smeserver-automysqlbackup-update
 +
 +
==Manual Settings==
 +
There are many options you can modify in automysqlbackup, so for a personal need you can adjust by hand the files below. make a copy before.
  
−
===RESTORING===
+
cp /etc/automysqlbackup/myserver.conf /etc/automysqlbackup/myserver.conf-old
 +
nano /etc/automysqlbackup/myserver.conf
 +
 
 +
==RESTORING==
 +
===restoring a .sql file===
 
In a root terminal
 
In a root terminal
−
  cd /root/backup/db/ and choose your backup
+
  cd /root/backup/db/  
 +
and choose your backup
 
  gunzip file-name.sql.gz
 
  gunzip file-name.sql.gz
 
Next you will need to use the mysql client to restore the DB from the sql file.
 
Next you will need to use the mysql client to restore the DB from the sql file.
Line 71: Line 187:
  
 
NOTE: Make sure you use < and not > in the above command because you are piping the file.sql to mysql and not the other way around
 
NOTE: Make sure you use < and not > in the above command because you are piping the file.sql to mysql and not the other way around
 +
 +
If you want to play with another user or a remote mysql server you can use this command line
 +
 +
mysql --user=username --pass=password --host=dbserver database < /path/file.sql
 +
 +
===restoring databases sent by mail attachment===
 +
 +
The attachments have been split into multiple files, use this command line to combine them :
 +
cat mail_attachment_2011-08-13_13h15m_* > mail_attachment_2011-08-13_13h15m.tar.bz2
 +
and do this to extract the content
 +
bunzip2 <mail_attachment_2011-08-13_13h15m.tar.bz2 | pax -rv
 +
 +
==Known Issues==
 +
 +
===Skip mysql.event===
 +
* Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. SOLVED in [[bugzilla:8146]]
 +
See http://www.linuxbrigade.com/warning-skipping-data-table-mysql-event/#more-135
 +
See this discussion http://bugs.mysql.com/bug.php?id=68376
 +
 +
=== Empty backupdir key===
 +
* Don't leave this key blank or you may get errors:
 +
 +
config show automysqlbackup Backupdir
 +
 +
The system will try and default to /root/backup/db but you may get issues if it is empty
 +
 +
See bugs [[bugzilla:10655]] and [[bugzilla:10654]]
 +
 +
===Backup databases===
 +
* Some solutions :
 +
 +
if we want to backup databases
 +
mysqldump -uroot --events mysql > /tmp/mysql.sql
 +
or if we don't want to backup them
 +
mysqldump -uroot --events --ignore-table=mysql.event mysql > /tmp/mysql.sql
 +
 +
==Bugs==
 +
 +
Please raise bugs under the SME-Contribs section in {{BugzillaFileBug|product=|component=|title=bugzilla}}and select the smeserver-automysqlbackup component or use
 +
{{BugzillaFileBug|product=SME%20Contribs|component=smeserver-automysqlbackup |title=this link}}.
 +
 +
 +
{{#bugzilla:columns=id,product,version,status,summary |sort=id |order=desc |disablecache=1 |component=smeserver-automysqlbackup |noresultsmessage="No open bugs found."}}
 +
 +
 +
==Changelog==
 +
 +
Only released version in smecontrib are listed here.
 +
 +
{{ #smechangelog: smeserver-automysqlbackup }}
 +
 +
 +
[[Category: Contrib]]

Latest revision as of 15:21, 11 January 2024

Geographylogo.png Languages: 

English


Automysqlbackup

AutoMySQLBackup associates with DB configuration will create Daily, Weekly and Monthly backups of your MySQL databases. At least there is no panel in the server-manager but the configuration is quite simple with few commands listed above, handle by "config setprop automysqlbackup". I'm fairly sure that you do not have to modify huge options except the mail where logs and files are sent. With this contribs you save your mysql databases in a simple way.

Other Features include:

  • Email notification of backups
  • Databases split and sent by mails
  • Backup Compression and Encryption
  • Configurable backup rotation (no db command yet, you have to do it manualy)
  • Incremental database backups (no db command yet, you have to do it manualy)

Version

Contrib 10:
Contrib 9:
smeserver-automysqlbackup
The latest version of smeserver-automysqlbackup is available in the SME repository, click on the version number(s) for more information.


Contrib 10:
Contrib 9:
automysqlbackup
The latest version of automysqlbackup is available in the SME repository, click on the version number(s) for more information.


Maintainer

stephdl Stéphane de Labrusse AKA Stephdl

Installation

For SME8

This contrib is in the stephdl repository, you have to enable it before to install the contrib 


db yum_repositories set stephdl repository \
BaseURL http://mirror.de-labrusse.fr/smeserver/\$releasever \
EnableGroups no GPGCheck yes \
Name "Mirror de Labrusse" \
GPGKey http://mirror.de-labrusse.fr/RPM-GPG-KEY \
Visible yes status disabled


After adding it to the database updating the configuration file is required: 

signal-event yum-modify

yum install --enablerepo=stephdl smeserver-automysqlbackup
config set UnsavedChanges no

For SME9 and SME10

yum install --enablerepo=smecontribs smeserver-automysqlbackup
config set UnsavedChanges no

You can also install pigz for multi CPU support which can speed up your backups and remove the warning from the status email: 

yum --enablerepo=epel install pigz

smeserver Panel

There is no panel for manage this contrib, you have to play with db configuration command but normally the options by default could be sufficient.

How to launch the backup

You can launch manually the database backup by a command line with automysqlbackup in a root terminal. 

automysqlbackup

otherwise every night, a cron job start à 4h00 AM to save your databases.

DB command option

If you want to see the db configuration of automysqlbackup. 

config show automysqlbackup

Time

You can adjust the time when start the cronJob

  • Each 30 Minutes
 config setprop automysqlbackup Hour '*' Minute '*/30'
 signal-event automysqlbackup-update
  • Each Hour
 config setprop automysqlbackup Hour '*/1' Minute 1
 signal-event automysqlbackup-update
  • At 3 Hour Am
 config setprop automysqlbackup Hour 3 Minute 1
 signal-event automysqlbackup-update

Backup directory

It is the folder where you save you database backup (default is /root/backup/db). This is the architecture of your backup folder.

Important.png Note:
keep in mind that the /root folder is saved with the e-smith-backup of your server-manager 


#ls /root/backup/db/
daily  fullschema  latest  monthly  status  tmp  weekly

If you want to save in another place, you have to do the command below. 

config setprop automysqlbackup Backupdir /path/to/other/folder

you have to create manually your new backup directory 

mkdir -p /path/to/other/folder

Backup a local directory

the possibility is given to you to save a local directory by automysqlbackup. I suppose that you can not use it for large folder but for a web site with its database it could be useful. not activated by default 

config setprop automysqlbackup Backup_local_files /path/to/folder

Mysql53, Mysql55, Mysql57, MariaDB

With SCL on SME you may be running other database versions.

You must make sure you have a backup user for the newer databases 

Creating backup user for default mysql53 database
done
*** If you add mysql55/mysql57 you must add a backup user ***
You can use the password from config show automysqlbackup
e.g.: 
mysql55 -e " GRANT EVENT,SELECT,LOCK TABLES ON *.* TO backupuser@'localhost' " 
mysql55 -u root -e "SET PASSWORD FOR backupuser@localhost = PASSWORD( ' -from config show automysqlbackup DbPassword- ' ) "

We can now choose other databases to backup:

mysql53, mysql55, mysql57, mariadb

You can enable or disable as follows: 

config setprop automysqlbackup Mysql55 enabled
signal-event automysqlbackup-update

For Koozali SME v10

The default Maria 5.5 is included. To add Maria 10.5 added via the contrib: 

config setprop automysqlbackup Mymaria105 enabled
signal-event smeserver-automysqlbackup-update

Encryption

If you wish to encrypt your backups using openssl (no is default) 

config setprop automysqlbackup Encrypt yes

you need to set a password (default is 01234567899876543210) 

config setprop automysqlbackup Dbencrypt_password your-password


Important.png Note:
This option may be useful when you want to save your databases outside of your server by sending them by mails. keep in mind to change the default password


Decryption

To decrypt run : 

openssl enc -aes-256-cbc -d -in encrypted_file_name(ex: *.enc.gz) -out outputfilename.gz -pass pass:your-password

example : 

openssl enc -aes-256-cbc -d -in daily_horde_2013-04-26_01h41m_Friday.sql.gz.enc -out daily_horde_2013-04-26_01h41m_Friday.sql.gz \
-pass pass:01234567899876543210

Mailcontent

you can choose your type of mail send to the admin (log by default) 

config setprop automysqlbackup Mailcontent option

option :

  • log : send only log file (default)
  • files : send log file and sql files as attachments
  • stdout : will simply output the log to the screen if run manually.
  • quiet : Only send logs if an error occurs to the MAILADDR.


Information.png Tip:
Use the "files" option with the Mailcontent db to save your mysql databases in a mailbox outside of your server, moreover they are saved too in a local folder of your server (by default/root/backup/db)


Mailto

You can choose the mail account where you sent your mails (default is the mailbox admin of your server).

Note that this may not work to places like Gmail or Microsoft as it bypasses DKIM etc and their checking is extremely strict. 

config setprop automysqlbackup Mailto foo@foo.com

Sizemail

you can determine the size of mails sent (default is 8000 KB). 

config setprop automysqlbackup Sizemail 8000
Important.png Note:
keep in mind that a lot of smtp server reject mail bigger than 10 000 KB or 10MB



Debug

This should enable some logging to be emailed when it runs: 

config setprop automysqlbackup debug enabled
signal-event smeserver-automysqlbackup-update

Manual Settings

There are many options you can modify in automysqlbackup, so for a personal need you can adjust by hand the files below. make a copy before. 

cp /etc/automysqlbackup/myserver.conf /etc/automysqlbackup/myserver.conf-old
nano /etc/automysqlbackup/myserver.conf

RESTORING

restoring a .sql file

In a root terminal 

cd /root/backup/db/

and choose your backup 

gunzip file-name.sql.gz

Next you will need to use the mysql client to restore the DB from the sql file. 

mysql database < /path/file.sql

NOTE: Make sure you use < and not > in the above command because you are piping the file.sql to mysql and not the other way around

If you want to play with another user or a remote mysql server you can use this command line 

mysql --user=username --pass=password --host=dbserver database < /path/file.sql

restoring databases sent by mail attachment

The attachments have been split into multiple files, use this command line to combine them : 

cat mail_attachment_2011-08-13_13h15m_* > mail_attachment_2011-08-13_13h15m.tar.bz2

and do this to extract the content 

bunzip2 <mail_attachment_2011-08-13_13h15m.tar.bz2 | pax -rv

Known Issues

Skip mysql.event

  • Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. SOLVED in bugzilla:8146

See http://www.linuxbrigade.com/warning-skipping-data-table-mysql-event/#more-135 See this discussion http://bugs.mysql.com/bug.php?id=68376

Empty backupdir key

  • Don't leave this key blank or you may get errors:
config show automysqlbackup Backupdir

The system will try and default to /root/backup/db but you may get issues if it is empty

See bugs bugzilla:10655 and bugzilla:10654

Backup databases

  • Some solutions :

if we want to backup databases 

mysqldump -uroot --events mysql > /tmp/mysql.sql

or if we don't want to backup them 

mysqldump -uroot --events --ignore-table=mysql.event mysql > /tmp/mysql.sql

Bugs

Please raise bugs under the SME-Contribs section in bugzilla and select the smeserver-automysqlbackup component or use

this link .


"No open bugs found."


Changelog

Only released version in smecontrib are listed here.

smeserver-automysqlbackup Changelog: SME 10 (smecontribs)
2023/06/14 John Crisp 3.0.RC6-32.sme
- Add create_database to backups
2022/10/04 John Crisp 3.0.RC6-31.sme
- Remove myserver.conf template expansion as template no longer exists
2022/08/22 John Crisp 3.0.RC6-30.sme
- Forgot to change the version on the Changelog line below
2022/08/22 John Crisp 3.0.RC6-29.sme
- Bump automysqlbackup requires
2022/08/15 John Crisp 3.0.RC6-28.sme
- Add debug patch to conf files
Retrieved from "https://wiki.koozali.org/index.php?title=AutoMysqlBackup&oldid=42459"