One common mistake when we configure a server is forgot run backups, No enough time and other excuses can convince you that make a backup of a new server is not a priority, but sometimes a hipervisor fails and recover data from your virtual machine is hard or impossible. Trust me i saw a lot of unrecoverable databases in development environments unrecoverable after a power outage or hardware fail.
There are a big set of possible ways to backup mysql databases, it depends of several factors like database size, number of databases, reliability …
This tutorial will covered a basic backup for a database server with a small number of small – medium size databases.
Suppose that you have a MariaDB, Percona or Mysql service running and secured,
1 step create a mysql backup user.
- mysql -uroot -p
- grant select, insert, update, create, drop, reload, shutdown, alter, super, lock tables, replication client on *.* to ‘zrmbackup’@’localhost’ identified by ‘pass123’;
- flush PRIVILEGES;
verify permissions
- show grants for ‘zrmbackup’@’localhost’;
2 step install Mysql-zrm
yum install epel-release
yum install MySQL-zrm.noarch
3 Step create a backup dir
mkdir /var/backup
*trick in my case I mounted a nfs share in this directory.
4 Step configure Mysql-zrm
We need edit mysql-zrm config file
vim /etc/mysql-zrm/mysql-zrm.conf
001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037 038 039 040 041 042 043 044 045 046 047 048 049 050 051 052 053 054 055 056 057 058 059 060 061 062 063 064 065 066 067 068 069 070 071 072 073 074 075 076 077 078 079 080 081 082 083 084 085 086 087 088 089 090 091 092 093 094 095 096 097 098 099 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 | ## Template for Zmanda Recovery Manager for MySQL configuration file## Global configuration file is /etc/mysql-zrm/mysql-zrm.conf# The file should be copied to /etc/mysql-zrm/<backup set name>/mysql-zrm.conf# if backup set specific modifications are required.## MySQL ZRM configuration file describes the backup configuration for# a backup set. This file is organized into five sections for convenience# - Backup parameters,# - Databases/tables that are part of backup set,# - MySQL server parameters# - ZRM parameters.# - ZRM plugin parameters.## For more information about Zmanda Recovery Manager for MySQL, please# see mysql-zrm(1) and/or Administration manual at Zmanda Network.### Any line starting with '#' are comments and will be ignored## Backup parameters## Backup comment. This is a text string which can be retrieved# using the mysql-zrm-reporter(1) tool. You can store some notes# about the backup set.# This parameter is optional and has no defaults.#comment=This is a comment# Backup level. It can be full or incremental# Use 0 for full and 1 for incremental backups# This parameter is optional and default value is full backup.#backup-level=0# Backup method# Values can be "raw" or "logical". Logical backup are backups using# mysqldump(1) tool# This parameter is optional and default value is "raw".#backup-mode=logical# Size of LVM snapshot. This parameter is optional and is required only# for "raw" backup-mode and if the MySQL database data are stored in# LVM logical volumes.# A size suffix of k for kilobyte, m for megabyte, g for gigabyte# or t for terabyte##lvm-snapshot=10M# specifies the plugin for snapshot operations##snapshot-plugin="/usr/share/mysql-zrm/plugins/lvm-snapshot.pl"# Specifies the type of backup# Values can be "regular" or "quick".# Quick backup type uses the snapshot itself as the backup# without copying the data from the snapshot volume#backup-type=quick# Directory to which backups are done. All backups are stored under this# directory. This parameter is optional and the default# value is "/var/lib/mysql-zrm"#destination=/var/backup/database# Specifies how long the backup should be retained. The value can be# specified in days (suffix D), weeks (suffix: W), months (suffix: M) or# years (suffix Y). 30 days in a month and 365 days in a year are assumed# This parameter is optional and the default is the backups are retained# forever.#retention-policy=1Y# This parameter should be set to 1 if MySQL ZRM backups are being on done on a# MySQL replication slave.#replication=1# This parameter should be set to 1 if backups should be compressed. If this# parameter is set, gzip(1) command is used by default. If different# compression algorithm should be used, it must be set in "compress-plugin"# parameter. Default: There is no data compression.compress=1# This specifies the program to be used for compression. The "compression"# parameter must be set for this parameter to be used. The compression# command should also support -d option for uncompress backup images. If# value is not specified then gzip(1) is used for compression.#compress-plugin=/usr/bin/gzip# This parameter should be set to 1 if backups should be encrypted.# The "encrypt-plugin" parameter must be configured. Default: There is no# data encryption.#encrypt=1# This parameter specifies that the program that should be used for# backup data encryption. "decrypt-option" parameter should also be specified.#encrypt-plugin="/usr/share/mysql-zrm/plugins/encrypt.pl"# This specifies the option to be passed to the encryption# program specified as "encrypt-plugin" parameter for decryption.#decrypt-option="-d"## Databases/Tables in the backup set## One of the "all-databases" or "databases" or "tables"/"database" parameters# should be specified. If none of the them are specified, "all-databases"# is assumed.## This parameter should be set to 1 if all databases are part of this backup set#all-databases=1# List of databases that are part of this backup set. Multiple database# names are separated by space character. This parameter is ignored if# "all-databases" is set 1.##databases=wikidb forums# List of specific tables that are part of this backup set. This parameter# should not be specified if all tables in the databases in "databases"# parameter are part of the backup set. Multiple table names should be# separated by space character. The database to which these tables belong# to should be specified in "database" parameter.##tables=text user page#database="wikidb"## The list of databases or tables that are excluded from the backup if the# database name or table name matches the pattern. Wildcard characters *, ?,# [, ] are supported. See mysql-zrm-backup man page for details## exclude-pattern=<pattern># MySQL server parameters## MySQL database user used for backup and recovery of the backup set.# This parameter is optional. If this parameter is not specified, values from# my.cnf configuration file.#user="zrmbackup"# MySQL database user password.# This parameter is optional. If this parameter is not specified, values from# my.cnf configuration file or no password is used.#password="pass123"# Fully qualified domain name of the MySQL server.# This parameter is optional. If this parameter is not specified, values from# my.cnf configuration file.##host="localhost.company.com"# Port to which the MySQL server is listening to. This parameter is optional# and default value is 3306##port=3306#Name of Socket file that can be used for connecting to MySQL##socket=/var/lib/mysql/mysql.sock# ssl-options are arguments that are passed to MySQL client commands# for SSL connection to the MySQL server. This parameter is optional and is# required only if MySQL server allows SSL connections.##ssl-options="--ssl --ssl-ca=file1 --ssl-cert=file2 --ssl-key=file3"# This can be set to specify that mysqldump should dump stored routines also.# This paramter is optional and the default is that stored routines are# not dumped my mysqldumproutines=1# This can be set to 0 to specify that the --single-transaction# should not be used for mysqldumpsingle-transaction=1# This can be used to specif the character set name that mysqldump should# use as default. This parameter is optional.# If not specified utf8 is used as the default character set.#default-character-set=latin1# Directory where MySQL commands can be found. The parameter is optional.##mysql-binpath="/opt/lampp/bin"# Directory where MySQL binary logs can be found. The parameter is optional.##mysql-binlog-path="/var/log/mysql"# Directory to use for temporary storage. This parameter is optional##tmpdir=/tmp## ZRM parameters## This parameter controls the verbosity of MySQL ZRM logging. The MySQL ZRM logs# are available at /var/log/mysql-zrm/mysql-zrm.log. This parameter is optional# default value is 0 (less verbose).# The valid values are 0 and 1#verbose=1# After a backup run the backup report is emailed to the mailto address# This parameter is optional and default behavior is not to send mail# notifications.#mailto="backups@youremail.com"# Policy on when the mail should be sent# Values can be "always", "never" or "only-on-error"mail-policy=only-on-error# The list of backup reports that are generated after each backup run if# "html-report-directory" parameter is specified.# If this parameter is not specified, "backup-status-info" report is generated.# Valid report names are : backup-method-info, backup-status-info,# backup-retention-info, backup-performance-info,# restore-full-info, restore-incr-info,# replication-info, backup-app-performance-info# See mysql-zrm-reporter(1) for details of backup reports.# Multiple report names should be separated by ",".#html-reports=backup-status-info# Directory in which Text/HTML reports will be created by mysql-zrm-reporter(1)# tool. If this parameter is specified, the mysql-zrm-reporter(1) creates the# backup reports in this directory after each successful or unsuccessful# backup run.# Text reports will be created under "Text" sub-directory# HTML reports will be created under "Html" sub-directory##html-report-directory=/var/www/mysql-zrm/reports/# If backup reports are required as RSS feed, "webserver-url" parameter must# be specified. The value must be set to a valid location on the web server# in which HTML reports are located and that URL can be used by# administrator/user to browse HTML reports and can get to the RSS feeds.# If this parameter is not specified, backup reports are not generated as# RSS feeds. The list of reports that are available as RSS feed is specified# in "html-reports".##webserver-url=http://www.company.com/reports/html/# Location of RSS header file. Administrators can customize RSS channel# properties using this file. A template for RSS header is available in# /usr/share/mysql-zrm/plugins/RSS.header file. Location of RSS header# must be provided if "webserver-url" is specified.##rss-header-location=/etc/mysql-zrm/## ZRM plugin parameters.# ZRM provides plugin interfaces to allow MySQL administrators to customize# the backup to their environment.## COPY plugin: Only one copy-plugin must be configured for a backup set.## Socket Copy plugin is to used to transfer backup files from MySQL server to# the machine running ZRM for MySQL with sockets.## Please read the Notes at /usr/share/doc/mysql-zrm/README-plugin-socket-copy##copy-plugin=/usr/share/mysql-zrm/plugins/socket-copy.pl# SSH Copy plugin is to used to transfer backup files from MySQL server to# the machine running ZRM for MySQL with ssh## Please read the Notes at /usr/share/doc/mysql-zrm/README-plugin-ssh-copy##copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl# PRE-BACKUP plugin: Plugin that will be called before a backup run for# the backup set.#pre-backup-plugin="/usr/share/mysql-zrm/plugins/pre-backup.pl"# Set of parameters passed to the pre-backup-plugin. These parameters are# passed to "pre-backup-plugin" before a backup run for the backup set.# "pre-backup-plugin" parameter must be specified.#pre-backup-plugin-options="option1 option2"# POST-BACKUP plugin: Plugin that will be called after a backup run for# the backup set.#post-backup-plugin="/usr/share/mysql-zrm/plugins/post-backup.pl"# Set of parameters passed to the post-backup-plugin. These parameters are# passed to "post-backup-plugin" after a backup run for the backup set.# "post-backup-plugin" parameter must be specified.#post-backup-plugin-options="option1 option2"# PRE-SCHEDULER plugin: Plugin that can be used to dynamically determine the# start time for a backup run.#pre-scheduler-plugin="/usr/share/mysql-zrm/plugins/pre-scheduler.pl"# ZRM Plugin configuration parameters# This parameter is used by the encrypt plugin and# specifies the file containing the passphrase.#passfile="/tmp/a.pass"# This parameter is used by ssh-plugin.pl plugin to specify the user to be# used to ssh to the remote host#ssh-user="root"# This parameter is used by the ssh-copy.pl and socket-copy.pl plugins# to specify the location of mysql client binaries on the remote host.#remote-mysql-binpath="/usr/bin"# This parameter is used by the socket-copy.pl plugin to specify the port# to be opened on the remote host.#socket-remote-port="25300"# This parameter is used by the windows-copy.pl plugin to specify the port# to be opened on the windows machine during backup#windows-backup-port="10080"# This parameter is used by the windows-copy.pl plugin to specify the port# to be opened on the windows machine during restore#windows-restore-port="10081" |
Step 5. Launch a backup
mysql-zrm-scheduler -now