Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 211 Next 30 Older Entries

Displaying posts with tag: Backup (reset)

xtrabackup_51: not found & no ‘mysqld’ group in MySQL options
+0 Vote Up -0Vote Down
Recently I happen to setup a new MySQL instance with my tools – a standard MySQL 5.1+, xtrabackup setup and last-hotbackup.tar.gz. To restore from the backup we used xtrabackup binaries and ran into issues following standard commands (assuming no changes): To prepare the backup I used apply-log as follows: $] innobackupex-1.5.1 --defaults-file=/usr/local/mysql/data/backup-my.cnf --apply-log  /usr/local/mysql/data --ibbackup […]
mysqldump now safe for InnoDB!
Employee +6 Vote Up -0Vote Down

It's now safe to backup InnoDB with mysqldump

Before MySQL 5.6, running mysqldump to make backup of InnoDB tables could cause your backup to 'loose' some data. The problem is described in our manual  here.

In latest MySQL 5.6 this is no longer a problem, this means you no longer risk 'loosing' data when using mysqlbackup together with DDL statements on your InnoDB tables. If you are interested in metadata-locking (MDL) you can read more about MDL  here.

To test this we need to create a few tables and also look at which order mysqldump processes tables.

mysql> USE ted;
mysql> CREATE TABLE `a` (`i`

  [Read more...]
MEB : The journey so far 2010-2013
Employee +4 Vote Up -0Vote Down

MySQL Enterprise Backup (MEB) was born 3 years ago as a newly branded avatar of InnoDB Hot backup. Wanted to share what has gone on so far, how we at Oracle think about backup, the milestones that we have achieved and the road ahead. The idea for this blog came to me after looking at Mikael's latest blog. While Mikael talks about MySQL, I want to talk about MEB.

When we started with InnoDB Hot backup the first challenge was to have it adhere to the development, quality and release processes for MySQL. This meant creating a quality plan, getting it into the development trees of MySQL and ensuring that each piece of new code went through architecture and code review. Though the initial implementer and architect of Hot backup continues to work with the MEB team, there

  [Read more...]
Why use encrypted backup with Percona XtraBackup 2.1 for MySQL?
+0 Vote Up -0Vote Down

We just released our first alpha of Percona XtraBackup 2.1 for MySQL and with it we included the ability to encrypt backups on the fly (full documentation here). This feature is different than simply piping the backup stream through the openssl or gpg binaries, which is what some people have used in the past. A big benefit of using the built-in encryption is that multiple CPU cores can be used for encryption

  [Read more...]
MySQL 5.6 Replication with GTID – Global Transaction ID
+0 Vote Up -0Vote Down
Send to Kindle

Hi guys, Early February Oracle released the new version of MySQL named 5.6, one of the enhancements is the GTID (Global Transaction ID)

GTID is an unique identifier which will be added at each transaction, and will be very useful on the slave. remember before we needed to set MASTER_LOG_FILE and MASTER_LOG_POS, now we don’t need them anymore.

Let’s see some new variables which we need to add to our cnf file:
gtid-mode : It will enable GTID, in order to this function work, we need to turn on log-bin and log-slave-updates
enforce-gtid-consistency : It will guarantee that only allowed command will be executed ( more information

  [Read more...]
Truly Parallel backup (MySQL Enterprise Backup 3.8 and later)
Employee +0 Vote Up -0Vote Down
How do you implement a parallel algorithm for a software which needs to be streamed to tapes?
How do you ensure that you have the capability to be able to tune the level of parallelism for varying input and output devices and varying levels of load?
These were some of the questions that we needed to answer when we were trying to implement multi-threading capability for MySQL Enterprise Backup (MEB).
The trivial way of achieving parallelism is by having the multiple threads pick up the different files (in a file per table) scenario. But this did not seem adequate because:
a) The sizes of these files (corresponding to the tables) could be different and then one large file would limit the level of parallelism since it would be processed by a single thread.
b) If you have to stream the backup how do you reconcile these

  [Read more...]
MySQL Enterprise Backup 3.8.1 release for 5.6 Server
Employee +0 Vote Up -0Vote Down

The MySQL Enterprise Backup 3.8.1 release's main goal was support MySQL 5.6 server. But also beyond that primary goal MEB team added some valuable new options and features to ensure you'll get most from the new features in 5.6 as well. At a glance, here are some of the highlights,

MEB copy of InnoDB undo log tablespaces

MySQL 5.6 introduces a new feature to store undo logs in separate files called as undo tablespaces for improved performance. These undo tablespaces are logically part of system  tablespace. All the commands associated with MEB - "backup", "apply-log" and "copy-back"  now take care of the undo tablespaces in the same way as they process the system tablespace. MEB now supports innodb_undo_directory[logs][tablespace] option variables. When backup is executed, undo datafiles (up to number

  [Read more...]
Consistent backup – snapshot
+0 Vote Up -0Vote Down
Send to Kindle

Hi guys, today let’s learn about how to have a consistent backup (snapshot)
First of all, in what situations do we use a snapshot?

1. Lets say that your production server now will have a replica, how do you do the first load of data in this slave? what was the master bin log position when you started the backup, during the backup process, does anyone wrote any query to db?

2. In case you want to implement an incremental backup strategy, you can take a snapshot once a week and in case you need to restore you server, you just restore the snapshot and apply the binary logs.

Then, let’s start.

To grantee this data integrity we will need 2 sessions open on master, first one to lock all databases, second

  [Read more...]
Automating Backups
Employee +0 Vote Up -0Vote Down

If you need to automate backups, you might wonder about the different techniques available to you.

With regards to scheduling backups using built-in features of MySQL, you have two main options:

  • Either run mysqldump (or mysqlbackup if you have an Enterprise licence) from an operating system scheduler, for example in Linux using "cron" or in Windows using the "Task Scheduler". This is the most commonly used option.
  • Alternatively, use the Event Scheduler to perform a series of  SELECT ... INTO OUTFILE ... commands, one for each table you need to back up. This is a less commonly used option, but you might still find it useful.

Scheduling mysqlbackup with cron

mysqldump is a client program, so when you run it, you run it from a shell

  [Read more...]
How To Back Up MySQL Databases With mylvmbackup On Ubuntu 12.10
+1 Vote Up -0Vote Down

How To Back Up MySQL Databases With mylvmbackup On Ubuntu 12.10

mylvmbackup is a Perl script for quickly creating MySQL backups. It uses LVM's snapshot feature to do so. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, creates a snapshot of the volume containing the MySQL data directory, and unlocks the tables again. This article shows how to use it on an Ubuntu 12.10 server.

Excluding a Table From Backup
Employee +0 Vote Up -0Vote Down

Let's say you have a database that stores not only current transactional data, but also historic data that's unchanging. In particular, you have a large table containing hundreds of gigabytes worth of last year's data, and it won't change. Having backed it up already, you don't need to back it up every time. Is there any way to exclude this table from a backup?

For InnoDB tables with innodb-file-per-table enabled (the default as of MySQL 5.6), MySQL Enterprise Backup supports this feature in inverse. Specifically, you can choose to include specific innodb-file-per-table tables in addition to those stored in the system tablespace.

In order to exclude a specific table, you need to provide a regular expression to the --include option that includes every table except

  [Read more...]
Set Up Rsnapshot, Archiving Of Snapshots And Backup Of MySQL Databases On Debian
+0 Vote Up -0Vote Down

Set Up Rsnapshot, Archiving Of Snapshots And Backup Of MySQL Databases On Debian

This howto will show you how to install and set up Rsnapshot, enable archiving of snapshots and how to back up MySQL databases on Debian.

Is your backup complete? – backup MySQL users privileges / grants – pt-show-grants
+1 Vote Up -0Vote Down
Everybody knows the criticality of backup. You might have had your database dumped, data directory copied, flat files exported and even config and binary log files backed-up. But did you backup your MySQL user privileges? One can easily forget to backup the user privileges but it is much easier to do and you should backup […]
MySQL Connect Conference: My Experience
Employee +0 Vote Up -0Vote Down
It was a great experience to attend the MySQL Connect Conference for the first time ever. Personally I was very much enthralled to present about "How to make MySQL Backups" besides attending different sessions to absorb more knowledge about the technical prospects of MySQL. One of the agenda items in my presentation was "MySQL Enterprise Backup" functionality and features. There were total of 40 attendees in the session, who were very much interested about the MySQL Enterprise Backup product and gave positive feedback as well as areas of improvements on our product. Some of our features brought lot of  [Read more...]
MEB Support to NetBackup MMS
Employee +0 Vote Up -0Vote Down

In MySQL Enterprise Backup 3.6, new option was introduced to support backup to tapes via SBT interface. SBT stands for System Backup to Tape, an Oracle API that helps to perform backup and restore jobs via media management software such as Oracle's Secure Backup (OSB). There are other storage managers like IBM's Tivoli Storage Manager (TSM) and Symantec's Netbackup (NB) which are also supported by MEB but we don't guarantee that it will function as expected for every release. MEB supports SBT API version 2.0

In this blog, I am primarily going to focus the interface of MEB and Symantec's NB. If we are using tapes for backup, ensure that tape library and tape drives are compatible.

Test Setup

1. Install NB 7.5 master and media servers in Linux OS. ( NB 7.1 can also be used but for testing purpose I used NB

  [Read more...]
MySQL on S3: security and backups
+1 Vote Up -0Vote Down

I got a few questions like the ones below that I’d like to address to avoid further confusion.
How exactly secure is ClouSE for MySQL, the first secure database in the cloud? Am I protected against standard application level security attacks or even accidental admin mistakes?
With the help of ClouSE I get instantaneous backup for my database on the highly durable cloud storage. But how would I protect my data in case a malicious attack or an accident did occur?

Re: security

I’ve got a comment pointing out that data encryption on the storage level doesn’t protect from SQL injections.  Of course, data encryption does not protect from SQL injections (as long as there is SQL involved, there will be a risk of a SQL

  [Read more...]
5 Things You Overlooked with MySQL Dumps
+1 Vote Up -1Vote Down

Read the original article at 5 Things You Overlooked with MySQL Dumps

1. Point In Time Recovery If you’ve never done point in time recovery, it’s time to take a second look. With a standard mysqldump you restore your database to the time when the backup happened. Only do them once a day, then you can lose as much as 24 hours of data. Enter point-in-time recovery, [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:
  • Ten things to remember about MySQL backups
  • Easy MySQL replication with hotbackups
  •   [Read more...]
    What compression do you use?
    +4 Vote Up -0Vote Down

    The following is an evaluation of various compression utilities that I tested when reviewing the various options for MySQL backup strategies. The overall winner in performance was pigz, a parallel implementation of gzip. If you use gzip today as most organizations do, this one change will improve your backup compression times.

    Details of the test:

    • The database is 5.4GB of data
    • mysqldump produces a backup file of 2.9GB
    • The server is an AWS t1.xlarge with a dedicated EBS volume for backups

    The following testing was performed to compare the time and % compression savings of various available open source products. This was not an exhaustive test with multiple iterations and different types of data files.

    Utility Compression Time
    (sec) Decompression Time
    (sec) New Size
    (% Saving) lzo

      [Read more...]
    A new MySQL backups temperature scale, with showers
    +0 Vote Up -0Vote Down

    We're used to Cold, Warm and Hot backups. This scale of three temperatures does not quite reflect the impact of backups on your MySQL database.

    In this post I offer a new backup temperature scale, and (somewhat seriously) compare it with showers. Call it the backup shower scale.

    A database backup is like a shower: the colder it is, the less time you want to spend doing it.


    A cold backup requires taking your database down (i.e. stop the service).

    Example: file system copy

    This can work well for replicating slaves, which may not be required for normal operation. You take the slave down, turn off the service, make your backup, turn everything on again, and let the backup catch up. Just make sure its master has all the necessary binary logs.

    A friend was staying at my place and

      [Read more...]
    MEB: Taking Incremental Backup using last successful backup
    Employee_Team +3 Vote Up -0Vote Down


    In MySQL Enterprise Backup v3.7.0 (MEB 3.7.0) a new option '–incremental-base' was introduced. Using this option a user can take in incremental backup without specifying the '–start-lsn' option. Description of this option can be found here. Instead of '–start-lsn' the user can provide the

      [Read more...]
    An elaborate way to break a MySQL server with XtraBackup
    +2 Vote Up -0Vote Down

    XtraBackup is a great piece of software from Percona, which allows creating (nearly) lock-less MySQL/InnoDB backups. The tool has been around for quite some time and recently even received a major version bump. I have relied on it many times over the years. As it turns out, using it in some configurations may lead to heavy swapping or prevent MySQL from running queries.

    So far I only kept complaining about the wrapper script XtraBackup has been distributed with and which was taken from Oracle’s InnoDB Hot Backup. The infamous innobackupex-1.5.1 was neither well written, nor was it even fully compatible with the XtraBackup’s feature set. This sometimes led to weird problems where there should not be any.

    This time the problem can appear elsewhere. Mostly when one using the tool does not understand how it works in

      [Read more...]
    WordPress on S3: no more backups
    +2 Vote Up -1Vote Down

    WordPress on S3: no more backups

    How much trouble will it be if your webserver failed?  No trouble at all, if your website keeps its content on reliable Amazon S3 storage.

    There are a lot of nuances in ensuring proper backups and restores of websites. When was the last backup taken? How much data might have been lost? How long will it take to recover it? When was the last time you tested restore? Do you even have an offsite backup?

    Now that you can run dynamic websites off Amazon S3 storage, we’ll demonstrate why you no longer need to worry about backing up and restoring your website data. Losing the webserver is no longer a disaster. Cloud storage offers almost unsurpassable reliability a lot of website owners (small & large) would benefit from. In a way you get an

      [Read more...]
    How To Back Up MySQL Databases With mylvmbackup On Debian Squeeze
    +0 Vote Up -0Vote Down

    How To Back Up MySQL Databases With mylvmbackup On Debian Squeeze

    mylvmbackup is a Perl script for quickly creating MySQL backups. It uses LVM's snapshot feature to do so. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, creates a snapshot of the volume containing the MySQL data directory, and unlocks the tables again. This article shows how to use it on a Debian Squeeze server.

    How to exclude a database from your dump with ZRM (MySQL Community help needed)
    +1 Vote Up -0Vote Down

    Last month, Ronald Bradford, Giuseppe Maxia and Mark Leith spoke about how to simulate a mysqldump –ignore-database.
    This mysqldump option doesn’t exist and these three guys have given us various helpful solutions.

    But for those of us who use ZRM community to make backup with mysqldump, the –exclude-pattern seems to do the job :

    --exclude-pattern "pattern" 
    All databases or tables that match the pattern are not backed up. 
    If --all-databases or --databases are

      [Read more...]
    Managing MySQL Backups
    Employee_Team +2 Vote Up -0Vote Down

    Database backups are typically critical to organizations, and are an important part of an overall disaster recovery strategy.

    MySQL Enterprise Backup performs online "Hot", non-blocking backups of your MySQL databases, and interfaces with media management software such as Symantec NetBackup, Oracle Secure Backup and IBM Tivoli Storage Manager to execute backup and restore operations.

    Two new white papers are available to help you better understand:

    • How to Connect MySQL Enterprise Backup with Media Management systems through System Backup to Tape (SBT) interface. (http://www.mysql.com/why-mysql/white-papers/mysql_wp_meb_sbt.php)
    • How to specifically use MySQL Enterprise Backup with Symantec NetBackup. (http://www.mysql.com/why-mysql/white-papers/mysql_wp_meb_sbt.php)

    Enjoy the white papers.

    Improved script for extracting table from MySQL text dump
    +1 Vote Up -0Vote Down

    A few days ago I showed a quick way to extract one table form a mysqldump output. Here is a more complete version which supports extracting either a full schema or a table from the specified schema. If full schema is being extracted, the script also looks for any associated views and routines.

    Usage is simple:
    garfield ~ # ./extract.sh -f dump.sql -d redmine > redmine.sql
    garfield ~ # ./extract.sh -f dump.sql -d redmine -t workflows > redmine-workflows.sql

    You can grab the script from Downloads page.


    # Extracts a schema or a table from a text dump generated by mysqldump or phpMyAdmin
    # (c) 2012 Maciej Dobrzanski http://www.dbasquare.com/
    # Released under GNU General Public License, version 2

      [Read more...]
    Implementing mysqldump –ignore-database
    Employee +4 Vote Up -0Vote Down

    Ronald Bradford and Giuseppe Maxia (hey guys!) wrote about different ways to ignore a database when using mysqldump –all-databases over the past couple of days.

    Whilst the solutions are interesting, I wondered why not attack it from the proper approach, and add the option to mysqldump itself? Honestly, the patch is trivial, and doing anything against INFORMATION_SCHEMA with lots of databases and tables … well let’s just say … group_concat_max_len is the least of your worries..

    15 minutes later I had a working solution:

    To my surprise, I also

      [Read more...]
    Extracting one table from mysqldump or phpMyAdmin backup
    +1 Vote Up -0Vote Down

    Using mysqldump is a quick way to do backups, although usually limited to only smaller databases – perhaps up to a few gigabytes large. It is still a fairly popular solution as majority of databases aren’t even that big. Also phpMyAdmin provides a variant of mysqldump format through its Export function. Everything works well for plain dump and restore, but in certain situations it is necessary to restore only a single table. With all data being in a single text file, it may not be a trivial task. Here is how I deal with the problem.

    Rather than editing out parts of the file that I don’t need, I wrote simple one-liners that do that for me. These are not perfect as for example they can’t deal with multiple tables by the same name existing in several different schemas, but that so far

      [Read more...]
    A few hacks to simulate mysqldump --ignore-database
    +4 Vote Up -0Vote Down

    A few days ago, Ronald Bradford asked for a mysqldump –ignore-database option.

    As a workaround, he proposes:
    mysqldump --databases `mysql --skip-column-names \
    -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') \
    FROM information_schema.schemata WHERE schema_name \
    NOT IN ('mysql','performance_schema','information_schema');" \
    >` >/mysql/backup/rds2.sql

    It's a clever solution, but unfortunately it only works if you have a handful of schemas. If your databases happens to have several dozens (or hundreds or thousands) of schemas (which is where you need this option more), then the output will be truncated to the length of group_concat_max_len (by default, 1024.)

    There are two

      [Read more...]
    Rename a MySQL database schema safely
    +0 Vote Up -0Vote Down
    This example uses a backup to rename a database in the MySQL instance.
    Previous 30 Newer Entries Showing entries 31 to 60 of 211 Next 30 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.