meta data for this page
Databases
apt-get install automysqlbackup autopostgresqlbackup backupninja
backupninja
Add to /etc/backup.d/20.mysql
nodata = performance_schema.cond_instances
add –skip-events parameter to mysqldump call in file /usr/share/backupninja/mysql
... DUMP="$DUMP_BASE $ignore $db --skip-events" ...
Add to /etc/my.cnf
[mysqldump] ... ignore-table=mysql.event ignore-table=performance_schema.cond_instances ignore-table=performance_schema.events_waits_current ignore-table=performance_schema.events_waits_history ignore-table=performance_schema.events_waits_history_long ignore-table=performance_schema.events_waits_summary_by_instance ignore-table=performance_schema.events_waits_summary_by_thread_by_event_name ignore-table=performance_schema.events_waits_summary_global_by_event_name ignore-table=performance_schema.file_instances ignore-table=performance_schema.file_summary_by_event_name ignore-table=performance_schema.file_summary_by_instance ignore-table=performance_schema.mutex_instances ignore-table=performance_schema.performance_timers ignore-table=performance_schema.rwlock_instances ignore-table=performance_schema.setup_consumers ignore-table=performance_schema.setup_instruments ignore-table=performance_schema.setup_timers ignore-table=performance_schema.threads
automysqlbackup
Default debian configuration should be enough. Pleae test if script has access to mysqldatabase by issuing command:
mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES"
You can customize configuration:
- /etc/default/automysqlbackup
BACKUPDIR="/var/lib/automysqlbackup" DBNAMES="all" DBEXCLUDE="information_schema performance_schema events"
Issues
ERROR | mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'debian-sys-maint'@'localhost' for table 'cond_instances' when using LOCK TABLES |
SOLUTION | Add DBNAMES and DBEXCLUDE configuration values like in example above. |
ERROR | Warning: Skipping the data of table mysql.event. Specify the –events option explicitly. |
SOLUTION | See above |
Default configuration for Debian is using debian-sys-maint user to get access to database. This user cannot get access to information_schema database.
autopostgresqlbackup
# Password # create a file $HOME/.pgpass containing a line like this # hostname:*:*:dbuser:dbpass # replace hostname with the value of DBHOST and postgres with. # the value of USERNAME
Default $HOME for user postgres is /var/lib/postgresql
Pleae test if script has access by issuing command:
mysqldump --defaults-file=/etc/mysql/debian.cnf --all-databases --no-data
You can customize configuration:
- /etc/default/autopostgresqlbackup
BACKUPDIR="/var/lib/autopostgresqlbackup"