Difference between revisions of "AutoMysqlBackup"
(→Mailto) |
|||
(33 intermediate revisions by 6 users not shown) | |||
Line 1: | Line 1: | ||
− | + | {{Languages|AutoMysqlBackup}} | |
==Automysqlbackup== | ==Automysqlbackup== | ||
[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. | [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. | ||
Line 10: | Line 10: | ||
*Configurable backup rotation (no db command yet, you have to do it manualy) | *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) | *Incremental database backups (no db command yet, you have to do it manualy) | ||
+ | |||
+ | === Version === | ||
+ | {{ #smeversion: smeserver-automysqlbackup}} | ||
+ | {{ #smeversion: automysqlbackup}} | ||
==Maintainer== | ==Maintainer== | ||
Line 15: | Line 19: | ||
==Installation== | ==Installation== | ||
− | For | + | ====For SME8==== |
− | This contrib is | + | 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== | ==smeserver Panel== | ||
Line 35: | Line 58: | ||
If you want to see the db configuration of automysqlbackup. | If you want to see the db configuration of automysqlbackup. | ||
config show 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=== | ===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. | It is the folder where you save you database backup '''(default is /root/backup/db)'''. This is the architecture of your backup folder. | ||
Line 43: | Line 82: | ||
If you want to save in another place, you have to do the command below. | If you want to save in another place, you have to do the command below. | ||
− | config setprop automysqlbackup Backupdir path/to/other/folder | + | config setprop automysqlbackup Backupdir /path/to/other/folder |
you have to create manually your new backup directory | you have to create manually your new backup directory | ||
mkdir -p /path/to/other/folder | mkdir -p /path/to/other/folder | ||
Line 50: | Line 89: | ||
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 | 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 | 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=== | ===Encryption=== | ||
Line 81: | Line 150: | ||
===Mailto=== | ===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 | config setprop automysqlbackup Mailto foo@foo.com | ||
Line 90: | Line 162: | ||
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}} | {{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== | ==Manual Settings== | ||
Line 118: | Line 198: | ||
and do this to extract the content | and do this to extract the content | ||
bunzip2 <mail_attachment_2011-08-13_13h15m.tar.bz2 | pax -rv | 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== | ==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]] | [[Category: Contrib]] |
Latest revision as of 15:21, 11 January 2024
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
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.
#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
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.
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
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
Changelog
Only released version in smecontrib are listed here.
- Add create_database to backups
- Remove myserver.conf template expansion as template no longer exists
- Forgot to change the version on the Changelog line below
- Bump automysqlbackup requires
- Add debug patch to conf files