-
Holland Backup Manager
Part 1 - Installing Holland Backup ManagerI spoke at Percona Live Conference and Expo 2013 about backups. Part of the talk focussed on the backup products in the ecosystem that will help you make a backup of your MySQL data. This follow-up article touches on one of the frameworks from my talk, the Holland Backup Manager. I was able to have a chat with some of the guys on the Rackspace booth about Holland and had some questions regarding features answered. Holland is a backup framework focussing mostly on MySQL backups but it is pluggable so you can write add backup providers to extend it to your own needs. Using the framework you're able to configure and deploy backup jobs of varying scope to multiple machines. The framework which was originally developed at Rackspace, currently supports mysqldump, lvm, xtrabackup and pgdump (Postgres) also sqllite. The latest version is number 1.0.8 which brings a few nice new features with it. I'll talk about the most significant of these in the third of the series. I want to show some installation, configuration and then use of the framework. I'm a fan of this project and if you've got the need to standardise whilst being flexible then Holland could be the solution you've been looking for. Firstly, head over to github to grab the files. https://github.com/holland-backup/holland I've been using vagrant to provide a quick VM environment. This OS I'm using here is a CentOS 6.4 64bit host running in Virtualbox. All dependencies for Holland should be available on the popular package managers but if you're using something other then debian (apt-get) or redhat based (yum) then you might require some research elsewhere to meet the requirements of Holland. Download the zip archive from github (the extension was lost in wget but it is a .zip file) [vagrant@node1 ~]$ wget https://github.com/holland-backup/holland/archive/master.zip ... Saving to: `master' 100%[==================================================>] 602,186 572K/s in 1.0s (572 KB/s) - `master' saved [602186/602186] So then proceed to unzip your archive [vagrant@node1 ~]$ unzip ./master Archive: ./master 832eaa6a0fff56a3009cdde22eda4746203c97f6 creating: holland-master/ inflating: holland-master/CHANGES.txt inflating: holland-master/INSTALL inflating: holland-master/LICENSE inflating: holland-master/README creating: holland-master/config/ inflating: holland-master/config/README... ProTip: Take some time to go through the various Holland directories. Throughout the directories there README and INSTALL files that will explain more about the accompanying files. [vagrant@node1 holland-master]$ ls -ltotal 56-rw-rw-r-- 1 vagrant vagrant 7064 May 13 17:45 CHANGES.txtdrwxrwxr-x 4 vagrant vagrant 4096 May 13 17:45 configdrwxrwxr-x 3 vagrant vagrant 4096 May 13 17:45 contribdrwxrwxr-x 4 vagrant vagrant 4096 May 13 17:45 docsdrwxrwxr-x 7 vagrant vagrant 4096 May 13 17:45 holland-rw-rw-r-- 1 vagrant vagrant 1782 May 13 17:45 INSTALL-rw-rw-r-- 1 vagrant vagrant 3290 May 13 17:45 LICENSEdrwxrwxr-x 13 vagrant vagrant 4096 May 13 17:45 plugins-rw-rw-r-- 1 vagrant vagrant 514 May 13 17:45 README-rw-rw-r-- 1 vagrant vagrant 62 May 13 17:45 setup.cfg-rw-rw-r-- 1 vagrant vagrant 1441 May 13 17:45 setup.pydrwxrwxr-x 2 vagrant vagrant 4096 May 13 17:45 tests-rwxr-xr-x 1 vagrant vagrant 1782 May 13 17:45 tomsay.pyBefore going any further lets take care of the dependencies. They're easily met using yum/apt or if you want to compile them yourself then go right ahead; - python-setuptools - mysqldb (python's mysql connector)Now, lets take care of some directories that need to exists. These are configurable later, but for simplicity sake we'll roll forward with the Holland defaults. Your environment is likely to be different especially if you're mounting remote storage to store the backup files you make. [vagrant@node1 config]$ sudo mkdir -p /etc/holland [vagrant@node1 plugins]$ sudo mkdir -p /var/log/holland [vagrant@node1 plugins]$ sudo mkdir -p /var/spool/holland Now the directories we've created will only be writable to root since we needed elevated privileges to make them. You might choose to chmod the directories if you're going to use a specific backup user to run your backup jobs. Now we're ready to run the python setup file from the root of the unarchived directory [vagrant@node1 holland-master]$ sudo python ./setup.py installrunning installrunning bdist_eggrunning egg_infocreating holland.egg-infowriting holland.egg-info/PKG-INFO...Installing holland script to /usr/binInstalled /usr/lib/python2.6/site-packages/holland-1.0.8-py2.6.eggProcessing dependencies for holland==1.0.8Finished processing dependencies for holland==1.0.8Without any errors in the previous output it's safe to assume we're installed. [vagrant@node1 config]$ holland --versionHolland Backup v1.0.8Copyright (c) 2008-2010 Rackspace US, Inc.More info available at http://hollandbackup.orgLets review what is in the plugin directory. These are the providers. Some are core libraries for the use of particular providers. There's also an example and random directory which we will reference in the next article on config. [vagrant@node1 plugins]$ ls -w1ACTIVEholland.backup.exampleholland.backup.mysqldumpholland.backup.mysqlhotcopyholland.backup.mysql_lvmholland.backup.pgdumpholland.backup.randomholland.backup.sqliteholland.backup.xtrabackupholland.lib.commonholland.lib.lvmholland.lib.mysqlREADME There are a couple of essential installs we need to take care of before we're ready to tackle the plugins for the backup tools. These are the holland.lib.common & holland.lib.mysql. For each you need to run the setup.py file from within the directories. The output should look something like the following. [vagrant@node1]$ cd holland.lib.common/[vagrant@node1 holland.lib.common]$ sudo python setup.py installrunning install...Adding holland.lib.common 1.0.8dev to easy-install.pth fileInstalled /usr/lib/python2.6/site-packages/holland.lib.common-1.0.8dev-py2.6.eggProcessing dependencies for holland.lib.common==1.0.8devFinished processing dependencies for holland.lib.common==1.0.8dev[vagrant@node1 holland.lib.common]$ cd ../holland.lib.mysql/[vagrant@node1 holland.lib.mysql]$ sudo python setup.py installrunning install...Adding holland.lib.mysql 1.0.8dev to easy-install.pth fileInstalled /usr/lib/python2.6/site-packages/holland.lib.mysql-1.0.8dev-py2.6.eggProcessing dependencies for holland.lib.mysql==1.0.8devFinished processing dependencies for holland.lib.mysql==1.0.8devNow, I'm mainly interested in mysqldump and xtrabackup but you might want to use the mysql_lvm. I don't think there are many takers for mysqlhotcopy these days. The same steps apply for each provider you want to install on each machine. [vagrant@node1 holland.backup.mysqldump]$ sudo python setup.py install running install ... Adding holland.backup.mysqldump 1.0.8dev to easy-install.pth file Installed /usr/lib/python2.6/site-packages/holland.backup.mysqldump-1.0.8dev-py2.6.egg Processing dependencies for holland.backup.mysqldump==1.0.8dev Finished processing dependencies for holland.backup.mysqldump==1.0.8dev and then finally from the xtrabackup directory; [vagrant@node1 plugins]$ cd holland.backup.xtrabackup/ [vagrant@node1 holland.backup.xtrabackup]$ sudo python setup.py install running install ... Adding holland.backup.xtrabackup 1.0.8dev to easy-install.pth file Installed /usr/lib/python2.6/site-packages/holland.backup.xtrabackup-1.0.8dev-py2.6.egg Processing dependencies for holland.backup.xtrabackup==1.0.8dev Finished processing dependencies for holland.backup.xtrabackup==1.0.8dev So in summary it's the library files for the plugins; - holland.lib.common - holland.lib.mysql - holland.lib.lvm (depending if you're going to use it)In part two I'll be giving an overview of the config files and then part three will be some cookbook-like config for the various providers.
-
Percona Server for MySQL 5.5.31-30.3 now available
Percona Server for MySQL version 5.5.31-30.3Percona is glad to announce the release of Percona Server for MySQL 5.5.31-30.3 on May 24, 2013 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.31, including all the bug fixes in it, Percona Server 5.5.31-30.3 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.31-30.3 milestone at Launchpad.New Features:Percona Server has ported the Atomic write support for Fusion-io devices patch from MariaDB. This feature adds atomic write support for directFS filesystem on Fusion-io devices. This feature implementation is considered BETA quality.Percona Server has introduced innodb_read_views_memory and innodb_descriptors_memory status variables in the Extended Show Engine InnoDB Status to improve InnoDB memory usage diagnostics.Bugs Fixed:Fix for bug #1131187 introduced a regression that could cause a memory leak if query cache was used together with InnoDB. Bug fixed #1170103.Fixed the RPM packaging regression that was introduced with the fix for bug #710799. This regression caused mysql schema to be missing after the clean RPM installation. Bug fixed #1174426.Fixed the Percona-Server-shared-55 and Percona-XtraDB-Cluster-shared RPM package dependences. Bug fixed #1050654.Fixed the upstream bug #68999 which caused compiling Percona Server to fail on CentOS 5 and Debian squeeze due to older OpenSSL version. Bug fixed #1183610.If a slave was running with its binary log enabled and then restarted with the binary log disabled, Crash-Resistant Replication could overwrite the relay log info log with an incorrect position. Bug fixed #1092593.Fixed the CVE-2012-5615 vulnerability. This vulnerability would allow remote attacker to detect what user accounts exist on the server. This bug fix comes originally from MariaDB (see MDEV-3909). Bug fixed #1171941.Fixed the CVE-2012-5627 vulnerability, where an unprivileged MySQL account owner could perform brute-force password guessing attack on other accounts efficiently. This bug fix comes originally from MariaDB (see MDEV-3915). Bug fixed #1172090.mysql_set_permission was failing on Debian due to missing libdbd-mysql-perl package. Fixed by adding the package dependency. Bug fixed #1003776.Rebuilding Debian source package would fail because dpatch and automake were missing from build-dep. Bug fixed #1023575 (Stephan Adig).Backported the fix for the upstream bug #65077 from the MySQL 5.6 version, which removed MyISAM internal temporary table mutex contention. Bug fixed #1179978.Release notes for Percona Server for MySQL 5.5.31-30.3 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.The post Percona Server for MySQL 5.5.31-30.3 now available appeared first on MySQL Performance Blog.
-
Tungsten University: Load a Vertica data warehouse with MySQL data
Continuent Tungsten offers real-time replication from MySQL to a variety of DBMS types including Vertica. In this Tungsten University webcast we will show you the details of setting up MySQL-to-Vertica replication, including the following topics:
Introduction to Continuent Tungsten features for data warehouse loading
Installation for MySQL to Vertica replication
Best practices for applications:
-
Log Buffer #321, A Carnival of the Vanities for DBAs
Can’t find what you want or need? Do you have a tip or trick to share? Do you want to lament over a technical woe? If yes then blog and send it to us for the Log Buffer :), just like this Log Buffer contains tips, tricks and woes.
Oracle:
Fahd Mirza and Tanel Poder throw spotlight on the v$cell_thread_history view with respect to the Exadata.
Mark W. Farnham‘s rightsizing goes on with a roar, as he declares that he is pretty much a green sneaker, tree hugging conservationist.
I would like to use the Microsoft Query tool in Microsoft Excel to extract records from the ERP database. Charles Hooper answers.
Kevin continues with yet another post about Exadata. The time he discusses: Oracle Exadata Database Machine: Proving 160 Xeon E7 Cores Are As “Slow” As 128 Xeon E5 Cores?
Alberto does a post which is a brief discussion about the advantages of activating parallelism by altering the session environment instead of using the alternative ways (hints, DDL).
SQL Server:
Security Questions: Differences between “Grant” and “With Grant?”
Using Scoped Assignments To Show Calendar and Financial Year-To-Date Values In The Same Calculated Measure.
A cool post about Business Objects on Linux and SQL Server.
Is Your Code an English Garden or Ikebana?
The Journey is the Reward: Speaking for SQLPass, where do you start?
Resolving Error Connecting Report Builder to a PowerPivot Data Source.
MySQL:
Stewart Smith is sharing an old note on the Storage Engine API.
A good look at the Non-Deterministic Query in Replication Stream.
The MariaDB project is pleased to announce the immediate availability of the MariaDB 5.5.31.
Fernando had recently had to do some customer work involving the McAfee MySQL Audit Plugin.
There’s a big difference in how plugins are treated in MySQL and how they are treated in Drizzle.
-
ZFS on Linux and MySQL
I am currently working with a large customer and I am involved with servers located in two data centers, one with Solaris servers and the other one with Linux servers. The Solaris side is cleverly setup using zones and ZFS and this provides a very low virtualization overhead. I learned quite a lot about these technologies while looking at this, thanks to Corey Mosher.On the Linux side, we recently deployed a pair on servers for backup purpose, boxes with 64 300GB SAS drives, 3 raid controllers and 192GB of RAM. These servers will run a few slave instances each of production database servers and will perform the backups. The write load is not excessive so a single server can easily handle the write load of all the MySQL instances. The original idea was to configure them with raid-10 + LVM, making sure to stripe the LV when we need to and align the partition correctly.We got decent tpcc performance, nearly 37k NoTPM using 5.6.11 and xfs. Then, since ZFS on Linux is available and there is in house ZFS knowledge, we decided to reconfigure one of the server and give ZFS a try. So I trashed the raid-10 arrays, configure JBODs and gave all those drives to ZFS (30 mirrors + spares + OS partition mirror) and I limited the ARC size to 4GB. I don’t want to start a war but ZFS performance level was less than half of xfs for the tpcc test and that’s maybe just normal. We didn’t try too hard to get better performance because we already had more than enough for our purpose and some ZFS features are just too useful for backups (most apply also for btrfs). Let’s review them.SnapshotsZFS does snapshot, like LVM but… since it is a copy on write filesystem, the snapshots are free, no performance penalty. You can easily run a server with hundreds of snapshots. With LVM, your IO performance drops to 33% after the first snapshot so keeping a large number of snapshots running is simply not an option. With ZFS you can easily have:one snapshot per day for the last 30 daysone snapshot per hour for the last 2 daysone snapshot per 5min for the last 2 hoursand that will be perfectly fine. Since starting a snapshot take less than a second, you could even be more zealous. Pretty interesting to speed up point in time recovery when you dataset is 700GB. If you google a bit with “zfs snapshot script” you’ll many scripts ready for the task. Snapshots work best with InnoDB, with MyISAM you’ll have to start the snapshot while holding a “flush tables with read lock” and the flush operation will take some time to complete.CompressionZFS can compress data on the fly and it is surprisingly cheap. In fact the best tpcc results I got were when using compression. I still have to explain this, maybe it is related to better raid controller write cache use. Even the fairly slow gzip-1 mode works well. The tpcc database, which contains a lot of random data that doesn’t compress well showed a compression ration of 1.70 with gzip-1. Real data will compress much more. That gives us much more disk space than we expected so even more snapshots!IntegrityWith ZFS each record on disk has a checksum. If a cosmic ray flip a bit on a drive, instead of crashing InnoDB, it will be caught by ZFS and the data will be read from the other drive in the mirror.Better availability and disk usageOn purpose, I allocated mirror pairs using drives from different controllers. That way, if a controller dies, the storage will still be working. Also, instead of having 1 or 2 spare drives per controller, I have 2 for the whole setup. A small but yet interesting saving.All put together, ZFS on Linux is a very interesting solution for MySQL backup servers. All backup solutions have an impact on performance with ZFS the impact is up front and the backups are almost free.The post ZFS on Linux and MySQL appeared first on MySQL Performance Blog.
|
News Feeds 
