Whats your Backup Solution?

SneakyDave said:
Can you take the site offline with a mysql update? Or do you replace the xenforo index page?
Click to expand...

I do this with the automysql backup script and a few lines added to the nginx config file. I don't recall if that ability was built-in to the script or if I added it later. But basically, I just created a custom 503 (Temporarily Unavailable) page. At the beginning of the script, it copies that file into the webroot. (cp /var/www/error/503.html /var/www/yoursite/) If nginx sees the file present, it rewrites all page requests to point to the custom 503 page instead. The script deletes the 503 page when it's done.
 
p4guru said:
Are you using mydumper with MariaDB 5.2 or 5.5 or 10.0.x ?
Click to expand...
I use mydumper 0.6.0 with MariaDB 5.5. I'm also using xtrabackup 2.1.6 for very large sites, to perform incremental backups. Both packages are available to everyone into Axivo repository. The xtrabackup package I build is specifically designed for MariaDB 5.5 using Percona XtraDB, which is the default implementation in MariaDB. BTW, nobody else provides this specific setup, not that I know of... and I have no idea why. Even Percona (XtraDB creators) offer their xtrabackup rpm's based on a standard MySQL InnoDB implementation, which is really crippled compared to XtraDB.
Code: 
[root@apollo my.cnf.d]# yum --enablerepo=axivo info mydumper
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: centos.mirrors.atwab.net
 * extras: centos.mirror.netelligent.ca
 * updates: www.cubiculestudio.com
Installed Packages
Name        : mydumper
Arch        : x86_64
Version     : 0.6.0
Release     : 1.el6
Size        : 99 k
Repo        : installed
From repo   : axivo
Summary     : High-performance MySQL backup tool
URL         : http://launchpad.net/mydumper
License     : GPLv3+
Description : Mydumper (MySQL Data Dumper) is a high-performance multi-threaded backup and
            : restore toolset for MySQL and Drizzle. The main developers originally worked
            : as Support Engineers at MySQL and this is how they would envisage mysqldump
            : based on years of user feedback.

# yum --enablerepo=axivo info xtrabackup
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: centos.mirrors.atwab.net
 * extras: centos.mirror.netelligent.ca
 * updates: www.cubiculestudio.com
Installed Packages
Name        : xtrabackup
Arch        : x86_64
Version     : 2.1.6
Release     : 1.el6
Size        : 10 M
Repo        : installed
From repo   : axivo
Summary     : Online backup for MariaDB
URL         : http://www.percona.com/software/percona-xtrabackup/
License     : GPLv2
Description : Percona XtraBackup is an online (non-blocking) backup solution for Percona
            : XtraDB storage engines. It features uninterrupted transaction processing during
            : backups for InnoDB, but can also backup MyISAM tables. Percona XtraDB is an
            : enhanced version of InnoDB storage engine, designed to better scale on modern
            : hardware and is the default InnoDB implementation in MariaDB.
 
Last edited:
Interesting about the 0.6 mydumper rpm you might check it out. Edit: doh, remember you only compile for 64bit and not 32bit :)

I know Centmin Mod since .04 release http://centminmod.com/changelog.html has added Percona and Varnish YUM Repos so you can install Percona Xtrabackup if you want.

Hmm didn't know Percona officially provided Xtrabackup RPM are crippled ? got a link to some reading regarding this ? I can only assume if they build against Oracle InnoDB is to be compatible with MySQL 5.6 and 5.7 etc ? As there's still many more installs out there using Oracle MySQL than Percona/MariaDB.
 
p4guru said:
Hmm didn't know Percona officially provided Xtrabackup RPM are crippled ? got a link to some reading regarding this ?
Click to expand...
How is an external link going to tell you that what I said is true?
Just look at their source code, to see how they build it. :)

Your logic is very good, Percona build their packages to cover a wide spread of users. For example, they compile the InnoDB binaries for MySQL 5.1 (/usr/bin/xtrabackup), 5.5 (/usr/bin/xtrabackup_55) and 5.6 (/usr/bin/xtrabackup_56) against default MySQL InnoDB engine, in their distributed rpm. To myself, having xtrabackup built against Percona XtraDB only is useful, because I have the matching MariaDB rpm into repository which uses XtraDB by default:
Rich (BB code): 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3015
Server version: 5.5.34-MariaDB Axivo

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| SPHINX             | YES     | Sphinx storage engine                                                      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)
On the other hand, that would exclude 80% of MySQL users which don't use Percona XtraDB. Which explains the "crippled" word I used previously, XtraDB is more performant than InnoDB in many areas.
 
Last edited:
Well the word 'crippled' had me.. assuming it meant that you couldn't do something with official Percona rpms versus ones compiled specifically against Percona XtraDB... so hence asking for a link to list the crippled features :)
 
p4guru said:
assuming it meant that you couldn't do something with official Percona rpms versus ones compiled specifically against Percona XtraDB...
Click to expand...
I see what you mean now by requesting a link, thanks for explaining. I think they highlight the differences in their documentation, but ya there is a fair amount of differences, or else Percona would not have specific build options for XtraDB only.
 
One of the (many) nice things about MySQL Cluster is it supports hot backups without anything complicated.

I run this command with crontab every night to do a full backup:
Code: 
ndb_mgm -e "START BACKUP"

Ends up taking about 12 minutes to do a full backup (42GB worth of databases), but I guess the time doesn't matter since it's 100% non-blocking.

A little bit of my own code to delete backups older than 1 year or older than 1 week if the backup wasn't done on the first of the month...
Code: 
drwxr-x--- 2 root root 4096 Jan  2 23:02 BACKUP-223
drwxr-x--- 2 root root 4096 Jan  1 23:02 BACKUP-222
drwxr-x--- 2 root root 4096 Dec 31 23:02 BACKUP-221
drwxr-x--- 2 root root 4096 Dec 30 23:02 BACKUP-220
drwxr-x--- 2 root root 4096 Dec 29 23:02 BACKUP-219
drwxr-x--- 2 root root 4096 Dec 28 23:02 BACKUP-218
drwxr-x--- 2 root root 4096 Dec 27 23:02 BACKUP-217
drwxr-x--- 2 root root 4096 Dec  1 23:02 BACKUP-191
drwxr-x--- 2 root root 4096 Nov  1 00:03 BACKUP-160
drwxr-x--- 2 root root 4096 Oct  1 00:02 BACKUP-129
drwxr-x--- 2 root root 4096 Sep  1 00:03 BACKUP-99
drwxr-x--- 2 root root 4096 Jul  1  2013 BACKUP-33
 
digitalpoint said:
One of the (many) nice things about MySQL Cluster is it supports hot backups without anything complicated.
Click to expand...
Remember the discussion we had on Skype? Google (and Red Hat 7) moved to MariaDB...
MySQL Cluster is a great example of NOT being a drop-in replacement technology.
 
Last edited:
Well it depends on what your definition of a drop-in replacement is... :) If you are talking about data files being usable in various MySQL types/forks, it works fine if you are using storage engines supported in both (like InnoDB).
Code: 
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | YES     | Clustered, fault-tolerant tables                               | YES          | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ndbinfo            | YES     | MySQL Cluster system information storage engine                | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

Of course if you aren't using ndbcluster storage engine, there's no reason to use MySQL Cluster.

But *if* you already went through the complexities of fully setting up MySQL Cluster, I was just saying that a non-blocking hot backup function is built into it, that's all. :)
 
This old school .sh script has been good to me:

Code: 
# modify the following to suit your environment
export DB_BACKUP="/backups/"
export DB_USER=""
export DB_PASSWD=""
export DB_TABLE=""

export FTP_USER=""
export FTP_PASS=""
export FTP_SERVER=""
export FTP_DIR=""


# title and version
echo ""
echo "mySQL_backup"
echo "----------------------"
echo "* Rotating backups..."
rm -rf $DB_BACKUP/04
mv $DB_BACKUP/03 $DB_BACKUP/04
mv $DB_BACKUP/02 $DB_BACKUP/03
mv $DB_BACKUP/01 $DB_BACKUP/02
mkdir $DB_BACKUP/01

echo "* Creating new backup..."
mysqldump --opt -q --user=$DB_USER --password=$DB_PASSWD $DB_TABLE | gzip > $DB_BACKUP/01/mysql-forum-xen.sql.gz
echo "----------------------"
echo "Done"
echo "----------------------"
echo "Transferring backup file..."

# login to remote server
ftp -n -v $FTP_SERVER << EOF
binary
user $FTP_USER $FTP_PASS
prompt
cd $FTP_DIR
lcd $DB_BACKUP/01
prompt off
put *.sql.gz
quit
EOF

echo "Backup file has been transferred."
echo "Job completed."

exit 0

Then I just set a cron job to run it.
 
I only run hobby sites these days, so nothing major
I do a cpanel full backup download once a week to my main computer and a copy to my 2tb USB hard drive.
 
I need to develop a more thorough backup plan. Right now I'm using cPanel to compress the files and databases of each site into a tar.gz on the backup drive independent of the server's RAID array. Once every could weeks I'll FTP the backups to my home desktop, but I know at the very least I should setup rsync to transfer these to a VPS or storage service.

Taking it a step further, I've heard many good things about Percona XtraBackup, so I was thinking of using that to for the database backup, then using something like rsync/duplicity/backuppc/rdiff-backup to transfer both the files and the DB backup to a VPS. This seems to be a fairly popular option?

Beyond that, Liquid Web offers their Guardian service, based on R1Soft. I've heard good things about this as well, but it's over $100 per month, and I'm not sure what benefits I'd realize over either of the two solutions above. I also have the option of setting up a VPS in a master/slave configuration, but the only benefit of this seems to be reducing downtime, and I'm alright with being down for 24 hours in the event of a major issue. I learned elsewhere that replication is not a valid backup plan :)

I appreciate any input!
 
0ptima said:
Cool. Have you ever restored from Glacier?
Click to expand...

I have not had to grab anything off of Glacier though I know a few that have. Its best not to put anything on there that you need with any quickness. I know some people use it to store original copies of upload videos so that they can go back and recompress them later to different formats. I basically just use it as a time capsule of my forums at this point.
 
Joe Link said:
I need to develop a more thorough backup plan. Right now I'm using cPanel to compress the files and databases of each site into a tar.gz on the backup drive independent of the server's RAID array. Once every could weeks I'll FTP the backups to my home desktop, but I know at the very least I should setup rsync to transfer these to a VPS or storage service.

Taking it a step further, I've heard many good things about Percona XtraBackup, so I was thinking of using that to for the database backup, then using something like rsync/duplicity/backuppc/rdiff-backup to transfer both the files and the DB backup to a VPS. This seems to be a fairly popular option?

Beyond that, Liquid Web offers their Guardian service, based on R1Soft. I've heard good things about this as well, but it's over $100 per month, and I'm not sure what benefits I'd realize over either of the two solutions above. I also have the option of setting up a VPS in a master/slave configuration, but the only benefit of this seems to be reducing downtime, and I'm alright with being down for 24 hours in the event of a major issue. I learned elsewhere that replication is not a valid backup plan :)

I appreciate any input!
Click to expand...

There's nothing wrong with your current method...you just need more storage locations. Relying on your webhost and a single backup at your home isn't necessarily the safest. Said you said you're using cPanel, it has built-in methods for transferring backups off-server via FTP and such. It's incredibly easy to set up. You just need to find some storage space to do it.
 
WSWD said:
There's nothing wrong with your current method...you just need more storage locations. Relying on your webhost and a single backup at your home isn't necessarily the safest. Said you said you're using cPanel, it has built-in methods for transferring backups off-server via FTP and such. It's incredibly easy to set up. You just need to find some storage space to do it.
Click to expand...

I think either a VPS or glacier setup would be best. The benefit of a VPS, as I understand it, is that I can set it up to pull the backup rather than have the main server push it, providing a bit more security?

Are you guys doing full, differential, or incremental backups?

Of the four utilities mentioned in this thread (rsync/duplicity/backuppc/rdiff-backup), are any of them better than the other, or is it really a matter of personal preference?
 
I use rsync to push my backup to my backup server and then use my NAS to rsync pull my backup from the backup server. I always have a full backup, but with rsync just the deltas are transferred/deleted.

My NAS also rotates a weeks worth of backups and tests the DB integrity on a daily basis.
 
You must log in or register to reply here.

Similar threads

P
No easy backup solution for xenforo yet?
Replies
2
Views
119
Mendalla
Mendalla
Fastline
  • Question Question
XF 2.0 Backup solution for XenForo 2
Replies
11
Views
5K
Fastline
Fastline
L
What's your backup setup?
Replies
0
Views
385
lostincable
L
K
easiest backup solution?
Replies
3
Views
838
Moshe1010
Moshe1010
Donny
Whats your moderator per registered user ratio on average?
Replies
6
Views
2K
Mr Lucky
Mr Lucky
Back
Top Bottom